Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
An example should clarify this point. Consider the following program fragment:
#include "my_header.h" ... VARCHAR name[VC_LEN]; /* a Pro*C-supplied datatype */ char another_name[VC_LEN]; /* a pure C datatype */ ...
Suppose the file my_header.h in the current directory contains, among other things, the line
#define VC_LEN 20
The precompiler reads the file my_header.h, and uses the defined value of VC_LEN (20) when it declares the structure for the VARCHAR name, replacing the defined constant VC_LEN with its value, 20.
However, the file my_header.h is not physically included in the precompiler source file by the Pro*C/C++ preprocessor. For this reason, VC_LEN in the declaration of the C char variable another_name is not replaced by the constant 20.
This does not matter, since the precompiler does not need to process declarations of C datatypes, even when they are used as host variables. It is left up to the C compiler's preprocessor to physically include the file my_header.h, and perform the substitution of 20 for VC_LEN in the declaration of another_name.
While your C compiler preprocessor may support these directives, Pro*C/C++ does not use them. Most of these directives are not used by the precompiler. You can use these directives in your Pro*C/C++ program if your compiler supports them, but only in C/C++ code, not in embedded SQL statements or declarations of variables using datatypes supplied by the precompiler, such as VARCHAR.
The following example uses the ORA_PROC macro to exclude the irrelevant.h file:
#ifndef ORA_PROC #include <irrelevant.h> #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.
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.
...
VARCHAR emp_name[10];
VARCHAR dept_loc[14];
...
...
/* much later in the code ... */
f42()
{
/* did you remember 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
#define RESEARCH_DEPT 40
...
EXEC SQL SELECT empno, sal INTO :emp_number, :salary /* host arrays */
FROM emp
WHERE deptno = RESEARCH_DEPT; /* INVALID! */
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 password IS STRING(STR_LEN) REFERENCE;
#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 supported by the Pro*C/C++ preprocessor.
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 user 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.
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.
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.
cc -DDEBUG ...
you should precompile using the DEFINE= option, namely
proc DEFINE=DEBUG ...
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.
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.
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" .
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.
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=CPP precompiler option.
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), ...);
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 support 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.
For SQL89 standards conformance, restrict the length of host variable names to 18 or fewer characters.
EXEC SQL INSERT INTO dept (deptno, dname) VALUES (50, 'PURCHAS\
ING');
In this context, the precompiler treats the backslash as a continuation character.
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.
SQL Operators | C Operator |
NOT | ! |
AND | && |
OR | || |
= | == |
Type | C Operator |
address | & |
bitwise | &, |, ^, ~ |
compound assignment | +=, -=, *=, etc. |
conditional | ?: |
decrement | -- |
increment | ++ |
indirection | * |
modulus | % |
shift | >>,<< |
EXEC SQL WHENEVER SQLERROR GOTO connect_error; ... connect_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE; printf("\nInvalid username/password\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 user's guide.
EXEC SQL DELETE FROM emp WHERE deptno = :dept_number;
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 supported. See the section ``Host Arrays'' for more information.
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.
Pointers to simple C types are supported. 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 supported.
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, ``enough" 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.
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).
char buf[15]; int 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;
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());
:host_variable INDICATOR :indicator_variable
which is equivalent to
:host_variable:indicator_variable
You can use both forms of expression in your host program.
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 about using indicator variables, see "Using Indicator Variables" .
ORA-01405: fetched column value is NULL
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" .
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 members 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;
strcpy(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 members 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;
struct
{
char emp_name[3][10];
int emp_number[3];
int dept_number[3];
} emp_rec;
...
strcpy(emp_rec.emp_name[0], "ANQUETIL");
strcpy(emp_rec.emp_name[1], "MERCKX");
strcpy(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);
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;
...
struct
{
int emp_number;
struct
{
float salary;
float commission;
} sal_info; /* INVALID */
int dept_number;
} emp_record;
...
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.
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.
/* * 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 20 #define PWD_LEN 40 /* * 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 username; asciiz password; 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. */ strcpy(username, "SCOTT"); strcpy(password, "TIGER"); EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--"); EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username); /* 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); EXEC SQL ROLLBACK RELEASE; exit(1); }
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 */
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 */
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.
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)
VALUES (:emp_number, :dept_number,
:commission INDICATOR :comm_ind);
You cannot use host arrays with the CURRENT OF clause in an UPDATE or DELETE statement.
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.
EXEC SQL BEGIN DECLARE SECTION;
int dept_number[3] = {10, 20, 30};
EXEC SQL END DECLARE SECTION;
/* * 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 *username = "SCOTT"; char *password = "TIGER"; /* Declare a host structure tag. */ struct { int emp_number[ARRAY_LENGTH]; char emp_name[ARRAY_LENGTH][NAME_LENGTH]; float salary[ARRAY_LENGTH]; } emp_rec; /* 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 :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username); EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:"); /* Declare a cursor for the FETCH. */ 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 Employee Salary"); printf("\n------ -------- ------\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); }
int *int_ptr;
char *char_ptr;
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 members 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;
} 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. */
...
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 ``address of" notation (&) is not required; in fact, it is an error to use it.
VARCHAR username[20];
into the following struct with array and length members:
struct
{
unsigned short len;
unsigned char arr[20];
} username;
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
username.arr[username.len] = '\0';
or using the length in a strncpy or printf statement; for example:
printf("Username is %.*s\n", username.len, username.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" .
...
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);
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).
VARCHAR emp_name[20];
...
emp_name.len = 20;
SELECT ename INTO :emp_name FROM emp
WHERE empno = 7499;
...
print_employee_name(&emp_name); /* pass by pointer */
...
print_employee_name(name)
VARCHAR *name;
{
...
printf("name is %.*s\n", name->len, name->arr);
...
}
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 sqlvcp() function--part of the SQLLIB runtime library--returns the actual (possibly padded) length of the array member.
You pass the sqlvcp() function the length of the data for a VARCHAR host variable or a VARCHAR pointer host variable, and sqlvcp() 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 sqlvcp() is
sqlvcp(size_t *datlen, size_t *totlen);
Put the length of the VARCHAR in the first parameter before calling sqlvcp(). 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 passed by reference.
/* * The sqlvcp.pc program demonstrates how you can use the * sqlvcp() 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 <sqlcpr.h> /* Fake a varchar pointer type. */ struct my_vc_ptr { 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 *username = "scott/tiger"; EXEC SQL END DECLARE SECTION; void sql_error(); extern void sqlvcp(), sqlgls(); main() { unsigned int vcplen, function_code, padlen, buflen; int i; char stmt_buf[120]; EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username; 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. */ vcplen = 10; /* Use SQLVCP to help find the length to malloc. */ sqlvcp(&vcplen, &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). */ buflen = (long) sizeof (stmt_buf); /* 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, 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); }
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); }
For character data, there are two ways that you can use the DBMS option:
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).
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];
...
strcpy(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
strncpy(emp_name, "MILLER ", 10); /* 4 trailing blanks */
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES
(1234, :emp_name, 20);
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 */
...
strcpy(emp_name, "MILLER"); /* No blank-padding required */
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES
(1234, :emp_name, 20);
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.
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;
...
p_name1 = (char *) malloc(11);
p_name2 = (char *) malloc(11);
strcpy(p_name1, " ");
strcpy(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" /* 4 trailing blanks and a null terminator */
"KING######\0" /* 6 blanks and null */
VARCHAR emp_name1[10]; /* VARCHAR variable */
VARCHAR *emp_name2; /* pointer to VARCHAR */
You cannot mix declarations of VARCHAR variables and VARCHAR pointers on the same line.
strcpy(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 */
strcpy(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);
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);
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 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 assigning 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 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.
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.
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.
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.
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.
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 |
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.
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 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 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.
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 assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.
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.
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.
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.
At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.
Before assigning 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 assigning 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.
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 pass 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 except the following:
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 user_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 <= 65533-byte data component. Second, use typedef to define a new datatype based on the struct. Then, equivalence your new user-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.
typedef unsigned char *my_raw;
EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE;
my_raw graphics_buffer;
...
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).
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.
/*************************************************************** 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 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. If you 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. 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 <stdio.h> #include <sys/types.h> #include <sys/file.h> #include <fcntl.h> #include <string.h> #include <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 user's selection. */ print_menu(); gets(reply); while (ok) { switch(reply[0]) { case 'I': case 'i': /* User 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': /* User 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': /* User selected list - just call the list routine. */ list_executables(); break; case 'Q': case 'q': /* User 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); 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. */ memcpy(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. */ 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, "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: "); }
Oracle provides National Language Support (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 user session. Thus, NLS allows users 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_LANG = <language>_<territory>.<character set>
where language specifies the value of NLS_LANGUAGE for the user 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_France.WE8ISO8859P1
To change the values of NLS parameters during a session, you use the ALTER SESSION statement as follows:
ALTER SESSION SET <nls_parameter> = <value>
The Pro*C Precompiler fully supports 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 pass 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 pass 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.
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.
For more information, see the documentation of the following options:
Dynamic SQL is not available for NLS multibyte character string host variables in Pro*C Release 2.1.
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 supported for NLS data, since they can be specified only by datatype equivalencing or dynamic SQL, neither of which are supported for NLS data.
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.
The advantages of cursor variables are:
EXEC SQL BEGIN DECLARE SECTION;
sql_cursor emp_cursor; /* a cursor variable */
SQL_CURSOR dept_cursor; /* another cursor variable */
sql_cursor *ecp; /* a pointer to a cursor variable */
...
EXEC SQL END DECLARE SECTION;
ecp = &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 pass 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 cursor variables.
Caution: A SQL_CURSOR is implemented as a C struct in the code that Pro*C/C++ generates. So you can always pass it by pointer to another function, or return a pointer to a cursor variable from a function. But you can only pass it or return it by value if your C compiler supports these operations.
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.
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 (;;)
{
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;
...
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 stand-alone 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;
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.
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.
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.
-- 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;
/* * 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. * */ #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 (0 to exit): "); gets(temp); dept_num = atoi(temp); if (dept_num <= 0) break; 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]; 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); }
EXEC SQL CONNECT :username IDENTIFIED BY :password;
where username and password 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 username and password 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 username and password separately, you define two host variables as character strings or VARCHARs. (If you supply a username containing both username and password, only one host variable is needed.)
Make sure to set the username and password 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 *username = "SCOTT";
char *password = "TIGER";
...
EXEC SQL WHENEVER SQLERROR ...
EXEC SQL CONNECT :username IDENTIFIED BY :password;
However, you cannot hardcode a username and password 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';
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.
OPS$username
where username is the current operating system username, and OPS$username is a valid Oracle database username. (The actual value for OPS$ is defined in the INIT.ORA parameter file.) You simply pass to the Pro*C Precompiler a slash character, as follows:
...
char oracleid = '/';
...
EXEC SQL CONNECT :oracleid;
This automatically connects you as user OPS$username. For example, if your operating system username is RHILL, and OPS$RHILL is a valid Oracle username, connecting with '/' automatically logs you on to Oracle as user OPS$RHILL.
You can also pass a '/' in a string to the precompiler. However, the string cannot contain trailing blanks. For example, the following CONNECT statement will fail:
...
char oracleid[10] = "/ ";
...
EXEC SQL CONNECT :oracleid;
OPS$<username>
where username is your current operating system user or task name and OPS$username is a valid Oracle userid.
When AUTO_CONNECT=NO, you must use the CONNECT statement in your program to connect to Oracle.
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++ supports distributed processing via SQL*Net. You learn how your application can
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
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.
EXEC SQL CONNECT :username IDENTIFIED BY :password;
You can also use
EXEC SQL CONNECT :usr_pwd;
where usr_pwd contains username/password.
You can automatically connect to Oracle with the userid
OPS$username
where username is your current operating system user or task name and OPS$username is a valid Oracle userid. You simply pass to the precompiler a slash (/) character, as follows:
char oracleid = '/';
...
EXEC SQL CONNECT :oracleid;
This automatically connects you as user OPS$username.
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.
/* declare needed host variables */
char username[10] = "scott";
char password[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 :username IDENTIFIED BY :password
AT DB_NAME USING :db_string;
The identifiers in this example serve the following purposes:
Alternatively, you can use a character host variable in the AT clause, as the following example shows:
/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_name[10] = "oracle1";
char db_string[20] = "NYNON";
/* connect to the non-default database using db_name */
EXEC SQL CONNECT :username IDENTIFIED BY :password
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 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 ...
strcpy(db_name, "oracle1");
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO ...
strcpy(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
strcpy(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;
/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_string1[20] = "NYNON1";
char db_string2[20] = "CHINON";
...
/* 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 :username IDENTIFIED BY :password
AT DB_NAME1 USING :db_string1;
EXEC SQL CONNECT :username IDENTIFIED BY :password
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 username[10] = "scott"; char password[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 :username IDENTIFIED BY :password 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:
strcpy(db_string, "NYNON"); for (i = 0; i < ndefs; i++) { /* connect to the non-default database */ printf("Database name: "); gets(db_name); EXEC SQL CONNECT :username IDENTIFIED BY :password 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 accounting databases. You debit an account on one database and credit an account 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.
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.
EXEC SQL CREATE DATABASE LINK db_link
CONNECT TO username IDENTIFIED BY password
USING 'NYNON';
Then, the program can query the non-default EMP table using the database link, as follows:
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 username and password 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.
EXEC SQL CREATE DATABASE LINK db_link1
CONNECT TO username1 IDENTIFIED BY password1
USING 'NYNON';
EXEC SQL CREATE DATABASE LINK db_link2
CONNECT TO username2 IDENTIFIED BY password2
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 join between the non-default EMP table at db_link1 and the non-default DEPT table at db_link2.
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.
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.
#include <ocidfn.h> Lda_Def lda1; Lda_Def lda2; char username[10], password[10], db_string1[20], dbstring2[20]; ... strcpy(username, "scott"); strcpy(password, "tiger"); strcpy(db_string1, "NYNON"); strcpy(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 :username IDENTIFIED BY :password; AT DB_NAME1 USING :db_string1; /* set up first LDA */ sqllda(&lda1); /* connect to second non-default database */ EXEC SQL CONNECT :username IDENTIFIED BY :password; 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.
The Pro*C/C++ Precompiler supports development of multi-threaded Oracle7 Server applications (on platforms that support multi-threaded applications) using the following:
Note: While your platform may support a particular thread package, see your platform-specific Oracle documentation to determine whether Oracle supports it.
The following topics discuss how to use the preceding features to develop multi-threaded Pro*C/C++ applications:
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 user input, another thread, T2, is spawned (or an existing thread is used) and the runtime context for T1 is passed to T2 so it can fetch the next 10 rows by processing the same cursor.
Figure 3 - 2. Loosely Coupling Connections and Threads
SQL-02131: Runtime context in use
Figure 3 - 3. Context Sharing Among Threads
Figure 3 - 4. No Context Sharing Among Threads
PCC-02390: No EXEC SQL CONTEXT USE statement encountered
For more information about the THREADS option, see "THREADS" .
sql_context <context_variable>;
EXEC SQL ENABLE THREADS
This executable SQL statement initializes a process that supports 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) passing ctx1 */ thread_create(..., function1, ctx1); /* spawn thread, execute function2 (in the thread) passing 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) passing ctx */ thread_create(..., function1, ctx); /* spawn thread, execute function2 (in the thread) passing 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. */ ... }
void sqlglm(char *message_buffer, size_t *buffer_size, size_t *message_length);
while the thread-safe version, sqlglmt(), is:
void sqlglmt(void *context char *message_buffer, size_t *buffer_size, size_t *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.
Attention: For the specific datatypes used in the argument lists for these functions, refer to your platform-specific sqlcpr.h file.
In addition, multi-threaded requires design decisions regarding the following:
Existing requirements for precompiled applications also apply. For example, all references to a given cursor must appear in the same source file.
/* * Requirements: * The program requires a table "ACCOUNTS" to be in * the schema SCOTT/TIGER. The description of * ACCOUNTS is: * SQL> desc accounts * Name Null? Type * ------------------------------- ------- ------------ * ACCOUNT NUMBER(10) * BALANCE NUMBER(12,2) * * For proper execution, the table should be filled with the * accounts 10001 to 1008. */ #include <stdio.h> #include <stdlib.h> #include <string.h> #ifndef ORA_PROC #include <pthread.h> #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_account; unsigned int to_account; float amount; }; typedef struct record_log record_log; record_log records[]= { { 'M', 10001, 10002, 12.50 }, { 'M', 10001, 10003, 25.00 }, { 'M', 10001, 10003, 123.00 }, { 'M', 10001, 10003, 125.00 }, { 'M', 10002, 10006, 12.23 }, { 'M', 10007, 10008, 225.23 }, { 'M', 10002, 10008, 0.70 }, { 'M', 10001, 10003, 11.30 }, { 'M', 10003, 10002, 47.50 }, { 'M', 10002, 10006, 125.00 }, { 'M', 10007, 10008, 225.00 }, { 'M', 10002, 10008, 0.70 }, { 'M', 10001, 10003, 11.00 }, { 'M', 10003, 10002, 47.50 }, { 'M', 10002, 10006, 125.00 }, { 'M', 10007, 10008, 225.00 }, { 'M', 10002, 10008, 0.70 }, { 'M', 10001, 10003, 11.00 }, { 'M', 10003, 10002, 47.50 }, { 'M', 10008, 10001, 1034.54}}; 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<THREADS; i++) { printf("Start Session %d....",i); EXEC SQL CONTEXT ALLOCATE :ctx[i]; logon(ctx[i],CONNINFO); } /* 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<THREADS; i++) { params[i].ctx=ctx[i]; params[i].thread_id=i; printf("Thread %d... ",i); if (pthread_create(&thread[i],pthread_attr_default, (pthread_startroutine_t)do_transaction, (pthread_addr_t) ¶ms[i])) printf("Cant create thread %d\n",i); else printf("Created\n"); }
/* Logoff sessions. */ for(i=0;i<THREADS;i++) { printf("Thread %d ....",i); /* waiting for thread to end */ if (pthread_join(thread[i],&status)) printf("Error waiting for thread % to terminate\n", i); else printf("stopped\n"); printf("Detach thread..."); if (pthread_detach(&thread[i])) printf("Error detaching thread! \n"); else printf("Detached!\n"); printf("Stop Session %d....",i); logoff(ctx[i]); EXEC SQL CONTEXT FREE :ctx[i]; } /* Destroy mutex. */ if (pthread_mutex_destroy(&mutex)) { printf("Can't destroy mutex\n"); exit(1); } } /* end main() */ /* * 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; printf("Thread %d executing transaction\n", params->thread_id); switch(trx->action) { case 'M': EXEC SQL UPDATE ACCOUNTS SET BALANCE=BALANCE+:trx->amount WHERE ACCOUNT=:trx->to_account; EXEC SQL UPDATE ACCOUNTS SET BALANCE=BALANCE-:trx->amount WHERE ACCOUNT=:trx->from_account; 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); }
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 user's guide.
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.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |