MassBot1: Created page with "=Introduction to ProC - Embedded SQL ----= Overview * Pro*C Syntax ** yurttas/..."

2013-11-05T05:35:33Z

Created page with "=Introduction to Pro*C - Embedded SQL ----= * Overview * Pro*C Syntax ** yurttas/..."

New page

=Introduction to Pro*C - Embedded SQL ----=

* [[yurttas/PL/DBL/oracle/F/R/#overview|Overview]]
* [[yurttas/PL/DBL/oracle/F/R/#pro*c syntax|Pro*C Syntax]]
** [[yurttas/PL/DBL/oracle/F/R/#sql|SQL]]
** [[yurttas/PL/DBL/oracle/F/R/#preprocessor directives|Preprocessor Directives]]
** [[yurttas/PL/DBL/oracle/F/R/#statement labels|Statement Labels]]
* [[yurttas/PL/DBL/oracle/F/R/#host variables|Host Variables]]
** [[yurttas/PL/DBL/oracle/F/R/#basics|Basics]]
** [[yurttas/PL/DBL/oracle/F/R/#pointers|Pointers]]
** [[yurttas/PL/DBL/oracle/F/R/#structures|Structures]]
** [[yurttas/PL/DBL/oracle/F/R/#arrays|Arrays]]
** [[yurttas/PL/DBL/oracle/F/R/#indicator variables|Indicator Variables]]
** [[yurttas/PL/DBL/oracle/F/R/#datatype equivalencing|Datatype Equivalencing]]
* [[yurttas/PL/DBL/oracle/F/R/#error handling|Error Handling]]
** [[yurttas/PL/DBL/oracle/F/R/#sqlca|SQLCA]]
** [[yurttas/PL/DBL/oracle/F/R/#whenever statement|WHENEVER Statement]]
* [[yurttas/PL/DBL/oracle/F/R/#demo programs|Demo Programs]]
* [[yurttas/PL/DBL/oracle/F/R/#c++ users|C++ Users]]
* [[yurttas/PL/DBL/oracle/F/R/#list of embedded sql statements supported by pro*c|List of Embedded SQL Statements Supported by Pro*C]]

===---- Overview===

Embedded SQL is a method of combining the computing power of a high-level language like C/C++ and the database manipulation capabilities of SQL. It allows you to execute any SQL statement from an application program. Oracle's embedded SQL environment is called Pro*C.

A Pro*C program is compiled in two steps. First, the Pro*C ''precompiler'' recognizes the SQL statements embedded in the program, and replaces them with appropriate calls to the functions in the SQL runtime library. The output is pure C/C++ code with all the pure C/C++ portions intact. Then, a regular C/C++ compiler is used to compile the code and produces the executable. For details, see the section on [[yurttas/PL/DBL/oracle/F/R/#demo programs|Demo Programs]].

===---- Pro*C Syntax===

====---- SQL====

All SQL statements need to start with <tt>EXEC SQL</tt> and end with a semicolon "<tt>;</tt>". You can place the SQL statements anywhere within a C/C++ block, with the restriction that the declarative statements do not come after the executable statements. As an example:

 {
 int a;
 /* ... */
 EXEC SQL SELECT salary INTO :a
 FROM Employee
 WHERE SSN=876543210;
 /* ... */
 printf("The salary is %d\n", a);
 /* ... */
 }

====---- Preprocessor Directives====

The C/C++ preprocessor directives that work with Pro*C are <tt>#include</tt> and <tt>#if</tt>. Pro*C does not recognize <tt>#define</tt>. For example, the following code is invalid:

<tt> #define THE_SSN 876543210
 /* ... */
 EXEC SQL SELECT salary INTO :a
 FROM Employee
 WHERE SSN = THE_SSN; /* INVALID */</tt>

====---- Statement Labels====

You can connect C/C++ labels with SQL as in:

<tt> EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;
 /* ...</tt> */
<tt>error_in_SQL:
 /* do error handling */</tt>

We will come to what <tt>WHENEVER</tt> means later in the section on [[yurttas/PL/DBL/oracle/F/R/#error handling|Error Handling]].

===---- Host Variables===

====---- Basics====

Host variables are the key to the communication between the host program and the database. A host variable expression must resolve to an ''lvalue'' (''i.e.'', it can be assigned). You can declare host variables according to C syntax, as you declare regular C variables. The host variable declarations can be placed wherever C variable declarations can be placed. (C++ users need to use a ''declare section''; see the section on [[yurttas/PL/DBL/oracle/F/R/#c++ users|C++ Users]].) The C datatypes that can be used with Oracle include:

* <tt>char</tt>
* <tt>char[</tt>''n''<tt>]</tt>
* <tt>int</tt>
* <tt>short</tt>
* <tt>long</tt>
* <tt>float</tt>
* <tt>double</tt>
* <tt>VARCHAR[</tt>''n''<tt>]</tt> - This is a psuedo-type recognized by the Pro*C precompiler. It is used to represent blank-padded, variable-length strings. Pro*C precompiler will convert it into a structure with a 2-byte length field and a ''n''-byte character array.

You cannot use <tt>register</tt>''' '''storage-class specifier for the host variables.

A host variable reference must be prefixed with a colon "<tt>:</tt>" in SQL statements, but should not be prefixed with a colon in C statements. C function calls and most of the pointer arithmetic expressions ''cannot'' be used as host variable references even though they may indeed resolve to lvalues. The following code illustrates both legal and illegal host variable references:

int deptnos[3] = { 000, 111, 222 };
int get_deptno() { return deptnos[2]; }
int *get_deptnoptr() { return &(deptnos[2]); }
int main() {
 int x; char *y; int z;
 /* ... */
 EXEC SQL INSERT INTO emp(empno, ename, deptno)
 VALUES(:x, :y, :z); /* LEGAL */
 EXEC SQL INSERT INTO emp(empno, ename, deptno)
 VALUES(:x + 1, /* LEGAL: the reference is to x */
 'Big Shot', /* LEGAL: but not really a host var */
 :deptnos[2]); /* LEGAL: array element is fine */
 EXEC SQL INSERT INTO emp(empno, ename, deptno)
 VALUES(:x, :y,
 :(*(deptnos+2))); /* ILLEGAL: although it is lvalue */
 EXEC SQL INSERT INTO emp(empno, ename, deptno)
 VALUES(:x, :y,
 :get_deptno()); /* ILLEGAL: no function calls */
 EXEC SQL INSERT INTO emp(empno, ename, deptno)
 VALUES(:x, :y,
 :(*get_depnoptr())); /* ILLEGAL: although it is lvalue */
 /* ... */
}

====---- Pointers====

You can define pointers using the regular C syntax, and use them in embedded SQL statements. As usual, prefix them with a colon:

 int *x;
 /* ... */
 EXEC SQL SELECT xyz INTO :x FROM ...;

The result of this <tt>SELECT</tt> statement will be written into <tt>*x</tt>, not <tt>x</tt>.

====---- Structures====

Structures can be used as host variables, as illustrated in the following example:

 typedef struct {
 char name[21]; /* one greater than column length; for '\0' */
 int SSN;
 } Emp;
 /* ... */
 Emp bigshot;
 /* ... */
 EXEC SQL INSERT INTO emp (ename, eSSN)
 VALUES (:bigshot);

====---- Arrays====

Host arrays can be used in the following way:

 int emp_number[50];
 char name[50][11];
 /* ... */
 EXEC SQL INSERT INTO emp(emp_number, name)
 VALUES (:emp_number, :emp_name);

which will insert all the 50 tuples in one go.

Arrays can only be single dimensional. The example <tt>char name[50][11]</tt> would seem to contradict that rule. However, Pro*C actually considers <tt>name</tt> a one-dimensional array of strings rather than a two-dimensional array of characters. You can also have arrays of structures.

When using arrays to store the results of a query, if the size of the host array (say ''n'') is smaller than the actual number of tuples returned by the query, then only the first ''n'' result tuples will be entered into the host array.

====---- Indicator Variables====

Indicator variables are essentially "<tt>NULL</tt> flags" attached to host variables. You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte integer (using the type <tt>short</tt>) and, in SQL statements, must be prefixed by a colon and immediately follow its host variable. Or, you may use the keyword <tt>INDICATOR</tt> in between the host variable and indicator variable. Here is an example:

 short indicator_var;
 EXEC SQL SELECT xyz INTO :host_var:indicator_var
 FROM ...;
 /* ... */
 EXEC SQL INSERT INTO R
 VALUES(:host_var INDICATOR :indicator_var, ...);

You can use indicator variables in the <tt>INTO</tt> clause of a <tt>SELECT</tt> statement to detect <tt>NULL</tt>'s or truncated values in the output host variables. The values Oracle can assign to an indicator variable have the following meanings: 

<center>

{| width="100%" border="BORDER"
| <tt>-1</tt>
| The column value is <tt>NULL</tt>, so the value of the host variable is indeterminate.
|-
| <tt>0</tt>
| Oracle assigned an intact column value to the host variable.
|-
| <tt>>0</tt>
| Oracle assigned a truncated column value to the host variable. The integer returned by the indicator variable is the original length of the column value.
|-
| <tt>-2</tt>
| Oracle assigned a truncated column variable to the host variable, but the original column value could not be determined.
|}

</center>

 You can also use indicator variables in the <tt>VALUES</tt> and <tt>SET</tt> clause of an <tt>INSERT</tt> or <tt>UPDATE</tt> statement to assign <tt>NULL</tt>'s to input host variables. The values your program can assign to an indicator variable have the following meanings: 

{| width="100%" border="BORDER"
| <tt>-1</tt>
| Oracle will assign a <tt>NULL</tt> to the column, ignoring the value of the host variable.
|-
| <tt>>=0</tt>
| Oracle will assign the value of the host variable to the column.
|}

====---- Datatype Equivalencing====

Oracle recognizes two kinds of datatypes: internal and external. ''Internal datatypes'' specify how Oracle stores column values in database tables. ''External datatypes'' specify the formats used to store values in input and output host variables. At precompile time, a default Oracle external datatype is assigned to each host variable. Datatype equivalencing allows you to override this default equivalencing and lets you control the way Oracle interprets the input data and formats the output data.

The equivalencing can be done on a variable-by-variable basis using the <tt>VAR</tt> statement. The syntax is:

 EXEC SQL VAR <host_var> IS <type_name> [ (<length>) ];

For example, suppose you want to select employee names from the <tt>emp</tt> table, and then pass them to a routine that expects C-style <tt>'\0'</tt>-terminated strings. You need not explicitly <tt>'\0'</tt>-terminate the names yourself. Simply equivalence a host variable to the <tt>STRING</tt> external datatype, as follows:

 char emp_name[21];
 EXEC SQL VAR emp_name IS STRING(21);

The length of the <tt>ename</tt> column in the <tt>emp</tt> table is 20 characters, so you allot <tt>emp_name</tt> 21 characters to accommodate the <tt>'\0'</tt>-terminator. <tt>STRING</tt> is an Oracle external datatype specifically designed to interface with C-style strings. When you select a value from the <tt>ename</tt> column into <tt>emp_name</tt>, Oracle will automatically <tt>'\0'</tt>-terminate the value for you.

You can also equivalence user-defined datatypes to Oracle external datatypes using the <tt>TYPE</tt> statement. The syntax is:

 EXEC SQL TYPE <user_type> IS <type_name> [ (<length>) ] [REFERENCE];

You can declare a user-defined type to be a pointer, either explicitly, as a pointer to a scalar or structure, or implicitly as an array, and then use this type in a <tt>TYPE</tt> statement. In these cases, you need to use the <tt>REFERENCE</tt> clause at the end of the statement, as shown below:

 typedef unsigned char *my_raw;
 EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE;
 my_raw buffer;
 /* ... */
 buffer = malloc(4004);

Here we allocated more memory than the type length (4000) because the precompiler also returns the length, and may add padding after the length in order to meet the alignment requirement on your system.

===---- Error Handling===

After each executable SQL statement, your program can find the status of execution either by explicit checking of SQLCA, or by implicit checking using the <tt>WHENEVER</tt> statement. These two ways are covered in details below.

====---- SQLCA====

SQLCA (SQL Communications Area) is used to detect errors and status changes in your program. This structure contains components that are filled in by Oracle at runtime after every executable SQL statement.

To use SQLCA you need to include the header file <tt>sqlca.h</tt> using the <tt>#include</tt> directive. In case you need to include <tt>sqlca.h</tt> at many places, you need to first undefine the macro <tt>SQLCA</tt> with <tt>#undef SQLCA</tt>. The relevant chunk of <tt>sqlca.h</tt> follows:

<nowiki>#ifndef SQLCA
#define SQLCA 1

struct sqlca {
 /* ub1 */ char sqlcaid[8];
 /* b4 */ long sqlabc;
 /* b4 */ long sqlcode;
 struct {
 /* ub2 */ unsigned short sqlerrml;
 /* ub1 */ char sqlerrmc[70];
 } sqlerrm;
 /* ub1 */ char sqlerrp[8];
 /* b4 */ long sqlerrd[6];
 /* ub1 */ char sqlwarn[8];
 /* ub1 */ char sqlext[8];
};
/* ... */</nowiki>

The fields in <tt>sqlca</tt> have the following meaning: 

<center>

{| width="100%" border="BORDER"
| valign="TOP" | <tt>sqlcaid</tt>
| This string component is initialized to "SQLCA" to identify the SQL Communications Area. 
|-
| valign="TOP" | <tt>sqlcabc</tt>
| This integer component holds the length, in bytes, of the SQLCA structure. 
|-
| valign="TOP" | <tt>sqlcode</tt>
|
This integer component holds the status code of the most recently executed SQL statement: <center>

{| width="90%" border="BORDER"
| <tt>0</tt>
| No error.
|-
| <tt>>0</tt>
| Statement executed but exception detected. This occurs when Oracle cannot find a row that meets your <tt>WHERE</tt> condition or when a <tt>SELECT INTO</tt> or <tt>FETCH</tt> returns no rows.
|-
| <tt><0</tt>
| Oracle did not execute the statement because of an error. When such errors occur, the current transaction should, in most cases, be rolled back.
|}

</center>
|-
| valign="TOP" | <tt>sqlerrm</tt>
|
This embedded structure contains the following two components:

* <tt>sqlerrml</tt> - Length of the message text stored in <tt>sqlerrmc</tt>.
* <tt>sqlerrmc</tt> - Up to 70 characters of the message text corresponding to the error code stored in <tt>sqlcode</tt>. 
|-
| valign="TOP" | <tt>sqlerrp</tt>
| Reserved for future use. 
|-
| valign="TOP" | <tt>sqlerrd</tt>
| valign="TOP" |
This array of binary integers has six elements:

* <tt>sqlerrd[0]</tt> - Future use.
* <tt>sqlerrd[1]</tt> - Future use.
* <tt>sqlerrd[2]</tt> - Numbers of rows processed by the most recent SQL statement.
* <tt>sqlerrd[3]</tt> - Future use.
* <tt>sqlerrd[4]</tt> - Offset that specifies the character position at which a parse error begins in the most recent SQL statement.
* <tt>sqlerrd[5]</tt> - Future use. 
|-
| valign="TOP" | <tt>sqlwarn</tt>
|
This array of single characters has eight elements used as warning flags. Oracle sets a flag by assigning to it the character <tt>'W'</tt>. <center>

{| width="90%" border="BORDER"
| <tt>sqlwarn[0]</tt>
| Set if any other flag is set.
|-
| <tt>sqlwarn[1]</tt>
| Set if a truncated column value was assigned to an output host variable.
|-
| <tt>sqlwarn[2]</tt>
| Set if a <tt>NULL</tt> column value is not used in computing a SQL aggregate such as <tt>AVG</tt> or <tt>SUM</tt>.
|-
| <tt>sqlwarn[3]</tt>
| Set if the number of columns in <tt>SELECT</tt> does not equal the number of host variables specified in <tt>INTO</tt>.
|-
| <tt>sqlwarn[4]</tt>
| Set if every row in a table was processed by an <tt>UPDATE</tt> or <tt>DELETE</tt> statement without a <tt>WHERE</tt> clause.
|-
| <tt>sqlwarn[5]</tt>
| Set if a procedure/function/package/package body creation command fails because of a PL/SQL compilation error.
|-
| <tt>sqlwarn[6]</tt>
| No longer in use.
|-
| <tt>sqlwarn[7]</tt>
| No longer in use.
|}

</center>
|-
| valign="TOP" | <tt>sqlext</tt>
| Reserved for future use. 
|}

</center>

''' ''' SQLCA can only accommodate error messages up to 70 characters long in its <tt>sqlerrm</tt> component. To get the full text of longer (or nested) error messages, you need the <tt>sqlglm()</tt> function:

 void sqlglm(char *msg_buf, size_t *buf_size, size_t *msg_length);

where <tt>msg_buf</tt> is the character buffer in which you want Oracle to store the error message; <tt>buf_size</tt> specifies the size of <tt>msg_buf</tt> in bytes; Oracle stores the actual length of the error message in <tt>*msg_length</tt>. The maximum length of an Oracle error message is 512 bytes.

====---- WHENEVER Statement====

This statement allows you to do automatic error checking and handling. The syntax is:

 EXEC SQL WHENEVER <condition> <action>;

Oracle automatically checks SQLCA for <tt><condition></tt>, and if such condition is detected, your program will automatically perform <tt><action></tt>.

<tt><condition></tt> can be any of the following:

* <tt>SQLWARNING</tt> - <tt>sqlwarn[0]</tt> is set because Oracle returned a warning
* <tt>SQLERROR</tt> - <tt>sqlcode</tt> is negative because Oracle returned an error
* <tt>NOT FOUND</tt> - <tt>sqlcode</tt> is positive because Oracle could not find a row that meets your <tt>WHERE</tt> condition, or a <tt>SELECT INTO</tt> or <tt>FETCH</tt> returned no rows

<tt><action></tt> can be any of the following:

* <tt>CONTINUE</tt> - Program will try to continue to run with the next statement if possible
* <tt>DO</tt> - Program transfers control to an error handling function
* <tt>GOTO <label></tt>'' ''- Program branches to a labeled statement
* <tt>STOP</tt> - Program exits with an <tt>exit()</tt> call, and uncommitted work is rolled back

Some examples of the <tt>WHENEVER</tt> statement:

 EXEC SQL WHENEVER SQLWARNING DO print_warning_msg();
 EXEC SQL WHENEVER NOT FOUND GOTO handle_empty;

Here is a more concrete example:

/* code to find student name given id */
/* ... */
for (;;) {
 printf("Give student id number : ");
 scanf("%d", &id);
 EXEC SQL WHENEVER NOT FOUND GOTO notfound;
 EXEC SQL SELECT studentname INTO :st_name
 FROM student
 WHERE studentid = :id;
 printf("Name of student is %s.\n", st_name);
 continue;
notfound:
 printf("No record exists for id %d!\n", id);
}
/* ... */

===---- Demo Programs===

'''Note:''' The demo programs will create and use four tables named <tt>DEPT</tt>, <tt>EMP</tt>, <tt>PAY1</tt>, and <tt>PAY2</tt>. Be careful if any table in your PDA happens to have the same name!

Several demo programs are available in <tt>/usr/class/cs145/code/proc</tt> on the leland system. They are named <tt>sample*.pc</tt> (for C users) and <tt>cppdemo*.pc</tt> (for C++ users). "<tt>.pc</tt>" is the extension for Pro*C code. Do ''not'' copy these files manually, since there are a couple of customizations to do. To download and customize the demo programs, follow the instructions below:

# Make sure that you have run <tt>source /usr/class/cs145/cora.env</tt>
# In your home directory, run <tt>load_samples <db_username> <db_passwd> <sample_dir></tt>, where <tt><sample_dir></tt> is the name of the directory where you wish to put demo programs (''e.g.'', <tt>load_samples sally etaoinshrdlu cs145_samples</tt>)
# <tt>cd <sample_dir></tt>
# Run <tt>make samples</tt> (or <tt>make cppsamples</tt> for C++) to compile all demo programs

Step (2) will set up the sample database, create a new directory as specified in <tt><sample_dir></tt>, and copy the demo files into that directory. It will also change the user name and password in the sample programs to be yours, so that you do not have to type in your username and password every time when running a sample program. However, <tt>sample1</tt> and <tt>cppdemo1</tt> do provide an interface for the user to input the username and password, in case you would like to learn how to do it.

If you happen to make any mistake when entering username or password in Step (2), just run <tt>clean_samples <db_username> <db_passwd> <sample_dir></tt> in your home directory, and then repeat Steps (2) to (4).

For Step (4), you can also compile each sample program separately. For example, <tt>make sample1</tt> compiles <tt>sample1.pc</tt> alone. The compilation process actually has two phases:

# <tt>proc iname=sample1.pc</tt> <tt>sample1.c</tt>
# <tt>cc <a_number_of_flags_here> sample1.c</tt> <tt>sample1</tt>

To compile your own code, say, <tt>foo.pc</tt>, just change a few variables in <tt>Makefile</tt>: Add the program name <tt>foo</tt> to variable <tt>SAMPLES</tt> and the source file name <tt>foo.pc</tt> to variable <tt>SAMPLE_SRC</tt>. Then, do <tt>make foo</tt> after <tt>foo.pc</tt> is ready. <tt>foo.pc</tt> will be precompiled to <tt>foo.c</tt> and then compiled to <tt>foo</tt>, the executable. C++ users will need to add their program name to <tt>CPPSAMPLES</tt> instead of <tt>SAMPLES</tt>, and source file name to <tt>CPPSAMPLE_SRC</tt> instead of <tt>SAMPLE_SRC</tt>.

The demo programs operate on the following tables:

CREATE TABLE DEPT
 (DEPTNO NUMBER(2) NOT NULL,
 DNAME VARCHAR2(14),
 LOC VARCHAR2(13));

CREATE TABLE EMP
 (EMPNO NUMBER(4) NOT NULL,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7, 2),
 COMM NUMBER(7, 2),
 DEPTNO NUMBER(2));

CREATE TABLE PAY1
 (ENAME VARCHAR2(10),
 SAL NUMBER(7, 2));

CREATE TABLE PAY2
 (ENAME VARCHAR2(10),
 SAL NUMBER(7, 2));

These tables are created automatically when you run <tt>load_samples</tt> in Step (2). A few tuples are also inserted. You may like to browse the tables before running the samples on them. You can also play with them as you like (''e.g.'', inserting, deleting, or updating tuples). These tables will be dropped automatically when you run <tt>clean_samples</tt>. '''Note:''' <tt>clean_samples</tt> also wipes out the entire <tt><sample_dir></tt>; make sure you move your own files to some other place before running this command!

You should take a look at the sample source code before running it. The comments at the top describe what the program does. For example, <tt>sample1</tt> takes an employee's <tt>EMPNO</tt> and retrieve the name, salary, and commission for that employee from the table <tt>EMP</tt>.

You are supposed to study the sample source code and learn the following:

* How to connect to Oracle from the host program
* How to embed SQL in C/C++
* How to use cursors
* How to use host variables to communicate with the database
* How to use <tt>WHENEVER</tt> to take different actions on error messages
* How to use indicator variables to detect <tt>NULL</tt>'s in the output

Now, you can use these techniques to code your own PDA interface program. And have fun!

===---- C++ Users===

To get the precompiler to generate appropriate C++ code, you need to be aware of the following issues:

* Code emission by precompiler. To get C++ code, you need to set the option <tt>CODE=CPP</tt> while executing <tt>proc</tt>. C users need not worry about this option; the default caters to their needs.

* Parsing capability. The <tt>PARSE</tt> option of <tt>proc</tt> may take the following values:

** <tt>PARSE=NONE</tt>. C preprocessor directives are understood only inside a declare section, and all host variables need to be declared inside a declare section.
** <tt>PARSE=PARTIAL</tt>. C preprocessor directives are understood; however, all host variables need to be declared inside a declare section.
** <tt>PARSE=FULL</tt>. C preprocessor directives are understood and host variables can be declared anywhere. This is the default when <tt>CODE</tt> is anything other than <tt>CPP</tt>; it is an error to specify <tt>PARSE=FULL</tt> with <tt>CODE=CPP</tt>.

<tt>PARSE=NONE</tt><tt>PARSE=PARTIAL</tt>''declare sections'' EXEC SQL BEGIN DECLARE SECTION;
 // declarations...
 EXEC SQL END DECLARE SECTION;

* File extension. You need to specify the option <tt>CPP_SUFFIX=cc</tt> or <tt>CPP_SUFFIX=C</tt>.

* Location of header files. By default, <tt>proc</tt> searches for header files like <tt>stdio.h</tt> in standard locations. However, C++ has its own header files, such as <tt>iostream.h</tt>, located elsewhere. So you need to use the <tt>SYS_INCLUDE</tt> option to specify the paths that <tt>proc</tt> should search for header files.

===---- List of Embedded SQL Statements Supported by Pro*C===

<center>

{| width="100%" border="BORDER"
| colspan="2" align="CENTER" | '''Declarative Statements'''
|-
| <tt>EXEC SQL ARRAYLEN</tt>
| To use host arrays with PL/SQL
|-
| <tt>EXEC SQL BEGIN DECLARE SECTION</tt> <tt>EXEC SQL END DECLARE SECTION</tt>
| To declare host variables
|-
| <tt>EXEC SQL DECLARE</tt>
| To name Oracle objects
|-
| <tt>EXEC SQL INCLUDE</tt>
| To copy in files
|-
| <tt>EXEC SQL TYPE</tt>
| To equivalence datatypes
|-
| <tt>EXEC SQL VAR</tt>
| To equivalence variables
|-
| <tt>EXEC SQL WHENEVER</tt>
| To handle runtime errors
|-
| colspan="2" align="CENTER" | '''Executable Statements'''
|-
| <tt>EXEC SQL ALLOCATE</tt>
| rowspan="14" | To define and control Oracle data
|-
| <tt>EXEC SQL ALTER</tt>
|-
| <tt>EXEC SQL ANALYZE</tt>
|-
| <tt>EXEC SQL AUDIT</tt>
|-
| <tt>EXEC SQL COMMENT</tt>
|-
| <tt>EXEC SQL CONNECT</tt>
|-
| <tt>EXEC SQL CREATE</tt>
|-
| <tt>EXEC SQL DROP</tt>
|-
| <tt>EXEC SQL GRANT</tt>
|-
| <tt>EXEC SQL NOAUDIT</tt>
|-
| <tt>EXEC SQL RENAME</tt>
|-
| <tt>EXEC SQL REVOKE</tt>
|-
| <tt>EXEC SQL TRUNCATE</tt>
|-
| <tt>EXEC SQL CLOSE</tt>
|-
| <tt>EXEC SQL DELETE</tt>
| rowspan="8" | To query and manipulate Oracle data
|-
| <tt>EXEC SQL EXPLAIN PLAN</tt>
|-
| <tt>EXEC SQL FETCH</tt>
|-
| <tt>EXEC SQL INSERT</tt>
|-
| <tt>EXEC SQL LOCK TABLE</tt>
|-
| <tt>EXEC SQL OPEN</tt>
|-
| <tt>EXEC SQL SELECT</tt>
|-
| <tt>EXEC SQL UPDATE</tt>
|-
| <tt>EXEC SQL COMMIT</tt>
| rowspan="4" | To process transactions
|-
| <tt>EXEC SQL ROLLBACK</tt>
|-
| <tt>EXEC SQL SAVEPOINT</tt>
|-
| <tt>EXEC SQL SET TRANSACTION</tt>
|-
| <tt>EXEC SQL DESCRIBE</tt>
| rowspan="3" | To use dynamic SQL
|-
| <tt>EXEC SQL EXECUTE</tt>
|-
| <tt>EXEC SQL PREPARE</tt>
|-
| <tt>EXEC SQL ALTER SESSION</tt>
| rowspan="2" | To control sessions
|-
| <tt>EXEC SQL SET ROLE</tt>
|-
| <tt>EXEC SQL EXECUTE</tt> <tt>END-EXEC</tt>
| To embed PL/SQL blocks
|}

</center>
----

This document is written originally for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.

Yurttas/PL/DBL/oracle/F/R/or-proc.html - Revision history

MassBot1: Created page with "=Introduction to Pro*C - Embedded SQL ----= * Overview * Pro*C Syntax ** yurttas/..."

MassBot1: Created page with "=Introduction to ProC - Embedded SQL ----= Overview * Pro*C Syntax ** yurttas/..."