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.

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.

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.

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:

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:

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.