4 Sample Programs

This chapter provides several embedded SQL programs to guide you in writing your own. These programs illustrate the key concepts and features of Pro*PL/1 programming and demonstrate techniques that let you take full advantage of SQL's power and flexibility.

4.1 Sample Programs

Each sample program in this chapter is available online. The following table shows the usual filenames of the sample programs. However, the exact filename and storage location of the online files can be system dependent. Check the Oracle installation or user's guide for your system.

File Name	Demonstrates
SAMPLE1.PPL	a simple query
SAMPLE2.PPL	cursor operations
SAMPLE3.PPL	array fetches
SAMPLE4.PPL	datatype equivalencing
SAMPLE5.PPL	a SQL*Forms user exit
SAMPLE6.PPL	dynamic SQL Method 1
SAMPLE7.PPL	dynamic SQL Method 2
SAMPLE8.PPL	dynamic SQL Method 3
SAMPLE9.PPL	calling a stored procedure

4.2 Sample Program 1: Login and Query

/*****************************************************************
This program connects to Oracle, prompts the user for an employee
number, queries the database for the employee's name, salary, 
and commission, then displays the result. It continues until 
the user enters a 0 for the employee number.
*****************************************************************/

QUERYEX: PROCEDURE OPTIONS(MAIN);

EXEC SQL BEGIN DECLARE SECTION;
 DCL USERNAME CHAR(10) VARYING,
 PASSWORD CHAR(10) VARYING,
 EMP_NUMBER BIN FIXED(31),
 EMP_NAME CHAR(10) VARYING,
 SALARY DECIMAL FLOAT(6),
 COMMISSION DECIMAL FLOAT(6);
EXEC SQL END DECLARE SECTION;

 DCL TOTAL BIN FIXED(31);

EXEC SQL INCLUDE SQLCA;

/* log in to Oracle */

USERNAME = 'SCOTT';
PASSWORD = 'TIGER';

EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP EDIT('Connected to Oracle as user: ',USERNAME)(A, A);

TOTAL = 0;

LOOP: DO WHILE (1=1);

 PUT SKIP(2) LIST('Enter employee number (0 to exit): ');
 GET LIST(EMP_NUMBER);
 IF (EMP_NUMBER = 0)
 THEN LEAVE LOOP;

 EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;

 EXEC SQL SELECT ENAME, SAL, NVL(COMM,0)
 INTO :EMP_NAME, :SALARY, :COMMISSION
 FROM EMP
 WHERE EMPNO = :EMP_NUMBER;

 PUT SKIP(2) LIST('Employee Name Salary Commission');
 PUT SKIP LIST('------------- ------ ----------');
 PUT SKIP EDIT(EMP_NAME, SALARY, COMMISSION)
 (A(13), X(2), F(7,2), X, F(9,2));

 TOTAL = TOTAL + 1;
 GOTO LOOP;

 NOTFND:
 PUT SKIP LIST('Not a valid employee number - try again.');

 END;

 PUT SKIP(2) LIST('Total number queried was ', TOTAL, '.');
 PUT SKIP(2) LIST('Have a good day.');

 EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */
 STOP;

SQLERR: PROCEDURE;

 EXEC SQL WHENEVER SQLERROR CONTINUE;

 PUT SKIP(2) LIST('Oracle error detected:');
 PUT SKIP(2) LIST(SQLCA.SQLERRM);

 EXEC SQL ROLLBACK WORK RELEASE;
 STOP;

END SQLERR;

END QUERYEX;

4.3 Sample Program 2: Using a Cursor

/*****************************************************************
This program logs on to Oracle, declares and opens a cursor,
fetches the names, salaries, and commissions of all salespeople,
displays the results, then closes the cursor.
*****************************************************************/

CURSDEM: PROCEDURE OPTIONS(MAIN);

EXEC SQL BEGIN DECLARE SECTION;
 DCL USERNAME CHAR(10) VARYING,
 PASSWORD CHAR(10) VARYING,
 EMP_NAME CHAR(10) VARYING,
 SALARY DECIMAL FLOAT(6),
 COMMISSION DECIMAL FLOAT(6);
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;

/* log in to Oracle */

USERNAME = 'SCOTT';
PASSWORD = 'TIGER';

EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A);

/* Establish the cursor. */

EXEC SQL DECLARE salespeople CURSOR FOR
 SELECT ENAME, SAL, COMM
 FROM EMP
 WHERE JOB LIKE 'SALES%';

EXEC SQL OPEN salespeople;

PUT SKIP(2) LIST('Employee Name Salary Commission');
PUT SKIP LIST('------------- ------ ----------');

LOOP: DO WHILE (1 = 1);

 EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;

 EXEC SQL FETCH salespeople
 INTO :EMP_NAME, :SALARY, :COMMISSION;

 PUT SKIP EDIT(EMP_NAME, SALARY, COMMISSION)
 (A(13), X(2), F(7,2), X(1), F(9,2));
 GOTO LOOP;

 NOTFND: LEAVE LOOP;

 END;

 EXEC SQL CLOSE salespeople;
 PUT SKIP(2) LIST('Have a good day.');

 EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */
 STOP;

SQLERR: PROCEDURE;

 EXEC SQL WHENEVER SQLERROR CONTINUE;

 PUT SKIP(2) LIST('Oracle error detected:');
 PUT SKIP(2) LIST(SQLCA.SQLERRM);

 EXEC SQL ROLLBACK WORK RELEASE;
 STOP;

END SQLERR;

END CURSDEM;

4.4 Sample Program 3: Fetching in Batches

/**************************************************************
This program logs on to Oracle, declares and opens a cursor,
fetches in batches using arrays, and prints the results using
the function print_rows().
**************************************************************/

ARRDEM: PROCEDURE OPTIONS(MAIN);

EXEC SQL BEGIN DECLARE SECTION;
 DCL USERNAME CHAR(10) VARYING,
 PASSWORD CHAR(10) VARYING,
 EMP_NAME(5) CHAR(10) VARYING,
 EMP_NUMBER(5) BIN FIXED(31),
 SALARY(5) DECIMAL FLOAT(6);
EXEC SQL END DECLARE SECTION;

DCL NUM_RET BIN FIXED(31);

EXEC SQL INCLUDE SQLCA;

/* log in to Oracle */

USERNAME = 'SCOTT';
PASSWORD = 'TIGER';

EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A);

/* Establish the cursor. */

EXEC SQL DECLARE c1 CURSOR FOR
 SELECT EMPNO, ENAME, SAL FROM EMP;

EXEC SQL OPEN c1;

NUM_RET = 0; /* initialize number of rows returned */

LOOP: DO WHILE(1 = 1); /* terminate when NOT FOUND is raised */

 EXEC SQL WHENEVER NOT FOUND GOTO NOTFND;
 EXEC SQL FETCH c1 INTO :EMP_NUMBER, :EMP_NAME, :SALARY;

 CALL PRINT_ROWS(SQLCA.SQLERRD(3) - NUM_RET);
 NUM_RET = SQLCA.SQLERRD(3);

 END;

NOTFND:

 /* Print remaining rows from last fetch, if any. */
 IF ((SQLCA.SQLERRD(3) - NUM_RET) >> 0) THEN
 CALL PRINT_ROWS(SQLCA.SQLERRD(3) - NUM_RET);

 EXEC SQL CLOSE c1;
 PUT SKIP(2) LIST('Have a good day.');

 EXEC SQL COMMIT WORK RELEASE; /* log off Oracle */
 STOP;

PRINT_ROWS: PROCEDURE(N);

 DCL (N,I) BIN FIXED (31);

 PUT SKIP;
 PUT SKIP(2) LIST('Employee Number Employee Name Salary');
 PUT SKIP LIST('--------------- ------------- ------');

 DO I = 1 TO N BY 1;
 PUT SKIP EDIT(EMP_NUMBER(I), EMP_NAME(I), SALARY(I))
 (F(4), X(13), A(13), X(2), F(7,2));
 END;

END PRINT_ROWS;

SQLERR: PROCEDURE;

 EXEC SQL WHENEVER SQLERROR CONTINUE;

 PUT SKIP LIST('Oracle error detected:');
 PUT SKIP(2) LIST(SQLCA.SQLERRM);

 EXEC SQL ROLLBACK RELEASE;
 STOP;

END SQLERR;

END ARRDEM;

4.5 Sample Program 4: Datatype Equivalencing

/**************************************************************
This program features an in-depth example of the use of
Datatype Equivalencing. After logging in, it creates a new
table in the SCOTT account, IMAGE, and simulates placement of
bitmap images of employees in it. Later, when an employee
number is entered, his/her bitmap is selected back out of the
IMAGE table, and pseudo-displayed on the terminal screen.
**************************************************************/

DTYEQV: PROCEDURE OPTIONS(MAIN);

EXEC SQL BEGIN DECLARE SECTION;

 DCL USERNAME CHAR(10) VARYING,
 PASSWORD CHAR(10) VARYING,

 EMP_NUMBER BIN FIXED(31),
 EMP_NAME CHAR(10) VARYING,
 SALARY DECIMAL FLOAT(6),
 COMMISSION DECIMAL FLOAT(6);

 DCL BUFFER CHAR(8192);
 EXEC SQL VAR BUFFER IS LONG RAW;
 DCL SELECTION BIN FIXED(31);

EXEC SQL END DECLARE SECTION;

 DCL REPLY CHAR(10) VARYING;
EXEC SQL INCLUDE SQLCA;

/* log in to Oracle */

USERNAME = 'SCOTT';
PASSWORD = 'TIGER';

EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP EDIT('Connected to Oracle as user: ', USERNAME)(A, A);

PUT SKIP(2)
 LIST('Program is about to drop the IMAGE table - OK [y/N]? ');
GET EDIT(REPLY)(A(1));
IF ((REPLY ^= 'Y') & (REPLY ^= 'y')) THEN CALL SIGNOFF;

EXEC SQL WHENEVER SQLERROR CONTINUE;

EXEC SQL DROP TABLE IMAGE;

IF (SQLCA.SQLCODE = 0) THEN
 PUT SKIP(2)
 LIST('Table IMAGE has been dropped - creating new table.');
ELSE IF (SQLCA.SQLCODE = -942) THEN
 PUT SKIP(2)
 LIST('Table IMAGE does not exist - creating new table.');
ELSE CALL SQLERR;

EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

EXEC SQL CREATE TABLE IMAGE
 (empno NUMBER(4) NOT NULL, bitmap LONG RAW);

EXEC SQL DECLARE EMPCUR CURSOR FOR
 SELECT EMPNO, ENAME FROM EMP;

EXEC SQL OPEN EMPCUR;

PUT SKIP(2)
 LIST('INSERTing bitmaps into IMAGE for all employees ...');
PUT SKIP;

GLOOP: DO WHILE (1 = 1);

 EXEC SQL WHENEVER NOT FOUND GOTO GNOTFND;

 EXEC SQL FETCH EMPCUR INTO :EMP_NUMBER, :EMP_NAME;

 PUT SKIP EDIT('Employee ', EMP_NAME)(A, A(10));
 CALL GETIMG(EMP_NUMBER, BUFFER);
 EXEC SQL INSERT INTO IMAGE VALUES (:EMP_NUMBER, :BUFFER);
 PUT EDIT(' is done!')(A);

 GOTO GLOOP;

 GNOTFND: LEAVE GLOOP;

 END;

EXEC SQL CLOSE EMPCUR;
EXEC SQL COMMIT WORK;

PUT SKIP(2)
 LIST('Done INSERTing bitmaps. Next, lets display some.');

SLOOP: DO WHILE (1 = 1);

 PUT SKIP(2) LIST('Enter employee number (0 to exit): ');
 GET LIST(SELECTION);
 IF (SELECTION = 0) THEN CALL SIGNOFF;

 EXEC SQL WHENEVER NOT FOUND GOTO SNOTFND;

 EXEC SQL SELECT EMP.EMPNO, ENAME, SAL, NVL(COMM,0), BITMAP
 INTO :EMP_NUMBER, :EMP_NAME, :SALARY, :COMMISSION, :BUFFER
 FROM EMP, IMAGE
 WHERE EMP.EMPNO = :SELECTION AND EMP.EMPNO = IMAGE.EMPNO;

 CALL SHWIMG(BUFFER);

 PUT SKIP(2) EDIT('Employee ', EMP_NAME)(A, A(10));
 PUT EDIT(' has salary ', SALARY)(A, F(7,2));
 PUT EDIT(' and commission ', COMMISSION)(A, F(7,2));

 GOTO SLOOP;

 SNOTFND:
 PUT SKIP LIST('Not a valid employee number - try again.');

 END;

STOP;

GETIMG: PROCEDURE(ENUM, BUF);

 DCL ENUM BIN FIXED(31),
 BUF CHAR(8192);
 DCL I BIN FIXED(31);

DO I=1 TO 8192 BY 1;
 SUBSTR(BUF,I,1) = '*';
 IF (MOD(I,256) = 0) THEN PUT EDIT('.')(A);
END;

END GETIMG;

SHWIMG: PROCEDURE(BUF);

 DCL BUF CHAR(8192);
 DCL I BIN FIXED(31);

PUT SKIP;
DO I=1 TO 10 BY 1;
 PUT SKIP LIST(' *************************');
END;

END SHWIMG;

SIGNOFF: PROCEDURE;

 PUT SKIP(2) LIST('Have a good day.');

 EXEC SQL COMMIT WORK RELEASE;
 STOP;

END SIGNOFF;

SQLERR: PROCEDURE;

 EXEC SQL WHENEVER SQLERROR CONTINUE;

 PUT SKIP(2) LIST('Oracle error detected:');
 PUT SKIP(2) LIST(SQLCA.SQLERRM);

 EXEC SQL ROLLBACK WORK RELEASE;
 STOP;

END SQLERR;

END DTYEQV;

4.6 Sample Program 5: A SQL*Forms User Exit

This user exit concatenates form fields. To call the user exit from a SQL*Forms trigger, use the syntax

user_exit('CONCAT field1, field2, ..., result_field');

where user_exit is a packaged procedure supplied with SQL*Forms and CONCAT is the name of the user exit. A sample form named CONCAT invokes the user exit. For more information about SQL*Forms user exits, see Chapter 10 in the Programmer's Guide to the Oracle Precompilers.

Note:

The sample code listed is for a SQL*Forms user exit and is not intended to be compiled in the same manner as the other sample programs listed in this chapter.

CONCAT: 

PROCEDURE(CMD,CMDLEN,MSG,MSGLEN,QUERY) RETURNS(FIXED BINARY(31)); 

EXEC SQL BEGIN DECLARE SECTION; 
 DCL FIELD CHARACTER(81) VARYING, 
 VALUE CHARACTER(81) VARYING, 
 OUTVALUE CHARACTER(241) VARYING; 
EXEC SQL END DECLARE SECTION; 
EXEC SQL INCLUDE SQLCA; 
EXEC SQL WHENEVER SQLERROR GOTO ER_EXIT; 

/* parameter declarations */ 
DCL CMD CHAR(80),
 MSG CHAR(80),
 CMDLEN FIXED BIN(31),
 MSGLEN FIXED BIN(31),
 QUERY FIXED BIN(31),

/* local declarations */ 
 CMDCNT FIXED BIN(31),
 I FIXED BIN(31),

/* local copy of cmd */ 
 LOCCMD CHAR(80),

/* dynamically built error message to be 
 returned to SQL*Forms */
 ERRMSG CHAR(80),
 ERRLEN FIXED BIN(31); 


/* strip off "concat" keyword in the command string */
LOCCMD = SUBSTR(CMD, 8, CMDLEN-7);
OUTVALUE = ''; 

I = INDEX(LOCCMD,','); 
DO WHILE(I >> 0); /* found field delimited by (,) */ 
 FIELD = SUBSTR(LOCCMD, 1, I-1); /* field name minus (,) */ 
 EXEC IAF GET :FIELD INTO :VALUE; 
 OUTVALUE = OUTVALUE || VALUE; 
/* skip over (,) and following blank space */
 CMDCNT = I + 2;
/* take previous field off command line */ 
 LOCCMD = SUBSTR(LOCCMD, CMDCNT, CMDLEN-I); 
 I = INDEX(LOCCMD, ','); 
END; 
I = INDEX(LOCCMD, ' '); 
/* get last field concat */ 
FIELD = SUBSTR(LOCCMD, 1, I-1); 
EXEC IAF PUT :FIELD VALUES (:OUTVALUE); 

RETURN(SQL_IAPXIT_SUCCESS); 

ER_EXIT: 
ERRMSG = 'CONCAT: ' || SQLCA.SQLERRM;
ERRLEN = 80; 
CALL SQLIEM(ADDR(ERRMSG), ADDR(ERRLEN)); 
RETURN(SQL_IAPXIT_FAILURE); 

END CONCAT;

4.7 Sample Program 6: Dynamic SQL Method 1

Dynamic SQL Method 1 executes a SQL statement contained in a host character string that is constructed at runtime. The statement must not be a SELECT and must not contain input or output host variables. Method 1 has only one step:

EXEC SQL EXECUTE IMMEDIATE {:string_var | 'string_literal'};

This program demonstrates the use of dynamic SQL Method 1 to create a table, insert a row, commit the insert, and drop the table. It accesses Oracle through the SCOTT/TIGER account. It does not require user input or existing database tables. The program displays the SQL statements before their execution.

The program is available online in the file Sample6.

DYN1DEM: PROCEDURE OPTIONS(MAIN);

/* Include the SQL Communications Area, a structure
 through which Oracle makes runtime status information
 such as error codes, warning flags, and diagnostic text
 available to the host program. */

EXEC SQL INCLUDE SQLCA; 

/* Include the Oracle Communications Area, a structure
 through which Oracle makes additional runtime status
 information available to the program. */ 

EXEC SQL INCLUDE ORACA; 

/* The ORACA=YES option must be specified to enable use
 of the ORACA. */ 

EXEC Oracle OPTION (ORACA=YES); 

/* Specifying the RELEASE_CURSOR=YES option instructs
 Pro*PL/1 to release resources associated with embedded
 SQL statements after they are executed. 
 This ensures that Oracle does not keep parse locks
 on tables after DML operations, so that subsequent DDL
 operations on those tables do not result in a
 "resource locked" Oracle run-time error. */ 


EXEC Oracle OPTION (RELEASE_CURSOR=YES); 

/* All host variables used in embedded SQL must appear
 in the DECLARE SECTION. */ 

EXEC SQL BEGIN DECLARE SECTION; 
DCL USERNAME CHAR(10) VARYING, 
 PASSWORD CHAR(10) VARYING, 
 SQLSTMT CHAR(80) VARYING; 
EXEC SQL END DECLARE SECTION; 

/* Branch to label 'SQL_ERR' if an Oracle error occurs. */ 

EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; 

/* Save text of current SQL statement in the ORACA if
 an error occurs. */ 

ORACA.ORASTXTF = 1; 

/* Connect to Oracle. */ 
USERNAME = 'SCOTT'; 
PASSWORD = 'TIGER'; 
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP LIST('CONNECTED TO Oracle.'); 

/* Execute a string literal to create the table. */
PUT SKIP LIST('CREATE TABLE DYN1 (COL1 CHAR(4))'); 
EXEC SQL EXECUTE IMMEDIATE 'CREATE TABLE DYN1 (COL1 CHAR(4))';

/* Assign a SQL statement to the character string
 SQLSTMT. */ 
SQLSTMT = 'INSERT INTO DYN1 VALUES (''TEST'')';
PUT SKIP LIST(SQLSTMT); 

/* Execute sqlstmt to insert a row. This usage is
 "dynamic" because the SQL statement is a string
 variable whose contents the program may determine
 at runtime. */ 

EXEC SQL EXECUTE IMMEDIATE :SQLSTMT; 

/* Commit the insert. */ 
EXEC SQL COMMIT WORK;

/* Change sqlstmt and execute it to drop the table. */
SQLSTMT = 'DROP TABLE DYN1'; 
PUT SKIP LIST(SQLSTMT); 
EXEC SQL EXECUTE IMMEDIATE :SQLSTMT; 

/* Commit any outstanding changes and disconnect from
 Oracle. */ 

EXEC SQL COMMIT RELEASE; 
PUT SKIP LIST('DISCONNECTED FROM Oracle.'); 
STOP; 

SQL_ERR: 

/* Oracle error handler. Print diagnostic text
 containing error message, current SQL statement,
 line number and file name of error. */ 

PUT SKIP(2) LIST(SQLCA.SQLERRM); 
PUT SKIP EDIT('IN "', ORACA.ORASTXT, '..."') 
 (A, A(LENGTH(ORACA.ORASTXT)), A); 
PUT SKIP EDIT('ON LINE ', ORACA.ORASLNR, ' OF ', ORACA.ORASFNM) 
 (A, F(3), A, A(LENGTH(ORACA.ORASFNM))); 

/* Disable Oracle error checking to avoid an infinite
 loop should another error occur within this routine. */

EXEC SQL WHENEVER SQLERROR CONTINUE; 

/* Roll back any outstanding changes and disconnect
 from Oracle. */ 

EXEC SQL ROLLBACK RELEASE; 

END DYN1DEM;

4.8 Sample Program 7: Dynamic SQL Method 2

Dynamic SQL Method 2 processes a SQL statement contained in a host character string constructed at runtime. The statement must not be a SELECT but may contain input host variables. Method 2 has two steps:

EXEC SQL PREPARE statement_name FROM
 { :string_var | 'string_literal' };

EXEC SQL EXECUTE statement_name
 [USING :invar1[, :invar2...]];

This program demonstrates the use of dynamic SQL Method 2 to insert two rows into the EMP table and then delete them. It accesses Oracle through the SCOTT/TIGER account and requires the EMP table. It does not require user input. The program displays the SQL statements before their execution.

This program is available online in the file Sample7.

DYN2DEM: PROCEDURE OPTIONS(MAIN); 

/* Include the SQL Communications Area, a structure
 through which Oracle makes runtime status information
 such as error codes, warning flags, and 
 diagnostic text available to the program. */ 

EXEC SQL INCLUDE SQLCA; 

/* All host variables used in embedded SQL must
 appear in the DECLARE SECTION. */ 

EXEC SQL BEGIN DECLARE SECTION; 
 DCL USERNAME CHAR(10) VARYING, 
 PASSWORD CHAR(10) VARYING, 
 SQLSTMT CHAR(80) VARYING, 
 EMPNO FIXED DECIMAL(4) INIT(1234), 
 DEPTNO1 FIXED DECIMAL(2) INIT(97), 
 DEPTNO2 FIXED DECIMAL(2) INIT(99); 
EXEC SQL END DECLARE SECTION; 

/* Branch to label 'sqlerror' if an Oracle error
 occurs. */ 

EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; 

/* Connect to Oracle. */ 
USERNAME = 'SCOTT'; 
PASSWORD = 'TIGER'; 
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
PUT SKIP LIST('CONNECTED TO Oracle.'); 

/* Assign a SQL statement to the character string
 SQLSTMT. Note that the statement contains two
 host variable placeholders, V1 and V2, for which
 actual input host variables must be supplied at the
 EXECUTE (following code). */ 

SQLSTMT = 'INSERT INTO EMP (EMPNO, DEPTNO) VALUES(:V1, :V2)'; 

/* Display the SQL statement and the values to be used for
 its input host variables. */ 

PUT SKIP LIST(SQLSTMT); 
PUT SKIP LIST(' V1 = ', EMPNO, ', V2 = ', DEPTNO1);

/* The PREPARE statement associates a statement name
 with a string containing a SQL statement.
 The statement name is a SQL identifier, not a host 
 variable, and therefore does not appear in the
 DECLARE SECTION. 
 A single statement name may be PREPAREd more than
 once, optionally FROM a different string variable. */ 

EXEC SQL PREPARE S FROM :SQLSTMT; 

/* The EXECUTE statement performs a PREPAREd SQL
 statement USING the specified input host variables,
 which are substituted positionally for placeholders
 in the PREPAREd statement. For each occurrence of
 a placeholder in the statement there must be a
 variable in the USING clause, that is if a placeholder
 occurs multiple times in the statement then the
 corresponding variable must appear multiple times
 in the USING clause. The USING clause may be 
 omitted only if the statement contains no placeholders.
 A single PREPAREd statement may be EXECUTEd more
 than once, optionally USING different
 input host variables. */

EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO1; 

/* Increment empno and display new input host 
 variables. */ 

EMPNO = EMPNO + 1; 
PUT SKIP LIST(' V1 = ', EMPNO, ', V2 = ', DEPTNO2);

/* ReEXECUTE S to insert the new value of EMPNO and a
 different input host variable, DEPTNO2. A rePREPARE
 is not necessary. */

EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO2; 

/* Assign a new value to sqlstmt. */ 

SQLSTMT = 'DELETE FROM EMP WHERE DEPTNO = :V1 OR DEPTNO = :V2';

/* Display the new SQL statement and the values to
 be used for its current input host variables. */ 

PUT SKIP LIST(SQLSTMT); 
PUT SKIP LIST(' V1 = ', DEPTNO1, ', V2 = ', DEPTNO2);

/* RePREPARE S FROM the new sqlstmt. */ 

EXEC SQL PREPARE S FROM :SQLSTMT; 

/* EXECUTE the new S to delete the two rows previously
 inserted. */ 

EXEC SQL EXECUTE S USING :DEPTNO1, :DEPTNO2; 

/* Commit any outstanding changes and disconnect from
 Oracle. */ 

EXEC SQL COMMIT RELEASE; 
PUT SKIP LIST('Disconnected from Oracle.'); 
STOP; 

SQL_ERR: 

/* Oracle error handler. */

PUT SKIP(2) LIST(SQLCA.SQLERRM); 

/* Disable Oracle error checking to avoid an
 infinite loop should another error occur
 within this routine. */ 

EXEC SQL WHENEVER SQLERROR CONTINUE; 

/* Roll back any outstanding changes and disconnect
 from Oracle. */ 

EXEC SQL ROLLBACK RELEASE; 

END DYN2DEM;

4.9 Sample Program 8: Dynamic SQL Method 3

Dynamic SQL Method 3 processes a SQL statement contained in a host character string constructed at runtime. The statement may be a SELECT, and may contain input host variables but not output host variables (the INTO clause is on the FETCH instead). This Dynamic SQL Method 3 example processes a query, and uses the following five steps:

EXEC SQL PREPARE statement_name
 FROM { :string_var | 'string_literal' };

EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;

EXEC SQL OPEN cursor_name [USING :invar1[,:invar2...]];

EXEC SQL FETCH cursor_name INTO :outvar1[,:outvar2...];

EXEC SQL CLOSE cursor_name;

This program demonstrates the use of dynamic SQL Method 3 to retrieve all the names from the EMP table. It accesses Oracle through the SCOTT/TIGER account and requires the EMP table. It does not require user input. The program displays the query and its results

The program is available online in the file Sample8.

DYN3DEM: PROCEDURE OPTIONS(MAIN); 

/* Include the SQL Communications Area, a structure
 through which Oracle makes runtime status
 information such as error codes, warning flags, and
 diagnostic text available to the program. */ 

EXEC SQL INCLUDE SQLCA; 

/* All host variables used in embedded SQL must appear
 in the DECLARE SECTION. */ 

EXEC SQL BEGIN DECLARE SECTION; 
 DCL USERNAME CHAR(10) VARYING, 
 PASSWORD CHAR(10) VARYING, 
 SQLSTMT CHAR(80) VARYING, 
 ENAME CHAR(10) VARYING, 
 DEPTNO FIXED DECIMAL(2) INIT(10); 
EXEC SQL END DECLARE SECTION; 


/* Branch to label SQL_ERR: if an Oracle error
 occurs. */ 

EXEC SQL WHENEVER SQLERROR GOTO SQL_ERR; 

/* Connect to Oracle. */ 

USERNAME = 'SCOTT'; 
PASSWORD = 'TIGER'; 
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; 
PUT SKIP LIST('CONNECTED TO Oracle.'); 

/* Assign a SQL query to the character string SQLSTMT.
 Note that the statement contains one host variable
 placeholder, V1, for which an actual input 
 host variable must be supplied at the OPEN
 (see following). */ 

SQLSTMT = 'SELECT ENAME FROM EMP WHERE DEPTNO = :V1'; 

/* Display the SQL statement and the value to be used
 for its current input host variable. */ 

PUT SKIP LIST(SQLSTMT); 
PUT SKIP LIST(' V1 = ', DEPTNO); 

/* The PREPARE statement associates a statement
 name with a string containing an SQL statement.
 The statement name is a SQL identifier, not a host 
 variable, and therefore does not appear in the
 DECLARE SECTION. A single statement name may be
 PREPAREd more than once, optionally FROM a
 different string variable. */ 

EXEC SQL PREPARE S FROM :SQLSTMT; 

/* The DECLARE statement associates a cursor with a
 PREPAREd statement. The cursor name, like the
 statement name, does not appear in the DECLARE
 SECTION. A single cursor name may not be DECLAREd
 more than once. */ 


EXEC SQL DECLARE C CURSOR FOR S; 

/* The OPEN statement evaluates the active set of the
 PREPAREd query USING the specified input host
 variables, which are substituted positionally for 
 placeholders in the PREPAREd query. For each
 occurrence of a placeholder in the statement there
 must be a variable in the USING clause. That is, if
 a placeholder occurs multiple times in the statement
 then the corresponding variable must appear multiple
 times in the USING clause. The USING clause may be 
 omitted only if the statement contains no placeholders. 
 OPEN places the cursor at the first row of the active
 set in preparation for a FETCH. 

 A single DECLAREd cursor may be OPENed more than
 once, optionally USING different input host variables. 
*/ 

EXEC SQL OPEN C USING :DEPTNO; 

/* Branch to label 'notfound' when all rows have been
 retrieved. */ 

EXEC SQL WHENEVER NOT FOUND GOTO N_FND; 

/* Loop until NOT FOUND condition is raised. */

DO WHILE (1 = 1); 

/* The FETCH statement places the SELECT list of the
 current row into the variables specified by the INTO
 clause then advances the cursor to the next row.
 If there are more SELECT list fields than output
 host variables, the extra fields will not be returned.
 More output host variables than SELECT list fields
 will result in an Oracle error. */ 

 EXEC SQL FETCH C INTO :ENAME; 
 PUT SKIP LIST(ENAME); 
END; 

N_FND: 

/* Print the cumulative number of rows processed by the
 current SQL statement. */ 

PUT SKIP LIST('QUERY RETURNED ', SQLCA.SQLERRD(3), ' ROW(S).');

/* The CLOSE statement releases resources associated
 with the cursor. */ 

EXEC SQL CLOSE C; 

/* Commit any outstanding changes and disconnect from
 Oracle. */ 

EXEC SQL COMMIT RELEASE; 
PUT SKIP LIST('DISCONNECTED FROM Oracle.'); 
STOP; 

SQL_ERR: 

/* Oracle error handler. Print diagnostic text
 containing error message. */

PUT SKIP(2) LIST(SQLCA.SQLERRM); 

/* Disable Oracle error checking to avoid an infinite
 loop should another error occur within this routine. */ 

EXEC SQL WHENEVER SQLERROR CONTINUE; 

/* Release resources associated with the cursor. */

EXEC SQL CLOSE C; 

/* Roll back any outstanding changes and disconnect
 from Oracle. */ 

EXEC SQL ROLLBACK RELEASE; 

END DYN3DEM;

4.10 Sample Program 9: Calling a Stored procedure

Before trying the sample program, you must create a PL/SQL package named calldemo. You do that by running a script named CALLDEMO.SQL, which is supplied with Pro*C and shown in the following. The script can be found in the Pro*C demo library.

CREATE OR REPLACE PACKAGE calldemo AS 
 
 TYPE char_array IS TABLE OF VARCHAR2(20) 
 INDEX BY BINARY_INTEGER; 
 TYPE num_array IS TABLE OF FLOAT 
 INDEX BY BINARY_INTEGER; 
 
 PROCEDURE get_employees( 
 dept_number IN number, -- department to query 
 batch_size IN INTEGER, -- rows at a time 
 found IN OUT INTEGER, -- rows actually returned 
 done_fetch OUT INTEGER, -- all done flag 
 emp_name OUT char_array, 
 job OUT char_array, 
 sal OUT num_array); 
 
END calldemo; 
/ 
 
CREATE OR REPLACE PACKAGE BODY calldemo AS 
 
 CURSOR get_emp (dept_number IN number) IS 
 SELECT ename, job, sal FROM emp 
 WHERE deptno = dept_number; 
 
 -- Procedure "get_employees" fetches a batch of employee 
 -- rows (batch size is determined by the client/caller 
 -- of the procedure). It can be called from other 
 -- stored procedures or client application programs. 
 -- The procedure opens the cursor if it is not 
 -- already open, fetches a batch of rows, and 
 -- returns the number of rows actually retrieved. At 
 -- end of fetch, the procedure closes the cursor. 
 
 PROCEDURE get_employees( 
 dept_number IN number, 
 batch_size IN INTEGER, 
 found IN OUT INTEGER, 
 done_fetch OUT INTEGER, 
 emp_name OUT char_array, 
 job OUT char_array, 
 sal OUT num_array) IS 
 
 BEGIN 
 IF NOT get_emp%ISOPEN THEN -- open the cursor if 
 OPEN get_emp(dept_number); -- not already open 
 END IF; 
 
 -- Fetch up to "batch_size" rows into PL/SQL table, 
 -- tallying rows found as they are retrieved. When all 
 -- rows have been fetched, close the cursor and exit 
 -- the loop, returning only the last set of rows found. 
 
 done_fetch := 0; -- set the done flag FALSE 
 found := 0; 
 
 FOR i IN 1..batch_size LOOP 
 FETCH get_emp INTO emp_name(i), job(i), sal(i); 
 IF get_emp%NOTFOUND THEN -- if no row was found 
 CLOSE get_emp; 
 done_fetch := 1; -- indicate all done 
 EXIT; 
 ELSE 
 found := found + 1; -- count row 
 END IF; 
 END LOOP; 
 END; 
END; 
/ 
/*
 * This program connects to Oracle, prompts the user for a
 * department number, uses a stored procedure to fetch Oracle
 * data into PL/SQL tables, returns the data in host arrays, then
 * displays the name, job title, and salary of each employee in
 * the department.
 * For this example to work, the package CALLDEMO must be in
 * the SCOTT schema, or SCOTT must have execute privileges on the
 * package.
 */

EXEC SQL BEGIN DECLARE SECTION;
 DCL USERNAME STATIC CHAR(10) VARYING,
 PASSWORD STATIC CHAR(10) VARYING,

 TABLE_SIZE STATIC BIN FIXED(31),
 DEPT_NUMBER STATIC BIN FIXED(31),
 DONE_FLAG STATIC BIN FIXED(31),
 NUM_RET STATIC BIN FIXED(31),
 EMP_NAME(10) STATIC CHAR(20) VARYING,
 JOB(10) STATIC CHAR(20) VARYING,
 SALARY(10) STATIC DECIMAL FLOAT(6);
EXEC SQL END DECLARE SECTION;

SAMP9: PROCEDURE OPTIONS(MAIN);

 /* connect to Oracle */

 EXEC SQL INCLUDE SQLCA;

 USERNAME = 'SCOTT';
 PASSWORD = 'TIGER';

 EXEC SQL WHENEVER SQLERROR DO CALL SQLERR;

 EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;
 PUT SKIP EDIT
 ('Connected to Oracle as user: ', USERNAME)(A, A);

 PUT SKIP(2) LIST('Enter the department number: ');
 GET LIST (DEPT_NUMBER);
 PUT SKIP;
 TABLE_SIZE = 2;
 DONE_FLAG = 0;

CLOOP: DO WHILE (1 = 1);
 EXEC SQL EXECUTE 
 BEGIN
 CALLDEMO.GET_EMPLOYEES (
 :DEPT_NUMBER, :TABLE_SIZE, :NUM_RET,
 :DONE_FLAG, :EMP_NAME, :JOB, :SALARY);
 END;
 END-EXEC;
 CALL PRINT_ROWS(NUM_RET);
 IF (DONE_FLAG ^= 0) THEN
 CALL SIGNOFF;
 ELSE
 GOTO CLOOP;
 END;
STOP;

PRINT_ROWS: PROCEDURE(N);
 DCL N BIN FIXED(31),
 I BIN FIXED(31);


 IF N = 0 THEN DO;
 PUT SKIP(2) LIST('No rows retrieved.');
 END;
 ELSE DO;
 PUT SKIP(2) EDIT('Got', N, ' rows.') (A, F(3));
 PUT SKIP(2) LIST
 ('Employee name Job Salary');
 PUT SKIP LIST
 ('-----------------------------------------------');
 DO I = 1 TO N;
        PUT SKIP EDIT(EMP_NAME(I)) (A(20));
 PUT EDIT (JOB(I)) (A(20));
 PUT EDIT (SALARY(I)) (F(7,2));
 END;
 END;
END PRINT_ROWS;

SIGNOFF: PROCEDURE;
 PUT SKIP(2) LIST('Have a good day.');
 EXEC SQL COMMIT WORK RELEASE;
 STOP;
END SIGNOFF;

SQLERR: PROCEDURE;
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 PUT SKIP(2) LIST('Oracle error detected:');
 PUT SKIP(2) LIST(SQLCA.SQLERRM);
 EXEC SQL ROLLBACK WORK RELEASE;
 STOP;
END SQLERR;

END SAMP9;