Oracle Precompiler Environment

After writing a program, you precompile the source file. The precompiler checks the program for syntax errors, then generates a modified source file, which can be compiled, linked, and executed in the usual way.

Embedding PL/SQL Blocks

EXEC SQL EXECUTE
   BEGIN
      ...
   END;
END-EXEC;

Be sure to follow the keyword END-EXEC with the host-language statement terminator.

Using Host Variables

Both the host program and the PL/SQL block can set and reference the value of a host variable. The value of an input host variable is set by the host program and referenced by Oracle. Conversely, the value of an output host variable is set by Oracle and referenced by the host program.

All references to host variables in a PL/SQL block must be prefixed with a colon. That way, the precompiler can tell host variables from PL/SQL variables and database objects.

Some Examples

-- available online in file EXAMP9
#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
   VARCHAR  empname[11];
   VARCHAR  jobtype[9];
   VARCHAR  hired[9];
   int      salary;
   int      dept;
   int      served_longer;
   int      higher_sal;
   int      total_in_dept;
   VARCHAR  uid[20];
   VARCHAR  pwd[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;

void sqlerror();
main()
{
      /* Set up userid and password */
   strcpy (uid.arr,"scott");
   uid.len = strlen(uid.arr);
   strcpy (pwd.arr,"tiger");
   pwd.len = strlen(pwd.arr);
  
   printf("\n\n\tEmbedded PL/SQL Demo\n\n");
   printf("Trying to connect...");
      /* Check for SQL errors */
   EXEC SQL WHENEVER SQLERROR DO sqlerror();
      /* Connect to Oracle */
   EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
   printf(" connected.\n");
   
   for (;;) /* Loop indefinitely */
   {
      printf("\n** Name of employee? (<CR> to quit)  ");
      gets(empname.arr);               /* Get the name      */
      if (strlen(empname.arr) == 0)    /* No name entered,  */
      {
         EXEC SQL COMMIT WORK RELEASE; /* so log off Oracle */
         exit(0);                      /* and exit program  */
      }
      empname.len = strlen(empname.arr);
      jobtype.len = 9;
      hired.len = 9;   

      /* ----- Begin PL/SQL block ----- */
      EXEC SQL EXECUTE
      BEGIN
         SELECT job, hiredate, sal, deptno
            INTO :jobtype, :hired, :salary, :dept FROM emp
            WHERE ename = UPPER(:empname);
            /* Get number of people whose length *
             * of service is longer              */
         SELECT COUNT(*) INTO :served_longer FROM emp
            WHERE hiredate < :hired;
            /* Get number of people with a higher salary */
         SELECT COUNT(*) INTO :higher_sal FROM emp
            WHERE sal > :salary;
            /* Get number of people in same department */
         SELECT COUNT(*) INTO :total_in_dept FROM emp
            WHERE deptno = :dept;
      END;
      END-EXEC;
      /* ----- End PL/SQL block ----- */

         /* Null-terminate character strings returned by Oracle */
      jobtype.arr[jobtype.len] = '\0';
      hired.arr[hired.len] = '\0';
         /* Display the information */
      printf("\n%s's job is: %s\n", empname.arr, jobtype.arr);
      printf("Hired on: %s\n", hired.arr);
      printf("    %d people have served longer\n", served_longer);
      printf("Salary is: %d\n", salary);
      printf("    %d people have a higher salary\n", higher_sal);
      printf("Department number is: %d\n", dept);
      printf("    %d people in the department\n", total_in_dept);
   }  /* End of loop */
}  
  
void sqlerror()
{
      /* Avoid infinite loop if rollback causes an error */ 
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   printf("\nOracle error detected:\n");
      /* Print error message and disconnect from Oracle */
   printf("\n%.70s\n", sqlca.sqlerrm.sqlerrmc);
   EXEC SQL ROLLBACK WORK RELEASE;
   exit(1);
}

Notice that the host variable empname is assigned a value before the PL/SQL block is entered and that the other host variables are assigned values inside the block. When necessary, Oracle converts between its internal datatypes and standard host-language datatypes.

The next Pro*C example shows how two PL/SQL banking transactions might be implemented:

-- available online in file EXAMP10
#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
   int     acct, amount;
   VARCHAR tran_type[10];
   VARCHAR status[65];
   VARCHAR uid[20];
   VARCHAR pwd[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;

void sqlerror();
main()
{
      /* Set up userid and password */
   strcpy (uid.arr,"scott");
   uid.len=strlen(uid.arr);
   strcpy (pwd.arr,"tiger");
   pwd.len=strlen(pwd.arr);
  
   printf("\n\n\tEmbedded PL/SQL Demo\n\n");
   printf("Trying to connect...");
      /* Check for SQL errors */
   EXEC SQL WHENEVER SQLERROR DO sqlerror();
      /* Connect to Oracle */
   EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;
   printf(" connected.\n");
   
   for (;;) /* Loop indefinitely */
   {
      printf("\n\n** Account number? (-1 to quit)");
      scanf("%d", &acct);
      if (acct == -1)   /* Disconnect from Oracle and */
      {                 /* exit program if acct is -1 */
         EXEC SQL COMMIT WORK RELEASE;
         exit(0);
      }
      printf("\n   Transaction type? (C)redit or (D)ebit   ");
      scanf("%s", &tran_type.arr);
      tran_type.len = 1;   /* Only want first character */

      printf("\n   Transaction amount? (in whole dollars)  ");
      scanf("%d", &amount);
   
      /* ----- Begin PL/SQL block ----- */
      EXEC SQL EXECUTE
      DECLARE
         old_bal    NUMBER(11,2);
         no_account EXCEPTION;
      BEGIN
         :tran_type := UPPER(:tran_type);
         IF :tran_type = 'C' THEN       -- credit the account
            UPDATE accounts SET bal = bal + :amount
               WHERE account_id = :acct;
            IF SQL%ROWCOUNT = 0 THEN    -- no rows affected
               RAISE no_account;
            ELSE
               :status := 'Credit complete.';
            END IF;
         ELSIF :tran_type = 'D' THEN    -- debit the account
            SELECT bal INTO old_bal FROM accounts
               WHERE account_id = :acct;
            IF old_bal >= :amount THEN  -- has sufficient funds
                UPDATE accounts SET bal = bal - :amount
                   WHERE account_id = :acct;
               :status := 'Debit applied';
            ELSE
               :status := 'Insufficient funds';
            END IF;
         ELSE
            :status := :tran_type || ' is an illegal transaction';
         END IF;
         COMMIT;
      EXCEPTION
         WHEN NO_DATA_FOUND OR no_account THEN
            :status := 'Nonexistent account';
         WHEN OTHERS THEN
            :status := 'Error: ' || SQLERRM(SQLCODE);
      END;
      END-EXEC;
      /* ----- End the PL/SQL block ----- */
  
      status.arr[status.len] = '\0';  /* null-terminate string */
      printf("\n\n   Status: %s", status.arr);
   }  /* End of loop */
}

void sqlerror()
{
      /* Avoid infinite loop if rollback causes an error */ 
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   printf("\nOracle error detected:\n");
      /* Print error message and disconnect from Oracle */
   printf("\n%.70s\n", sqlca.sqlerrm.sqlerrmc);
   EXEC SQL ROLLBACK WORK RELEASE;
   exit(1);
}

Using Indicator Variables

For input host variables, the values your program can assign to an indicator variable have the following meanings:

-1	Ignoring the value of the host variable, Oracle will assign a null to the database column.
>= 0	Oracle will assign the value of the host variable to the database column.

For output host variables, the values Oracle can assign to an indicator variable have the following meanings:

-2	Oracle assigned a truncated column value to the host variable but could not store the original length of the column value in the indicator variable because the number was too large.
-1	The database column contains a null, so the value of the host variable is indeterminate.
0	Oracle assigned an intact column value to the host variable.
> 0	Oracle assigned a truncated column value to the host variable and stored the original length of the column value in the indicator variable.

An indicator variable must be defined in the Declare Section as a 2-byte integer and, in SQL statements, must be prefixed with a colon and appended to its host variable unless you use the keyword INDICATOR, as follows:

:host_variable INDICATOR :indicator_variable

A host language needs indicator variables because it cannot manipulate nulls. PL/SQL meets this need by allowing an embedded PL/SQL block to accept nulls from the host program and return nulls or truncated values to it.

In the following Pro*COBOL example, the PL/SQL block uses an indicator variable to return a null status code to the host program:

EXEC SQL EXECUTE
BEGIN
   ...
   SELECT ENAME, COMM INTO :MY-ENAME, :MY-COMM:COMM-IND FROM EMP
      WHERE EMPNO = :MY-EMPNO
END;
END-EXEC.
MOVE MY-COMM TO MY-COMM-OUT.
DISPLAY "Commission: "
   WITH NO ADVANCING.
IF COMM-IND = -1
*  If the value returned by an indicator variable is -1,
*  its output host variable is null.
   DISPLAY "N/A"
ELSE
   DISPLAY MY-COMM-OUT.

Inside a PL/SQL block, an indicator variable must be prefixed with a colon and appended to its host variable.

You cannot refer to an indicator variable by itself. Furthermore, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block. In the next example, because the host variable appears with its indicator variable in the SELECT statement, it must also appear that way in the IF statement:

EXEC SQL EXECUTE
DECLARE
   ...
   status_unknown EXCEPTION;
BEGIN
   ...
   SELECT ename, job INTO :my_ename, :my_job:job_ind FROM emp      WHERE empno = :my_empno;
   IF :my_job:job_ind IS NULL THEN
      RAISE status_unknown;
   END IF;
   ...
END;
END-EXEC;

Although you cannot refer directly to indicator variables inside a PL/SQL block, PL/SQL checks their values upon entering the block and sets their values correctly upon exiting the block.

Nulls

EXEC SQL EXECUTE
DECLARE
   ...
   name_missing EXCEPTION;
BEGIN
   ...
   IF :my_ename:ename_ind IS NULL THEN
      RAISE name_missing;
   END IF;
   ...
EXCEPTION
   WHEN name_missing THEN
      ...
END;
END-EXEC;

Truncated Values

EXEC SQL EXECUTE
DECLARE
   new_ename CHAR(10);
   ...
BEGIN
   ...
   :my_ename:ename_ind := new_ename;
   ...
END;
END-EXEC;

Using the VARCHAR Pseudotype

EXEC SQL BEGIN DECLARE SECTION;
   VARCHAR my_ename[10]
EXEC SQL END DECLARE SECTION;

into the following data structure:

struct {
   unsigned short len;
   unsigned char  arr[10];
}  my_ename;

To get the length of a VARCHAR, simply refer to its length field. You need not use a string function or character-counting algorithm.

Oracle automatically sets the length field of a VARCHAR output host variable. However, to use a VARCHAR output host variable in your PL/SQL block, you must initialize the length field before entering the PL/SQL block. So, set the length field to the declared (maximum) length of the VARCHAR, as shown in the following Pro*C example:

EXEC SQL BEGIN DECLARE SECTION;
   int     my_empno;
   VARCHAR my_ename[10] /* declare variable-length string */
   float   my_sal;
   ...
EXEC SQL END DECLARE SECTION;
   ...
   my_ename.len = 10;   /* initialize length field */
   EXEC SQL EXECUTE
   BEGIN
      SELECT ename, sal INTO :my_ename, :my_sal FROM emp
         WHERE empno = :my_empno;
      ...
   END;
   END-EXEC;

Using the DECLARE TABLE Statement

EXEC SQL DECLARE dept TABLE(
   deptno NUMBER(2),
   dname  VARCHAR2(14),
   loc    VARCHAR2(13));

If you use DECLARE TABLE to define a table that already exists, the precompiler uses your definition, ignoring the one in the Oracle data dictionary. Note that you cannot use the DECLARE TABLE statement inside a PL/SQL block.

Using the SQLCHECK Option

• SEMANTICS

• SYNTAX

• NONE

The precompiler gets information needed for the semantic check by using embedded DECLARE TABLE statements or by connecting to Oracle and accessing the data dictionary. So, unless every database table referenced in a SQL statement or PL/SQL block is defined by a DECLARE TABLE statement., you must specify the option USERID on the command line. For more information see Programmer's Guide to the Oracle Precompilers.

Using Dynamic SQL

The Oracle Precompilers treat a PL/SQL block like a single SQL statement. So, like a SQL statement, a PL/SQL block can be stored in a string host variable for processing by dynamic SQL commands.

However, recall from Chapter 2 that you cannot use single-line comments in a PL/SQL block that will be processed dynamically. Instead, use multi-line comments.

Following is a brief look at how PL/SQL is used with dynamic SQL Methods 1, 2, and 4. For more information, see Programmer's Guide to the Oracle Precompilers.

With Method 1

main()
{
   printf("\nEnter a PL/SQL block: ");
   scanf("%s", user_block);
   EXEC SQL EXECUTE IMMEDIATE :user_block;

When you store a PL/SQL block in a string host variable, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the statement terminator.

With Method 2

main()
{
   printf("\nEnter a PL/SQL block: ");
   scanf("%s", user_block);
   EXEC SQL PREPARE my_block FROM :user_block;
   EXEC SQL EXECUTE my_block USING :my_empno;

Note that my_block is an identifier used by the precompiler, not a host or program variable.

The precompiler treats all PL/SQL host variables as input host variables whether they serve as input or output host variables (or both) inside the PL/SQL block. So, you must put all host variables in the USING clause.

When the PL/SQL string is executed, host variables in the USING clause replace corresponding placeholders in the prepared string. Although the precompiler treats all PL/SQL host variables as input host variables, values are assigned correctly. Input (program) values are assigned to input host variables, and output (column) values are assigned to output host variables.

With Method 4

Mimicking Dynamic SQL

DECLARE
   colx     VARCHAR2(10);
   my_ename VARCHAR2(10);
   ...
BEGIN
   colx := 'ename';
   SELECT colx INTO my_ename FROM emp WHERE ...
   ...
END;

However, you can mimic dynamic SQL by using the DECODE function. In the following example, the data returned depends on the value of my_column:

DECLARE
   my_column VARCHAR2(10);
   my_data   emp.ename%TYPE;
BEGIN
   ...
   my_column := 'hiredate';
   ...
   SELECT DECODE(my_column, 'ename', ename, 'hiredate',
         TO_CHAR(hiredate, 'ddmmyy'), 'empno', empno)
      INTO my_data FROM emp WHERE ... ;
END;

The value that DECODE returns is always forced to the datatype of the first result expression. In this example, the first result expression is ename, which has datatype VARCHAR2, so the returned value is forced to type VARCHAR2. Thus, my_data is correctly declared as emp.ename%TYPE.

You can use this technique in many environments. For example, it works in SQL*Plus and Oracle Forms.

Calling Stored Subprograms

EXEC SQL EXECUTE
BEGIN
    create_dept(:number, :name, :location);
END;
END-EXEC;

Notice that the actual parameters number, name, and location are host variables.

In the next example, the procedure create_dept is part of a package named emp_actions, so you must use dot notation to qualify the procedure call:

EXEC SQL EXECUTE
BEGIN
    emp_actions.create_dept(:number, :name, :location);
END;
END-EXEC;