Proc 194h72

#endif

Because ORA_PROC is defined during precompilation, the irrelevant.h file is never included. The ORA_PROC macro is available only for C preprocessor directives, such as #ifdef or #ifndef. The EXEC ORACLE conditional statements do not share the same the namespaces as the C preprocessor macros. Therefore, the condition in the following example does not use the predefined ORA_PROC macro: EXEC ORACLE IFNDEF ORA_PROC; <section of code to be ignored> EXEC ORACLE ENDIF;

ORA_PROC, in this case, must be set using either the DEFINE option or an EXEC ORACLE DEFINE statement for this conditional code fragment to work properly.

Specifying the Location of Header Files The Pro*C/C++ Precompiler for each port assumes a standard location for header files to be read by the preprocessor, such as sqlca.h, oraca.h, and sqlda.h. For example, on most UNIX ports, the standard location is $ORACLE_HOME/proc/lib. For the default location on your system, see your system-specific Oracle documentation. If header files that you need to include are not in the default location, you must use the INCLUDE= option, on the command line or as an EXEC ORACLE option. See Chapter 7 for more information about the precompiler options, and about the EXEC ORACLE options. To specify the location of system header files, such as stdio.h or iostream.h, where the location might be different from that hard-coded into Pro*C/C++, use the SYS_INCLUDE precompiler option. See Chapter 7 for more information.

Some Preprocessor Examples You can use the #define command to create named constants, and use them in place of ``magic numbers" in your source code. You can use #defined constants for declarations that the precompiler requires, such as VARCHAR[]. For example, instead of potentially buggy code like ... VARCHAR

emp_name[10];

VARCHAR

dept_loc[14];

...

http://docs.oracle.com/cd/A57673_01/DOC/api/doc/PC_22/ch03a.htm

31-08-2012

Developing a Pro*C/C++ Application

Page 3 of 55

... /* much later in the code ... */ f42() { /* did you the correct size? */ VARCHAR new_dept_loc[10]; ... }

you can code #define ENAME_LEN

10

#define LOCATION_LEN

14

VARCHAR

new_emp_name[ENAME_LEN];

... /* much later in the code ... */ f42() { VARCHAR new_dept_loc[LOCATION_LEN]; ... }

You can use preprocessor macros with arguments for objects that the precompiler must process, just as you can for C objects. For example: #define ENAME_LEN

10

#define LOCATION_LEN 14 #define MAX(A,B)

((A) > (B) ? (A) : (B))

... f43() { /* need to declare a temporary variable to hold either an employee name or a department location */ VARCHAR

name_loc_temp[MAX(ENAME_LEN, LOCATION_LEN)];

... }

You can use the #include, #ifdef and #endif preprocessor directives to conditionally include a file that the precompiler requires. For example: #ifdef ORACLE_MODE #

include <sqlca.h>

#else long SQLCODE; #endif

Using #define There are restrictions on the use of the #define preprocessor directive in Pro*C/C++. You cannot use the #define directive to create symbolic constants for use in executable SQL statements. The following invalid example demonstrates this: #define RESEARCH_DEPT

40

... EXEC SQL SELECT empno, sal INTO :emp_number, :salary /* host arrays */ FROM emp WHERE deptno = RESEARCH_DEPT;

/* INVALID! */


31-08-2012


Page 4 of 55

The only declarative SQL statements where you can legally use a #defined macro are TYPE and VAR statements. So, for example, the following uses of a macro are legal in Pro*C/C++: #define STR_LEN 40 ... typedef char asciiz[STR_LEN]; ... EXEC SQL TYPE asciiz IS STRING(STR_LEN) REFERENCE; ... EXEC SQL VAR IS STRING(STR_LEN) REFERENCE;

Other Preprocessor Restrictions You cannot use the preprocessor commands # and ## to create tokens that the precompiler must recognize. You can, of course, use these commands (if your C compiler's preprocessor s them) in pure C code, that the precompiler does not have to process. Using the preprocessor command ## is not valid in this example: #define MAKE_COL_NAME(A)

col ## A

... EXEC SQL SELECT MAKE_COL_NAME(1), MAKE_COL_NAME(2) INTO :x, :y FROM table1;

The example is invalid because the precompiler must be able to process the embedded SQL statement, and ## is not ed by the Pro*C/C++ preprocessor.

An Important Restriction Because of the way the Pro*C/C++ preprocessor handles the #include directive, as described in the previous section, you cannot use the #include directive to include files that contain embedded SQL statements. You use #include to include files that contain purely declarative statements and directives; for example, #defines, and declarations of variables and structures required by the precompiler, such as in sqlca.h.

Including the SQLCA, ORACA, and SQLDA You can include the sqlca.h, oraca.h, and sqlda.h declaration header files in your Pro*C/C++ program using either the Pro*C/C++ preprocessor #include command, or the EXEC SQL INCLUDE command. For example, you use the following statement to include the SQL Communications Area structure (SQLCA) in your program with the EXEC SQL option: EXEC SQL INCLUDE sqlca;

To include the SQLCA using the Pro*C/C++ preprocessor directive, add the following code: #include <sqlca.h>

When you use the preprocessor #include directive, you must specify the file extension (such as .h). Note: If you need to include the SQLCA in multiple places, using the #include directive, you should precede the #include with the directive #undef SQLCA. This is because sqlca.h starts with the lines #ifndef SQLCA #define SQLCA 1

and then declares the SQLCA struct only in the case that SQLCA is not defined. When you precompile a file that contains a #include directive or an EXEC SQL INCLUDE statement, you have to tell the precompiler the location of all files to be included. You can use the INCLUDE= option, either on the command line or in a system or configuration file. See Chapter 7 for more information about the INCLUDE precompiler option and configuration files. The default location for standard preprocessor include files, such as sqlca.h, oraca.h, and sqlda.h, is built into the precompiler. The location varies from system to system. See your system-specific Oracle documentation for the default location on your system. When you compile the .c output file that Pro*C/C++ generates, you must use the option provided by your compiler and operating system to identify the location of #include'd files. For example, on most UNIX systems, you can compile the generated C/C++ source file using the command cc -o progname -I$ORACLE_HOME/sqllib/public ... filename.c ...

On VAX/OPENVMS systems, you would prepend the include directory path to the value in the logical VAXC$INCLUDE.

EXEC SQL INCLUDE and #include Summary When you use an EXEC SQL INCLUDE statement in your program, the precompiler textually includes the source in the output (.c) file. So, you can have declarative and executable embedded SQL statements in a file that is included using EXEC SQL INCLUDE. When you include a file using #include, the precompiler merely reads the file, and keeps track of #defined macros. Warning: VARCHAR declarations and SQL statements are NOT allowed in #included files. For this reason, you cannot have SQL statements in files that are included using the Pro*C/C++ preprocessor #include directive.

Migrating an Application


31-08-2012


Page 5 of 55

Release 2.2 of the Pro*C/C++ Precompiler is compatible with earlier Pro*C and Pro*C/C++ releases. However, there are several things that you should consider when you migrate your application from Pro*C release 1.6, or an earlier Pro*C/C++ release, to Pro*C/C++ release 2.2. This section discusses some of these issues.

Character Strings Many applications have been written under the assumption that character strings are of varying length (such as VARCHAR2). By default, Oracle7 uses fixedlength character strings (CHAR), to conform to the current SQL standards. If your application assumes that character strings are varying in length (and this is especially important in the string comparison semantics), then you should precompile your application using the DBMS=V6 or DBMS=V6_CHAR options. DBMS=V6 provides Oracle V6 semantics in several areas, not just character string semantics. V6_CHAR gives you complete Oracle7 semantics except for character strings, which retain Oracle V6 semantics. Note: The DBMS option partially replaces the MODE option of the release 1.5 and 1.6 Pro*C Precompilers. See the description of the DBMS option

for a complete list of the effects of the DBMS=V6 and DBMS=V6_CHAR options.

Precompiler Options The following precompiler options, which are simply parsed and ignored, are not ed in Pro*C/C++ release 2.2: z

ASACC

z

IRECLEN

z

LRECLEN

z

ORECLEN

z

PAGELEN

z

TEST

z

XREF

The AREASIZE, REBIND, and REENTRANT options have not been ed since Pro*C release 1.4. If you use them on the command line, the fatal error PCC-F-02041 is returned by Pro*C/C++. The other options (those listed above) are quietly accepted by the precompiler option processor but have no effect. For more information, see "Obsolete Options" .

Defined Macros If you define macros on the C compiler's command line, you might also have to define these macros on the precompiler command line, depending on the requirements of your application. For example, if you C compile with a UNIX command line such as cc -DDEBUG ...

you should precompile using the DEFINE= option, namely proc DEFINE=DEBUG ...

Error Message Codes Error and warning codes (PCC errors) are different between Pro*C release 1.6 and Pro*C/C++ release 2.2. See the Oracle7 Server Messages manual for a complete list of PCC codes and messages. The runtime messages issued by SQLLIB now have the prefix SQL-, rather than the RTL- prefix used in earlier Pro*C/C++ and Pro*C releases. The message codes remain the same as those of earlier releases.

Include Files Because Pro*C/C++ does a complete C parse of the input file, which involves a preprocessing step, the location of all included files that need to be precompiled must be specified on the command line, or in a configuration file. (See Chapter 7 for complete information about precompiler options and configuration files.) For example, if you are developing under UNIX, and your application includes files in the directory /home/project42/include, you must specify this directory both on the Pro*C/C++ command line and on the cc command line. You use commands like these: proc iname=my_app.pc include=/home/project42/include . . . cc -I/home/project42/include . . . my_app.c

or you would include the appropriate macros in a makefile. For complete information about compiling and linking your Pro*C/C++ release 2.2 application, see your system-specific Oracle documentation.

Output Files With Pro*C releases 1.6 and earlier, the output file (.c) is generated in the working directory, i.e., the directory from which the precompiler command line is issued. Beginning with Pro*C release 2.0 and continuing with Pro*C/C++ release 2.2, the output file is generated in the same directory as the input file. To generate an output file in a different directory from the input file, use the ONAME option to explicitly specify the desired location of your output file. For more information, see "ONAME" .

Indicator Variables If you are migrating an application from Pro*C release 1.3 or release 1.4, used with Oracle V6, to Oracle7, there is a major change in behavior if you do not use indicator variables. Oracle V6 does not return an error if you SELECT or FETCH a null into a host variable that has no associated indicator variable. With


31-08-2012


Page 6 of 55

Oracle7, the normal behavior is that SELECTing or FETCHing a null into a host variable that has no associated indicator variable does cause an error. The error code is -01405 in SQLCODE and ``22002'' in SQLSTATE. To avoid this error without recoding your application, you can specify DBMS=V6, or you can specify UNSAFE_NULL=YES (as described ) with DBMS=V7 or V6_CHAR and MODE=ORACLE. See the description of the DBMS option for complete information. However, Oracle recommends that you always use indicator variables in new Pro*C/C++ applications.

Programming Guidelines This section deals with embedded SQL syntax, coding conventions, and C-specific features and restrictions. Topics are arranged alphabetically for quick reference.

C++ Release 2.2 of the Pro*C/C++ Precompiler can optionally generate code that can be compiled using ed C++ compilers. See Chapter 6 for a complete explanation of this capability.

Comments You can place C-style comments (/* ... */) in a SQL statement wherever blanks can be placed (except between the keywords EXEC SQL). Also, you can place ANSI-style comments (- - ...) within SQL statements at the end of a line, as the following example shows: EXEC SQL SELECT ENAME, SAL INTO :emp_name, :salary

-- output host variables

FROM EMP WHERE DEPTNO = :dept_number;

You cannot nest C-style comments. You can use C++ style comments (//) in your Pro*C/C++ source if you precompile using the CODE=P precompiler option.

Constants An L or l suffix specifies a long integer constant, a U or u suffix specifies an unsigned integer constant, a 0X or 0x prefix specifies a hexadecimal integer constant, and an F or f suffix specifies a float floating-point constant. These forms are not allowed in SQL statements.

Delimiters While C uses apostrophes to delimit single characters, as in ch = getchar(); switch (ch) { case 'U': update();

break;

case 'I': insert();

break;

...

SQL uses apostrophes to delimit character strings, as in EXEC SQL SELECT ENAME, SAL FROM EMP WHERE JOB = 'MANAGER';

While C uses double quotes to delimit character strings, as in printf("\nG'Day, mate!");

SQL uses quotation marks to delimit identifiers containing special or lowercase characters, as in EXEC SQL CREATE TABLE "Emp2" (empno

number(4), ...);

Embedded SQL Syntax The maximum number of open cursors per process is set by the Oracle initialization parameter OPEN_CURSORS. You can override this parameter by using MAXOPENCURSORS to specify a lower (but not higher) value. The only special requirement for building SQL commands into your Pro*C/C++ program is that you preface them with the EXEC SQL clause. The Pro*C/C++ Precompiler translates all EXEC SQL statements into Oracle calls recognizable by your C compiler.

File Length The Pro*C/C++ Precompiler cannot process arbitrarily long source files. Some of the variables used internally limit the size of the generated file. There is a limit to the number of lines allowed; the following aspects of the source file are contributing factors to the file-size constraint: z

complexity of the embedded SQL statements (for example, the number of bind and define variables)

z

whether a database name is used (for example, connecting to a database with an AT clause)


31-08-2012


z

Page 7 of 55

number of embedded SQL statements

To prevent problems related to this limitation, use multiple program units to sufficiently reduce the size of the source files.

Function Prototyping The ANSI C standard (X3.159-1989) provides for function prototyping. A function prototype declares a function and the datatypes of its arguments, so that the C compiler can detect missing or mismatched arguments. The CODE option, which you can enter on the command line or in a configuration file, determines the way that the Pro*C precompiler generates C or C++ code. When you precompile your program with CODE=ANSI_C, the precompiler generates fully prototyped function declarations; for example: extern void sqlora(long *, void *);

When you precompile with the option CODE=KR_C (KR for ``Kernighan and Ritchie"--the default), the precompiler generates function prototypes in the same way that it does for ANSI_C, except that function parameter lists are commented out. For example: extern void sqlora(/*_ long *, void *

_*/);

So, make sure to set the precompiler option CODE to KR_C if you use a C compiler that does not ANSI C. When the CODE option is set to ANSI_C, the precompiler can also generate other ANSI-specific constructs; for example, the const type qualifier.

Host Variable Names Host variable names can consist of upper or lowercase letters, digits, and underscores, but must begin with a letter. They can be any length, but only the first 31 characters are significant to the Pro*C/C++ Precompiler. Your C compiler or linker might require a shorter maximum length, so check your C compiler 's guide. For SQL89 standards conformance, restrict the length of host variable names to 18 or fewer characters.

Line Continuation You can continue SQL statements from one line to the next. You must use a backslash (\) to continue a string literal from one line to the next, as the following example shows: EXEC SQL INSERT INTO dept (deptno, dname) VALUES (50, 'PURCHAS\ ING');

In this context, the precompiler treats the backslash as a continuation character.

MAXLITERAL Default Value The precompiler option MAXLITERAL lets you specify the maximum length of string literals generated by the precompiler, so that compiler limits are not exceeded. The MAXLITERAL default value is 1024. But, you might have to specify a lower value. For example, if your C compiler cannot handle string literals longer than 512 characters, you would specify MAXLITERAL=512. Check your C compiler 's guide.

Nulls In C, a null statement is written as a single semicolon, strings are terminated by the null character ('\0'), and the pointer to null points to a special address (0). In SQL, a null column value is simply one that is missing, unknown, or inapplicable; it equates neither to zero nor to a blank. So, use the NVL function to convert nulls to non-nulls, use the IS [NOT] NULL comparison operator to search for nulls, and use indicator variables to insert and test for nulls.

Operators The logical operators and the `èqual to" relational operator are different in C and SQL, as the list below shows. These C operators are not allowed in SQL statements. SQL Operators C Operator NOT ! AND

&&

OR

||

=

==

Nor are the following C operators: Type address

C Operator

bitwise

&, |, ^, ~

&

compound assignment +=, -=, *=, etc. conditional

?:

decrement

--

increment

++

indirection

*

modulus

%

shift

>>,<<


31-08-2012


Page 8 of 55

Statement Labels You can associate standard C statement labels (label_name:) with SQL statements, as this example shows: EXEC SQL WHENEVER SQLERROR GOTO connect_error; ... connect_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE; printf("\nInvalid name/\n"); exit(1);

Label names can be any length, but only the first 31 characters are significant. Your C compiler might require a different maximum length. Check your C compiler 's guide.

Statement Terminator Embedded SQL statements are always terminated by a semicolon, as the following example shows: EXEC SQL DELETE FROM emp WHERE deptno = :dept_number;

Host Variables Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data to Oracle, and Oracle outputs data to the program. Oracle stores input data in database columns, and stores output data in program host variables. A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must be an lvalue. Host arrays of most host variables are also ed. See the section ``Host Arrays'' for more information.

Declaring Host Variables You declare a host variable according to the rules of C, specifying a C datatype ed by the Oracle program interface. You do not have to declare host variables in a special Declare Section. However, if you do not use a Declare Section, the FIPS flagger warns you about this, as the Declare Section is part of the SQL Standard. The C datatype must be compatible with that of the source or target database column. Table 3 - 1 shows the C datatypes and the pseudotypes that you can use when declaring host variables. Only these datatypes can be used for host variables. Table 3 - 2 shows the compatible Oracle internal datatypes. C Datatype or Pseudotype char char[n] int short long float double VARCHAR[n]

Description single character n-character array (string) integer small integer large integer floating-point number (usually single precision) floating-point number (always double precision) variable-length string

Table 3 - 1. C Datatypes for Host Variables Internal Type VARCHAR2(Y) (Note 1) CHAR(X) (Note 1)

C Type char char[n] VARCHAR[n] int short long float double

NUMBER NUMBER(P,S) (Note 2)

int short long float double char char[n] VARCHAR [n] char[n] VARCHAR[n] char[n] VARCHAR[n] unsigned char[n] VARCHAR[n] unsigned char[n] VARCHAR[n] unsigned char[n] VARCHAR[n] unsigned char[n] VARCHAR[n]

DATE LONG RAW(X) (Note 1) LONG RAW ROWID MLSLABEL

Description single character n-byte character array n-byte variable-length character array integer small integer large integer floating-point number double-precision floating-point number integer small integer large integer floating-point number double-precision floating-point number single character n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array n-byte character array n-byte variable-length character array

Notes: 1. X ranges from 1 to 255. 1 is the default value. Y ranges from 1 to 2000. 2. P ranges from 2 to 38. S ranges from -84 to 127. Table 3 - 2. C-Oracle Datatype Compatibility One-dimensional arrays of simple C types can also serve as host variables. For char[n] and VARCHAR[n], n specifies the maximum string length, not the number of strings in the array. Two-dimensional arrays are allowed only for char[m][n] and VARCHAR[m][n], where m specifies the number of strings in the array and n specifies the maximum string length.


31-08-2012


Page 9 of 55

Pointers to simple C types are ed. Pointers to char[n] and VARCHAR[n] variables should be declared as pointer to char or VARCHAR (with no length specification). Arrays of pointers, however, are not ed. Storage-Class Specifiers Pro*C lets you use the auto, extern, and static storage-class specifiers when you declare host variables. However, you cannot use the storage-class specifier to store host variables, since the precompiler takes the address of host variables by placing an ampersand (&) before them. Following the rules of C, you can use the auto storage class specifier only within a block. To comply with the ANSI C standard, the Pro*C Precompiler allows you to declare an extern char[n] host variable with or without a maximum length, as the following example shows: extern char

protocol[15];

extern char

msg[];

However, you should always specify the maximum length. In the last example, if msg is an output host variable declared in one precompilation unit but defined in another, the precompiler has no way of knowing its maximum length. If you have not allocated enough storage for msg in the second precompilation unit, you might corrupt memory. (Usually, `ènough" is the number of bytes in the longest column value that might be SELECTed or FETCHed into the host variable, plus one byte for a possible null terminator.) If you neglect to specify the maximum length for an extern char[ ] host variable, the precompiler issues a warning message. Also, it assumes that the host variable will store a CHARACTER column value, which cannot exceed 255 characters in length. So, if you want to SELECT or FETCH a VARCHAR2 or a LONG column value of length greater than 255 characters into the host variable, you must specify a maximum length. Type Qualifiers You can also use the const and volatile type qualifiers when you declare host variables. A const host variable must have a constant value, that is, your program cannot change its initial value. A volatile host variable can have its value changed in ways unknown to your program (by a device attached to the system, for instance).

Referencing Host Variables You use host variables in SQL data manipulation statements. A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in C statements, as the following example shows: char int

buf[15]; emp_number;

float

salary;

... gets(buf); emp_number = atoi(buf);

EXEC SQL SELECT sal INTO :salary FROM emp WHERE empno = :emp_number;

Though it might be confusing, you can give a host variable the same name as an Oracle table or column, as this example shows: int

empno;

char

ename[10];

float

sal;

... EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp WHERE empno = :empno;

Restrictions A host variable name is a C identifier, hence it must be declared and referenced in the same upper/lower case format. It cannot substitute for a column, table, or other Oracle object in a SQL statement, and must not be an Oracle reserved word. See Appendix B for a list of Oracle reserved words and keywords. A host variable must resolve to an address in the program. For this reason, function calls and numeric expressions cannot serve as host variables. The following code is invalid: #define MAX_EMP_NUM

9000

... int get_dept(); ... EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (:MAX_EMP_NUM + 10, 'CHEN', :get_dept());


31-08-2012


Page 10 of 55

Indicator Variables You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte integer and, in SQL statements, must be prefixed with a colon and immediately follow its host variable (unless you use the keyword INDICATOR). If you are using Declare Sections, you must also declare indicator variables inside the Declare Sections.

Using the Keyword INDICATOR To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is :host_variable INDICATOR :indicator_variable

which is equivalent to :host_variable:indicator_variable

You can use both forms of expression in your host program.

An Example Typically, you use indicator variables to assign nulls to input host variables and detect nulls or truncated values in output host variables. In the example below, you declare three host variables and one indicator variable, then use a SELECT statement to search the database for an employee number matching the value of host variable emp_number. When a matching row is found, Oracle sets output host variables salary and commission to the values of columns SAL and COMM in that row and stores a return code in indicator variable ind_comm. The next statements use ind_comm to select a course of action. EXEC SQL BEGIN DECLARE SECTION; int

emp_number;

float

salary, commission;

short comm_ind;

/* indicator variable

*/

EXEC SQL END DECLARE SECTION; char temp[16]; float

pay;

/* not used in a SQL statement */

... printf("Employee number? "); gets(temp); emp_number = atof(temp); EXEC SQL SELECT SAL, COMM INTO :salary, :commission:ind_comm FROM EMP WHERE EMPNO = :emp_number; if(ind_comm == -1)

/* commission is null */

pay = salary; else pay = salary + commission;

For more information ing indicator variables, see "Using Indicator Variables" .

Guidelines The following guidelines apply to declaring and referencing indicator variables. An indicator variable must z

be declared explicitly (in the Declare Section if present) as a 2-byte integer

z

be prefixed with a colon (:) in SQL statements

z

immediately follow its host variable in SQL statements and PL/SQL blocks (unless preceded by the keyword INDICATOR)

An indicator variable must not z

be prefixed with a colon in host language statements

z

follow its host variable in host language statements

z

be an Oracle reserved word

Oracle Restrictions When DBMS=V6, Oracle does not issue an error if you SELECT or FETCH a null into a host variable that is not associated with an indicator variable. However, when DBMS=V7, if you SELECT or FETCH a null into a host variable that has no indicator, Oracle issues the following error message: ORA-01405: fetched column value is NULL


31-08-2012


Page 11 of 55

When precompiling with MODE=ORACLE and DBMS=V7 or V6_CHAR specified, you can specify UNSAFE_NULL=YES to disable the ORA-01405 message. For more information, see "UNSAFE_NULL" .

Host Structures You can use a C structure to contain host variables. You reference a structure containing host variables in the INTO clause of a SELECT or a FETCH statement, and in the VALUES list of an INSERT statement. Every component of the host structure must be a legal Pro*C/C++ host variable, as defined in Table 3 - 1. When a structure is used as a host variable, only the name of the structure is used in the SQL statement. However, each of the of the structure sends data to Oracle, or receives data from Oracle on a query. The following example shows a host structure that is used to add an employee to the EMP table: typedef struct { char

emp_name[11]; /* one greater than column length */

int

emp_number;

int

dept_number;

float salary; } emp_record; ... /* define a new structure of type "emp_record" */ emp_record new_employee;

stry(new_employee.emp_name, "CHEN"); new_employee.emp_number = 9876; new_employee.dept_number = 20; new_employee.salary = 4250.00;

EXEC SQL INSERT INTO emp (ename, empno, deptno, sal) VALUES (:new_employee);

The order that the are declared in the structure must match the order that the associated columns occur in the SQL statement, or in the database table if the column list in the INSERT statement is omitted. For example, the following use of a host structure is invalid, and causes a runtime error: struct { int empno; float salary;

/* struct components in wrong order */

char emp_name[10]; } emp_record;

... SELECT empno, ename, sal INTO :emp_record FROM emp;

The example is wrong because the components of the structure are not declared in the same order as the associated columns in the select list. The correct form of the SELECT statement is SELECT empno, sal, ename

/* reverse order of sal and ename */

INTO :emp_record FROM emp;

Host Structures and Arrays You can use host arrays as components of host structures. See the section ``Host Arrays" structure containing arrays is used to INSERT three new entries into the EMP table:

for more information about host arrays. In the following example, a

struct {


31-08-2012


Page 12 of 55

char emp_name[3][10]; int emp_number[3]; int dept_number[3]; } emp_rec; ... stry(emp_rec.emp_name[0], "ANQUETIL"); stry(emp_rec.emp_name[1], "MERCKX"); stry(emp_rec.emp_name[2], "HINAULT"); emp_rec.emp_number[0] = 1964; emp_rec.dept_number[0] = 5; emp_rec.emp_number[1] = 1974; emp_rec.dept_number[1] = 5; emp_rec.emp_number[2] = 1985; emp_rec.dept_number[2] = 5;

EXEC SQL INSERT INTO emp (ename, empno, deptno) VALUES (:emp_rec);

Arrays of Structures You cannot, however, use an array of structures as a host array. The following declaration of an array of structs is illegal as a host array: struct { char emp_name[11]; int emp_number; int dept_number; } emp_rec[3];

It is possible to use a single struct within an array of structs as a host variable. The following code fragment shows an example of this: struct employee { char emp_name[11]; int emp_number; int dept_number; } emp_rec[3]; ... EXEC SQL SELECT ename, empno, deptno INTO :emp_rec[1] FROM emp; ...

PL/SQL Records You cannot use a C struct as a host variable for a PL/SQL RECORD variable.

Nested Structures and Unions You cannot nest host structures. The following example is invalid: struct { int emp_number; struct { float salary; float commission; } sal_info;

/* INVALID */

int dept_number; } emp_record;


31-08-2012


Page 13 of 55

... EXEC SQL SELECT empno, sal, comm, deptno INTO :emp_record FROM emp;

Also, you cannot use a C union as a host structure, nor can you nest a union in a structure that is to be used as a host structure.

Host Indicator Structures When you need to use indicator variables, but your host variables are contained in a host structure, you set up a second structure that contains an indicator variable for each host variable in the host structure. You must have an indicator for each host variable, even if you do not need it. For example, suppose you declare a host structure student_record as follows: struct { char s_name[32]; int s_id; char grad_date[9]; } student_record;

If you want to use the host structure in a query such as EXEC SQL SELECT student_name, student_idno, graduation_date INTO :student_record FROM college_enrollment WHERE student_idno = 7200;

and you need to know if the graduation date can be NULL, then you must declare a separate host indicator structure. You declare this as struct { short s_name_ind;

/* indicator variables must be shorts */

short s_id_ind; short grad_date_ind; } student_record_ind;

You must have an indicator variable in the indicator structure for each component of the host structure, even though you might only be interested in the null/not null status of the grad_date component. Reference the indicator structure in the SQL statement in the same way that you would a host indicator variable: EXEC SQL SELECT student_name, student_idno, graduation_date INTO :student_record INDICATOR :student_record_ind FROM college_enrollment WHERE student_idno = 7200;

When the query completes, the null/not null status of each selected component is available in the host indicator structure. Note: This Guide conventionally names indicator variables and indicator structures by appending _ind to the host variable or structure name. However, the names of indicator variables are completely arbitrary. You can adopt a different convention, or use no convention at all.

Sample Program: Cursor and a Host Structure The demonstration program in this section shows a query that uses an explicit cursor, selecting data into a host structure. This program is available on-line in the file sample2.pc in your demo directory. /* * * * * * */

sample2.pc This program connects to ORACLE, declares and opens a cursor, fetches the names, salaries, and commissions of all salespeople, displays the results, then closes the cursor.

#include <stdio.h> #include <sqlca.h> #define UNAME_LEN #define PWD_LEN

20 40

/*


31-08-2012


Page 14 of 55

* Use the precompiler typedef'ing capability to create * null-terminated strings for the authentication host * variables. (This isn't really necessary--plain char *'s * would work as well. This is just for illustration.) */ typedef char asciiz[PWD_LEN]; EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; asciiz name; asciiz ; struct emp_info { asciiz emp_name; float salary; float commission; }; /* Declare function to handle unrecoverable errors. */ void sql_error(); main() { struct emp_info *emp_rec_ptr; /* Allocate memory for emp_info struct. */ if ((emp_rec_ptr = (struct emp_info *) malloc(sizeof(struct emp_info))) == 0) { fprintf(stderr, "Memory allocation error.\n"); exit(1); } /* Connect to ORACLE. */ stry(name, "SCOTT"); stry(, "TIGER"); EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--"); EXEC SQL CONNECT :name IDENTIFIED BY :; printf("\nConnected to ORACLE as : %s\n", name); /* Declare the cursor. All static SQL explicit cursors * contain SELECT commands. 'salespeople' is a SQL identifier, * not a (C) host variable. */ EXEC SQL DECLARE salespeople CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%'; /* Open the cursor. */ EXEC SQL OPEN salespeople; /* Get ready to print results. */ printf("\n\nThe company's salespeople are--\n\n"); printf("Salesperson Salary Commission\n"); printf("-------------------------\n"); /* Loop, fetching all salesperson's statistics. * Cause the program to break the loop when no more * data can be retrieved on the cursor. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { EXEC SQL FETCH salespeople INTO :emp_rec_ptr; printf("%-11s%9.2f%13.2f\n", emp_rec_ptr->emp_name, emp_rec_ptr->salary, emp_rec_ptr->commission); } /* Close the cursor. */ EXEC SQL CLOSE salespeople; printf("\nArrivederci.\n\n"); EXEC SQL COMMIT WORK RELEASE; exit(0); }

void sql_error(msg) char *msg; { char err_msg[512]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); /* Call sqlglm() to get the complete text of the * error message. */ buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg);


31-08-2012


Page 15 of 55

EXEC SQL ROLLBACK RELEASE; exit(1); }

Host Arrays Host arrays can increase performance by letting you manipulate an entire collection of data items with a single SQL statement. With few exceptions, you can use host arrays wherever scalar host variables are allowed. Also, you can associate an indicator array with any host array.

Declaring Host Arrays You declare host arrays following the normal rules of C. The following example shows three arrays, dimensioned with 50 elements each: int

emp_number[50];

/* array of 50 char arrays, each 11 bytes long */ char emp_name[50][11]; float

salary[50];

Arrays of VARCHARs are also allowed. The following declaration is a valid host language declaration: VARCHAR v_array[10][30];

/* a valid host array */

Restrictions Host arrays of pointers are not allowed. Neither are multi-dimensional host arrays--with one exception. C does not have a string datatype, so you must declare an array of strings as a two-dimensional character array. The Pro*C/C++ Precompiler treats the two-dimensional character array as a one-dimensional array of strings. The following declaration of a two-dimensional integer host array is invalid as a host array. int

hi_lo_scores[25][25];

/* won't work as a host array */

Referencing Host Arrays If you use multiple host arrays in a single SQL statement, their dimensions should be the same. This is not a requirement, however, because the Pro*C/C++ Precompiler always uses the smallest dimension for the SQL operation. In this example, only 25 rows are INSERTed: int

emp_number[50];

char

emp_name[50][10];

int

dept_number[25];

/* Populate host arrays here. */ EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (:emp_number, :emp_name, :dept_number);

It is possible to subscript host arrays in SQL statements, and use them in a loop to INSERT or fetch data. For example, you could INSERT every fifth element in an array using a loop such as: for (i = 0; i < 50; i += 5) EXEC SQL INSERT INTO emp (empno, deptno) VALUES (:emp_number[i], :dept_number[i]);

However, if the array elements that you need to process are contiguous, you should not process host arrays in a loop. Simply use the unsubscripted array names in your SQL statement. Oracle treats a SQL statement containing host arrays of dimension n like the same statement executed n times with n different scalar variables. For more information, see Chapter 10.

Using Indicator Arrays You can use indicator arrays to assign nulls to input host arrays, and to detect null or truncated values in output host arrays. The following example shows how to INSERT with indicator arrays: int

emp_number[50];

int

dept_number[50];

float

commission[50];

short

comm_ind[50];

/* indicator array */

/* Populate the host and indicator arrays.

To insert a null

into the comm column, assign -1 to the appropriate element in the indicator array. */ EXEC SQL INSERT INTO emp (empno, deptno, comm)


31-08-2012


Page 16 of 55

VALUES (:emp_number, :dept_number, :commission INDICATOR :comm_ind);

Oracle Restrictions Mixing scalar host variables with host arrays in the VALUES, SET, INTO, or WHERE clause is not allowed. If any of the host variables is an array, all must be arrays. You cannot use host arrays with the CURRENT OF clause in an UPDATE or DELETE statement.

ANSI Restriction and Requirements The array interface is an Oracle extension to the ANSI/ISO embedded SQL standard. However, when you precompile with MODE=ANSI, array SELECTs and FETCHes are still allowed. The use of arrays can be flagged using the FIPS flagger precompiler option, if desired. When DBMS=V6, no error is generated if you SELECT or FETCH null columns into a host array that is not associated with an indicator array. Still, when doing array SELECTs and FETCHes, always use indicator arrays. That way, you can test for nulls in the associated output host array. When you precompile with the precompiler options DBMS=V6_CHAR or DBMS=V7, if a null is selected or fetched into a host variable that has no associated indicator variable, Oracle stops processing, sets sqlca.sqlerrd[2] to the number of rows processed, and returns the following error: ORA-01405: fetched column value is NULL

When DBMS=V6, if you SELECT or FETCH a truncated column value into a host array that is not associated with an indicator array, Oracle stops processing, sets sqlca.sqlerrd[2] to the number of rows processed, and issues the following error message: ORA-01406: fetched column value was truncated

When DBMS=V6_CHAR or DBMS=V7, Oracle does not consider truncation to be an error. Initialization When MODE=ANSI, you should declare host variables inside a Declare Section. When you do use a Declare Section, you can initialize host arrays in the Declare Section. For instance, the following declaration is allowed: EXEC SQL BEGIN DECLARE SECTION; int

dept_number[3] = {10, 20, 30};

EXEC SQL END DECLARE SECTION;

Sample Program: Host Arrays The demonstration program in this section shows how you can use host arrays when writing a query in Pro*C/C++. Pay particular attention to the use of the ``rows processed count" in the SQLCA (sqlca.sqlerrd[2]). See Chapter 9 for more information about the SQLCA. This program is available on-line in the file sample3.pc in your demo directory. /* * * * * * * */

sample3.pc Host Arrays This program connects to ORACLE, declares and opens a cursor, fetches in batches using arrays, and prints the results using the function print_rows().

#include <stdio.h> #include <string.h> #include <sqlca.h> #define NAME_LENGTH 20 #define ARRAY_LENGTH 5 /* Another way to connect. */ char *name = "SCOTT"; char * = "TIGER"; /* Declare struct { int char float } emp_rec;

a host structure tag. */ emp_number[ARRAY_LENGTH]; emp_name[ARRAY_LENGTH][NAME_LENGTH]; salary[ARRAY_LENGTH];

/* Declare this program's functions. */ void print_rows(); /* produces program output */ void sql_error(); /* handles unrecoverable errors */ main() { int

num_ret;

/* number of rows returned */

/* Connect to ORACLE. */ EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:"); EXEC SQL CONNECT :name IDENTIFIED BY :; printf("\nConnected to ORACLE as : %s\n", name); EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:"); /* Declare a cursor for the FETCH. */


31-08-2012


Page 17 of 55

EXEC SQL DECLARE c1 CURSOR FOR SELECT empno, ename, sal FROM emp; EXEC SQL OPEN c1; /* Initialize the number of rows. */ num_ret = 0; /* Array fetch loop - ends when NOT FOUND becomes true. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { EXEC SQL FETCH c1 INTO :emp_rec; /* Print however many rows were returned. */ print_rows(sqlca.sqlerrd[2] - num_ret); num_ret = sqlca.sqlerrd[2]; /* Reset the number. */ } /* Print remaining rows from last fetch, if any. */ if ((sqlca.sqlerrd[2] - num_ret) > 0) print_rows(sqlca.sqlerrd[2] - num_ret); EXEC SQL CLOSE c1; printf("\nAu revoir.\n\n\n"); /* Disconnect from the database. */ EXEC SQL COMMIT WORK RELEASE; exit(0); } void print_rows(n) int n; { int i; printf("\nNumber printf("\n------

Employee --------

Salary"); ------\n");

for (i = 0; i < n; i++) printf("%-9d%-15.15s%9.2f\n", emp_rec.emp_number[i], emp_rec.emp_name[i], emp_rec.salary[i]); } void sql_error(msg) char *msg; { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s", msg); printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }

Pointer Variables C s pointers, which ``point" to other variables. A pointer holds the address (storage location) of a variable, not its value.

Declaring Pointer Variables You define pointers as host variables following the normal C practice, as the next example shows: int

*int_ptr;

char

*char_ptr;

Referencing Pointer Variables In SQL statements, prefix pointers with a colon, as shown in the following example: EXEC SQL SELECT intcol INTO :int_ptr FROM ...

Except for pointers to character strings, the size of the referenced value is given by the size of the base type specified in the declaration. For pointers to character strings, the referenced value is assumed to be a null-terminated string. Its size is determined at run time by calling the strlen() function. For details, see the section ``Handling Character Data" . You can use pointers to reference the of a struct. First, declare a pointer host variable, then set the pointer to the address of the desired member, as shown in the example below. The datatypes of the struct member and the pointer variable must be the same. Most compilers will warn you of a mismatch. struct { int

i;

char c;


31-08-2012


Page 18 of 55

} structvar; int

*i_ptr;

char

*c_ptr;

... main() { i_ptr = &structvar.i; c_ptr = &structvar.c; /* Use i_ptr and c_ptr in SQL statements. */ ...

Structure Pointers You can use a pointer to a structure as a host variable. The following example z

declares a structure

z

declares a pointer to the structure

z

allocates memory for the structure

z

uses the struct pointer as a host variable in a query

z

dereferences the struct components to print the results

struct EMP_REC { int emp_number; float salary; }; char *name = "HINAULT"; ... struct EMP_REC *sal_rec; sal_rec = (struct EMP_REC *) malloc(sizeof (struct EMP_REC)); ... SELECT empno, sal INTO :sal_rec FROM emp WHERE ename = :name; printf("Employee number and salary for %s: ", name); printf("%d, %g\n", sal_rec->emp_number, sal_rec->salary);

In the SQL statement, pointers to host structures are referred to in exactly the same way as a host structure. The `àddress of" notation (&) is not required; in fact, it is an error to use it.

VARCHAR Variables You can use the VARCHAR pseudotype to declare variable-length character strings. When your program deals with strings that are output from, or input to, VARCHAR2 or LONG columns, you might find it more convenient to use VARCHAR host variables instead of standard C strings. The datatype name VARCHAR can be uppercase or lowercase, but it cannot be mixed case. In this Guide, uppercase is used to emphasize that VARCHAR is not a native C datatype.

Declaring VARCHAR Variables Think of a VARCHAR as an extended C type or predeclared struct. For example, the precompiler expands the VARCHAR declaration VARCHAR

name[20];

into the following struct with array and length : struct { unsigned short

len;


31-08-2012


unsigned char

Page 19 of 55

arr[20];

} name;

The advantage of using VARCHAR variables is that you can explicitly reference the length member of the VARCHAR structure after a SELECT or FETCH. Oracle puts the length of the selected character string in the length member. You can then use this member to do things such as adding the null terminator name.arr[name.len] = '\0';

or using the length in a strny or printf statement; for example: printf("name is %.*s\n", name.len, name.arr);

You specify the maximum length of a VARCHAR variable in its declaration. The length must lie in the range 1..65,533. For example, the following declaration is invalid because no length is specified: VARCHAR

null_string[];

/* invalid */

The length member holds the current length of the value stored in the array member. You can declare multiple VARCHARs on a single line; for example: VARCHAR emp_name[ENAME_LEN], dept_loc[DEPT_NAME_LEN];

The length specifier for a VARCHAR can be a #defined macro, or any complex expression that can be resolved to an integer at precompile time. You can also declare pointers to VARCHAR datatypes. See the section "Handling Character Data" .

Referencing VARCHAR Variables In SQL statements, you reference VARCHAR variables using the struct name prefixed with a colon, as the following example shows: ... int

part_number;

VARCHAR

part_desc[40];

... main() { ... EXEC SQL SELECT pdesc INTO :part_desc FROM parts WHERE pnum = :part_number; ...

After the query is executed, part_desc.len holds the actual length of the character string retrieved from the database and stored in part_desc.arr. In C statements, you reference VARCHAR variables using the component names, as the next example shows: printf("\n\nEnter part description: "); gets(part_desc.arr); /* You must set the length of the string before using the VARCHAR in an INSERT or UPDATE */ part_desc.len = strlen(part_desc.arr);

Returning Nulls to a VARCHAR Variable Oracle automatically sets the length component of a VARCHAR output host variable. If you SELECT or FETCH a null into a VARCHAR, the server does not change the length or array . Note: If you select a null into a VARCHAR host variable, and there is no associated indicator variable, an ORA-01405 error occurs at run time. Avoid this by coding indicator variables with all host variables. (As a temporary fix, use the DBMS precompiler option. See page 7 - 13).

Inserting Nulls Using VARCHAR Variables If you set the length of a VARCHAR variable to zero before performing an UPDATE or INSERT statement, the column value is set to null. If the column has a NOT NULL constraint, Oracle returns an error.

ing VARCHAR Variables to a Function VARCHARs are structures, and most C compilers permit ing of structures to a function by value, and returning structures by copy out from functions. However, in Pro*C/C++ you must VARCHARs to functions by reference. The following example shows the correct way to a VARCHAR variable to a function: VARCHAR emp_name[20];


31-08-2012


Page 20 of 55

... emp_name.len = 20; SELECT ename INTO :emp_name FROM emp WHERE empno = 7499; ... print_employee_name(&emp_name); /* by pointer */ ...

print_employee_name(name) VARCHAR *name; { ... printf("name is %.*s\n", name->len, name->arr); ... }

Finding the Length of the VARCHAR Array Component When the precompiler processes a VARCHAR declaration, the actual length of the array element in the generated structure can be longer than that declared. For example, on a Sun Solaris 1.0 system, the Pro*C/C++ declaration VARCHAR my_varchar[12];

is expanded by the precompiler to struct my_varchar { unsigned short len; unsigned char

arr[12];

};

However, the precompiler or the C compiler on this system pads the length of the array component to 14 bytes. This alignment requirement pads the total length of the structure to 16 bytes: 14 for the padded array and 2 bytes for the length. The sqlv() function--part of the SQLLIB runtime library--returns the actual (possibly padded) length of the array member. You the sqlv() function the length of the data for a VARCHAR host variable or a VARCHAR pointer host variable, and sqlv() returns the total length of the array component of the VARCHAR. The total length includes any padding that might be added by your C compiler. The syntax of sqlv() is sqlv(size_t *datlen, size_t *totlen);

Put the length of the VARCHAR in the first parameter before calling sqlv(). When the function returns, the second parameter contains the total length of the array element. Both parameters are pointers to long integers, so must be ed by reference.

Sample Program: Using sqlv() The following sample program shows how you can use the sqlv() function in a Pro*C/C++ application. (The sample also uses the sqlgls() function, which is described .) The sample declares a VARCHAR pointer, then uses the sqlv() function to determine the size required for the VARCHAR buffer. The program FETCHes employee names from the EMP table and prints them. Finally, the sample uses the sqlgls() function to print out the SQL statement and its function code and length attributes. This program is available on line as sqlv.pc in your demo directory. /* * * * * * * * * * * */

The sqlv.pc program demonstrates how you can use the sqlv() function to determine the actual size of a VARCHAR struct. The size is then used as an offset to increment a pointer that steps through an array of VARCHARs. This program also demonstrates the use of the sqlgls() function, to get the text of the last SQL statement executed. sqlgls() is described in the "Error Handling" chapter of _The Programmer's Guide to the Oracle Pro*C Precompiler_.

#include <stdio.h> #include <sqlca.h> #include <sqlr.h> /*

Fake a varchar pointer type. */

struct my_vc_ptr {


31-08-2012


Page 21 of 55

unsigned short len; unsigned char arr[32767]; }; /* Define a type for the varchar pointer */ typedef struct my_vc_ptr my_vc_ptr; my_vc_ptr *vc_ptr;

EXEC SQL BEGIN DECLARE SECTION; VARCHAR *names; int limit; /* for use in FETCH FOR clause char *name = "scott/tiger"; EXEC SQL END DECLARE SECTION; void sql_error(); extern void sqlv(), sqlgls();

*/

main() { unsigned int vlen, function_code, padlen, buflen; int i; char stmt_buf[120]; EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :name; printf("\nConnected.\n"); /*

Find number of rows in table. */ EXEC SQL SELECT COUNT(*) INTO :limit FROM emp;

/*

Declare a cursor for the FETCH statement. */ EXEC SQL DECLARE emp_name_cursor CURSOR FOR SELECT ename FROM emp; EXEC SQL FOR :limit OPEN emp_name_cursor;

/*

Set the desired DATA length for the VARCHAR. */ vlen = 10;

/*

Use SQLV to help find the length to malloc. */ sqlv(&vlen, &padlen); printf("Actual array length of varchar is %ld\n", padlen);

/*

Allocate the names buffer for names. Set the limit variable for the FOR clause. */ names = (VARCHAR *) malloc((sizeof (short) + (int) padlen) * limit); if (names == 0) { printf("Memory allocation error.\n"); exit(1); }

/* Set the maximum lengths before the FETCH. * Note the "trick" to get an effective VARCHAR *. */ for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) { vc_ptr->len = (short) padlen; vc_ptr = (my_vc_ptr *)((char *) vc_ptr + padlen + sizeof (short)); } /* Execute the FETCH. */ EXEC SQL FOR :limit FETCH emp_name_cursor INTO :names; /*

Print the results. */ printf("Employee names--\n"); for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) { printf ("%.*s\t(%d)\n", vc_ptr->len, vc_ptr->arr, vc_ptr->len); vc_ptr = (my_vc_ptr *)((char *) vc_ptr + padlen + sizeof (short)); }

/* * * * * */

Get statistics about the most recent SQL statement using SQLGLS. Note that the most recent statement in this example is not a FETCH, but rather "SELECT ENAME FROM EMP" (the cursor).

/*

The returned value should be 1, indicating no error. */ sqlgls(stmt_buf, &buflen, &function_code); if (buflen != 0) { /* Print out the SQL statement. */ printf("The SQL statement was--\n%.*s\n", buflen,

buflen = (long) sizeof (stmt_buf);

stmt_buf);

/* Print the returned length. */ printf("The statement length is %ld\n", buflen); /* Print the attributes. */ printf("The function code is %ld\n", function_code); EXEC SQL COMMIT RELEASE; exit(0); }


31-08-2012


Page 22 of 55

else { printf("The SQLGLS function returned an error.\n"); EXEC SQL ROLLBACK RELEASE; exit(1); } } void sql_error() { char err_msg[512]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); }

Handling Character Data This section explains how the Pro*C/C++ Precompiler handles character host variables. There are four host variable character types: z

character arrays

z

pointers to strings

z

VARCHAR variables

z

pointers to VARCHARs

Do not confuse VARCHAR (a host variable data structure supplied by the precompiler) with VARCHAR2 (an Oracle internal datatype for variable-length character strings).

Effect of the DBMS Option The DBMS option, which you can specify on the command line, determines how Pro*C/C++ treats data in character arrays and strings. The DBMS option allows your program to observe compatibility with ANSI fixed-length strings, or to maintain compatibility with previous releases of Oracle and Pro*C that use variable-length strings. See Chapter 7 for a complete description of the DBMS option. For character data, there are two ways that you can use the DBMS option: z

DBMS=V7

z

DBMS=V6_CHAR

These choices are referred to in this section as Oracle7 character behavior, and Oracle V6 character behavior, respectively. V7 is the default setting, and is in effect when the DBMS option is not specified on the command line or in a configuration file. The DBMS option value V6 also provides V6 character semantics. Note: The DBMS option does not affect the way Pro*C handles VARCHAR host variables. The DBMS option affects character data both on input (from your host variables to the Oracle table) and on output (from an Oracle table to your host variables). On Input Character Array On input, the DBMS option determines the format that a host variable character array must have in your program. When the DBMS is set to V6_CHAR (or V6), host variable character arrays must be blank padded, and should not be null-terminated. When the DBMS=V7, character arrays must be null-terminated. When the DBMS option is set to V6 or V6_CHAR, trailing blanks are stripped up to the first non-blank character before the value is sent to the database. Be careful! An uninitialized character array can contain null characters. To make sure that the nulls are not inserted into the table, you must blank-pad the character array to its length. For example, if you execute the statements char emp_name[10]; ... stry(emp_name, "MILLER");

/* WRONG! Note no blank-padding */

EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (1234, :emp_name, 20);

you might find that the string "MILLER" was inserted as "MILLER\0\0\0\0" (with four null bytes appended to it). This value would not meet the following search condition: . . . WHERE ename = 'MILLER';

To INSERT the character array when DBMS is set to V6 or V6_CHAR, you should execute the statements strny(emp_name, "MILLER

", 10); /* 4 trailing blanks */


31-08-2012


Page 23 of 55


When DBMS=V7, input data in a character array must be null-terminated. So, make sure that your data ends with a null. char emp_name[11];

/* Note: one greater than column size of 10 */

... stry(emp_name, "MILLER");

/* No blank-padding required */


Character Pointer The pointer must address a null-terminated buffer that is large enough to hold the input data. Your program must allocate this buffer and place the data in it before performing the input statement. On Output Character Array On output, the DBMS option determines the format that a host variable character array will have in your program. When DBMS=V6_CHAR, host variable character arrays are blank padded up to the length of the array, but never null-terminated. When DBMS=V7, character arrays are blank padded, then null-terminated in the final position in the array. Consider the following example of character output: CREATE TABLE test_char (C_col CHAR(10), V_col VARCHAR2(10));

INSERT INTO test_char VALUES ('MILLER', 'KING');

A precompiler program to select from this table contains the following embedded SQL: ... char name1[10]; char name2[10]; ... EXEC SQL SELECT C_col, V_col INTO :name1, :name2 FROM test_char;

If you precompile the program with DBMS=V6_CHAR (or V6), name1 will contain "MILLER####"

that is, the name "MILLER" followed by 4 blanks, with no null-termination. (Note that if name1 had been declared with a size of 15, there would be 9 blanks following the name.) name2 will contain "KING######"

/* 6 trailing blanks */

If you precompile the program with DBMS=V7, name1 will contain "MILLER###\0" /* 3 trailing blanks, then a null-terminator */

that is, a string containing the name, blank-padded to the length of the column, followed by a null terminator. name2 will contain "KING#####\0"

/* 5 trailing blanks, then a null terminator */

In summary, if DBMS=V6 or DBMS=V6_CHAR, the output from either a CHARACTER column or a VARCHAR2 column is blank-padded to the length of the host variable array. If DBMS=V7, the output string is always null-terminated. Character Pointer The DBMS option does not affect the way character data are output to a pointer host variable. When you output data to a character pointer host variable, the pointer must point to a buffer large enough to hold the output from the table, plus one extra byte to hold a null terminator. The precompiler runtime environment calls strlen() to determine the size of the output buffer, so make sure that the buffer does not contain any embedded nulls ('\0'). Fill allocated buffers with some value other than '\0', then null-terminate the buffer, before fetching the data. Note: C pointers can be used in a Pro*C program that is precompiled with DBMS=V7 and MODE=ANSI. However, pointers are not legal host variable types in a SQL standard compliant program. The FIPS flagger warns you if you use pointers as host variables. The following code fragment uses the columns and table defined in the previous section, and shows how to declare and SELECT into character pointer host variables: ... char *p_name1; char *p_name2;


31-08-2012


Page 24 of 55

... p_name1 = (char *) malloc(11); p_name2 = (char *) malloc(11); stry(p_name1, "

");

stry(p_name2, "0123456789");

EXEC SQL SELECT C_col, V_col INTO :p_name1, :p_name2 FROM test_char;

When the SELECT statement above is executed with DBMS=V7 or DBMS={V6 | V6_CHAR}, the value fetched is: "MILLER####\0"

"KING######\0"

/* 4 trailing blanks and a null terminator */

/* 6 blanks and null */

VARCHAR Variables and Pointers The following example shows how VARCHAR host variables are declared: VARCHAR VARCHAR

emp_name1[10]; *emp_name2;

/* VARCHAR variable

*/

/* pointer to VARCHAR */

You cannot mix declarations of VARCHAR variables and VARCHAR pointers on the same line. On Input VARCHAR Variables When you use a VARCHAR variable as an input host variable, your program need only place the desired string in the array member of the expanded VARCHAR declaration (emp_name1.arr in our example) and set the length member (emp_name1.len). There is no need to blank-pad the array. Exactly emp_name1.len characters are sent to Oracle, counting any blanks and nulls. In the following example, you set emp_name1.len to 8: stry(emp_name1.arr, "VAN HORN"); emp_name1.len = strlen(emp_name1.arr);

Pointer to a VARCHAR When you use a pointer to a VARCHAR as an input host variable, you must allocate enough memory for the expanded VARCHAR declaration. Then, you must place the desired string in the array member and set the length member, as shown in the following example: emp_name2 = malloc(sizeof(short) + 10)

/* len + arr */

stry(emp_name2->arr, "MILLER"); emp_name2->len = strlen(emp_name2->arr);

Or, to make emp_name2 point to an existing VARCHAR (emp_name1 in this case), you could code the assignment emp_name2 = &emp_name1;

then use the VARCHAR pointer in the usual way, as in EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (:emp_number, :emp_name2, :dept_number);

On Output VARCHAR Variables When you use a VARCHAR variable as an output host variable, the program interface sets the length member but does not nullterminate the array member. As with character arrays, your program can null-terminate the arr member of a VARCHAR variable before ing it to a function such as printf() or strlen(). An example follows: emp_name1.arr[emp_name1.len] = '\0'; printf("%s", emp_name1.arr);

Or, you can use the length member to limit the printing of the string, as in: printf("%.*s", emp_name1.len, emp_name1.arr);

An advantage of VARCHAR variables over character arrays is that the length of the value returned by Oracle is available right away. With character arrays, you might need to strip the trailing blanks yourself to get the actual length of the character string. VARCHAR Pointers When you use a pointer to a VARCHAR as an output host variable, the program interface determines the variable's maximum length by checking the length member (emp_name2->len in our example). So, your program must set this member before every fetch. The fetch then sets the length member to the actual number of characters returned, as the following example shows: emp_name2->len = 10;

/* Set maximum length of buffer. */

EXEC SQL SELECT ENAME INTO :emp_name2 WHERE EMPNO = 7934; printf("%d characters returned to emp_name2", emp_name2->len);


31-08-2012


Page 25 of 55

Oracle Datatypes Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores column values in database tables, as well as the formats used to represent pseudocolumn values. External datatypes specify the formats used to store values in input and output host variables. For descriptions of the Oracle datatypes, see the Oracle7 Server SQL Reference.

Internal Datatypes For values stored in database columns, Oracle uses the internal datatypes shown in Table 3 - 3. Name VARCHAR2 NUMBER LONG ROWID DATE RAW LONG RAW CHAR MLSLABEL

Description variable-length character string, < 64Kbytes numeric value, represented in a binary coded decimal format variable-length character string <2**31-1 bytes binary value, internally 6 bytes in size fixed-length date + time value, 7 bytes variable-length binary data, <255 bytes variable-length binary data, <2**31-1 bytes fixed-length character string, < 255 bytes tag for operating system label, 2-5 bytes

Table 3 - 3. Oracle Internal Datatypes These internal datatypes can be quite different from C datatypes. For example, C has no datatype that is equivalent to the Oracle NUMBER datatype. However, NUMBERs can be converted between C datatypes such as float and double, with some restrictions. For example, the Oracle NUMBER datatype allows up to 38 decimal digits of precision, while no current C implementations can represent doubles with that degree of precision. Also, the Oracle NUMBER datatype represents values exactly (within the precision limits), while floating-point formats cannot represent values such as 10.0 exactly.

External Datatypes As shown in Table 3 - 4, the external datatypes include all the internal datatypes plus several datatypes that closely match C constructs. For example, the STRING external datatype refers to a C null-terminated string. There are also several external datatypes designed mainly for use with other languages, such as COBOL. Name VARCHAR2 NUMBER INTEGER FLOAT STRING VARNUM DECIMAL LONG VARCHAR ROWID DATE VARRAW RAW LONG RAW UNSIGNED DISPLAY LONG VARCHAR LONG VARRAW CHAR CHARZ CHARF MLSLABEL

Description variable-length character string, <= 64Kbytes decimal number, represented using a binary-coded floating-point format signed integer real number null-terminated variable length character string decimal number, like NUMBER, but includes representation length component COBOL packed decimal fixed-length character string, up to 2**31-1 bytes variable-length character string, <=65533 bytes binary value, external length is system dependent fixed-length date/time value, 7 bytes variable-length binary data, <=65533 bytes fixed-length binary data, <=65533 bytes fixed-length binary data, <=2**31-1 bytes unsigned integer COBOL numeric character data variable-length character string, <=2**31-5 bytes variable-length binary data, <=2**31-5 bytes fixed-length character string, <=255 bytes fixed-length, null-terminated character string, <=65534 bytes used in TYPE or VAR statements to force CHAR to default to CHAR, instead of VARCHAR2 tag for operating system label, 2-5 bytes (Trusted Oracle only)

Table 3 - 4. Oracle External Datatypes Brief descriptions of the external datatypes follow. VARCHAR2 You use the VARCHAR2 datatype to store variable-length character strings. The maximum length of a VARCHAR2 value is 64Kbytes. You specify the maximum length of a VARCHAR2(n) value in bytes, not characters. So, if a VARCHAR2(n) variable stores multibyte characters, its maximum length can be less than n characters. When you precompile using the options DBMS=V6 or DBMS=V6_CHAR, Oracle assigns the VARCHAR2 datatype to all host variables that you declare as char[n] or char. On Input Oracle reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An uninitialized host variable can contain nulls. So, always blank-pad a character input host variable to its declared length, and do not null


31-08-2012


Page 26 of 55

terminate it. If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value contains nothing but blanks, Oracle treats it like a null. Oracle can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle generates an error. On Output Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks. If the output value is longer than the declared length of the host variable, Oracle truncates the value before asg it to the host variable. If there is an indicator variable associated with the host variable, Oracle sets it to the original length of the output value. Oracle can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you SELECT the column value 123456789 into a character host variable of length 6, Oracle returns the value `1.2E08'. NUMBER You use the NUMBER datatype to store fixed or floating-point Oracle numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127. NUMBER values are stored in a variable-length format, starting with an exponent byte and followed by up to 20 mantissa bytes. The high-order bit of the exponent byte is a sign bit, which is set for positive numbers. The low-order 7 bits represent the exponent, which is a base-100 digit with an offset of 65. Each mantissa byte is a base-100 digit in the range 1 .. 100. For positive numbers, 1 is added to the digit. For negative numbers, the digit is subtracted from 101, and, unless there are 20 mantissa bytes, a byte containing 102 is appended to the data bytes. Each mantissa byte can represent two decimal digits. The mantissa is normalized, and leading zeros are not stored. You can use up to 20 data bytes for the mantissa, but only 19 are guaranteed to be accurate. The 19 bytes, each representing a base-100 digit, allow a maximum precision of 38 digits. On output, the host variable contains the number as represented internally by Oracle. To accommodate the largest possible number, the output host variable must be 21 bytes long. Only the bytes used to represent the number are returned. Oracle does not blank-pad or null-terminate the output value. If you need to know the length of the returned value, use the VARNUM datatype instead. There is seldom a need to use this external datatype. INTEGER You use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2-byte or 4-byte binary number. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a real number, Oracle truncates any fractional part. FLOAT You use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables. Oracle can represent numbers with greater precision than most floating-point implementations because the internal format of Oracle numbers is decimal. This can cause a loss of precision when fetching into a FLOAT variable. STRING The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated. On Input Oracle uses the specified length to limit the scan for the null terminator. If a null terminator is not found, Oracle generates an error. If you do not specify a length, Oracle assumes the maximum length of 2000 bytes. The minimum length of a STRING value is 2 bytes. If the first character is a null terminator and the specified length is 2, Oracle inserts a null unless the column is defined as NOT NULL; if the column is defined as NOT NULL, an error occurs. An all-blank value is stored intact. On Output Oracle appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle truncates the output value and appends a null byte. If a null is SELECTed, Oracle returns a null byte in the first character position. VARNUM The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the representation. On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After SELECTing a column value into a VARNUM host variable, you can check the first byte to get the length of the value. Normally, there is little reason to use this datatype. DECIMAL This type is normally used only with COBOL and PL/I programs. See the Programmer's Guide to the Oracle Precompilers for more information. LONG You use the LONG datatype to store fixed-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes or two gigabytes. VARCHAR You use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype.


31-08-2012


Page 27 of 55

ROWID You can use the ROWID datatype to store binary rowids in (typically 13-byte) fixed-length fields. The field size is port specific. So, check your system-specific Oracle documentation. You can use character host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a character host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format BBBBBBBB.RRRR.FFFF

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid 0000000E.000A.0007

points to the 11th row in the 15th block in the 7th database file. Typically, you FETCH a rowid into a character host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor. For an example, see the section "Mimicking CURRENT OF" . Note: If you need full portability or your application communicates with a non-Oracle database using Oracle Open Gateway technology, specify a maximum length of 256 (not 18) bytes when declaring the host variable. Though you can assume nothing about its contents, the host variable will behave normally in SQL statements. DATE You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 3 - 5 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right. Byte 1 2 3 4 5 6 7 Meaning Century Year Month Day Hour Minute Second Example 17-OCT-1994 at 1:23:12 PM 119 194 10 17 14 24 13 Table 3 - 5. DATE Format The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1). Normally, there is little reason to use this datatype. VARRAW You use the VARRAW datatype to store variable-length binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a <65533-byte data field. For longer strings, use the LONG VARRAW datatype. When you specify the length of a VARRAW variable, be sure to include 2 bytes for the length field. The first two bytes of the variable must be interpretable as an integer. To get the length of a VARRAW variable, simply refer to its length field. RAW You use the RAW datatype to store binary data or byte strings. The maximum length of a RAW value is 255 bytes. RAW data is like CHARACTER data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another. LONG RAW You use the LONG RAW datatype to store binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes or two gigabytes. LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another. UNSIGNED You use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a floating-point number, Oracle truncates the fractional part. DISPLAY With Pro*COBOL, you use the DISPLAY datatype to store numeric character data. The DISPLAY datatype refers to a COBOL "DISPLAY SIGN LEADING SEPARATE" number, which typically requires n + 1 bytes of storage for PIC S9(n), and n + d + 1 bytes of storage for PIC S9(n)V9(d). LONG VARCHAR You use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 (2**31 - 5) bytes. When you specify the length of a LONG VARCHAR for use in a VAR or TYPE statement, do not include the 4 length bytes. LONG VARRAW You use the LONG VARRAW datatype to store variable-length binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by


31-08-2012


Page 28 of 55

a data field. The maximum length of the data field is 2147483643 bytes. . When you specify the length of a LONG VARRAW for use in a VAR or TYPE statement, do not include the 4 length bytes. CHAR You use the CHAR datatype to store fixed-length character strings. The maximum length of a CHAR value is 255 bytes. On Input Oracle reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column. If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a character value. On Output Oracle returns the number of bytes specified for the output host variable, doing blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks. If the output value is longer than the declared length of the host variable, Oracle truncates the value before asg it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value. CHARZ By default, when DBMS=V7, Oracle assigns the CHARZ datatype to all character host variables in a Pro*C program. The CHARZ datatype indicates fixedlength, null-terminated character strings. The maximum length of a CHARZ value is 255 bytes. On input, the CHARZ and STRING datatypes work the same way. You must null-terminate the input value. The null terminator serves only to delimit the string; it does not become part of the stored data. On output, CHARZ host variables are blank-padded if necessary, then null terminated. The output value is always null terminated, even if data must be truncated. CHARF The CHARF datatype is used in EXEC SQL TYPE and EXEC SQL VAR statements. When you precompile with the DBMS option set to V7 (the default setting), specifying the external datatype CHAR in a TYPE or VAR statement equivalences the C type or variable to the fixed-length, null-terminated datatype CHARZ. When you precompile with DBMS=V6 or DBMS=V6_CHAR, the C type or variable is equivalenced to VARCHAR2. However, you might not want either of these type equivalences, but rather an equivalence to the fixed-length external type CHAR. If you use the external type CHARF, the C type or variable is always equivalenced to the fixed-length ANSI datatype CHAR, regardless of the DBMS value. CHARF never allows the C type to be equivalenced to VARCHAR2 or CHARZ. MLSLABEL You use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see the Trusted Oracle7 Server 's Guide. You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store nulls only. With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL. On Input Trusted Oracle translates the input value into a binary label, which must be a valid operating system label. If it is not, Trusted Oracle issues an error message. If the label is valid, Trusted Oracle stores it in the target database column On Output Trusted Oracle converts the binary label to a character string, which can be of type CHAR, CHARZ, STRING, VARCHAR, or VARCHAR2.

Datatype Conversion At precompile time, a default external datatype is assigned to each host variable. For example, the precompiler assigns the INTEGER external datatype to host variables of type short and int. At run time, the datatype code of every host variable used in a SQL statement is ed to Oracle. Oracle uses the codes to convert between internal and external datatypes. Before asg a SELECTed column (or pseudocolumn) value to an output host variable, Oracle must convert the internal datatype of the source column to the datatype of the host variable. Likewise, before asg or comparing the value of an input host variable to a column, Oracle must convert the external datatype of the host variable to the internal datatype of the target column. Conversions between internal and external datatypes follow the usual data conversion rules. For example, you can convert a CHAR value of "1234" to a C short value. But, you cannot convert a CHAR value of "65543" (number too large) or "10F" (number not decimal) to a C short value. Likewise, you cannot convert a char[n] value that contains any alphabetic characters to a NUMBER value.

Datatype Equivalencing Datatype equivalencing lets you control the way Oracle interprets input data, and the way Oracle formats output data. It allows you to override the default external datatypes that the precompiler assigns. On a variable-by-variable basis, you can equivalence ed C host variable datatypes to Oracle external datatypes. You can also equivalence -defined datatypes (that you typedef) to Oracle external datatypes.

Host Variable Equivalencing By default, the Pro*C Precompiler assigns a specific external datatype to every host variable. Table 3 - 6 shows the default assignments: C Type, or Pseudotype

Oracle External Type


31-08-2012


char char[n] char* int, int* short, short* long, long* float, float* double, double* VARCHAR *, VARCHAR[n]

Page 29 of 55

VARCHAR2 (DBMS=V6_CHAR) VARCHAR2 CHARZ (DBMS=V7) CHARZ INTEGER INTEGER INTEGER FLOAT FLOAT VARCHAR

Table 3 - 6. Default Type Assignments With the VAR statement, you can override the default assignments by equivalencing host variables to Oracle external datatypes. The syntax you use is EXEC SQL VAR host_variable IS type_name [ length ];

where host_variable is an input or output host variable (or host array) declared earlier, type_name is the name of a valid external datatype, and length is an integer literal specifying a valid length in bytes. Host variable equivalencing is useful in several ways. For example, suppose you want to SELECT employee names from the EMP table, then them to a routine that expects null-terminated strings. You need not explicitly null-terminate the names. Simply equivalence a host variable to the STRING external datatype, as follows: ... char

emp_name[11];

EXEC SQL VAR emp_name IS STRING (11);

The length of the ENAME column in the EMP table is 10 characters, so you allot the new emp_name 11 characters to accommodate the null terminator. When you SELECT a value from the ENAME column into emp_name, the program interface null-terminates the value for you. You can use any of the datatypes listed in the external datatypes table z

NUMBER (if you must, use VARNUM instead)

z

DECIMAL (COBOL only)

z

DISPLAY (COBOL only)

except the following:

-Defined Type Equivalencing You can also equivalence -defined datatypes to Oracle external datatypes. First, define a new datatype structured like the external datatype that suits your needs. Then, equivalence your new datatype to the external datatype using the TYPE statement. With the TYPE statement, you can assign an Oracle external datatype to a whole class of host variables. The syntax you use is EXEC SQL TYPE _type IS type_name [ ( length ) ] [REFERENCE];

Suppose you need a variable-length string datatype to hold graphics characters. First, declare a struct with a short length component followed by a <= 65533byte data component. Second, use typedef to define a new datatype based on the struct. Then, equivalence your new -defined datatype to the VARRAW external datatype, as shown in the following example: struct

screen

{ short

len;

char

buff[4000];

}; typedef struct screen graphics; EXEC SQL TYPE graphics IS VARRAW (4000); graphics

crt;

-- host variable of type graphics

...

You specify a length of 4000 bytes for the new graphics type because that is the maximum length of the data component in your struct. The precompiler allows for the len component (and any padding) when it sends the length to the Oracle server. REFERENCE Clause You can declare a -defined type to be a pointer, either explicitly, as a pointer to a scalar or struct type, or implicitly, as an array, and use this type in an EXEC SQL TYPE statement. In this case, you must use the REFERENCE clause at the end of the statement, as shown in the following example: typedef unsigned char *my_raw;

EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE; my_raw

graphics_buffer;


31-08-2012


Page 30 of 55

...

graphics_buffer = (my_raw) malloc(4004);

In this example, you allocated additional memory over and above the type length (4000). This is necessary because the precompiler also returns the length (the size of a short), and can add padding after the length due to alignment restrictions on your system. If you do not know the alignment practices on your system, make sure to allocate sufficient extra bytes for the length and padding (9 should usually be sufficient).

CHARF External Datatype Release 1.6 of the Pro*C Precompiler introduced a new external datatype named CHARF, which is a fixed-length character string. You can use this new datatype in VAR and TYPE statements to equivalence C datatypes to the fixed-length SQL standard datatype CHAR, regardless of the setting of the DBMS option. When DBMS=V7, specifying the external datatype CHARACTER in a VAR or TYPE statement equivalences the C datatype to the fixed-length datatype CHAR (datatype code 96). However, when DBMS=V6_CHAR, the C datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1). Now, you can always equivalence C datatypes to the fixed-length SQL standard type CHARACTER by using the CHARF datatype in the VAR or TYPE statement. When you use CHARF, the equivalence is always made to the fixed-length character type, regardless of the setting of the DBMS option.

Using the EXEC SQL VAR and TYPE Directives You can code an EXEC SQL VAR ... or EXEC SQL TYPE ... statement anywhere in your program. These statements are treated as executable statements, that change the datatype of any variable affected by them from the point that the TYPE or VAR statement was made to the end of the scope of the variable. If you precompile with MODE=ANSI, you should use Declare Sections. In this case, the TYPE or VAR statement must be in a Declare Section.

Sample Program: Datatype Equivalencing The demonstration program in this section shows you how you can use datatype equivalencing in your Pro*C programs. This program is available on-line in the file sample4.pc in your demo directory. /*************************************************************** sample4.pc This program demonstrates the use of type equivalencing using the LONG RAW external datatype. In order to provide a useful example that is portable across different systems, the program inserts binary files into and retrieves them from the database. For example, suppose you have a file called 'hello' in the current directory. You can create this file by compiling the following source code: #include <stdio.h> int main() { printf("Hello World!\n"); } When this program is run, we get: $hello Hello World! Here is some sample output from a run of sample4: $sample4 Connected. Do you want to create (or recreate) the EXECUTABLES table (y/n)? y EXECUTABLES table successfully dropped. Now creating new table... EXECUTABLES table created. Sample 4 Menu. Would you like to: (I)nsert a new executable into the database (R)etrieve an executable from the database (L)ist the executables currently stored in the database (Q)uit the program Enter i, r, l, or q: l Executables currently stored: ----------- --------- -----Total: 0 Sample 4 Menu. Would you like to: (I)nsert a new executable into the database (R)etrieve an executable from the database (L)ist the executables currently stored in the database (Q)uit the program Enter i, r, l, or q: i Enter the key under which you will insert this executable: hello Enter the filename to insert under key 'hello'. If the file is not in the current directory, enter the full path: hello Inserting file 'hello' under key 'hello'... Inserted. Sample 4 Menu. Would you like to: (I)nsert a new executable into the database (R)etrieve an executable from the database (L)ist the executables currently stored in the database (Q)uit the program


31-08-2012


Page 31 of 55

Enter i, r, l, or q: l Executables currently stored: ----------- --------- -----hello Total: 1 Sample 4 Menu. Would you like to: (I)nsert a new executable into the database (R)etrieve an executable from the database (L)ist the executables currently stored in the database (Q)uit the program Enter i, r, l, or q: r Enter the key for the executable you wish to retrieve: hello Enter the file to write the executable stored under key hello into. don't want the file to be in the current directory, enter the full path: h1 Retrieving executable stored under key 'hello' to file 'h1'... Retrieved.

If you

Sample 4 Menu. Would you like to: (I)nsert a new executable into the database (R)etrieve an executable from the database (L)ist the executables currently stored in the database (Q)uit the program Enter i, r, l, or q: q We now have the binary file 'h1' created, and we can run it: $h1 Hello World! ***************************************************************/ #include #include #include #include #include #include

<stdio.h> <sys/types.h> <sys/file.h> <string.h> <sqlca.h>

/* Oracle error code for 'table or view does not exist'. */ #define NON_EXISTENT -942 /* This is the maximum size (in bytes) of a file that * can be inserted and retrieved. * If your system cannot allocate this much contiguous * memory, this value might have to be lowered. */ #define MAX_FILE_SIZE 500000 /* This is the definition of the long varraw structure. * Note that the first field, len, is a long instead * of a short. This is becuase the first 4 * bytes contain the length, not the first 2 bytes. */ typedef struct { long len; char buf[MAX_FILE_SIZE]; } long_varraw; /* Type Equivalence long_varraw to long varraw. * All variables of type long_varraw from this point * on in the file will have external type 95 (long varraw) * associated with them. */ EXEC SQL type long_varraw is long varraw (MAX_FILE_SIZE); /* This program's functions declared. */ void do_connect(); void create_table(); void sql_error(); void list_executables(); void print_menu();

main() { char reply[20], key[20], filename[100]; int ok = 1; /* Connect to the database. */ do_connect(); printf("Do you want to create (or recreate) the EXECUTABLES table (y/n)? "); gets(reply); if ((reply[0] == 'y') || (reply[0] == 'Y')) create_table(); /* Print the menu, and read in the 's selection. */ print_menu(); gets(reply);


31-08-2012


Page 32 of 55

while (ok) { switch(reply[0]) { case 'I': case 'i': /* selected insert - get the key and file name. */ printf(" Enter the key under which you will insert this executable: "); gets(key); printf( "Enter the filename to insert under key '%s'.\n", key); printf( "If the file is not in the current directory, enter the full\n"); printf("path: "); gets(filename); insert(key, filename); break; case 'R': case 'r': /* selected retrieve - get the key and file name. */ printf( "Enter the key for the executable you wish to retrieve: "); gets(key); printf( "Enter the file to write the executable stored under key "); printf("%s into. If you\n", key); printf( "don't want the file to be in the current directory, enter the\n"); printf("full path: "); gets(filename); retrieve(key, filename); break; case 'L': case 'l': /* selected list - just call the list routine. */ list_executables(); break; case 'Q': case 'q': /* selected quit - just end the loop. */ ok = 0; break; default: /* Invalid selection. */ printf("Invalid selection.\n"); break; } if (ok) { /* Print the menu again. */ print_menu(); gets(reply); } } EXEC SQL commit work release; } /* Connect to the database. */ void do_connect() { /* Note this declaration: uid is a char * * pointer, so Oracle will do a strlen() on it * at runtime to determine the length. */ char *uid = "scott/tiger"; EXEC SQL whenever sqlerror do sql_error("Connect"); EXEC SQL connect :uid; }

printf("Connected.\n");

/* Creates the executables table. */ void create_table() { /* We are going to check for errors ourselves * for this statement. */ EXEC SQL whenever sqlerror continue; EXEC SQL drop table executables; if (sqlca.sqlcode == 0) { printf("EXECUTABLES table successfully dropped. printf("Now creating new table...\n"); } else if (sqlca.sqlcode == NON_EXISTENT) { printf("EXECUTABLES table does not exist. "); printf("Now creating new table...\n"); } else sql_error("create_table");

");

/* Reset error handler. */ EXEC SQL whenever sqlerror do sql_error("create_table"); EXEC SQL create table executables (name varchar2(20), binary long raw);


31-08-2012


Page 33 of 55

printf("EXECUTABLES table created.\n"); }

/* Opens the binary file identified by 'filename' for * reading, and copies it into 'buf'. * 'bufsize' should contain the maximum size of * 'buf'. Returns the actual length of the file read in, * or -1 if there is an error. */ int read_file(filename, buf, bufsize) char *filename, *buf; long bufsize; { /* We will read in the file LOCAL_BUFFERSIZE bytes at a time. */ #define LOCAL_BUFFERSIZE 512 /* Buffer to store each section of the file. */ char local_buffer[LOCAL_BUFFERSIZE]; /* Number of bytes read each time. */ int number_read; /* Total number of bytes read (the size of the file). */ int total_size = 0; /* File descriptor for the input file. */ int in_fd; /* Open the file for reading. */ in_fd = open(filename, O_RDONLY, 0); if (in_fd == -1) return(-1); /* While loop to actually read in the file, * LOCAL_BUFFERSIZE bytes at a time. */ while ((number_read = read(in_fd, local_buffer, LOCAL_BUFFERSIZE)) > 0) { if (total_size + number_read > bufsize) { /* The number of bytes we have read in so far exceeds the buffer * size - close the file and return an error. */ close(in_fd); return(-1); } /* Copy the bytes just read in from the local buffer into the output buffer. */ memy(buf+total_size, local_buffer, number_read); /* Increment the total number of bytes read by the number we just read. */ total_size += number_read; } /* Close the file, and return the total file size. */ close(in_fd); return(total_size); } /* Generic error handler. The 'routine' parameter * should contain the name of the routine executing when * the error occured. This would be specified in the * 'EXEC SQL whenever sqlerror do sql_error()' statement. */ void sql_error(routine) char *routine; { char message_buffer[512]; int buffer_size; int message_length; /* Turn off the call to sql_error() to avoid * a possible infinite loop. */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nOracle error while executing %s!\n", routine); /* Use sqlglm() to get the full text of the error message. */ buffer_size = sizeof(message_buffer); sqlglm(message_buffer, &buffer_size, &message_length); printf("%.*s\n", message_length, message_buffer); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } /* * * * *

Opens the binary file identified by 'filename' for writing, and copies the contents of 'buf' into it. 'bufsize' should contain the size of 'buf'. Returns the number of bytes written (should be == bufsize), or -1 if there is an error.


31-08-2012


Page 34 of 55

*/ int write_file(filename, buf, bufsize) char *filename, *buf; long bufsize; { int out_fd; /* File descriptor for the output file. */ int num_written; /* Number of bytes written. */ /* Open the file for writing. This command replaces * any existing version. */ out_fd = creat(filename, 0755); if (out_fd == -1) { /* Can't create the output file - return an error. */ return(-1); } /* Write the contents of buf to the file. */ num_written = write(out_fd, buf, bufsize); /* Close the file, and return the number of bytes written. */ close(out_fd); return(num_written); }

/* Inserts the binary file identified by file into the * executables table identified by key. */ int insert(key, file) char *key, *file; { long_varraw lvr; printf("Inserting file '%s' under key '%s'...\n", file, key); lvr.len = read_file(file, lvr.buf, MAX_FILE_SIZE); if (lvr.len == -1) { /* File size is too big for the buffer we have * exit with an error. */ fprintf(stderr, "\n\nError while reading file '%s':\n", file); fprintf(stderr, "The file you selected to read is too large for the buffer.\n"); fprintf(stderr, "Increase the MAX_FILE_SIZE macro in the source code,\n"); fprintf(stderr, "reprecompile, compile, and link, and try again.\n"); fprintf(stderr, "The current value of MAX_FILE_SIZE is %d bytes.\n", MAX_FILE_SIZE); EXEC SQL rollback work release; exit(1); } EXEC SQL whenever sqlerror do sql_error("insert"); EXEC SQL insert into executables (name, binary) values (:key, :lvr); EXEC SQL commit; printf("Inserted.\n"); } /* Retrieves the executable identified by key into file */ int retrieve(key, file) char *key, *file; { /* Type equivalence key to the string external datatype.*/ EXEC SQL VAR key is string(21); long_varraw lvr; short ind; int num_written; printf("Retrieving executable stored under key '%s' to file '%s'...\n", key, file); EXEC SQL whenever sqlerror do sql_error("retrieve"); EXEC SQL select binary into :lvr :ind from executables where name = :key; num_written = write_file(file, lvr.buf, lvr.len); if (num_written != lvr.len) { /* Error while writing - exit with an error. */ fprintf(stderr, "\n\nError while writing file '%s':\n", file); fprintf(stderr, "Can't create the output file. Check to be sure that you\n"); fprintf(stderr, "have write permissions in the directory into which you\n"); fprintf(stderr,


31-08-2012


Page 35 of 55

"are writing the file, and that there is enough disk space.\n"); EXEC SQL rollback work release; exit(1);

}

printf("Retrieved.\n"); } void list_executables() { char key[21]; /* Type equivalence key to the string external * datatype, so we don't have to null-terminate it. */ EXEC SQL VAR key is string(21); EXEC SQL whenever sqlerror do sql_error("list_executables"); EXEC SQL declare key_cursor cursor for select name from executables; EXEC SQL open key_cursor; printf("\nExecutables currently stored:\n"); printf("----------- --------- ------\n"); while (1) { EXEC SQL whenever not found do break; EXEC SQL fetch key_cursor into :key; printf("%s\n", key); } EXEC SQL whenever not found continue; EXEC SQL close key_cursor; printf("\nTotal: %d\n", sqlca.sqlerrd[2]); } /* Prints the menu selections. */ void print_menu() { printf("\nSample 4 Menu. Would you like to:\n"); printf("(I)nsert a new executable into the database\n"); printf("(R)etrieve an executable from the database\n"); printf("(L)ist the executables currently stored in the database\n"); printf("(Q)uit the program\n\n"); printf("Enter i, r, l, or q: "); }

National Language Although the widely-used 7- or 8-bit ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, some Asian languages, such as Japanese, contain thousands of characters. These languages can require at least 16 bits (two bytes) to represent each character. How does Oracle deal with such dissimilar languages? Oracle provides National Language (NLS), which lets you process single-byte and multibyte character data and convert between character sets. It also lets your applications run in different language environments. With NLS, number and date formats adapt automatically to the language conventions specified for a session. Thus, NLS allows s around the world to interact with Oracle in their native languages. You control the operation of language-dependent features by specifying various NLS parameters. Default values for these parameters can be set in the Oracle initialization file. Table 3 - 7 shows what each NLS parameter specifies. NLS Parameter NLS_LANGUAGE NLS_TERRITORY NLS_DATE_FORMAT NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_SORT

Specifies ... language-dependent conventions territory-dependent conventions date format language for day and month names decimal character and group separator local currency symbol ISO currency symbol sort sequence

Table 3 - 7. NLS Parameters The main parameters are NLS_LANGUAGE and NLS_TERRITORY. NLS_LANGUAGE specifies the default values for language-dependent features, which include z

language for Server messages

z

language for day and month names

z

sort sequence


31-08-2012


Page 36 of 55

NLS_LANGUAGE specifies the default values for territory-dependent features, which include z

date format

z

decimal character

z

group separator

z

local currency symbol

z

ISO currency symbol

You can control the operation of language-dependent NLS features for a session by specifying the parameter NLS_LANG as follows NLS_LANG = _ .

where language specifies the value of NLS_LANGUAGE for the session, territory specifies the value of NLS_TERRITORY, and character set specifies the encoding scheme used for the terminal. An encoding scheme (usually called a character set or code page) is a range of numeric codes that corresponds to the set of characters a terminal can display. It also includes codes that control communication with the terminal. You define NLS_LANG as an environment variable (or the equivalent on your system). For example, on UNIX using the C shell, you might define NLS_LANG as follows: setenv NLS_LANG French_.WE8ISO8859P1

To change the values of NLS parameters during a session, you use the ALTER SESSION statement as follows: ALTER SESSION SET =

The Pro*C Precompiler fully s all the NLS features that allow your applications to process multilingual data stored in an Oracle database. For example, you can declare foreign-language character variables and them to string functions such as INSTRB, LENGTHB, and SUBSTRB. These functions have the same syntax as the INSTR, LENGTH, and SUBSTR functions, respectively, but operate on a per-byte basis rather than a per-character basis. You can use the functions NLS_INITCAP, NLS_LOWER, and NLS_UPPER to handle special instances of case conversion. And, you can use the function NLSSORT to specify WHERE-clause comparisons based on linguistic rather than binary ordering. You can even NLS parameters to the TO_CHAR, TO_DATE, and TO_NUMBER functions. For more information about NLS, see the Oracle7 Server Application Developer's Guide.

Multi-byte Character Sets The Pro*C/C++ Precompiler extends for multi-byte NLS character sets through z

recognition of multi-byte character strings by the precompiler in embedded SQL statements.

z

a Pro*C precompiler option that allows host character variables to be interpreted by the precompiler as multibyte character strings

The current release (Oracle7 Server 7.3 with Pro*C/C++ 2.2) s multi-byte strings through the precompiler runtime library, SQLLIB.

Character Strings in Embedded SQL A multibyte character string in an embedded SQL statement consists of a character literal that identifies the string as multibyte, immediately followed by the string. The string is enclosed in the usual single quotes. For example, an embedded SQL statement like EXEC SQL SELECT empno INTO :emp_num FROM emp WHERE ename=N'Kuroda';

contains a multibyte character string ('Kuroda' would actually be in Kanji), since the N character literal preceding the string 'Kuroda' identifies it as a multibyte string.

Dynamic SQL Since dynamic SQL statements are not processed at precompile time, and since release 7.2 of the Oracle Server does not itself process multibyte strings, you cannot embed multibyte strings in dynamic SQL statements. If you do so, an error is returned by the server at runtime.

Host Variable Multibyte Character Strings Release 2.1 of Pro*C does not provide character string host variables that directly multibyte character sets. Instead, you use normal Pro*C character string host variables such as char or VARCHAR. You tell the precompiler which host variables should be interpreted as multibyte strings by using the NLS_CHAR precompiler option. For more information, see the documentation of the following options : z

DEF_SQLCODE

z

NLS_CHAR

z

NLS_LOCAL

z

VARCHAR

Restrictions


31-08-2012


Page 37 of 55

You cannot use datatype equivalencing (the TYPE or VAR commands) with NLS multibyte character strings. Dynamic SQL is not available for NLS multibyte character string host variables in Pro*C Release 2.1.

Blank Padding When a Pro*C character variable is defined as an NLS multibyte variable, using the NLS_CHAR option, the following blank padding and blank stripping rules apply, depending on the external datatype of the variable. See the section "External Datatypes'' . CHARZ This is the default character type when a character string is defined as multibyte. Input data must contain a null terminator at the end of the string (which does not become part of the entry in the database column). The string is stripped of any trailing double-byte spaces. However, if a string consists only of double-byte spaces, one double-byte space is left in the string. Output host variables are padded with double-byte spaces, and contain a null terminator at the end of the string. VARCHAR On input, host variables are not stripped of trailing double-byte spaces. The length component of the structure is the length of the data in characters, not bytes. On output, the host variable is not blank padded at all. The length of the buffer is set to the length of the data in characters, not in bytes. STRING and LONG VARCHAR These host variables are not ed for NLS data, since they can be specified only by datatype equivalencing or dynamic SQL, neither of which are ed for NLS data.

Indicator Variables You can use indicator variables with host character variables that are NLS multibyte (as specified using the NLS_CHAR option). Possible indicator values, and their meanings, are 0 The operation was successful. -1 A null was returned, inserted, or updated. -2 Output to a character host variable from a ``long'' type was truncated, but the original column length cannot be determined. >0 The result of a SELECT or FETCH into a character host variable was truncated. In this case, if the host variable is an NLS multibyte variable, the indicator value is the original column length in characters. If the host variable is not an NLS variable, then the indicator length is the original column length in bytes.

Cursor Variables Starting with release 2.1 of the Pro*C/C++ Precompiler, you can use cursor variables in your Pro*C/C++ program for cursors for queries. A cursor variable is a handle for a cursor that must be defined and opened on the Oracle 7.2 or later server, using PL/SQL. See the PL/SQL 's Guide and Reference for complete information about cursor variables. The advantages of cursor variables are: z

z

Ease of maintenance: queries are centralized, in the stored procedure that opens the cursor variable. If you need to change the cursor, you only need to make the change in one place: the stored procedure. There is no need to change each application. Convenient security: the of the application is the name used when the Pro*C/C++ application connects to the server. The must have execute permission on the stored procedure that opens the cursor but does not read permission on the tables used in the query. This capability can be used to limit access to the columns in the table, and access to other stored procedures.

Declaring a Cursor Variable You declare a cursor variable in your Pro*C/C++ program using the Pro*C/C++ pseudotype SQL_CURSOR. For example: EXEC SQL BEGIN DECLARE SECTION; sql_cursor

emp_cursor;

/* a cursor variable */

SQL_CURSOR

dept_cursor;

sql_cursor

*e;

/* another cursor variable */

/* a pointer to a cursor variable */

... EXEC SQL END DECLARE SECTION; e = &emp_cursor;

/* assign a value to the pointer */

You can declare a cursor variable using the type specification SQL_CURSOR, in all upper case, or sql_cursor, in all lower case; you cannot use mixed case. A cursor variable is just like any other host variable in the Pro*C/C++ program. It has scope, following the scoping rules of C. You can it as a parameter to other functions, even functions external to the source file in which you declared it. You can also define functions that return cursor variables, or pointers to


31-08-2012


Page 38 of 55

cursor variables. Caution: A SQL_CURSOR is implemented as a C struct in the code that Pro*C/C++ generates. So you can always it by pointer to another function, or return a pointer to a cursor variable from a function. But you can only it or return it by value if your C compiler s these operations.

Allocating a Cursor Variable Before you can use a cursor variable, either to open it or to FETCH using it, you must allocate the cursor. You do this using the new precompiler command ALLOCATE. For example, to allocate the SQL_CURSOR emp_cursor that was declared in the example above, you write the statement: EXEC SQL ALLOCATE :emp_cursor;

Allocating a cursor does not require a call to the server, either at precompile time or at runtime. If the ALLOCATE statement contains an error (for example, an undeclared host variable), Pro*C/C++ issues a precompile time (PCC) error. Allocating a cursor variable does cause heap memory to be used. For this reason, you should normally avoid allocating a cursor variable in a program loop. Memory allocated for cursor variables is not freed when the cursor is closed, but only when the connection is closed.

Opening a Cursor Variable You must open a cursor variable on the Oracle Server. You cannot use the embedded SQL OPEN command to open a cursor variable. You can open a cursor variable either by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement). Or, you can open and define a cursor variable using an anonymous PL/SQL block in your Pro*C/C++ program. For example, consider the following PL/SQL package, stored in the database: CREATE PACKAGE demo_cur_pkg AS TYPE EmpName IS RECORD (name VARCHAR2(10)); TYPE cur_type IS REF CURSOR RETURN EmpName; PROCEDURE open_emp_cur ( curs

IN OUT cur_type,

dept_num IN

NUMBER);

END; CREATE PACKAGE BODY demo_cur_pkg AS CREATE PROCEDURE open_emp_cur ( curs

IN OUT cur_type,

dept_num IN

NUMBER) IS

BEGIN OPEN curs FOR SELECT ename FROM emp WHERE deptno = dept_num ORDER BY ename ASC; END; END;

After this package has been stored, you can open the cursor curs by calling the open_emp_cur stored procedure from your Pro*C/C++ program, and FETCH from the cursor in the program. For example: ... sql_cursor

emp_cursor;

char

emp_name[11];

... EXEC SQL ALLOCATE :emp_cursor;

/* allocate the cursor variable */

... /* Open the cursor on the server side. */ EXEC SQL EXECUTE begin demo_cur_pkg.open_emp_cur(:emp_cursor, :dept_num); end; END-EXEC; EXEC SQL WHENEVER NOT FOUND DO break; for (;;)


31-08-2012


Page 39 of 55

{ EXEC SQL FETCH :emp_cursor INTO :emp_name; printf("%s\n", emp_name); } ...

To open a cursor using a PL/SQL anonymous block in your Pro*C/C++ program, you define the cursor in the anonymous block. For example: int dept_num = 10; ... EXEC SQL EXECUTE BEGIN OPEN :emp_cursor FOR SELECT ename FROM emp WHERE deptno = :dept_num; END; END-EXEC; ...

Opening in a Stand-Alone Stored Procedure In the example above, a reference cursor was defined inside a package, and the cursor was opened in a procedure in that package. But it is not always necessary to define a reference cursor inside the package that contains the procedures that open the cursor. If you need to open a cursor inside a stand-alone stored procedure, you can define the cursor in a separate package, and then reference that package in the standalone stored procedure that opens the cursor. Here is an example: PACKAGE dummy IS TYPE EmpName IS RECORD (name VARCHAR2(10)); TYPE emp_cursor_type IS REF CURSOR RETURN EmpName; END; -- and then define a stand-alone procedure: PROCEDURE open_emp_curs ( emp_cursor IN OUT dummy.emp_cursor_type; dept_num IN NUMBER) IS BEGIN OPEN emp_cursor FOR SELECT ename FROM emp WHERE deptno = dept_num; END; END;

Return Types When you define a reference cursor in a PL/SQL stored procedure, you must declare the type that the cursor returns. See the PL/SQL 's Guide and Reference for complete information on the reference cursor type and its return types.

Closing a Cursor Variable Use the CLOSE command to close a cursor variable. For example, to close the emp_cursor cursor variable that was OPENed in the examples above, use the embedded SQL statement: EXEC SQL CLOSE :emp_cursor;

Note that the cursor variable is a host variable, and so you must precede it with a colon. You can re-use ALLOCATEd cursor variables. You can open, FETCH, and CLOSE as many times as needed for your application. However, if you disconnect from the server, then reconnect, you must re-ALLOCATE cursor variables. Note: Cursors are automatically deallocated by the SQLLIB runtime library upon exiting the current connection.

Using Cursor Variables with the OCI You can share a Pro*C/C++ cursor variable with an OCI function. To do so, you must use the SQLLIB conversion functions, sqlcda() and sqlcur(). These functions convert between OCI cursor data areas and Pro*C/C++ cursor variables. The sqlcda() function translates an allocated cursor variable to an OCI cursor data area. The syntax is: void sqlcda(Cda_Def *cda, void *cur, int *retval);

where the parameters are cda A pointer to the destination OCI cursor data area. cur A pointer to the source Pro*C cursor variable.


31-08-2012


Page 40 of 55

retval 0 if no error, otherwise a SQLLIB (RTL) error number. Note: In the case of an error, the V2 and rc return code fields in the CDA also receive the error codes. The rows processed count field in the CDA is not set. The sqlcur() function translates an OCI cursor data area to a Pro*C cursor variable. The syntax is: void sqlcur(void *cur, Cda_Def *cda, int *retval);

where the parameters are cur A pointer to the destination Pro*C cursor variable. cda A pointer to the source OCI cursor data area. retval 0 if no error, otherwise an error code. Note: The SQLCA structure is not updated by this routine. The SQLCA components are only set after a database operation is performed using the translated cursor. ANSI and K&R prototypes for these functions are provided in the sqlapr.h and slqkpr.h header files, respectively. Memory for both cda and cur must be allocated prior to calling these functions.

Restrictions The following restrictions apply to the use of cursor variables: z

You cannot use cursor variables with dynamic SQL.

z

You can only use cursor variables with the ALLOCATE, FETCH, and CLOSE commands The DECLARE CURSOR command does not apply to cursor variables.

z

You cannot FETCH from a CLOSEd cursor variable.

z

You cannot FETCH from a non-ALLOCATEd cursor variable.

z

If you precompile with MODE=ANSI, it is an error to close a cursor variable that is already closed.

z

You cannot use the AT clause with the ALLOCATE command, nor with the FETCH and CLOSE commands if they reference a cursor variable.

z

Cursor variables cannot be stored in columns in the database.

z

A cursor variable itself cannot be declared in a package specification. Only the type of the cursor variable can be declared in the package specification.

z

A cursor variable cannot be a component of a PL/SQL record.

A Sample Program The following sample programs--a SQL script and a Pro*C program--demonstrate how you can use cursor variables in Pro*C. These sources are available online in your demo directory. cv_demo.sql -- PL/SQL source for a package that declares and -- opens a ref cursor CONNECT SCOTT/TIGER CREATE OR REPLACE PACKAGE emp_demo_pkg as TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER); END emp_demo_pkg; CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS PROCEDURE open_cur(curs IN OUT emp_cur_type, dno IN NUMBER) IS BEGIN OPEN curs FOR SELECT * FROM emp WHERE deptno = dno ORDER BY ename ASC; END; END emp_demo_pkg;

sample11.pc /* * * * * * * *

Fetch from the EMP table, using a cursor variable. The cursor is opened in the stored PL/SQL procedure open_cur, in the EMP_DEMO_PKG package. This package is available on-line in the file sample11.sql, in the demo directory.


31-08-2012


Page 41 of 55

*/ #include <stdio.h> #include <sqlca.h> /* Error handling function. */ void sql_error(); main() { char temp[32]; EXEC SQL BEGIN DECLARE SECTION; char *uid = "scott/tiger"; SQL_CURSOR emp_cursor; int dept_num; struct { int emp_num; char emp_name[11]; char job[10]; int manager; char hire_date[10]; float salary; float commission; int dept_num; } emp_info; struct { short emp_num_ind; short emp_name_ind; short job_ind; short manager_ind; short hire_date_ind; short salary_ind; short commission_ind; short dept_num_ind; } emp_info_ind; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error"); /* Connect to Oracle. */ EXEC SQL CONNECT :uid; /* Allocate the cursor variable. */ EXEC SQL ALLOCATE :emp_cursor; /* Exit the inner for (;;) loop when NO DATA FOUND. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { printf("\nEnter department number gets(temp); dept_num = atoi(temp); if (dept_num <= 0) break;

(0 to exit): ");

EXEC SQL EXECUTE begin emp_demo_pkg.open_cur(:emp_cursor, :dept_num); end; END-EXEC; printf("\nFor department %d--\n", dept_num); printf("ENAME\t SAL\t COMM\n"); printf("-----\t ---\t ----\n");

/* Fetch each row in the EMP table into the data struct. Note the use of a parallel indicator struct. */ for (;;) { EXEC SQL FETCH :emp_cursor INTO :emp_info INDICATOR :emp_info_ind; printf("%s\t", emp_info.emp_name); printf("%8.2f\t\t", emp_info.salary); if (emp_info_ind.commission_ind != 0) printf(" NULL\n"); else printf("%8.2f\n", emp_info.commission); } } /* Close the cursor. */ EXEC SQL CLOSE :emp_cursor; exit(0); } void sql_error(msg) char *msg; { long clen, fc; char cbuf[128];


31-08-2012


Page 42 of 55

clen = (long) sizeof (cbuf); sqlgls(cbuf, &clen, &fc); printf("\n%s\n", msg); printf("Statement is--\n%s\n", cbuf); printf("Function code is %ld\n\n", fc); sqlglm(cbuf, (int *) &clen, (int *) &clen); printf ("\n%.*s\n", clen, cbuf); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK; exit(-1); }

Connecting to Oracle Your Pro*C/C++ program must connect to Oracle before querying or manipulating data. To log on, simply use the CONNECT statement EXEC SQL CONNECT :name IDENTIFIED BY :;

where name and are char or VARCHAR host variables. Or, you can use the statement EXEC SQL CONNECT :usr_pwd;

where the host variable usr_pwd contains your name and separated by a slash character (/). The CONNECT statement must be the first SQL statement executed by the program. That is, other SQL statements can physically but not logically precede the CONNECT statement in the precompilation unit. To supply the Oracle name and separately, you define two host variables as character strings or VARCHARs. (If you supply a name containing both name and , only one host variable is needed.) Make sure to set the name and variables before the CONNECT is executed, or it will fail. Your program can prompt for the values, or you can hardcode them as follows: char *name = "SCOTT"; char * = "TIGER"; ... EXEC SQL WHENEVER SQLERROR ... EXEC SQL CONNECT :name IDENTIFIED BY :;

However, you cannot hardcode a name and into the CONNECT statement. Nor can you use quoted literals. For example, both of the following statements are invalid: EXEC SQL CONNECT SCOTT IDENTIFIED BY TIGER; EXEC SQL CONNECT 'SCOTT' IDENTIFIED BY 'TIGER';

Connecting Using SQL*Net Version 2 Some of the examples in this and the following section might use SQL*Net V1 connection strings to show network protocols and server names. To connect using a SQL*Net V2 driver, substitute a service name, as defined in your tnsnames.ora configuration file or in Oracle Names, in place of the SQL*Net V1 connect string. If you are using Oracle Names, the name server obtains the service name from the network definition database. See Understanding SQL*Net for more information about SQL*Net V2.

Automatic Connects You can automatically connect to Oracle with the name OPS$name

where name is the current operating system name, and OPS$name is a valid Oracle database name. (The actual value for OPS$ is defined in the INIT.ORA parameter file.) You simply to the Pro*C Precompiler a slash character, as follows: ... char

oracleid = '/';

... EXEC SQL CONNECT :oracleid;

This automatically connects you as OPS$name. For example, if your operating system name is RHILL, and OPS$RHILL is a valid Oracle name, connecting with '/' automatically logs you on to Oracle as OPS$RHILL. You can also a '/' in a string to the precompiler. However, the string cannot contain trailing blanks. For example, the following CONNECT statement will fail:


31-08-2012


Page 43 of 55

... char

oracleid[10] = "/

";

... EXEC SQL CONNECT :oracleid;

The AUTO_CONNECT Precompiler Option If AUTO_CONNECT=YES, and the application is not already connected to a database when it processes the first executable SQL statement, it attempts to connect using the id OPS$<name>

where name is your current operating system or task name and OPS$name is a valid Oracle id. When AUTO_CONNECT=NO, you must use the CONNECT statement in your program to connect to Oracle.

Concurrent Connections The Pro*C/C++ Precompiler s distributed processing via SQL*Net. Your application can concurrently access any combination of local and remote databases or make multiple connections to the same database. In Figure 3 - 1, an application program communicates with one local and three remote Oracle databases. ORA2, ORA3, and ORA4 are simply logical names used in CONNECT statements. Figure 3 - 1. Connecting via SQL*Net By eliminating the boundaries in a network between different machines and operating systems, SQL*Net provides a distributed processing environment for Oracle tools. This section shows you how Pro*C/C++ s distributed processing via SQL*Net. You learn how your application can z

directly or indirectly access other databases

z

concurrently access any combination of local and remote databases

z

make multiple connections to the same database

For details on installing SQL*Net and identifying available databases, refer to the SQL*Net 's Guide and your system-specific Oracle documentation.

Some Preliminaries The communicating points in a network are called nodes. SQL*Net lets you transmit information (SQL statements, data, and status codes) over the network from one node to another. A protocol is a set of rules for accessing a network. The rules establish such things as procedures for recovering after a failure and formats for transmitting data and checking errors. The SQL*Net syntax for connecting to the default database in the local domain is simply to use the service name for the database. If the service name is not in the default (local) domain, you must use a global specification (all domains specified). For example: HR.US.ORACLE.COM

Default Databases and Connections Each node has a default database. If you specify a database name, but no domain, in your CONNECT statement, you connect to the default database on the named local or remote node. A default connection is made by a CONNECT statement that has no AT clause. The connection can be to any default or non-default database at any local or remote node. SQL statements without an AT clause are executed against the default connection. Conversely, a non-default connection is made by a CONNECT statement that has an AT clause. SQL statements with an AT clause are executed against the non-default connection. All database names must be unique, but two or more database names can specify the same connection. That is, you can have multiple connections to any database on any node.

Explicit Connections Usually, you establish a connection to Oracle as follows: EXEC SQL CONNECT :name IDENTIFIED BY :;

You can also use EXEC SQL CONNECT :usr_pwd;

where usr_pwd contains name/. You can automatically connect to Oracle with the id OPS$name

where name is your current operating system or task name and OPS$name is a valid Oracle id. You simply to the precompiler a slash (/) character, as follows:


31-08-2012


Page 44 of 55

char oracleid = '/'; ... EXEC SQL CONNECT :oracleid;

This automatically connects you as OPS$name. If you do not specify a database and node, you are connected to the default database at the current node. If you want to connect to a different database, you must explicitly identify that database. With explicit connections, you connect to another database directly, giving the connection a name that will be referenced in SQL statements. You can connect to several databases at the same time and to the same database multiple times. Single Explicit Connections In the following example, you connect to a single non-default database at a remote node: /* declare needed host variables */ char

name[10]

= "scott";

char

[10]

= "tiger";

char

db_string[20] = "NYNON";

/* give the database connection a unique name */ EXEC SQL DECLARE DB_NAME DATABASE; /* connect to the non-default database

*/

EXEC SQL CONNECT :name IDENTIFIED BY : AT DB_NAME USING :db_string;

The identifiers in this example serve the following purposes: z

The host variables name and identify a valid .

z

The host variable db_string contains the SQL*Net syntax for connecting to a non-default database at a remote node.

z

The undeclared identifier DB_NAME names a non-default connection; it is an identifier used by Oracle, not a host or program variable.

The USING clause specifies the network, machine, and database to be associated with DB_NAME. Later, SQL statements using the AT clause (with DB_NAME) are executed at the database specified by db_string. Alternatively, you can use a character host variable in the AT clause, as the following example shows: /* declare needed host variables */ char

name[10]

= "scott";

char

[10]

= "tiger";

char

db_name[10]

= "oracle1";

char

db_string[20] = "NYNON";

/* connect to the non-default database using db_name */ EXEC SQL CONNECT :name IDENTIFIED BY : AT :db_name USING :db_string; ...

If db_name is a host variable, the DECLARE DATABASE statement is not needed. Only if DB_NAME is an undeclared identifier must you execute a DECLARE DB_NAME DATABASE statement before executing a CONNECT ... AT DB_NAME statement. SQL Operations If granted the privilege, you can execute any SQL data manipulation statement at the non-default connection. For example, you might execute the following sequence of statements: EXEC SQL AT DB_NAME SELECT ... EXEC SQL AT DB_NAME INSERT ... EXEC SQL AT DB_NAME UPDATE ...

In the next example, db_name is a host variable: EXEC SQL AT :db_name DELETE ...

If db_name is a host variable, all database tables referenced by the SQL statement must be defined in DECLARE TABLE statements. Otherwise, the precompiler issues a warning. PL/SQL Blocks You can execute a PL/SQL block using the AT clause. The following example shows the syntax: EXEC SQL AT :db_name EXECUTE


31-08-2012


Page 45 of 55

begin /* PL/SQL block here */ end; END-EXEC;

Cursor Control Cursor control statements such as OPEN, FETCH, and CLOSE are exceptions--they never use an AT clause. If you want to associate a cursor with an explicitly identified database, use the AT clause in the DECLARE CURSOR statement, as follows: EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ... EXEC SQL OPEN emp_cursor ... EXEC SQL FETCH emp_cursor ... EXEC SQL CLOSE emp_cursor;

If db_name is a host variable, its declaration must be within the scope of all SQL statements that refer to the DECLAREd cursor. For example, if you OPEN the cursor in one subprogram, then FETCH from it in another subprogram, you must declare db_name globally. When OPENing, CLOSing, or FETCHing from the cursor, you do not use the AT clause. The SQL statements are executed at the database named in the AT clause of the DECLARE CURSOR statement or at the default database if no AT clause is used in the cursor declaration. The AT :host_variable clause allows you to change the connection associated with a cursor. However, you cannot change the association while the cursor is open. Consider the following example: EXEC SQL AT :db_name DECLARE emp_cursor CURSOR FOR ... stry(db_name, "oracle1"); EXEC SQL OPEN emp_cursor; EXEC SQL FETCH emp_cursor INTO ... stry(db_name, "oracle2"); EXEC SQL OPEN emp_cursor;

/*

illegal, cursor still open */

EXEC SQL FETCH emp_cursor INTO ...

This is illegal because emp_cursor is still open when you try to execute the second OPEN statement. Separate cursors are not maintained for different connections; there is only one emp_cursor, which must be closed before it can be reopened for another connection. To debug the last example, simply close the cursor before reopening it, as follows: ... EXEC SQL CLOSE emp_cursor;

-- close cursor first

stry(db_name, "oracle2"); EXEC SQL OPEN emp_cursor; EXEC SQL FETCH emp_cursor INTO ...

Dynamic SQL Dynamic SQL statements are similar to cursor control statements in that some never use the AT clause. For dynamic SQL Method 1, you must use the AT clause if you want to execute the statement at a non-default connection. An example follows: EXEC SQL AT :db_name EXECUTE IMMEDIATE :slq_stmt;

For Methods 2, 3, and 4, you use the AT clause only in the DECLARE STATEMENT statement if you want to execute the statement at a non-default connection. All other dynamic SQL statements such as PREPARE, DESCRIBE, OPEN, FETCH, and CLOSE never use the AT clause. The next example shows Method 2: EXEC SQL AT :db_name DECLARE slq_stmt STATEMENT; EXEC SQL PREPARE slq_stmt FROM :sql_string; EXEC SQL EXECUTE slq_stmt;

The following example shows Method 3: EXEC SQL AT :db_name DECLARE slq_stmt STATEMENT; EXEC SQL PREPARE slq_stmt FROM :sql_string; EXEC SQL DECLARE emp_cursor CURSOR FOR slq_stmt; EXEC SQL OPEN emp_cursor ... EXEC SQL FETCH emp_cursor INTO ... EXEC SQL CLOSE emp_cursor;

Multiple Explicit Connections You can use the AT db_name clause for multiple explicit connections, just as you would for a single explicit connection. In the following example, you connect to two non-default databases concurrently: /* declare needed host variables */


31-08-2012


char

name[10]

= "scott";

char

[10]

= "tiger";

char

db_string1[20] = "NYNON1";

char

db_string2[20] = "CHINON";

Page 46 of 55

... /* give each database connection a unique name */ EXEC SQL DECLARE DB_NAME1 DATABASE; EXEC SQL DECLARE DB_NAME2 DATABASE; /* connect to the two non-default databases */ EXEC SQL CONNECT :name IDENTIFIED BY : AT DB_NAME1 USING :db_string1; EXEC SQL CONNECT :name IDENTIFIED BY : AT DB_NAME2 USING :db_string2;

The undeclared identifiers DB_NAME1 and DB_NAME2 are used to name the default databases at the two non-default nodes so that later SQL statements can refer to the databases by name. Alternatively, you can use a host variable in the AT clause, as the following example shows: /* declare needed host variables */ char name[10] = "scott"; char [10] = "tiger"; char db_name[20]; char db_string[20]; int n_defs = 3; /* number of connections to make */ ... for (i = 0; i < n_defs; i++) { /* get next database name and SQL*Net string */ printf("Database name: "); gets(db_name); printf("SQL*Net string: "); gets(db_string); /* do the connect */ EXEC SQL CONNECT :name IDENTIFIED BY : AT :db_name USING :db_string; }

You can also use this method to make multiple connections to the same database, as the following example shows: stry(db_string, "NYNON"); for (i = 0; i < ndefs; i++) { /* connect to the non-default database */ printf("Database name: "); gets(db_name); EXEC SQL CONNECT :name IDENTIFIED BY : AT :db_name USING :db_string; } ...

You must use different database names for the connections, even though they use the same SQL*Net string. However, you can connect twice to the same database using just one database name because that name identifies the default and non-default databases. Ensuring Data Integrity Your application program must ensure the integrity of transactions that manipulate data at two or more remote databases. That is, the program must commit or roll back all SQL statements in the transactions. This might be impossible if the network fails or one of the systems crashes. For example, suppose you are working with two ing databases. You debit an on one database and credit an on the other database, then issue a COMMIT at each database. It is up to your program to ensure that both transactions are committed or rolled back.

Implicit Connections Implicit connections are ed through the Oracle distributed query facility, which does not require explicit connections, but only s the SELECT statement. A distributed query allows a single SELECT statement to access data on one or more non-default databases. The distributed query facility depends on database links, which assign a name to a CONNECT statement rather than to the connection itself. At run time, the embedded SELECT statement is executed by the specified Oracle Server, which implicitly connects to the non-default database(s) to get the required data. Single Implicit Connections In the next example, you connect to a single non-default database. First, your program executes the following statement to define a database link (database links are usually established interactively by the DBA or ): EXEC SQL CREATE DATABASE LINK db_link CONNECT TO name IDENTIFIED BY USING 'NYNON';

Then, the program can query the non-default EMP table using the database link, as follows:


31-08-2012


Page 47 of 55

EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title FROM emp@db_link WHERE DEPTNO = :dept_number;

The database link is not related to the database name used in the AT clause of an embedded SQL statement. It simply tells Oracle where the non-default database is located, the path to it, and what Oracle name and to use. The database link is stored in the data dictionary until it is explicitly dropped. In our example, the default Oracle Server logs on to the non-default database via SQL*Net using the database link db_link. The query is submitted to the default Server, but is "forwarded" to the non-default database for execution. To make referencing the database link easier, you can create a synonym as follows (again, this is usually done interactively): EXEC SQL CREATE SYNONYM emp FOR emp@db_link;

Then, your program can query the non-default EMP table, as follows: EXEC SQL SELECT ENAME, JOB INTO :emp_name, :job_title FROM emp WHERE DEPTNO = :dept_number;

This provides location transparency for emp. Multiple Implicit Connections In the following example, you connect to two non-default databases concurrently. First, you execute the following sequence of statements to define two database links and create two synonyms: EXEC SQL CREATE DATABASE LINK db_link1 CONNECT TO name1 IDENTIFIED BY 1 USING 'NYNON'; EXEC SQL CREATE DATABASE LINK db_link2 CONNECT TO name2 IDENTIFIED BY 2 USING 'CHINON'; EXEC SQL CREATE SYNONYM emp FOR emp@db_link1; EXEC SQL CREATE SYNONYM dept FOR dept@db_link2;

Then, your program can query the non-default EMP and DEPT tables, as follows: EXEC SQL SELECT ENAME, JOB, SAL, LOC FROM emp, dept WHERE emp.DEPTNO = dept.DEPTNO AND DEPTNO = :dept_number;

Oracle executes the query by performing a between the non-default EMP table at db_link1 and the non-default DEPT table at db_link2.

Embedding Oracle Call Interface (OCI) Calls To embed OCI calls in your Pro*C/C++ program, take the following steps: 1. Declare an OCI Logon Data Area (LDA) in your Pro*C/C++ program (outside the Declare Section if you precompile with MODE=ANSI). The LDA is a structure defined in the OCI header file ocidfn.h. For details, see the Programmer's Guide to the Oracle Call Interface. 2. Connect to Oracle using the embedded SQL statement CONNECT, not the OCI orlon() or onblon() calls. 3. Call the Oracle runtime library function sqllda() to set up the LDA. That way, the Pro*C Precompiler and the OCI "know" that they are working together. However, there is no sharing of Oracle cursors. You need not worry about declaring the OCI Host Data Area (HDA) because the Oracle runtime library manages connections and maintains the HDA for you.

Setting Up the LDA You set up the LDA by issuing the OCI call sqllda(&lda);

where lda identifies the LDA data structure. If the setup fails, the lda_rc field in the lda is set to 1012 to indicate the error.

Remote and Multiple Connections A call to sqllda() sets up an LDA for the connection used by the most recently executed SQL statement. To set up the different LDAs needed for additional


31-08-2012


Page 48 of 55

connections, just call sqllda() with a different lda after each CONNECT. In the following example, you connect to two non-default databases concurrently: #include Lda_Def lda1; Lda_Def lda2; char name[10], [10], db_string1[20], dbstring2[20]; ... stry(name, "scott"); stry(, "tiger"); stry(db_string1, "NYNON"); stry(db_string2, "CHINON"); /* give each database connection a unique name */ EXEC SQL DECLARE DB_NAME1 DATABASE; EXEC SQL DECLARE DB_NAME2 DATABASE; /* connect to first non-default database */ EXEC SQL CONNECT :name IDENTIFIED BY :; AT DB_NAME1 USING :db_string1; /* set up first LDA */ sqllda(&lda1); /* connect to second non-default database */ EXEC SQL CONNECT :name IDENTIFIED BY :; AT DB_NAME2 USING :db_string2; /* set up second LDA */ sqllda(&lda2);

DB_NAME1 and DB_NAME2 are not C variables; they are SQL identifiers. You use them only to name the default databases at the two non-default nodes, so that later SQL statements can refer to the databases by name.

Developing Multi-threaded Applications Multi-threaded applications have multiple threads executing in a shared address space. Threads are "lightweight" processes that execute within a process; they share data segments but have their own program counter, machine s and stack. For more detailed information about multi-threaded applications, refer to your thread-package specific reference material. The Pro*C/C++ Precompiler s development of multi-threaded Oracle7 Server applications (on platforms that multi-threaded applications) using the following: z

a command-line option to generate thread-safe code

z

embedded SQL statements and directives to multi-threading

z

thread-safe SQLLIB and other client-side Oracle libraries

Attention: If your development platform does not multi-threading, the information in this section does not apply. Note: While your platform may a particular thread package, see your platform-specific Oracle documentation to determine whether Oracle s it. The following topics discuss how to use the preceding features to develop multi-threaded Pro*C/C++ applications: z

runtime contexts for multi-threaded applications

z

two models for using runtime contexts

z

-interface features for multi-threaded applications

z

programming considerations for writing multi-threaded applications with Pro*C/C++

z

a sample multi-threaded Pro*C/C++ application

Runtime Contexts in Pro*C/C++ To loosely couple a thread and a connection, the Oracle Pro*C/C++ Precompiler introduces the notion of a runtime context. The runtime context includes the following resources and their current states: z

zero or more connections to one or more Oracle7 Servers

z

zero or more cursors used for the server connections

z

inline options, such as MODE, HOLD_CURSOR, RELEASE_CURSOR, and SELECT_ERROR

Rather than simply ing a loose coupling between threads and connections, the Pro*C/C++ Precompiler allows developers to loosely couple threads with runtime contexts. Pro*C/C++ allows an application to define a handle to a runtime context, and that handle from one thread to another. For example, an interactive application spawns a thread, T1, to execute a query and return the first 10 rows to the application. T1 then terminates. After obtaining the necessary input, another thread, T2, is spawned (or an existing thread is used) and the runtime context for T1 is ed to T2 so it can fetch the next 10 rows by processing the same cursor. Figure 3 - 2. Loosely Coupling Connections and Threads

Runtime Context Usage Models Two possible models for using runtime contexts in multi-threaded Pro*C/C++ applications are shown here:


31-08-2012


z

multiple threads sharing a single runtime context

z

multiple threads using separate runtime contexts

Page 49 of 55

Regardless of the model you use for runtime contexts, you cannot share a runtime context between multiple threads at the same time. If two or more threads attempt to use the same runtime context simultaneously, the following runtime error occurs: SQL-02131:

Runtime context in use

Multiple Threads Sharing a Single Runtime Context Figure 3 - 3 shows an application running in a multi-threaded environment. The various threads share a single runtime context to process one or more SQL statements. Again, runtime contexts cannot be shared by multiple threads at the same time; the mutexes in Figure 3 - 3 show how to prevent concurrent usage.

Figure 3 - 3. Context Sharing Among Threads Multiple Threads Sharing Multiple Runtime Contexts Figure 3 - 4 shows an application that executes multiple threads using multiple runtime contexts. In this situation, the application does not require mutexes, because each thread has a dedicated runtime context.

Figure 3 - 4. No Context Sharing Among Threads

-interface Features for Multi-threaded Applications


31-08-2012


Page 50 of 55

The Pro*C/C++ Precompiler provides the following -interface features to multi-threaded applications: z

command-line option, THREADS=YES|NO

z

embedded SQL statements and directives

z

thread-safe SQLLIB public functions

THREADS Option With THREADS=YES specified on the command line, the Pro*C/C++ Precompiler ensures that the generated code is thread-safe, given that you follow the guidelines described in "Programming Considerations" . With THREADS=YES specified, Pro*C/C++ verifies that all SQL statements execute within the scope of a -defined runtime context. If your program does not meet this requirement, the following precompiler error is returned: PCC-02390:

No EXEC SQL CONTEXT USE statement encountered

For more information about the THREADS option, see "THREADS" . Embedded SQL Statements and Directives The following embedded SQL statements and directives the definition and usage of runtime contexts and threads: z

EXEC SQL ENABLE THREADS;

z

EXEC SQL CONTEXT ALLOCATE :context_var;

z

EXEC SQL CONTEXT USE :context_var;

z

EXEC SQL CONTEXT FREE :context_var;

For these EXEC SQL statements, context_var is the handle to the runtime context and must be declared of type sql_context as follows: sql_context ;

EXEC SQL ENABLE THREADS This executable SQL statement initializes a process that s multiple threads. This must be the first executable SQL statement in your multi-threaded application. For more detailed information, see "ENABLE THREADS" . EXEC SQL CONTEXT ALLOCATE This executable SQL statement initializes and allocates memory for the specified runtime context; the runtime-context variable must be declared of type sql_context. For more detailed information, see "CONTEXT ALLOCATE" . EXEC SQL CONTEXT USE This directive instructs the precompiler to use the specified runtime context for subsequent executable SQL statements. The runtime context specified must be previously allocated using an EXEC SQL CONTEXT ALLOCATE statement. The EXEC SQL CONTEXT USE directive works similarly to the EXEC SQL WHENEVER directive in that it affects all executable SQL statements which positionally follow it in a given source file without regard to standard C scope rules. In the following example, the UPDATE statement in function2() uses the global runtime context, ctx1: sql_context ctx1;

/* declare global context ctx1

function1() { sql_context ctx1; /* declare local context ctx1 EXEC SQL CONTEXT USE :ctx1; EXEC SQL INSERT INTO ... /* local ctx1 used for this stmt ... } function2() { EXEC SQL UPDATE ... }

*/ */ */

/* global ctx1 used for this stmt */

In the next example, there is no global runtime context. The precompiler refers to the ctx1 runtime context in the generated code for the UPDATE statement. However, there is no context variable in scope for function2(), so errors are generated at compile time. function1() { sql_context ctx1; /* local context variable declared */ EXEC SQL CONTEXT USE :ctx1; EXEC SQL INSERT INTO ... /* ctx1 used for this statement */ ... } function2() { EXEC SQL UPDATE ... /* Error! No context variable in scope */ }

For more detailed information, see "CONTEXT USE" . EXEC SQL CONTEXT FREE This executable SQL statement frees the memory associated with the specified runtime context and places a null pointer in the host program variable. For more detailed information, see "CONTEXT FREE" . Examples The following code fragments show how to use embedded SQL statements and precompiler directives for two typical programming models; they use thread_create() to create threads. The first example showing multiple threads using multiple runtime contexts: main() { sql_context ctx1,ctx2; /* declare runtime contexts */ EXEC SQL ENABLE THREADS; EXEC SQL ALLOCATE :ctx1; EXEC SQL ALLOCATE :ctx2; ... /* spawn thread, execute function1 (in the thread) ing ctx1 */


31-08-2012


Page 51 of 55

thread_create(..., function1, ctx1); /* spawn thread, execute function2 (in the thread) ing ctx2 */ thread_create(..., function2, ctx2); ... EXEC SQL CONTEXT FREE :ctx1; EXEC SQL CONTEXT FREE :ctx2; ... } void function1(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* execute executable SQL statements on runtime context ctx1!!! */ ... } void function2(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* execute executable SQL statements on runtime context ctx2!!! */ ... }

The next example shows how to use multiple threads that share a common runtime context. Because the SQL statements executed in function1() and function2() potentially execute at the same time, you must place mutexes around every executable EXEC SQL statement to ensure serial, therefore safe, manipulation of the data. main() { sql_context ctx; /* declare runtime context */ EXEC SQL ALLOCATE :ctx; ... /* spawn thread, execute function1 (in the thread) ing ctx */ thread_create(..., function1, ctx); /* spawn thread, execute function2 (in the thread) ing ctx */ thread_create(..., function2, ctx); ... } void function1(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* Execute SQL statements on runtime context ctx. ... }

*/

void function2(sql_context ctx) { EXEC SQL CONTEXT USE :ctx; /* Execute SQL statements on runtime context ctx. ... }

*/

Thread-safe SQLLIB Public Functions Each standard SQLLIB public function is accompanied by a thread-safe counterpart, suffixed with a t, that accepts the runtime context as an additional argument at the beginning of the argument list. For example, the syntax for sqlglm() is: void sqlglm(char *message_buffer, size_t *buffer_size, size_t *message_length);

while the thread-safe version, sqlglmt(), is: void sqlglmt(void char size_t size_t

*context *message_buffer, *buffer_size, *message_length);

You must use the thread-safe versions of the SQLLIB functions when writing multi-threaded applications. Table 3 - 8 is a list of all the thread-safe SQLLIB public functions and their corresponding syntax. Cross-references to the related non-thread functions are provided to help you find more complete descriptions. Thread-safe Function Calls struct SQLDA *sqlaldt(void *context, int maximum_variables int maximum_name_length int maximum_ind_name_length);

Cross-reference see also sqlald()

void sqlcdat(void *context, Cda_Def *cda, void *cursor, int *return_value);

see also sqlcda()

void sqlclut(void *context, struct SQLDA *descriptor_name);

see also sqlclu()

void sqlcurt(void *context, void *cursor, Cda_Def *cda, int *return_value)

see also sqlcur()

void sqlglmt(void *context, char *message_buffer, size_t *buffer_size, size_t *message_length);

see also sqlglm()

void sqlglst(void *context, char *statement_buffer, size_t *statement_length, size_t *sqlfc);

see also sqlgls()

void sqlld2t(void *context, Lda_Def *lda, text *cname, sb4 *cname_length);

see also sqlld2()

void sqlldat(void *context, Lda_Def *lda);

see also sqllda()

void sqlnult(void *context, unsigned short *value_type, unsigned short *type_code, int *null_status);

see also sqlnul()

void sqlprct(void *context, long *length, int *precision, int *scale);

see also sqlprc()

void sqlpr2t(void *context, long *length, int *precision, int *scale);

see also sqlpr2()

void sqlvt(void *context, size_t *data_length, size_t *total_length);

see also sqlv()

Table 3 - 8. Thread-safe SQLLIB Public Functions

Attention: For the specific datatypes used in the argument lists for these functions, refer to your platform-specific sqlr.h file.


31-08-2012


Page 52 of 55

Programming Considerations While Oracle ensures that the SQLLIB code is thread-safe, you are responsible for ensuring that your Pro*C/C++ source code is designed to work properly with threads; for example, carefully consider your use of static and global variables. In addition, multi-threaded requires design decisions regarding the following: z

declaring the SQLCA as a thread-safe struct, typically an auto variable and one for each runtime context

z

declaring the SQLDA as a thread-safe struct, like the SQLCA, typically an auto variable and one for each runtime context

z

declaring host variables in a thread-safe fashion, in other words, carefully consider your use of static and global host variables.

z

avoiding simultaneous use of a runtime context in multiple threads

z

whether or not to use default database connections or to explicitly define them using the AT clause

Also, no more than one executable embedded SQL statement, for example, EXEC SQL UPDATE, may be outstanding on a runtime context at a given time. Existing requirements for precompiled applications also apply. For example, all references to a given cursor must appear in the same source file.

Example The following program is one approach to writing a multi-threaded embedded SQL application; it is written specifically for the DCE thread package and is, therefore, not portable. The program creates as many sessions as there are threads. Each thread executes zero or more transactions, that are specified in a transient structure called "records." /* * Requirements: * The program requires a table "S" to be in * the schema SCOTT/TIGER. The description of * S is: * SQL> desc s * Name Null? Type * ------------------------------- ------- -----------* NUMBER(10) * BALANCE NUMBER(12,2) * * For proper execution, the table should be filled with the * s 10001 to 1008. */ #include <stdio.h> #include <stdlib.h> #include <string.h> #ifndef ORA_PROC #include #endif #include <sqlca.h>

/* Function prototypes void err_report(); void do_transaction(); void get_transaction(); void logon(); void logoff();

*/

#define CONNINFO "SCOTT/TIGER" #define THREADS 3 struct parameters { sql_context * ctx; int thread_id; }; typedef struct parameters parameters; struct record_log { char action; unsigned int from_; unsigned int to_; float amount; }; typedef struct record_log record_log; record_log records[]= { { { { { { { { { { { { { { { { { { { { {

'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M', 'M',

10001, 10001, 10001, 10001, 10002, 10007, 10002, 10001, 10003, 10002, 10007, 10002, 10001, 10003, 10002, 10007, 10002, 10001, 10003, 10008,

10002, 10003, 10003, 10003, 10006, 10008, 10008, 10003, 10002, 10006, 10008, 10008, 10003, 10002, 10006, 10008, 10008, 10003, 10002, 10001,

12.50 }, 25.00 }, 123.00 }, 125.00 }, 12.23 }, 225.23 }, 0.70 }, 11.30 }, 47.50 }, 125.00 }, 225.00 }, 0.70 }, 11.00 }, 47.50 }, 125.00 }, 225.00 }, 0.70 }, 11.00 }, 47.50 }, 1034.54}};


31-08-2012


Page 53 of 55

static unsigned int trx_nr=0; pthread_mutex_t mutex; /* * Function: main() */ main() { sql_context ctx[THREADS]; pthread_t thread[THREADS]; parameters params[THREADS]; int i; pthread_addr_t status; /* Initialize a process in which to spawn threads. EXEC SQL ENABLE THREADS;

*/

EXEC SQL WHENEVER SQLERROR DO err_report(sqlca); /* Create THREADS sessions by connecting THREADS times, each connection in a separate runtime context. for(i=0; i
*/

/* Create mutex for transaction retrieval. if (pthread_mutex_init(&mutex,pthread_mutexattr_default)) { printf("Can't initialize mutex\n"); exit(1); }

*/

/* Spawn threads. for(i=0; i
*/

printf("Thread %d... ",i); if (pthread_create(&thread[i],pthread_attr_default, (pthread_startroutine_t)do_transaction, (pthread_addr_t) &params[i])) printf("Cant create thread %d\n",i); else printf("Created\n"); }

/* Logoff sessions. */ for(i=0;i
*/

*/

/* * Function: do_transaction() * * Description: This functions executes one transaction out of * the records array. The records array is managed * by get_transaction(). */ void do_transaction(params) parameters *params; { struct sqlca sqlca; record_log *trx; sql_context ctx; ctx = params->ctx;

/* Done all transactions ? while (trx_nr < (sizeof(records)/sizeof(record_log))) { get_transaction(&trx); EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);

*/

/* Use the specified SQL context to perform the executable SQL statements that follow. */ EXEC SQL CONTEXT USE :ctx;


31-08-2012


Page 54 of 55

printf("Thread %d executing transaction\n", params->thread_id); switch(trx->action) { case 'M': EXEC SQL UPDATE S SET BALANCE=BALANCE+:trx->amount WHERE =:trx->to_; EXEC SQL UPDATE S SET BALANCE=BALANCE-:trx->amount WHERE =:trx->from_; break; default: break; } EXEC SQL COMMIT; } } /* * Function: err_report() * * Description: This routine prints the most recent error. */ void err_report(sqlca) struct sqlca sqlca; { if (sqlca.sqlcode < 0) printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); exit(1); }

Developing X/Open Applications X/Open applications run in a distributed transaction processing (DTP) environment. In an abstract model, an X/Open application calls on resource managers (RMs) to provide a variety of services. For example, a database resource manager provides access to data in a database. Resource managers interact with a transaction manager (TM), which controls all transactions for the application.

Figure 3 - 5. Hypothetical DTP Model Figure 3 - 5 shows one way that components of the DTP model can interact to provide efficient access to data in an Oracle database. The DTP model specifies the XA interface between resource managers and the transaction manager. Oracle supplies an XA-compliant library, which you must link to your X/Open application. Also, you must specify the native interface between your application program and the resource managers. The DTP model that specifies how a transaction manager and resource managers interact with an application program is described in the X/Open guide Distributed Transaction Processing Reference Model and related publications, which you can obtain by writing to X/Open Company Ltd. 1010 El Camino Real, Suite 380 Menlo Park, CA 94025 For instructions on using the XA interface, see your Transaction Processing (TP) Monitor 's guide.

Oracle-Specific Issues You can use the precompiler to develop applications that comply with the X/Open standards. However, you must meet the following requirements. Connecting to Oracle The X/Open application does not establish and maintain connections to a database. Instead, the transaction manager and the XA interface, which is supplied by Oracle, handle database connections and disconnections transparently. So, normally an X/Open-compliant application does not execute CONNECT statements. Transaction Control The X/Open application must not execute statements such as COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION that affect the state of global transactions. For example, the application must not execute the COMMIT statement because the transaction manager handles commits. Also, the application


31-08-2012


Page 55 of 55

must not execute SQL data definition statements such as CREATE, ALTER, and RENAME because they issue an implicit COMMIT. The application can execute an internal ROLLBACK statement if it detects an error that prevents further SQL operations. However, this might change in later releases of the XA interface. OCI Calls If you want your X/Open application to issue OCI calls, you must use the runtime library routine sqlld2(), which sets up an LDA for a specified connection established through the XA interface. For a description of the sqlld2() call, see the Programmer's Guide to the Oracle Call Interface. Note that the following OCI calls cannot be issued by an X/Open application: OCOM, OCON, OCOF, ONBLON, ORLON, OLON, OLOGOF. Linking To get XA functionality, you must link the XA library to your X/Open application object modules. For instructions, see your system-specific Oracle documentation.

Prev Next

Copyright © 1997 Oracle Corporation. All Rights Reserved.


LibraryProductContents Index

31-08-2012

Proc 194h72

Overview 6h3y3j

More details h6z72

Related Documents 543cg

Proc 194h72

Proc 194h72

Linux Proc p5c5p

Proc Cdisc 1z4c2w

Gestion Proc 6o3ht

Proc-489 3o256e