PL/SQL User's Guide and Reference

OCI Environment

The OCI processes SQL statements and PL/SQL blocks similarly with one exception. Inside a PL/SQL block, you must use the OBNDRA, OBINDPS, or OBNDRV call, not ODEFIN or ODEFINPS, to bind all placeholders in a SQL or PL/SQL statement. This holds for both input and output placeholders. The ODEFIN and ODEFINPS calls are not supported for PL/SQL blocks.

In PL/SQL, all queries must have an INTO clause containing placeholders (host variables and/or PL/SQL variables) that correspond to items in the select list. For example, the following SELECT statement is not valid inside a PL/SQL block:

SELECT ename, sal FROM emp;

Instead, it must be coded as follows:

SELECT ename, sal INTO :my_ename, :my_sal FROM emp;

In the last statement, my_ename and my_sal are SQL placeholders that correspond to the ename and sal columns in the select list. You must bind these placeholders using the OBNDRA, OBINDPS, or OBNDRV call. You can bind host arrays to PL/SQL tables using the OBNDRA or OBINDPS call.

Also, you must use named placeholders such as my_ename in PL/SQL blocks. Numbered placeholders such as 10 and the corresponding OBNDRN call are not supported for PL/SQL blocks.

A Complete Example

The OCI program below, which is written in C, shows how two PL/SQL banking transactions might be implemented. You can find listings of the header files ocidfn.h and ocidem.h, in Programmer's Guide to the Oracle Call Interface.

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <oratypes.h>
#include <ocidfn.h>
#ifdef __STDC__
#include <ociapr.h>
#else
#include <ocikpr.h>
#endif
#include <ocidem.h>
Cda_Def cda;
Lda_Def lda;
ub1  hda[256];
text  sqlstm[2048];
void error_handler();

main()
{
   int    acct_number;
   text   trans_type[1];
   float  trans_amt;
   text   status[80];
   if (olog(&lda, hda, "scott/tiger", -1, (text *) 0, -1, 
      (text *) 0, -1, OCI_LM_DEF))
   {
      printf("Connect failed.\n");
      exit(EXIT_FAILURE);
   }
   if (oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1))
   {
      printf("Error opening cursor.  Exiting...\n");
      exit(EXIT_FAILURE);
   }
   printf("\nConnected to Oracle.\n");
   /* Construct a PL/SQL block. */
   strcpy(sqlstm, "DECLARE\
         old_bal      NUMBER(9,2);\
         err_msg      CHAR(70);\
         nonexistent  EXCEPTION;\
      BEGIN\
         :xtrans_type := UPPER(:xtrans_type);\
         IF :xtrans_type = 'C' THEN\
            UPDATE ACCTS SET BAL = BAL + :xtrans_amt\
               WHERE ACCTID = :xacct_number;\
            IF SQL%ROWCOUNT = 0 THEN\
               RAISE nonexistent;\
            ELSE\
               :xstatus := 'Credit applied';\
            END IF;\
         ELSIF :xtrans_type = 'D' THEN\
            SELECT BAL INTO old_bal FROM accts\
               WHERE acctid = :xacct_number;\
            IF old_bal = :xtrans_amt THEN\
               UPDATE accts SET bal = bal - :xtrans_amt\
                  WHERE acctid = :xacct_number;\
               :xstatus := 'Debit applied';\
            ELSE\
               :xstatus := 'Insufficient funds';\
            END IF;\
         ELSE\
            :xstatus := 'Invalid type: ' || :xtrans_type;\
         END IF;\
         COMMIT;\
      EXCEPTION\
         WHEN NO_DATA_FOUND OR nonexistent THEN\
            :xstatus := 'Nonexistent account';\
         WHEN OTHERS THEN\
            err_msg := SUBSTR(SQLERRM, 1, 70);\
            :xstatus := 'Error: ' || err_msg;\
      END;");

   /* Parse the PL/SQL block. */
   if (oparse(&cda, sqlstm, -1, 0, 2))
   {
      error_handler(&cda);
      exit(EXIT_FAILURE);
   }

   /* Bind the status variable. */
   if (obndrv(&cda,
      ":xstatus",
      -1,
      status,
      70,
      5,
      -1,
      (text *) 0,
      (text *) 0, -1, -1))
   {
      error_handler(&cda);
      exit(EXIT_FAILURE);
   }

   /* Bind the transaction type variable. */
   if (obndrv(&cda,
      ":xtrans_type",
      -1,
      trans_type,
      1,
      1,
      -1,
      (text *) 0,
      (text *) 0, -1, -1))
   {
       error_handler(&cda);
       exit(EXIT_FAILURE);
   }

   /* Bind the account number. */
   if (obndrv(&cda,
      ":xacct_number",
      -1,
      &acct_number,
      sizeof (int),
      3,
      -1,
      (text *) 0,
      (text *) 0, -1, -1))
   {
      error_handler(&cda);
      exit(EXIT_FAILURE);
   }

   /* Bind the transaction amount variable. */
   if (obndrv(&cda,
      ":xtrans_amt",
      -1,
      &trans_amt,
      sizeof (float),
      4,
      -1,
      (text *) 0,
      (text *) 0, -1, -1))
   {
      error_handler(&cda);
      exit(EXIT_FAILURE);
   }
   for (;;)   
   {
      printf("\nAccount number: ");
      scanf("%d", &acct_number);
      fflush(stdin);
      if (acct_number == 0)
         break;
      printf("Transaction type (D or C): ");
      scanf("%c", trans_type);
      fflush(stdin);
      printf("Transaction amount:        ");
      scanf("%f", &trans_amt);
      fflush(stdin);
      /* Execute the block. */
      if (oexec(&cda))
         error_handler(&cda);
      printf("%s\n", status);
   }
   printf("Have a good day!\n");
   exit(EXIT_SUCCESS);
}

void
error_handler(cursor)
    Cda_Def *cursor;
{
    sword n;
    text msg[512];
    printf("\n-- ORACLE error--\n");
    printf("\n");
    n = oerhms(&lda, cursor->rc, msg, (sword) sizeof msg);
    fprintf(stderr, "%s\n", msg);
    if (cursor->fc > 0)
        fprintf(stderr, "Processing OCI function %s",
            oci_func_tab[cursor->fc]);
}

Calling Stored Subprograms

To call a stored subprogram from an OCI program, you must use an anonymous PL/SQL block. In the following C example, a call to the standalone procedure raise_salary is copied into the string variable plsql_block:

strcpy(plsql_block, "BEGIN raise_salary(:emp_id, :amount); END;");

Then, the PL/SQL string can be bound and executed like a SQL statement.