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.
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 |
/*****************************************************************
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;
/*****************************************************************
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;
/**************************************************************
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;
/**************************************************************
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;
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;
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;
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;
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;
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;