Programmer's Guide to the Pro*Ada Precompiler 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

Using Embedded SQL And PL/SQL


This chapter helps you to understand and apply the basic techniques of embedded SQL programming. You learn how to use host variables, indicator variables, and the fundamental SQL commands that insert, update, select, and delete Oracle data. You also learn how to improve performance by embedding PL/SQL transaction processing blocks in your program. The following topics are discussed:


Using Host Variables

Oracle uses host variables to pass data and status information to your program. Your program uses host variables to pass data to Oracle.

Output Versus Input Host Variables

Depending on how they are used, host variables are called output or input host variables.

Host variables in the INTO clause of a SELECT or FETCH statement are called output host variables because they hold column values output by Oracle. Oracle assigns the column values to corresponding output host variables in the INTO clause.

All other host variables in a SQL statement are called input host variables because your program inputs their values to Oracle. For example, you use input host variables in the VALUES clause of an INSERT statement, and in the SET clause of an UPDATE statement. They are also used in the WHERE, HAVING, and FOR clauses. Input host variables can appear in a SQL statement wherever a value or expression is allowed.

You cannot use input host variables to supply SQL keywords or the names of database objects. Thus, you cannot use input host variables in data definition statements such as ALTER, CREATE, and DROP. In the following example, the DROP TABLE statement is invalid:

with text_io, varchar_text_io; 
use text_io; 
... 
TABLE_NAME   : ORACLE.VARCHAR(30); 
... 
PUT("Table name? "); 
VARCHAR_TEXT_IO.GET_LINE(TABLE_NAME); 
... 
EXEC SQL DROP TABLE :TABLE_NAME;  -- host variable not allowed 

Note: You can use dynamic SQL methods to perform the above example. For more information, see Chapter 8, "Introduction to Dynamic SQL," and Chapter 9, "Implementing Dynamic SQL Method 4."

Before Oracle executes a SQL statement containing input host variables, your program must assign values to them. For example:

EMP_NUMBER   : integer; 
EMP_NAME     : string(1..20); 
EMP_NAME_LEN : integer; 
... 
-- get values for input host variables 
PUT("Employee number: "); 
GET(EMP_NUMBER); 
PUT("Employee name: "); 
GET_LINE(EMP_NAME, EMP_NAME_LEN); 
... 
EXEC SQL INSERT INTO emp (empno, deptno, ename) 
    VALUES (:EMP_NUMBER, 20, :EMP_NAME(1..EMP_NAME_LEN)); 

Notice that the input host variables in the VALUES clause of the INSERT statement are prefixed with colons.


Using Indicator Variables

You can associate any host variable with an optional indicator variable. Each time the host variable is used in a SQL statement, a result code is stored in its associated indicator variable. Thus, indicator variables let you monitor host variables.

You use indicator variables in the VALUES or SET clause to assign NULLs to input host variables, and in the INTO clause 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

Oracle will assign a NULL to the column, ignoring the value of the associated host variable.

> = 0

Oracle will assign the value of the host variable to the column.

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

- 1

The column value is null, so the value of the associated 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. The integer returned by the indicator variable is the original length of the column value.

Remember, an indicator variable must be declared as a 2-byte integer or of type ORACLE.INDICATOR, and, in SQL statements, must be prefixed with a colon and appended to its host variable. Use the predefined subtype indicator in the Oracle package to declare all indicator variables. If you are using the SQL_STANDARD package, use the subtype INDICATOR_TYPE.

Indicator variables are used primarily to manipulate null values. The following subsections explain how this is done.

Inserting Nulls

You can use indicator variables to INSERT NULLs. Before the INSERT, for each column you want to be null, set the appropriate indicator variable to -1, as shown in the following example:

IND_COMM := -1; 
... 
EXEC SQL INSERT INTO emp (empno, comm) 
    VALUES (:EMP_NUMBER, :COMMISSION INDICATOR :IND_COMM); 

The indicator variable IND_COMM specifies that a NULL is to be stored in the COMM column.

You can hardcode NULLs instead, as follows:

EXEC SQL INSERT INTO emp (empno, comm) 
    VALUES (:EMP_NUMBER, NULL); 

While this is less flexible, it might be more readable.

Typically, you insert NULLs conditionally, as the next example shows:

PUT("Enter employee number or 0 if not available: "); 
GET(EMP_NUMBER); 
if EMP_NUMBER = 0 then 
    IND_EMPNUM := -1; 
else 
    IND_EMPNUM := 0; 
end if; 
... 
EXEC SQL INSERT INTO emp (empno, sal) 
    VALUES (:EMP_NUMBER INDICATOR :IND_EMPNUM, :SALARY); 

Handling Returned Nulls

You can also use indicator variables to manipulate returned NULLs, as the following example shows:

EXEC SQL SELECT ename, sal, comm 
    INTO :EMP_NAME, :SALARY, :COMMISSION INDICATOR :IND_COMM 
    FROM emp 
    WHERE empno = :EMP_NUMBER; 
if IND_COMM = -1 then 
    PAY := SALARY;   -- commission is null; ignore it 
else 
    PAY := SALARY + COMMISSION; 
end if; 

Fetching Nulls

When MODE={ANSI13 | ORACLE}, you can SELECT or FETCH NULLs into a host variable not associated with an indicator variable, as the following example shows:

-- assume that commission is NULL 
EXEC SQL SELECT ENAME, SAL, COMM 
    INTO :EMP_NAME, :SALARY, :COMMISSION 
    FROM EMP 
    WHERE EMPNO = :EMP_NUMBER; 

SQLCODE in the SQLCA is set to zero indicating that Oracle executed the statement without detecting an error or exception.

However, when MODE={ANSI | ANSI14}, if you SELECT or FETCH nulls into a host variable not associated with an indicator variable, Oracle issues the following error message:

ORA-01405: fetched column value is NULL 

For more information about the MODE option, see "Using the Precompiler Options" [*].

Testing for NULLs

You can use indicator variables in the WHERE clause to test for NULLs, as the following example shows:

EXEC SQL SELECT ENAME, SAL 
    INTO :EMP_NAME, :SALARY 
    FROM EMP 
    WHERE :COMMISSION INDICATOR :IND_COMM IS NULL ... 

However, you cannot use a relational operator to compare NULLs with each other or with other values. For example, the following SELECT statement fails if the COMM column contains one or more NULLs:

EXEC SQL SELECT ENAME, SAL 
    INTO :EMP_NAME, :SALARY 
    FROM EMP 
    WHERE COMM = :COMMISSION INDICATOR :IND_COMM; 

The next example shows how to compare values for equality when some of them might be NULLs:

EXEC SQL SELECT ENAME, SAL 
    INTO :EMP_NAME, :SALARY 
    FROM EMP 
    WHERE (COMM = :COMMISSION) OR ((COMM IS NULL) AND 
          (:COMMISSION INDICATOR :IND_COMM IS NULL)); 

Fetching NULLs Without Using Indicator Variables

When working with Pro*Ada source files that FETCH data into a host variable without an accompanying indicator variable, an ORA-01405 message is generated if a NULL is returned to the host variable. Beginning with Pro*Ada release 1.8, you can disable the ORA-01405 message by using the command-line option UNSAFE_NULL=YES.

The addition of the UNSAFE_NULL option eases migration from Oracle Version 6 to Oracle7. If you are upgrading to Oracle7 and use DBMS=V6 when precompiling, you will be unaffected by the addition of this option. Precompiler applications using DBMS=V6 maintain full compatibility with Oracle v6.

If you are upgrading to Oracle7 and use DBMS=V7 when precompiling, or if you intend to use new Oracle7 features that are different from Oracle Version 6, the change requires minimal modification of Pro*Ada applications in most instances. However, if your application may FETCH null values into host variables that are not accompanied by indicator variables, you can specify UNSAFE_NULL=YES to avoid a potentially large modification to your Pro*Ada source files.

The UNSAFE_NULL option has no effect on host variables in an embedded PL/SQL block. In that case, you must use indicator variables to avoid ORA-01405 errors.

See the description of the UNSAFE_NULL command-line option [*] for more information about its syntax and usage.

Fetching Truncated Values

In Pro*Ada, the DBMS option controls whether or not an error is generated when you SELECT or FETCH a truncated column value into a host variable which is not associated with an indicator variable.

If DBMS=V6 and you SELECT or FETCH a truncated column without an associated indicator variable, Oracle issues the error message:

ORA-01406: fetched column value was truncated 

However, when DBMS=V7, no error is returned.

In all cases (DBMS=V7 or DBMS=V6) a warning flag is set if the column value is truncated.


Basic SQL Statements

Executable SQL statements let you query, manipulate, and control Oracle data; and create, define, and maintain Oracle objects such as tables, views, and indexes..

When executing a data manipulation statement such as INSERT, UPDATE, or DELETE, you need to know whether the statement succeeds or fails. To find out, check the SQLSTATE status variable or SQLCA variables. Executing any SQL statement sets the variables that you can check in the following ways:

For more information about the SQLCA and the WHENEVER statement, see Chapter 5, "Handling Runtime Errors."

When executing a SELECT statement (query), however, you must also deal with the rows of data it returns. Queries can be classified as follows:

Queries that return more than one row require explicitly declared cursors or the use of host arrays (host variables declared as arrays).

Note: Host arrays let you process "batches" of rows. For more information, see Chapter 7, "Using Host Arrays." This chapter assumes the use of scalar host variables.

The following embedded SQL statements let you query and manipulate Oracle data:

SELECT

Returns rows from one or more tables.

INSERT

Adds new rows to a table.

UPDATE

Modifies rows in a table.

DELETE

Removes rows from a table.

The following embedded SQL statements let you define and manipulate an explicit cursor:

DECLARE

Names the cursor and associates it with a query.

OPEN

Executes the query and identifies the active set.

FETCH

Advances the cursor, and retrieves each row in the active set, one by one.

CLOSE

Disables cursor (the active set becomes undefined).

In the following sections, first you learn how to code INSERT, UPDATE, DELETE, and single-row SELECT statements. Then, you progress to multi-row SELECT statements. For a detailed discussion of each statement and its clauses, see the Oracle7 Server SQL Reference. For the syntax of each statement, see Appendix B.

Using the SELECT Statement

Querying the database is a common SQL operation. To issue a query you use the SELECT statement. In the following example, you query the EMP table:

EXEC SQL SELECT ename, job, sal + 2000 
    INTO :EMP_NAME, :JOB_TITLE, :SALARY 
    FROM emp 
    WHERE empno = :EMP_NUMBER; 

The column names and expressions following the keyword SELECT make up the select list. The select list in our example contains three items. Under the conditions specified in the WHERE clause (and following clauses, if present), Oracle returns column values to the host variables in the INTO clause.

The number of items in the select list should equal the number of host variables in the INTO clause, so there is a place for every returned value.

In the simplest case, when a query returns one row, its form is that shown in the last example. However, if a query can return more than one row, you must FETCH the rows using a cursor, or SELECT them into a host-variable array. For more information on cursors, see "Using Cursors" [*]. For more information on the FETCH statement, see "Using the FETCH statement" [*]. For more information on array processing, see Chapter 7, "Using Host Arrays."

If a query is coded to return only one row but might actually return several rows, the query result is indeterminate. Whether this causes an error depends on how you specify the SELECT_ERROR option. The default setting, SELECT_ERROR=YES, generates an error if more than one row is returned. For more information about the SELECT_ERROR option, see "Using the Precompiler Options" [*].

Available Clauses

You can use all of the following standard SQL clauses in your SELECT statements:

Except for the INTO clause, the text of embedded SELECT statements can be executed and tested interactively using SQL*Plus. In SQL*Plus, you use substitution variables or constants instead of input host variables.

Using the INSERT Statement

You use the INSERT statement to add rows to a table or view. In the following example, you add a row to the EMP table:

EXEC SQL INSERT INTO emp (ename, empno, sal, deptno) 
    VALUES (:EMP_NAME, :EMP_NUMBER, :SALARY, :DEPT_NUMBER); 

Each column you specify in the column list must belong to the table named in the INTO clause. The VALUES clause specifies the row of values to be inserted. The values can be those of constants, host variables, SQL expressions, or pseudocolumns such as USER and SYSDATE.

The number of values in the VALUES clause must equal the number of names in the column list. However, you can omit the column list if the VALUES clause contains a value for each column in the table in the order they were defined by CREATE TABLE.

Using Subqueries

A subquery is a nested SELECT statement. Subqueries let you conduct multi-part searches. They can be used to:

In the following example, use a subquery instead of the VALUES clause of an INSERT statement to copy rows from one table to another:

EXEC SQL INSERT INTO emp2 (ename, empno, job, sal) 
    SELECT ename, empno, job, sal FROM emp 
        WHERE deptno = :DEPT_NUMBER; 

Notice how the INSERT statement uses the subquery to obtain intermediate results.

Using the UPDATE Statement

You use the UPDATE statement to change the values of specified columns in a table or view. In the following example, you UPDATE the SAL and COMM columns in the EMP table:

EXEC SQL UPDATE emp 
    SET sal = :SALARY, comm = :COMMISSION 
    WHERE ename = :EMP_NAME; 

You can use the optional WHERE clause to specify the conditions under which rows are UPDATEd. See "Using the WHERE Clause" [*].

The SET clause lists the names of one or more columns for which you must provide values. You can use a subquery to provide the values, as the following example shows:

EXEC SQL UPDATE emp 
    SET sal = (SELECT AVG(sal)*1.1 FROM emp WHERE deptno = 20) 
    WHERE empno = :EMP_NUMBER; 

Using the DELETE Statement

You use the DELETE statement to remove rows from a table or view. In the following example, you delete all employees in a given department from the EMP table:

EXEC SQL DELETE FROM emp 
    WHERE deptno = :DEPT_NUMBER; 

You can use the optional WHERE clause to specify the condition under which rows are DELETEd.

Using the WHERE Clause

You use the WHERE clause to SELECT, UPDATE, or DELETE only those rows in a table or view that meet your search condition. The WHERE-clause search condition is a boolean expression that can include scalar host variables, host arrays (not in SELECT statements), and subqueries.

If you omit the WHERE clause, all rows in the table or view are processed. If you omit the WHERE clause in an UPDATE or DELETE statement, Oracle sets the 5th element of SQLWARN in the SQLCA to `W' to warn that all rows were processed. For more information on SQLWARN, see "SQLCA Components" [*].

Using Cursors

When a query returns multiple rows, you can explicitly define a cursor to do the following:

You can also use host arrays. See Chapter 7, "Using Host Arrays."

A cursor identifies the current row in the result set returned by the query. This allows your program to process the rows of the query one at a time. The following statements let you define and manipulate a cursor:

First you use the DECLARE statement to name the cursor and associate it with a query.

The OPEN statement executes the query and identifies all the rows that meet the query search condition. These rows form a set called the active set of the cursor. After OPENing the cursor, you can use it to retrieve the rows returned by its associated query.

Rows of the active set are retrieved one by one unless you use host arrays. You use a FETCH statement to retrieve the current row in the active set. You can execute FETCH repeatedly until all rows have been retrieved.

When done FETCHing rows from the active set, you disable the cursor with a CLOSE statement, and the active set becomes undefined. See "Using FOR UPDATE OF" [*] for additional information.

The following sections show you how to use these cursor control statements in your application program.

Using the DECLARE Statement

You use the DECLARE statement to define a cursor by giving it a name and associating it with a query, as the following example shows:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
SELECT ename, empno, sal 
    FROM emp 
    WHERE deptno = :DEPT_NUMBER; 

The cursor name is an identifier used by the precompiler, not a host or program variable and cannot be passed from one precompilation unit to another, and need not be declared in the program. Cursor names cannot contain hyphens. They can be any length, but only the first 31 characters are significant. For ANSI compatibility, use cursor names no longer than 18 characters.

The SELECT statement associated with the cursor cannot include an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement.

Because it is declarative, the DECLARE statement must physically (not just logically) precede all other SQL statements referencing the cursor. That is, forward references to the cursor are not allowed. In the following example, the OPEN statement is misplaced:

... 
EXEC SQL OPEN emp_cursor; 
... 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
SELECT ename, empno, sal 
    FROM emp 
    WHERE ename = :EMP_NAME; 

The cursor control statements (DECLARE, OPEN, FETCH, and CLOSE) must all occur within the same precompiled unit. For example, you cannot DECLARE a cursor in file A, then OPEN it in file B.

Your host program can DECLARE as many cursors as it needs. However, in a given file, every DECLARE statement must be unique. That is, you cannot DECLARE two cursors with the same name in one precompilation unit, even across blocks or procedures, because the scope of a cursor is global within a file.

If you will be using many cursors, you might want to specify the MAXOPENCURSORS option. For more information, see Chapter 11, "Running the Pro*Ada Precompiler," and Appendix D, "Performance Tuning."

Using the OPEN Statement

You use the OPEN statement to execute the query and identify the active set. In the following example, you OPEN a cursor named emp_cursor:

EXEC SQL OPEN emp_cursor; 

OPEN positions the cursor just before the first row of the active set. It also zeroes the rows-processed count kept by the 3rd element of SQLERRD in the SQLCA. However, none of the rows is actually retrieved at this point. That will be done by the FETCH statement.

Once you OPEN a cursor, the query's input host variables are not reexamined until you reOPEN the cursor. Thus, the active set does not change. To change the active set, you must re-OPEN the cursor.

Generally, you should CLOSE a cursor before re-OPENing it. However, if you specify MODE=ORACLE (the default), you need not CLOSE a cursor before reOPENing it. This can boost performance. For details, see Appendix D, "Performance Tuning."

The amount of work done by OPEN depends on the settings of three precompiler options: HOLD_CURSOR, RELEASE_CURSOR, and MAXOPENCURSORS. For more information, see Chapter 11, "Running the Pro*Ada Precompiler," and Appendix D, "Performance Tuning."

Using the FETCH Statement

You use the FETCH statement to retrieve rows from the active set and specify the output host variables that will contain the results. Recall that the SELECT statement associated with the cursor cannot include an INTO clause. Rather, the INTO clause and list of output host variables are part of the FETCH statement. In the following example, you FETCH INTO three host variables:

EXEC SQL FETCH emp_cursor 
    INTO :EMP_NAME, :EMP_NUMBER, :SALARY; 

The cursor must have been previously DECLAREd and OPENed. The first time you execute FETCH, the cursor moves from before the first row in the active set to the first row. This row becomes the current row. Each subsequent execution of FETCH advances the cursor to the next row in the active set, changing the current row. The cursor can only move forward in the active set. To return to a row that has already been FETCHed, you must re-OPEN the cursor, then begin again at the first row of the active set.

If you want to change the active set, you must assign new values to the input host variables in the query associated with the cursor, then re-OPEN the cursor. When MODE={ANSI | ANSI14 | ANSI13}, you must CLOSE the cursor before reOPENing it.

As the next example shows, you can FETCH from the same cursor using different sets of output host variables. In this case, corresponding host variables in the INTO clause of each FETCH statement must have the same datatype.

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 20; 
... 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND raise ... 
loop 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME1, :SALARY1; 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME2, :SALARY2; 
    ... 
end loop; 
... 

If the active set is empty, or if it contains no more rows, FETCH returns the "no data found" Oracle error code to SQLCODE in the SQLCA or the SQLSTATE status variable. The status of the output host variables is indeterminate in this case. In a typical program, the WHENEVER NOT FOUND statement detects this condition. To reuse the cursor, you must reOPEN it.

Using the CLOSE Statement

When done FETCHing rows from the active set, you CLOSE the cursor to free the resources (such as storage) acquired by OPENing the cursor. When a cursor is closed, parse locks are released. What resources are freed depends on how you specify the HOLD_CURSOR and RELEASE_CURSOR options. In the following example, you CLOSE the cursor named emp_cursor:

EXEC SQL CLOSE emp_cursor; 

You cannot FETCH from a closed cursor because its active set becomes undefined. If necessary, you can reOPEN a cursor (with new values for the input host variables, for example).

When MODE={ANSI13 | ORACLE}, issuing a COMMIT or ROLLBACK closes cursors referenced in a CURRENT OF clause. Other cursors are unaffected by COMMIT or ROLLBACK, and if open, remain open. However, when MODE={ANSI | ANSI14}, issuing a COMMIT or ROLLBACK closes all explicit cursors. For more information about the COMMIT and ROLLBACK statements, see Chapter 6, "Defining and Controlling Transactions." See the next section for more information about the CURRENT OF clause.

Using the CURRENT OF Clause

You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row FETCHed from the named cursor. The cursor must be open and positioned on a row. If no FETCH has been done or if the cursor is not open, the CURRENT OF clause results in an error and processes no rows.

The FOR UPDATE OF clause is optional when you DECLARE a cursor that is referenced in the CURRENT OF clause of an UPDATE or DELETE statement. The CURRENT OF clause signals the precompiler to add a FOR UPDATE clause if necessary. For more information, see the section "Using FOR UPDATE OF" [*].

In the following example, you use the CURRENT OF clause to refer to the latest row FETCHed from a cursor named emp_cursor:

EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ename, sal FROM emp WHERE job = 'CLERK' 
    FOR UPDATE OF sal; 
... 
EXEC SQL OPEN emp_cursor; 
EXEC SQL WHENEVER NOT FOUND raise ... 
loop 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME, :SALARY; 
    ... 
    EXEC SQL UPDATE emp SET sal = :NEW_SALARY 
        WHERE CURRENT OF emp_cursor; 
end loop; 

Restrictions

You cannot use host arrays with the CURRENT OF clause. For an alternative, see "Mimicking CURRENT OF" [*].

An explicit FOR UPDATE OF or an implicit FOR UPDATE acquires exclusive row locks. All rows are locked at the OPEN, not as they are FETCHed. Row locks are released when you COMMIT or ROLLBACK. If you try to FETCH from a FOR UPDATE cursor after a COMMIT, you will get the following Oracle error:

ORA-01002: fetch out of sequence 


Sequence of Cursor Statements

The following example shows the typical sequence of cursor control statements in an application program:

... 
-- define a cursor 
EXEC SQL DECLARE emp_cursor CURSOR FOR 
    SELECT ename, job 
    FROM emp 
    WHERE empno = :EMP_NUMBER 
    FOR UPDATE OF job; 
-- open the cursor and identify the active set 
EXEC SQL OPEN emp_cursor; 
-- exit if the last row was already fetched 
EXEC SQL WHENEVER NOT FOUND GOTO NO_MORE; 
-- fetch and process data in a loop 
loop 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME, :JOB_TITLE; 
    ... 
    -- optional host-language statements that operate on 
    -- the FETCHed data 
 
    EXEC SQL UPDATE emp 
        SET job = :NEW_JOB_TITLE 
        WHERE CURRENT OF emp_cursor; 
end loop; 
... 
<<NO_MORE>> 
-- disable the cursor 
EXEC SQL CLOSE emp_cursor; 
EXEC SQL COMMIT RELEASE; 
... 


A Complete Example

The following Pro*Ada program illustrates the use of a cursor and the FETCH statement. The program displays the names and hire dates of all employees that are in departments with numbers greater than 20.

All FETCHes except the final one return a row, and also return success status codes if no errors were detected during the FETCH. The final FETCH fails and returns the "no data found" Oracle error code to SQLCODE in the SQLCA. The cumulative number of rows actually FETCHed is found in the 3rd element of SQLERRD in the SQLCA.

--  FETCH : 
--    1)    Declare a statement to be executed in a 
--          cursor (Emp_Curs) 
--    2)    Connect to ORACLE 
--    3)    Open the cursor 
--    4)    Fetch the rows 
--    5)    Close the cursor and disconnect 
 
with text_io, integer_text_io; 
procedure FETCH_SAMPLE is 
use text_io, integer_text_io; 
 
USER         : constant STRING := "SCOTT/TIGER"; 
EMP_NAME     : STRING(1..20); 
DATES        : STRING(1..30); 
SQL_ERROR    : exception; 
 
EXEC SQL DECLARE emp_curs CURSOR FOR 
    SELECT ename, TO_CHAR(hiredate, 'DAY MONTH DD, YYYY') 
      FROM emp WHERE deptno > 20; 
 
EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 
 
begin 
    -- Logon as SCOTT/TIGER 
    EXEC SQL CONNECT :USER; 
    -- Execute the SELECT. 
    EXEC SQL OPEN EMP_CURS; 
    NEW_LINE; 
    PUT_LINE("Name                 Hire Date"); 
    PUT_LINE("----                 ----------"); 
    NEW_LINE; 
 
    -- Loop to FETCH all active rows that were 
    -- obtained when the cursor's statement was 
    -- executed.  Once a row is FETCHed, it cannot 
    -- be reFETCHed unless the cursor is re-opened. 
    FETCH_LOOP: loop 
    declare ALLFETCHED : exception; 
    -- Turn on the automatic exception generation. 
    EXEC SQL WHENEVER NOT FOUND raise ALLFETCHED; 
    begin 
        EXEC SQL FETCH emp_curs INTO :EMP_NAME, :DATES; 
        PUT_LINE(EMP_NAME & " " & DATES); 
        exception 
            when ALLFETCHED => 
                NEW_LINE; 
                PUT("Rows fetched: "); 
                PUT(SQLCA.SQLERRD(3), WIDTH => 3); 
                NEW_LINE; 
                exit; 
    end; 
    end loop FETCH_LOOP; 
 
    EXEC SQL CLOSE EMP_CURS; 
    EXEC SQL COMMIT RELEASE; 
 
    exception 
    -- Turn off automatic exception handling, 
    -- since we do not want an infinite loop if 
    -- an error occurs on log out. 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    when SQL_ERROR => 
        PUT(ORACLE.ERROR.MESSAGE); 
        EXEC SQL ROLLBACK RELEASE; 
end FETCH_SAMPLE; 


Advantages of PL/SQL

This section looks at some of the features and benefits offered by PL/SQL, such as

For more information, see the PL/SQL User's Guide and Reference.

Better Performance

PL/SQL can help you reduce overhead, improve performance, and increase productivity. For example, without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to the server and higher overhead. However, with PL/SQL, you can send an entire block of SQL statements to the Server. This minimizes communication between your application and Oracle.

Integration with Oracle

PL/SQL is tightly integrated with the Oracle7 Server. For example, most PL/SQL datatypes are native to the Oracle data dictionary. Furthermore, you can use the %TYPE attribute to base variable declarations on column definitions stored in the data dictionary, as the following example shows:

job_title  emp.job%TYPE; 

That way, you need not know the exact datatype of the column. Furthermore, if a column definition changes, the variable declaration changes accordingly and automatically. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes.

Cursor FOR Loops

With PL/SQL, you need not use the DECLARE, OPEN, FETCH, and CLOSE statements to define and manipulate a cursor. Instead, you can use a cursor FOR loop that implicitly declares its loop index as a record, opens the cursor associated with a given query, repeatedly fetches data from the cursor into the record, then closes the cursor. An example follows:

DECLARE 
    ... 
BEGIN 
    FOR emprec IN (SELECT empno, sal, comm FROM emp) LOOP 
        IF emprec.comm / emprec.sal > 0.25 THEN ... 
        ... 
    END LOOP; 
END; 

Notice that you use dot notation to reference fields in the record. In the example above, you reference the comm field in the emprec record by typing emprec.comm.

Procedures and Functions

Like Ada, PL/SQL has two types of subprograms called procedures and functions that aid application development by letting you isolate operations. Generally, you use a procedure to perform an action and a function to compute a value.

Procedures and functions provide extensibility. That is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates a new department, just write your own as follows:

PROCEDURE create_dept 
    (new_dname  IN CHAR(14), 
     new_loc    IN CHAR(13), 
     new_deptno OUT NUMBER(2)) IS 
BEGIN 
    SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual; 
    INSERT INTO dept VALUES (new_deptno, new_dname, new_loc); 
END create_dept; 

When called, this procedure accepts a new department name and location, selects the next value in a department-number database sequence, inserts the new number, name, and location into the dept table, then returns the new number to the caller.

You use parameter modes to define the behavior of formal parameters. There are three parameter modes: IN (the default), OUT, and IN OUT. Procedures can take IN, OUT, and IN OUT parameters, but functions can take only IN arguments.

An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of a procedure. An IN OUT parameter lets you pass initial values to the procedure being called and return updated values to the caller.

Packages

PL/SQL lets you bundle logically related types, program objects and subprograms into a package. Packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications.

As in Ada, packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification. In the following example, you "package" two employment procedures:

PACKAGE emp_actions IS  -- package specification 
    PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); 
    PROCEDURE fire_employee (emp_id NUMBER); 
END emp_actions; 
 
PACKAGE BODY emp_actions IS  -- package body 
    PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS 
    BEGIN 
        INSERT INTO emp VALUES (empno, ename, ...); 
    END hire_employee; 
    PROCEDURE fire_employee (emp_id NUMBER) IS 
    BEGIN 
        DELETE FROM emp WHERE empno = emp_id; 
    END fire_employee; 
END emp_actions; 

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.

PL/SQL Tables

PL/SQL provides a composite datatype named TABLE. Objects of type TABLE are called PL/SQL tables and are modelled as (but not the same as) database tables. PL/SQL tables have only one data column and use a primary key to give you array-like access to rows. The column can have any scalar type (such as CHAR, DATE, or NUMBER), but the primary key must have the type BINARY_INTEGER. Although you cannot use SQL statements to manipulate a PL/SQL table, its primary key gives you array-like access to rows. Think of the key and rows as the index and elements of a one-dimensional array.

Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection. However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. So, a PL/SQL table can be indexed by any series of integers (5, 10, 15, 20, 25, ... for instance).

You can declare PL/SQL table types in the declarative part of any block, procedure, function, or package. In the following example, you declare a TABLE type called NumTabTyp:

... 
EXEC SQL EXECUTE 
    DECLARE 
        TYPE NumTabTyp IS TABLE OF NUMBER 
            INDEX BY BINARY_INTEGER; 
        ... 
    BEGIN 
        ... 
    END; 
END-EXEC; 
... 

Once you define type NumTabTyp, you can declare PL/SQL tables of that type, as the next example shows:

num_tab  NumTabTyp; 

The identifier num_tab represents an entire PL/SQL table.

You reference rows in a PL/SQL table using array-like syntax to specify the primary key value. For example, you reference the ninth row in the PL/SQL table named num_tab as follows:

num_tab(9) ... 

User-Defined Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched by a cursor. However, you cannot specify the datatypes of fields in the record or define fields of your own. The composite datatype RECORD lifts those restrictions.

Objects of type RECORD are called records. Unlike PL/SQL tables, records have uniquely named fields that can belong to different datatypes. For example, suppose you have different kinds of data about an employee such as name, salary, hire date, and so on. This data is dissimilar in type but logically related. A record that contains such fields as the name, salary, and hire date of an employee would let you treat the data as a logical unit.

You can declare record types and objects in the declarative part of any block, procedure, function, or package. In the following example, you declare a RECORD type called DeptRecTyp:

DECLARE 
    TYPE DeptRecTyp IS RECORD 
        (deptno  NUMBER(4) := 10, 
         dname   VARCHAR2(9), 
         loc     VARCHAR2(14)); 

Notice that the field declarations are like variable declarations. Each field has a unique name and specific datatype. You can assign a value to any field declaration to prevent the assigning of nulls to that field.

Once you define type DeptRecTyp, you can declare records of that type, as the next example shows:

dept_rec  DeptRecTyp; 

The identifier dept_rec represents an entire record.

You use dot notation to reference individual fields in a record. For example, you reference the dname field in the dept_rec record as follows:

dept_rec.dname ... 


Embedding PL/SQL Blocks

The Pro*Ada Precompiler treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a Pro*Ada program that you can place a SQL statement.

To embed a PL/SQL block in your program, simply bracket the PL/SQL block with the keywords EXEC SQL EXECUTE and END-EXEC as follows:

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

The keyword END-EXEC must be followed by a semi-colon (;).

Note: Bear in mind that the syntax of PL/SQL is very similar to Ada, but it is not identical. All code within an EXEC SQL EXECUTE ... END-EXEC block must be PL/SQL. There cannot be any Ada code in a PL/SQL block.

After writing your program, you precompile the source file in the usual way.

Note: You must set SQLCHECK=FULL to precompile embedded PL/SQL blocks, even though this option does not perform semantic checking. For more information, see "Using the Precompiler Options" [*].


Using Host Variables with PL/SQL

Host variables are the key to communication between a host language and a PL/SQL block. Host variables can be shared with PL/SQL, meaning that PL/SQL can set and reference host variables.

For example, you can prompt a user for information and use host variables to pass that information to a PL/SQL block. Then, PL/SQL can access the database and use host variables to pass the results back to your host program.

Inside a PL/SQL block, host variables are treated as global to the entire block and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.

An Example

The following example illustrates the use of host variables with PL/SQL. The program prompts the user for an employee number, then displays the job title, hire date, and salary of that employee.

Notice that the host variable EMP_NUMBER is set before the PL/SQL block is entered, and the host variables JOB_TITLE, HIRE_DATE, and SALARY are set inside the block.

-- PLS1
-- Precompile this file with the options SQLCHECK=SEMANTICS
--   and USERID=SCOTT/TIGER.

with text_io, 
     float_io, 
     varchar_text_io, 
     integer_text_io; 

procedure PLS1 is 
use text_io, 
    float_io, 
    integer_text_io; 

USER_ID     : constant string := "SCOTT/TIGER"; 
EMP_NUMBER  : integer; 
EMP_NAME    : ORACLE.VARCHAR(20); 
JOB_TITLE   : ORACLE.VARCHAR(20); 
HIRE_DATE   : string(1..9); 
SALARY      : float; 
SPACES      : string(1..12) := (1..12 => ' '); 
SQL_ERROR   : exception; 


begin 
    EXEC SQL WHENEVER SQLERROR RAISE SQL_ERROR; 
    EXEC SQL CONNECT :USER_ID; 

    loop 
        PUT("Enter employee number (0 to end): "); 
        GET(EMP_NUMBER); 
        exit when EMP_NUMBER = 0; 
        EXEC SQL EXECUTE 
            BEGIN 
                SELECT ename, job, hiredate, sal INTO 
                :EMP_NAME, :JOB_TITLE, :HIRE_DATE, :SALARY 
                FROM emp 
                WHERE empno = :EMP_NUMBER; 
            END; 
        END-EXEC; 

        NEW_LINE; 
        PUT_LINE( 
          "Name      Number    Job Title  Hiredate  Salary"); 
        PUT_LINE( 
          "-----------------------------------------------"); 
        VARCHAR_TEXT_IO.PUT(EMP_NAME); 
        PUT(SPACES(1..10-INTEGER(EMP_NAME.LENGTH))); 
        PUT(EMP_NUMBER, 4); PUT("      "); 
        VARCHAR_TEXT_IO.PUT(JOB_TITLE); 
        PUT(SPACES(1..11-INTEGER(JOB_TITLE.LENGTH))); 
        PUT(HIRE_DATE); PUT(" "); 
        PUT(SALARY, FORE => 4, AFT => 2, EXP => 0); 
        NEW_LINE; 
    end loop; 

    EXEC SQL COMMIT RELEASE; 

    exception 
        when SQL_ERROR => 
            PUT_LINE(" ** ORACLE ERROR OCCURRED **"); 
            PUT_LINE(ORACLE.ERROR.MESSAGE); 

end PLS1; 

A More Complex Example

In the example below, you prompt the user for a bank account number, transaction type, and transaction amount, then debit or credit the account. If the account does not exist, you raise an exception. When the transaction is complete, you display its status.

-- PLS2
-- Precompile this program with the options SQLCHECK=SEMANTICS and
--  USERID=SCOTT/TIGER
-- Before running the program, create a table ACCTS as:
--   CREATE TABLE ACCTS (ACCTID NUMBER, BAL NUMBER);
--
-- And put at least one account ID in the table:
--   INSERT INTO ACCTS VALUES (1001, 0.0);
-- then you can use the credit (C) option to increase
-- the balance.

with text_io, 
     float_io, 
     varchar_text_io, 
     integer_text_io; 

procedure PLS2 is 

use text_io, 
    float_io, 
    varchar_text_io, 
    integer_text_io; 

USER_NAME    : ORACLE.VARCHAR(20); 
PASSWORD     : ORACLE.VARCHAR(20); 
ACCOUNT_NUM  : integer; 
TRANS_TYPE   : string(1..1); 
TRANS_AMOUNT : float; 
STATUS       : string(1..80); 
SPACES       : string(1..12) := (1..12 => ' '); 
SQL_ERROR    : exception; 

begin 
    EXEC SQL WHENEVER SQLERROR RAISE SQL_ERROR; 
    PUT("Your name: "); 
    GET_LINE(USER_NAME); 
    PUT("Your password: "); 
    GET_LINE(PASSWORD); 
    EXEC SQL CONNECT :USER_NAME IDENTIFIED BY :PASSWORD; 
    PUT_LINE("Connected to ORACLE"); 

    loop 
        PUT("Account number (0 to end)? "); 
        GET(ACCOUNT_NUM); 
        SKIP_LINE;
        exit when ACCOUNT_NUM = 0; 
        PUT("Transaction type: D)ebit or C)redit? "); 
        GET(TRANS_TYPE); 
        SKIP_LINE; 
        PUT("Enter transaction amount (999999.99): "); 
        GET(TRANS_AMOUNT); 
        SKIP_LINE; 

------- begin PL/SQL block ----------------------------- 
        EXEC SQL EXECUTE 
        DECLARE 
            old_bal         NUMBER(9,2); 
            err_msg         CHAR(70); 
            nonexistent     EXCEPTION; 
        BEGIN 
            :TRANS_TYPE := UPPER(:TRANS_TYPE); 
            IF :TRANS_TYPE = 'C' THEN 
                UPDATE ACCTS SET bal = bal + :TRANS_AMOUNT 
                    WHERE acctid = :ACCOUNT_NUM; 
                IF SQL%ROWCOUNT = 0 THEN 
                    RAISE nonexistent; 
                ELSE 
                    :STATUS := 'Credit applied'; 
                END IF; 
            ELSIF :TRANS_TYPE = 'D' THEN 
                SELECT bal INTO old_bal FROM accts 
                    WHERE acctid = :ACCOUNT_NUM; 
                IF old_bal >= :TRANS_AMOUNT THEN 
                    UPDATE accts SET bal = bal - :TRANS_AMOUNT 
                        WHERE acctid = :ACCOUNT_NUM; 
                    :STATUS := 'Debit applied'; 
                ELSE 
                    :STATUS := 'Insufficient funds'; 
                END IF; 
            ELSE 
                :STATUS := 'Invalid type: ' || :TRANS_TYPE; 
            END IF; 
            COMMIT; 

        EXCEPTION 
            WHEN NO_DATA_FOUND OR nonexistent THEN 
                :STATUS := 'Nonexistent account'; 
            WHEN OTHERS THEN 
                err_msg := SUBSTR(SQLERRM, 1, 70);
                :STATUS := 'Error: ' || err_msg; 
        END; 
        END-EXEC; 
------- end PL/SQL block ---------------------------------- 

        NEW_LINE; 
        PUT("Status: "); PUT_LINE(STATUS); 
    end loop; 

    exception 
        when SQL_ERROR => 
            PUT_LINE(" ** ORACLE ERROR OCCURRED **"); 
            PUT_LINE(ORACLE.ERROR.MESSAGE); 

end PLS2; 

Using VARCHAR with PL/SQL

You can use the ORACLE.VARCHAR datatype to declare variable-length strings. Oracle automatically sets the length component of a VARCHAR output host variable. However, to use a VARCHAR output host variable in your PL/SQL block, you must initialize the length component before entering the PL/SQL block.

If you use VARCHAR_TEXT_IO.GET_LINE or VARCHAR_TEXT_IO.GET to assign a value to the VARCHAR variable, the LENGTH component is set for you. But if you copy characters into the VARCHAR in a loop, or otherwise assign a string to the VARCHAR, you must set the length field. This is shown in the following example:

C_STR         : string(1..80); 
C_STR_LEN     : integer; 
V_STR         : ORACLE.VARCHAR(80); 
 
... 
TEXT_IO.GET_LINE(C_STR, C_STR_LEN); 
V_STR.buffer := C_STR(1..C_STR_LEN); 
-- this is MANDATORY!! 
V_STR.LENGTH := ORACLE.UNSIGNED_SHORT(C_STR_LEN); 
... 

Using Indicator Variables with PL/SQL

PL/SQL does not need indicator variables because it can manipulate nulls. For example, within PL/SQL, you can use the IS NULL operator to test for null values, as follows:

IF variable IS NULL THEN ... 

And, you can use the PL/SQL assignment operator (:=) to assign nulls, as follows:

variable := NULL; 

However, host languages need indicator variables because they cannot manipulate nulls. Embedded PL/SQL meets this need by letting you use indicator variables to:

When used in a PL/SQL block, indicator variables are subject to the following rules:

In the following example, the indicator variable IND_COMM appears with its host variable COMMISSION in the SELECT statement, so it must appear that way in the IF statement:

EXEC SQL EXECUTE 
    BEGIN 
        SELECT ename, comm 
            INTO :EMP_NAME, :COMMISSION INDICATOR :IND_COMM 
            FROM emp 
            WHERE empno = :EMP_NUMBER; 
 
        IF :COMMISSION INDICATOR :IND_COMM IS NULL THEN 
... 
        END IF; 
... 
    END; 
END-EXEC; 

Notice that PL/SQL treats :COMMISSION INDICATOR :IND_COMM like any other simple variable.

Although you cannot refer directly to an indicator variable inside a PL/SQL block, PL/SQL checks the value of the indicator variable when entering the block. PL/SQL then sets the value correctly when exiting the block.

Handling Nulls

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a null to the host variable. When exiting the block, if a host variable is a null, PL/SQL automatically assigns a value of -1 to the indicator variable.

In the next example, if IND_SAL had a value of -1 before the PL/SQL block was entered, the salary_missing exception is raised.

EXEC SQL EXECUTE 
    BEGIN 
    IF :SALARY :IND_SAL IS NULL THEN 
        RAISE salary_missing; 
    END IF; 
    ... 
    END; 
END-EXEC; 

Handling 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 IND_NAME, if a truncated value was assigned to EMP_NAME:

EXEC SQL EXECUTE 
    BEGIN 
    ... 
        :EMP_NAME INDICATOR :IND_NAME := NEW_NAME; 
    ... 
    END; 
END-EXEC; 


Using Host Arrays with PL/SQL

You can pass input host arrays and indicator arrays to a PL/SQL block. Like a locally declared PL/SQL table, they can be indexed by a PL/SQL variable of type BINARY_INTEGER or by a host variable compatible with that type.

Furthermore, you can use a procedure call to assign all the values in a host array to rows in a PL/SQL table. Given that the array subscript range is m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.

In the example below, you pass an Ada array named SALARY to a PL/SQL block that uses the array in a function call. The function is named median because it finds the middle value in a series of numbers. Its formal parameters include a PL/SQL table named tab. The function call assigns all the values in the actual parameter SALARY to rows in the formal parameter tab.

    ... 
    type sal_array is array (1..100) of real; 
    SALARY     sal_array; 
 
-- populate the host array, then execute a PL/SQL block 
 
EXEC SQL EXECUTE 
    DECLARE 
        TYPE NumTabTyp IS TABLE OF REAL 
            INDEX BY BINARY_INTEGER; 
        ... 
        FUNCTION median (tab NumTabTyp, n BINARY_INTEGER) 
            RETURN REAL IS 
        BEGIN
          ...
        END; 
        median_salary  REAL; 
        n  BINARY_INTEGER; 
    BEGIN 
        n := 100; 
        median_salary := median(:salary, n); 
        ... 
    END; 
END-EXEC; 
... 

Pro*Ada does not check your usage of host arrays. For example, no index range-checking is done.

Using Cursors with PL/SQL

Every embedded SQL statement is assigned a cursor, either explicitly by you in a DECLARE CURSOR statement or implicitly by the precompiler. Internally, the precompiler maintains a cache, called the cursor cache, to control the execution of embedded SQL statements. When executed, every SQL statement is assigned an entry in the cursor cache. This entry is linked to a private SQL area in your System Global Area (SGA) within Oracle.

Various precompiler options, including MAXOPENCURSORS, HOLD_CURSOR, and RELEASE_CURSOR, let you manage the cursor cache to improve performance. For example, RELEASE_CURSOR controls what happens to the link between the cursor cache and private SQL area. If you specify RELEASE_CURSOR=YES, the link is removed after Oracle executes the SQL statement. This frees memory allocated to the private SQL area and releases parse locks.

For purposes of cursor cache management, an embedded PL/SQL block is treated just like a SQL statement. At run time, a cursor, called a parent cursor, is associated with the entire PL/SQL block. A corresponding entry is made to the cursor cache, and this entry is linked to a private SQL area in the SGA.

Each SQL statement inside the PL/SQL block also requires a private SQL area in the SGA. So, PL/SQL manages a separate cache, called the child cursor cache, for these SQL statements. Their cursors are called child cursors. Because PL/SQL manages the child cursor cache, you do not have direct control over child cursors.

The maximum number of cursors your program can use simultaneously is set by the Oracle initialization parameter OPEN_CURSORS. To determine the number of cursors in use, sum the following:

Note: The sum of the cursors in use must not exceed the value of OPEN_CURSORS.

If your program exceeds the limit imposed by OPEN_CURSORS, you get the following Oracle error:

ORA-01000: maximum open cursors exceeded 

You can avoid this error by specifying the RELEASE_CURSOR=YES and HOLD_CURSOR=NO options. If you do not want to precompile the entire program with RELEASE_CURSOR set to YES, simply reset it to NO after each PL/SQL block, as follows:

EXEC ORACLE OPTION (RELEASE_CURSOR=YES); 
    -- first embedded PL/SQL block 
EXEC ORACLE OPTION (RELEASE_CURSOR=NO); 
    -- embedded SQL statements 
EXEC ORACLE OPTION (RELEASE_CURSOR=YES); 
    -- second embedded PL/SQL block 
EXEC ORACLE OPTION (RELEASE_CURSOR=NO); 
    -- embedded SQL statements 
... 

For more information, see Chapter 11, "Running the Pro*Ada Precompiler," and Appendix D, "Performance Tuning."

An Alternative

The MAXOPENCURSORS option specifies the initial size of the cursor cache. For example, when MAXOPENCURSORS=10, the cursor cache can hold up to ten entries. If a new cursor is needed and there are no free cache entries, the precompiler tries to reuse an entry if HOLD_CURSOR=NO. If you specify a very low value for MAXOPENCURSORS, the precompiler is forced to reuse the parent cursor more often. All the child cursors are released as soon as the parent cursor is reused.


Calling Stored Subprograms

Unlike anonymous blocks, PL/SQL subprograms (procedures and functions) can be compiled separately, stored in an Oracle database, and invoked. A subprogram explicitly CREATEd using an Oracle tool such as SQL*DBA is called a stored subprogram. Once compiled and stored in the data dictionary, it is a database object that can be re-executed without being recompiled.

When a subprogram within a PL/SQL block or stored procedure is sent to Oracle by your application, it is called an inline subprogram. Oracle compiles the inline subprogram and caches it in the System Global Area (SGA), but does not store the source or object code in the data dictionary.

Subprograms defined within a package are considered part of the package, and so are called packaged subprograms. Stored subprograms not defined within a package are called standalone subprograms.

To invoke (call) a stored subprogram from your host program, you must use an anonymous PL/SQL block. In the following example, you call a stand-alone procedure named raise_salary:

EXEC SQL EXECUTE 
    BEGIN 
        raise_salary(:EMP_ID, :INCREASE); 
    END; 
END-EXEC; 

Notice that stored subprograms can take parameters. In this example, the actual parameters EMP_ID and INCREASE are host variables.

In the next example, the procedure raise_salary is stored in a package named emp_actions, so you must use dot notation to fully qualify the procedure call:

EXEC SQL EXECUTE 
    BEGIN 
        emp_actions.raise_salary(:EMP_ID, :INCREASE); 
    END; 
END-EXEC; 

An actual IN parameter can be a literal, host variable, host array, PL/SQL constant or variable, PL/SQL user-defined record, function call, or expression. However, an actual OUT parameter can only be a host variable, host array, PL/SQL variable, or PL/SQL user-defined record. Also, the datatype of each actual parameter must be convertible to the datatype of its corresponding formal parameter. Refer to Figure 3 - 1 [*] for a table of legal conversions.

Before a stored procedure is exited, all OUT formal parameters must be assigned values. Otherwise, PL/SQL raises the NO_DATA_FOUND predefined exception.

Using Ada Arrays and PL/SQL Tables

You can call a stored procedure and pass Ada arrays to it as parameters. The PL/SQL stored procedure declares tables as the actual parameters corresponding to the Ada host arrays in the call.

The following complete program demonstrates how to use host arrays with PL/SQL tables in a stored procedure. The program is available on-line in the precompiler demo directory.

-- CALLSP.PAD:  Calling a Stored Procedure 
-- 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. 

-- The PL/SQL package is listed below, commented out as it not 
-- a part of this Pro*Ada program.  This package is also 
-- available on-line in the file CALLDEMO.SQL. 

-- To run this program, you must first store the package in 
-- the SCOTT schema, using SQL*DBA or SQL*Plus. 

-- CREATE OR REPLACE PACKAGE calldemo AS 
-- 
--    TYPE char_array IS TABLE OF VARCHAR2(20) 
--        INDEX BY BINARY_INTEGER; 
--    TYPE num_array IS TABLE OF number 
--        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, ROUND(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 get_employees; 
-- END calldemo; 
-- / 

with text_io, integer_text_io, float_text_io, 
     oracle_types, array_binds; 
use text_io, integer_text_io, float_text_io; 
 
procedure CALLSP is 

subtype NAME_STRING is STRING(1..10); 
type    NAME_ARRAY is array (INTEGER range <>) of NAME_STRING; 

package NAME_BINDS is new ARRAY_BINDS ( 
    NAME_STRING, 
    oracle.indicator, 
    NAME_ARRAY, 
    oracle.indicator_array, 
    ORACLE_TYPES.ORACLE_TYPE); 

use NAME_BINDS; 

USERNAME    : constant STRING := "SCOTT"; 
PASSWORD    : constant string := "TIGER"; 
DEPT_NUM, DONE_FLAG, NUM_RET, TABLE_SIZE  : integer; 
JOB                  : NAME_ARRAY(1..5); 
EMPLOYEE_NAME        : NAME_ARRAY(1..5); 
SALARY               : ORACLE.INTEGER_ARRAY(1..5); 
SQL_ERROR   : exception; 
SQL_WARNING : exception; 

procedure PRINT_ROWS (N : INTEGER) is 

begin 
    if N < 1 then 
        PUT_LINE("No rows returned."); 
    else 
        PUT("Got"); PUT(N, WIDTH => 3); 
        PUT_LINE(" rows."); 
        NEW_LINE; 
        PUT_LINE("Name      Job       Salary"); 
        PUT_LINE("--------------------------"); 
 
        for I in 1..N loop 
            PUT(EMPLOYEE_NAME(I)); 
            PUT(JOB(I)); 
            PUT(SALARY(I), WIDTH => 6); 
            NEW_LINE; 
        end loop; 
    end if; 

end PRINT_ROWS; 

begin 
    EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 
    EXEC SQL WHENEVER SQLWARNING raise SQL_WARNING; 
 
-- Connect to ORACLE 
    EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD; 
 
    NEW_LINE; 
    PUT_LINE("Connected to ORACLE as " & USERNAME); 
    NEW_LINE; 
     PUT("Enter department number: "); 
    GET(DEPT_NUM); 
    TABLE_SIZE := 3;   -- set small for demo purposes 
 
    loop 
        EXEC SQL EXECUTE 
            BEGIN 
                calldemo.get_employees ( 
                    :DEPT_NUM, :TABLE_SIZE, :NUM_RET, :DONE_FLAG, 
                    :EMPLOYEE_NAME, :JOB, :SALARY); 
            END; 
        END-EXEC; 
 
        PRINT_ROWS(NUM_RET); 
        exit when DONE_FLAG /= 0; 
    end loop; 
     EXEC SQL COMMIT RELEASE; 

  exception 
-- Turn off error checking, since we do not want to raise an
-- exception when logging out under any circumstance.
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL WHENEVER SQLWARNING CONTINUE; 
    when SQL_ERROR => 
      PUT_LINE(" ** ORACLE ERROR OCCURRED **"); 
      NEW_LINE; 
      PUT_LINE(ORACLE.ERROR.MESSAGE); 
      EXEC SQL ROLLBACK RELEASE; 
    when SQL_WARNING => 
      PUT_LINE(" ** ORACLE WARNING OCCURED **"); 
      NEW_LINE; 
      EXEC SQL ROLLBACK RELEASE;
end CALLSP; 

Remote Access

PL/SQL lets you access remote databases via database links. Typically, database links are established by your DBA and stored in the Oracle data dictionary. A database link tells Oracle where the remote database is located, the path to it, and what Oracle username and password to use. In the following example, you use the database link dallas to call the raise_salary procedure:

EXEC SQL EXECUTE 
    BEGIN 
        raise_salary@dallas(:EMP_ID, :INCREASE); 
    END; 
END-EXEC; 

You can create synonyms to provide location transparency for remote subprograms, as the following example shows:

CREATE PUBLIC SYNONYM raise_salary 
    FOR raise_salary@dallas; 

For more information, see the Oracle7 Server Administrator's Guide.

Using Dynamic SQL Methods with PL/SQL

The precompiler treats an entire PL/SQL block like a single SQL statement, so you can store a PL/SQL block in a string host variable.

If the PL/SQL block contains no host variables, you can use dynamic SQL Method 1 to EXECUTE the PL/SQL string. If the block contains a known number of host variables, you can use dynamic SQL Method 2 to PREPARE and EXECUTE the PL/SQL string. If the block contains an unknown number of host variables, you must use dynamic SQL Method 4.

For more information, see Chapter 8, "Introduction to Dynamic SQL," and Chapter 9, "Implementing Dynamic SQL Method 4."

Warning: In dynamic SQL Method 4, a host array cannot be bound to a PL/SQL procedure with a parameter of type "table."




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