PL/SQL User's Guide and Reference Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Execution Environments


Three things are to be looked to in a building: that it stand on the right spot; that it be securely founded; that it be successfully executed.

Goethe

You can use PL/SQL with a variety of application development tools. This chapter shows you how to use PL/SQL in the SQL*Plus, Oracle Precompiler, and OCI environments.


SQL*Plus Environment

After entering the SQL*Plus environment, you can use PL/SQL in several ways:

Note: This section discusses these topics briefly. For a full discussion, see SQL*Plus User's Guide and Reference.

Inputting an Anonymous Block

Every PL/SQL block begins with the keyword DECLARE or, if the block has no declarative part, with the keyword BEGIN. Typing either keyword at the SQL*Plus prompt (SQL>) signals SQL*Plus to

SQL*Plus expects you to input an unlabeled PL/SQL block, so you cannot start with a block label.

You input the PL/SQL block line by line. Ending the block with a period (.) on a line by itself stores the block in the SQL buffer.

You can save your PL/SQL block in a script file as follows:

SQL> SAVE <filename>

If you want to edit the file, you can use the SQL*Plus line editor. For instructions, see SQL*Plus User's Guide and Reference. After editing the file, you can save it again as follows:

SQL> SAVE <filename> REPLACE

Executing an Anonymous Block

After inputting a PL/SQL block, you need not end it with a period. Ending the block with a slash (/) on a line by itself stores the block in the SQL buffer, then runs the block. Once it is stored in the SQL buffer, you can run the PL/SQL block again, as follows:

SQL> RUN  or  SQL> /

When the block is finished running, you are returned to the SQL*Plus prompt. The SQL buffer is not cleared until you start inputting the next SQL statement or PL/SQL block.

Creating a Script

You can use your favorite text editor to create scripts containing SQL*Plus statements and PL/SQL blocks, subprograms, and/or packages. In the following example, a PL/SQL block is preceded by SQL*Plus statements that set up a report:

CLEAR BREAKS
CLEAR COLUMNS
COLUMN ENAME HEADING Name
TTITLE 'CLERICAL STAFF'
DECLARE
   avg_sal NUMBER(7,2);
BEGIN
   SELECT AVG(sal) INTO avg_sal FROM emp;
   IF avg_sal < 1500 THEN
      UPDATE emp SET sal = sal * 1.05 WHERE job = 'CLERK';
   END IF;
END;
/
SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK';

The two CLEAR statements get rid of any settings left over from a previous report. The COLUMN statement changes the ENAME column heading to Name. The TTITLE statement specifies a title that appears at the top of each page in the report. The semicolon (;) following each SQL*Plus statement executes that statement. Likewise, the slash (/) following the PL/SQL block executes that block.

Loading and Running a Script

After invoking SQL*Plus, you can load and run a script in one step, as follows:

SQL> START <filename>  or  SQL> @<filename>

Your PL/SQL block can take advantage of the SQL*Plus substitution variable feature. Before running a script, SQL*Plus prompts for the value of any variable prefixed with an ampersand (&). In the following example, SQL*Plus prompts for the value of num:

SQL> BEGIN
  2     FOR i IN 1..&num LOOP ...
  ...
  8  END;
  9  /
Enter value for num:

Creating a Stored Subprogram, Package, or Trigger

To create PL/SQL subprograms, packages, and triggers and store them permanently in an Oracle database, you use the following SQL commands:

When you type any of these commands, SQL*Plus clears the SQL buffer and enters INPUT mode. In the following example, you input a PL/SQL procedure, then create and store it in the database by typing a slash:

SQL> CREATE PROCEDURE create_dept (new_name CHAR, new_loc CHAR) AS
  2  BEGIN
  3     INSERT INTO dept
  4        VALUES (deptno_seq.NEXTVAL, new_name, new_loc);
  5  END create_dept;
  6  /
Procedure created.

If SQL*Plus tells you that the subprogram, package, or trigger was created with compilation errors, you can view them by typing the SQL*Plus command SHOW ERRORS, as follows:

SQL> SHOW ERRORS

Using Bind Variables

A bind variable is a variable you declare in SQL*Plus, then pass to one or more PL/SQL programs, which can use it like any other variable. Both SQL*Plus and PL/SQL can reference the bind variable, and SQL*Plus can display its value.

To declare a bind variable, you use the SQL*Plus command VARIABLE. In the following example, you declare a variable of type NUMBER:

VARIABLE return_code NUMBER

Note: If you declare a bind variable with the same name as a PL/SQL program variable, the latter takes precedence.

To reference a bind variable in PL/SQL, you must prefix its name with a colon(:), as the following example shows:

:return_code := 0;
IF credit_check_ok(acct_no) THEN 
   :return_code := 1;
END IF;

To display the value of a bind variable in SQL*Plus, you use the PRINT command, as follows:

SQL> PRINT return_code

RETURN_CODE
-----------
          1

In the script below, you declare a bind variable of type REFCURSOR. (The SQL*Plus datatype REFCURSOR lets you declare cursor variables, which you can use to return query results from stored subprograms.) You use the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.

CREATE PACKAGE emp_data AS
   TYPE EmpRecTyp IS RECORD (
      emp_id    NUMBER(4),
      emp_name  CHAR(10),
      job_title CHAR(9), 
      dept_name CHAR(14),
      dept_loc  CHAR(13));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
   PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS
   BEGIN
      OPEN emp_cv FOR 
         SELECT empno, ename, job, dname, loc FROM emp, dept
            WHERE emp.deptno = dept_no AND 
                  emp.deptno = dept.deptno 
            ORDER BY empno;
   END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv)

Calling Stored Subprograms

From SQL*Plus, you can call standalone and packaged subprograms stored in a local or remote database. For example, you might call the local standalone procedure create_dept, as follows:

SQL> EXECUTE create_dept('ADVERTISING', 'NEW YORK')

This call is equivalent to the following call issued from an anonymous PL/SQL block:

SQL> BEGIN create_dept('ADVERTISING', 'NEW YORK'); END;

In the next example, you use the database link newyork to call the remote stored procedure raise_salary:

SQL> EXECUTE raise_salary@newyork(7499, 1500)

You can create synonyms to provide location transparency for remote standalone procedures.

Displaying Output

Currently, PL/SQL does not support I/O. However, the package DBMS_OUTPUT (supplied with Oracle7) allows you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them. The procedure put_line lets you output information to a buffer. The SQL*Plus command SET SERVEROUTPUT ON lets you display the information. For example, suppose you create the following stored procedure:

CREATE PROCEDURE calc_payroll (payroll IN OUT REAL) AS
   CURSOR c1 IS SELECT sal,comm FROM emp;
BEGIN
   payroll := 0;
   FOR c1rec IN c1 LOOP
      c1rec.comm := NVL(c1rec.comm, 0);
      payroll := payroll + c1rec.sal + c1rec.comm;
   END LOOP;
   /* Display debug info. */
   dbms_output.put_line('payroll: ' || TO_CHAR(payroll));
END calc_payroll;

When you issue the following commands, SQL*Plus displays the value of payroll calculated by the procedure:

SQL> SET SERVEROUTPUT ON
SQL> VARIABLE num NUMBER
SQL> EXECUTE calc_payroll(:num)

For more information about package DBMS_OUTPUT, see Oracle7 Server Application Developer's Guide.


Oracle Precompiler Environment

The Oracle Precompilers allow you to embed PL/SQL blocks within programs written in any of the following high-level languages: Ada, C, COBOL, FORTRAN, Pascal, and PL/I. Such programs and languages are called host programs and host languages, respectively.

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

You can embed a PL/SQL block wherever you can embed a SQL statement; the precompiler treats them alike. To embed a PL/SQL block in your host program, you must place the block between the keywords EXEC SQL EXECUTE and END-EXEC, as follows:

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

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

Using Host Variables

You use host variables to pass values and status codes back and forth between a host program and an embedded PL/SQL block. You declare host variables in the program Declare Section using regular host language syntax. Inside a PL/SQL block, the scope of host variables is global.

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

The Pro*C program below illustrates the use of host variables in a PL/SQL block. The program prompts the user for the name of an employee, then passes the name to an embedded PL/SQL block, which uses the name to query an Oracle database. Finally, the results of the query are passed back to the host program, which displays them.

-- 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

You can associate any host variable with an optional indicator variable. An indicator variable is an integer variable that indicates the value or condition of a host variable. You use indicator variables to assign nulls to input host variables and to detect nulls or truncated values in output host 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

Upon entering a block, if an indicator variable has a value of -1, PL/SQL assigns a null to the host variable. Upon exiting the block, if a host variable is null, PL/SQL assigns a value of -1 to the indicator variable. In the following example, the exception name_missing is raised if the indicator variable ename_ind had a value of -1 before the PL/SQL block was entered:

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

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string. In the following example, the host program will be able to tell, by checking the value of ename_ind, if a truncated value was assigned to my_ename:

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

Using the VARCHAR Pseudotype

You can use the VARCHAR pseudotype to declare variable-length character strings. (A pseudotype is a datatype not native to your host language.) VARCHAR variables have a 2-byte length field followed by a string field of up to 65533 bytes. For example, the Pro*C Precompiler expands the declaration

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

If an embedded PL/SQL block refers to a database table that does not yet exist, the precompiler generates an error. To avoid such errors, you can use the DECLARE TABLE statement to tell the precompiler what the table will look like. In the following Pro*C example, you declare the dept table:

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

The Oracle Precompilers can help you debug a program by checking the syntax and semantics of embedded SQL statements and PL/SQL blocks. You control the level of checking by entering the SQLCHECK option inline or on the command line. You can specify the following values for SQLCHECK:

However, if you embed PL/SQL blocks in your program, you must specify SQLCHECK=SEMANTICS. When SQLCHECK=SEMANTICS, the precompiler checks the syntax and semantics of SQL data manipulation statements and PL/SQL blocks.

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

Unlike static SQL statements, dynamic SQL statements are not embedded in your source program. Instead, they are stored in character strings input to (or built by) the program at run time. For example, they might be entered interactively or read from a file.

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

If your PL/SQL block contains no host variables, you can use Method 1 to execute the PL/SQL string in the usual way. In the following Pro*C example, you prompt the user for a PL/SQL block, store it in a string host variable named user_block, then execute it:

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

If your PL/SQL block contains a known number of input and output host variables, you can use dynamic SQL Method 2 to prepare and execute the PL/SQL string in the usual way. In the Pro*C example below, the PL/SQL block uses one host variable named my_empno. The program prompts the user for a PL/SQL block, stores it in a string host variable named user_block, then prepares and executes the block:

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

If your PL/SQL block contains an unknown number of input or output host variables, you must use Method 4. To do so, you set up a bind descriptor for all the input and output host variables. Executing the DESCRIBE BIND VARIABLES statement stores information about input and output host variables in the bind descriptor.

Mimicking Dynamic SQL

Without dynamic SQL, you cannot use PL/SQL variables in a query to specify database columns. Consider the SELECT statement below, which does not assign a value from the ename database column to the variable my_ename. Instead, if the emp table has a column named colx, a value from that column is assigned to my_ename. If the table has no such column, the value of PL/SQL variable colx (that is, the string value 'ename') is assigned to my_ename.

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

To call a stored subprogram from a host program, you must use an anonymous PL/SQL block. In the following example, you call the standalone procedure create_dept:

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;


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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index