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

Introduction to Dynamic SQL


This chapter shows you how to use dynamic SQL, an advanced programming technique that adds flexibility and functionality to your applications. After weighing the advantages and disadvantages of dynamic SQL, you learn about four methods, from simple to complex, for writing programs that accept and process SQL statements "on the fly" at runtime. You learn the requirements and limitations of each method, and how to choose the right method for a given job. The following topics are discussed in this chapter:

Note: Method 4 is the most complex method and is explained[*], "Implementing Dynamic SQL Method 4."


Dynamic SQL

Most database applications do a specific job. For example, a simple program might prompt the user for an employee number, then UPDATE rows in the EMP and DEPT tables. In this case, you know the makeup of the UPDATE statement at precompile time. That is, you know which tables might be changed, the constraints defined for each table and column, which columns might be updated, and the datatype of each column.

However, some applications must accept (or build) and process a variety of SQL statements at runtime. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the statement's makeup is unknown until run time. Such statements can, and probably will, change from execution to execution. They are aptly called dynamic SQL statements.

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 runtime. They can be entered interactively or read from a file.


Advantages and Disadvantages of Dynamic SQL

Pro*Ada programs that accept and process dynamically defined SQL statements are more versatile than those using static embedded SQL statements. Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL.

For example, your program might simply prompt users for a search condition to be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more complex program might allow users to choose from menus listing SQL operations, table and view names, column names, and so on. Thus, dynamic SQL lets you write highly flexible applications.

However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing. While you might not notice the added processing time, you might find the coding difficult, unless you fully understand dynamic SQL concepts and methods.

In practice, static SQL will meet most of your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one or more of the following is unknown at precompile time:


Requirements for Dynamic SQL Statements

To represent a dynamic SQL statement, a character string must contain the text of a valid SQL statement, but not contain the EXEC SQL clause, host-language delimiters or statement terminator, or any of the following embedded SQL commands:

CLOSE       DECLARE      DESCRIBE     EXECUTE      FETCH
INCLUDE     OPEN         PREPARE      WHENEVER

In most cases, the character string can contain placeholders. They hold places in the SQL statement for actual host variables. Placeholders are not host variables. You do not need to declare them and can name them anything you like. For example, Oracle makes no distinction between the following two strings:

DELETE FROM emp WHERE mgr = :MGR_NUMBER AND job = :JOB_TITLE 
DELETE FROM emp WHERE mgr = :M AND job = :J 


How Dynamic SQL Statements Are Processed


Methods for Using Dynamic SQL

This section introduces four methods you can use to define dynamic SQL statements. It briefly describes the capabilities and limitations of each method, then offers guidelines for choosing the right method. Later sections show you how to use the methods.

The four methods are increasingly general. That is, Method 2 encompasses Method 1, Method 3 encompasses Methods 1 and 2, and so on. However, each method is most useful for handling a certain kind of SQL statement, as the following table shows:

Method Kind of SQL Statement
1 Nonquery without input host variables
2 Nonquery with known number of input host variables
3 Query with known number of select-list items and input host variables
4 Statement with unknown number of select-list items or input host variables
Table 8 - 1. Dynamic SQL Methods

Note: The term select-list item includes column names and expressions such as sal * 1.10 or MAX(sal).

Method 1

Method 1 lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement), and must not contain any placeholders for input host variables. For example, the following host strings qualify:

DELETE FROM emp WHERE deptno = 20;
GRANT SELECT ON emp TO scott;

With Method 1, the SQL statement is parsed each time it is executed.

Method 2

Method 2 lets your program accept or build a dynamic SQL statement, then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. For example, the following host strings fall into this category:

INSERT INTO emp (ename, job) VALUES (:EMP_NAME, :JOB_TITLE);
DELETE FROM emp WHERE empno = :EMP_NUMBER;

With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables.

Method 3

Method 3 lets your program accept or build a dynamic query, then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number and types of select-list items, the number and types of placeholders for input host variables, and the number and datatypes of the input host variables must be known at precompile time. For example, the following host strings qualify:

SELECT deptno, MIN(sal), MAX(sal) FROM emp GROUP BY deptno;
SELECT ename, empno FROM emp WHERE deptno = :DEPT_NUMBER;

Method 3 is used for dynamic queries with a known select list.

Method 4

Method 4 lets your program accept or build a dynamic SQL statement, then process it using descriptors. This is discussed[*], "Implementing Dynamic SQL Method 4." Use Method 4 when the number of select-list items, the number of placeholders for input host variables, or the datatype of an input host variable is not known until runtime. For example, the following host strings fall into this category:

INSERT INTO emp (<unknown>) VALUES (<unknown>);
SELECT <unknown> FROM emp WHERE deptno = 20;

Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables.

Guidelines

With all four methods, you must place the dynamic SQL statement in a character string, which must be a host variable or quoted literal. In either case, omit the keywords EXEC SQL and the statement terminating semicolon.

With Methods 2 and 3, the number and types of placeholders for input host variables and the number and datatypes of the input host variables must be known at precompile time.

Each succeeding method imposes fewer constraints on your application, but is more difficult to code. As a rule, use the simplest method you can. However, if a dynamic SQL statement will be executed repeatedly by Method 1, use Method 2 instead to avoid reparsing for each execution.

Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. In general, use Method 4 only if you cannot use Method 1, 2, or 3.

The decision logic in Figure 8 - 1 will help you choose the right method.

Figure 8 - 1. Choosing a Dynamic SQL Method


Using Method 1

The simplest kind of dynamic SQL statement results only in success or failure and uses no host variables. That excludes queries. Some examples follow:

DELETE FROM table_name WHERE column_name = constant;
CREATE TABLE table_name ...;
DROP INDEX index_name;
UPDATE table_name SET column_name = constant;
GRANT SELECT ON table_name TO username;
REVOKE RESOURCE FROM username;

This method is the standard way to perform dynamic DDL statements.

Method 1 parses, then immediately executes the SQL statement using the EXECUTE IMMEDIATE command. The command is followed by a character string containing the SQL statement to be executed. The string can be contained in a host variable, or it can be a literal.

The syntax of the EXECUTE IMMEDIATE statement is:

EXEC SQL EXECUTE IMMEDIATE { :HOST_STRING | STRING_LITERAL }; 

Using a host string variable, you use the EXECUTE IMMEDIATE command as follows:

COMMAND_STRING  : constant string := 
    "GRANT SELECT ON my_table TO MILLER"; 
... 
EXEC SQL EXECUTE IMMEDIATE :COMMAND_STRING; 

You can also use string literals, as the following example shows:

EXEC SQL EXECUTE IMMEDIATE "REVOKE RESOURCE FROM MILLER"; 

Because EXECUTE IMMEDIATE parses the input SQL statement before every execution, Method 1 is best for statements that are executed only once.

An Example

The program shown below prompts the user for a new table name, slices the table name into a CREATE TABLE... statement, and then executes the statement using Method 1. The effect is to copy the EMP table to the new table that the user names. This program is available online in the Pro*Ada demo directory.

-- Copyright (c) 1994 by Oracle Corporation 
--  DYN1 :
--      1)  Logon to ORACLE
--      2)  Prompt for a new table name
--      3)  Create the duplicate of emp with the new table name

with text_io;

procedure DYN1 is

USERNAME            : constant STRING := "SCOTT";
PASSWORD            : constant STRING := "TIGER";
CREATE_COMMAND      : STRING(1..39) :=
                        "CREATE TABLE       AS SELECT * FROM EMP";
CMD_LEN             : NATURAL;
SQL_ERROR           : exception;

begin
    EXEC SQL WHENEVER SQLERROR raise SQL_ERROR;

    -- logon
    EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD;

    TEXT_IO.PUT("Please enter a name for a new EMP table");
    TEXT_IO.PUT(" (5 characters or less): ");
    TEXT_IO.GET_LINE(CREATE_COMMAND(14..18), CMD_LEN);

    EXEC SQL EXECUTE IMMEDIATE :CREATE_COMMAND;

    TEXT_IO.NEW_LINE;
    TEXT_IO.PUT("The table """ & CREATE_COMMAND(14..18));
    TEXT_IO.PUT(""" was created successfully.");

    -- Note:    The COMMIT was actually done at the time the
    --          command was executed, since all DDL commands
    --          involve an implicit COMMIT.
    EXEC SQL COMMIT RELEASE;

    exception
        when SQL_ERROR =>
            EXEC SQL WHENEVER SQLERROR CONTINUE;
            TEXT_IO.PUT_LINE("** ORACLE ERROR OCCURED **");
            TEXT_IO.PUT_LINE(ORACLE.ERROR.MESSAGE);
            EXEC SQL ROLLBACK RELEASE;

end DYN1;


Using Method 2

What Method 1 does in one step, Method 2 does in two. The dynamic SQL statement is first PREPAREd (named and parsed), then EXECUTEd. The SQL statement must not be a query.

With Method 2, the SQL statement can contain placeholders for input host variables and indicator variables. You can PREPARE the SQL statement once, then EXECUTE it repeatedly using different values of the host variables. Furthermore, you need not rePREPARE the SQL statement after a COMMIT or ROLLBACK unless you log off and reconnect.

You can also use EXECUTE for nonqueries with Method 4. For more information about dynamic SQL Method 4, see chapter 9, "Implementing Dynamic SQL Method 4."

The syntax of the PREPARE statement follows:

EXEC SQL PREPARE statement_name 
    FROM { :HOST_STRING | STRING_LITERAL }; 

PREPARE parses the SQL statement and gives it a name.

The statement name is an identifier used by the precompiler, not a host or program variable, and should not be declared as a program variable. It simply designates the PREPAREd statement you want to EXECUTE.

The syntax of the EXECUTE statement is

EXEC SQL EXECUTE statement_name [USING host_variable_list]; 

where the host variable list syntax is

:HOST_VARIABLE1[[ ][ INDICATOR]:INDICATOR_VAR1] 
[, :HOST_VARIABLE2[[ ][ INDICATOR]:INDICATOR_VAR2], ...] 

Note: The optional keyword INDICATOR can be used to separate a host variable and an indicator variable.

EXECUTE executes the parsed SQL statement, using the values supplied for each input host variable.

In the following example, the input SQL statement contains the placeholder N:

... 
EMP_NUMBER  : integer; 
DELETE_STMT : constant string := 
    "DELETE FROM emp WHERE empno = :N"; 
... 
-- sql_stmt is the name of the prepared statement, not a variable 
EXEC SQL PREPARE sql_stmt FROM :DELETE_STMT; 
loop 
    PUT("Enter employee number: "); 
    INTEGER_TEXT_IO.GET(EMP_NUMBER); 
    exit when EMP_NUMBER = 0; 
    --  no reparsing needed 
    EXEC SQL EXECUTE sql_stmt USING :EMP_NUMBER; 
end loop; 
... 

With Method 2, the types of input host variables must be known at precompile time. In the last example, EMP_NUMBER was declared as type integer. It could also have been declared as type float or even string, because Oracle supports conversions from each of these datatypes to NUMBER (the datatype of the empno column in the EMP table).

The USING Clause

When the SQL statement is EXECUTEd, input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.

Every placeholder in the PREPAREd dynamic SQL statement must correspond to a host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.

The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd dynamic SQL statement must match the order of corresponding host variables in the USING clause.

If one of the host variables in the USING clause is an array, all must be arrays.

To specify null values, you can associate indicator variables with host variables in the USING clause. For more information, see "Using Indicator Variables" [*].

An Example

The following program prompts the user for a search condition to be used in the WHERE clause of an UPDATE statement, then prepares and executes the statement using Method 2. Notice that the SET clause of the UPDATE statement contains a placeholder.

-- Copyright (c) 1994 by Oracle Corporation 
--  DYN2 :
--      1)  Logon to ORACLE
--      2)  Load a string with an UPDATE statement
--      3)  Prompt user to finish the WHERE clause
--          of the UPDATE statement
--      4)  Prepare the concatenated command string
--      5)  Execute the prepared statement
--      6)  Print the rows processed

with text_io, varchar_text_io, integer_text_io;

procedure DYN2 is

use text_io, integer_text_io;

ORACLE_ID           : ORACLE.VARCHAR(20);
ERR_MESSAGE         : ORACLE.VARCHAR(70);
COMMAND_STR         : STRING(1..70);
COMMAND_LEN         : NATURAL;
COMMISSION          : NATURAL;
SQL_ERROR           : exception;

begin
    EXEC SQL WHENEVER SQLERROR raise SQL_ERROR;

    -- Logon using VARCHARs
    ORACLE_ID.BUFFER(1..11) := "SCOTT/TIGER";
    ORACLE_ID.LENGTH := 11;

    EXEC SQL CONNECT :ORACLE_ID;

    -- Note that the "PLACE_HOLDER" in the UPDATE statement
    -- is not a program variable.  The precompiler recognizes
    -- that there needs to be a variable bound in that position
    -- and it will locate the actual variable from the USING
    -- clause of the EXEC SQL EXECUTE statement.
    -- If there happened to be more than one placeholder,
    -- they would be bound in order of occurrence in the SQL
    -- statement to the same number and order of variables in the
    -- USING clause.

    COMMAND_STR(1..42) :=
        "UPDATE EMP SET COMM = :PLACE_HOLDER WHERE ";
    PUT_LINE("Please enter a WHERE clause for the following: ");
    NEW_LINE;
    PUT("  " & COMMAND_STR(1..42) & "? ");
    GET_LINE(COMMAND_STR(43..60), COMMAND_LEN);
    
    EXEC SQL PREPARE S1 FROM :COMMAND_STR(1..COMMAND_LEN);

    PUT("Set the commission to (enter an integer): ");
    GET(COMMISSION);

    -- Execute the prepared statement S1 using the variable
    -- COMMISSION in the place of PLACE_HOLDER.  If there were
    -- no variables in the statement, the USING clause could be
    -- omitted and/or Dynamic SQL Method #1 could be used.

    EXEC SQL EXECUTE S1 USING :COMMISSION;

    -- Now print the SQLROWS to see how many rows were updated.
    -- Note: ORACLE.SQLROWS is identical to SQLCA.SQLERRD(3).

    PUT("You just updated ");
    PUT(ORACLE.SQLROWS, WIDTH => 2);
    PUT(" row");
    if ORACLE.SQLROWS /= 1 then
        PUT_LINE("s.");
    else
        PUT_LINE(".");
    end if;

    EXEC SQL COMMIT RELEASE;

    exception
        when SQL_ERROR =>
            PUT_LINE("** ORACLE ERROR OCCURED **");
            NEW_LINE;
            -- get the error message (in a VARCHAR) using
            -- the error message procedure
            ORACLE.ERROR.MESSAGE(ERR_MESSAGE);
            VARCHAR_TEXT_IO.PUT_LINE(ERR_MESSAGE);
            EXEC SQL ROLLBACK RELEASE;

end DYN2;


Using Method 3

Method 3 is similar to Method 2 but combines the PREPARE statement with the statements needed to define and manipulate a cursor. This allows your program to accept and process queries. In fact, if the dynamic SQL statement is a query, you must use Method 3 or 4.

For Method 3, the number of columns in the query select list and the number of placeholders for input host variables must be known at precompile time. However, the names of database objects such as tables and columns need not be specified until runtime. Clauses that limit, group, and sort query results (such as WHERE, GROUP BY, and ORDER BY) can also be specified at runtime.

With Method 3, you use the following sequence of embedded SQL statements:

PREPARE statement_name FROM { :HOST_STRING | STRING_LITERAL }; 
DECLARE cursor_name CURSOR FOR statement_name; 
OPEN cursor_name [USING host_variable_list]; 
FETCH cursor_name INTO host_variable_list; 
CLOSE cursor_name; 

Each of these statements is described in the following sections.

PREPARE

PREPARE parses the dynamic SQL statement and gives it a name. In the following example, PREPARE parses the query stored in the character string SELECT_STMT and gives it the name SQL_STMT:

SELECT_STMT := "SELECT mgr, job FROM emp WHERE sal < :SALARY"; 
EXEC SQL PREPARE sql_stmt FROM :SELECT_STMT; 

Commonly, the query's WHERE clause is input from a terminal at runtime, or is generated by the application.

Remember, SQL_STMT is an identifier used by the precompiler, not a program variable. However it identifies the SQL statement, and it must be unique.

DECLARE

DECLARE defines a cursor by giving it a name and associating it with a specific query. The declaration of the cursor is local to a precompilation unit.

Continuing with our example, DECLARE defines a cursor named EMP_CURSOR and associates it with SQL_STMT, as follows:

EXEC SQL DECLARE EMP_CURSOR CURSOR FOR SQL_STMT; 

Like SQL_STMT, EMP_CURSOR is an identifier used by the precompiler, not a program variable. However, it must be unique. If you declare two cursors using the same statement name, the Pro*Ada Precompiler considers the two cursors synonymous.

OPEN

OPEN allocates an Oracle cursor, binds input host variables, and executes the query, identifying its active set. OPEN also positions the cursor on the first row in the active set, and zeroes the rows-processed count kept by the 3rd element of SQLERRD in the SQLCA. Input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.

In our example, OPEN allocates EMP_CURSOR and assigns the host variable salary to the WHERE clause, as follows:

EXEC SQL OPEN emp_cursor USING :SALARY; 

FETCH

FETCH returns a row from the active set, assigns column values in the select list to corresponding host variables in the INTO clause, and advances the cursor to the next row. If there are no more rows, FETCH returns the "no data found" Oracle error code to SQLCODE.

In our example, FETCH returns a row from the active set and assigns the values of columns MGR and JOB to host variables MGR_NUMBER and JOB_TITLE, as follows:

EXEC SQL FETCH emp_cursor INTO :MGR_NUMBER, :JOB_TITLE; 

CLOSE

CLOSE disables the cursor. Once you CLOSE a cursor, you can no longer FETCH from it.

In our example, CLOSE disables EMP_CURSOR, as follows:

EXEC SQL CLOSE emp_cursor; 

The sequence of statements used in the example follows:

SELECT_STMT := "SELECT mgr, job FROM emp WHERE sal < :SALARY"; 
EXEC SQL PREPARE sql_stmt FROM :SELECT_STMT; 
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; 
EXEC SQL OPEN emp_cursor USING :SALARY; 
EXEC SQL FETCH emp_cursor INTO :MGR_NUMBER, :JOB_TITLE; 
EXEC SQL CLOSE emp_cursor; 

An Example

The following program prompts the user for a search condition to be used in the WHERE clause of a query, then prepares and executes the query using Method 3. This program is available online in the Pro*Ada demo directory.

-- Copyright (c) 1994 by Oracle Corporation 
--  DYN3 :
--      1)  Declare the CONNECT BY statement
--      2)  Associate a cursor with the statement
--      3)  Logon to ORACLE
--      4)  Assign a value to the host variable and complete
--          the statement text
--      5)  OPEN the cursor (executing the statement)
--      6)  FETCH all the rows
--      7)  CLOSE the cursor and log off
with text_io, integer_text_io, varchar_text_io;

procedure DYN3 is
use text_io, integer_text_io;

function "="(LEFT, RIGHT : ORACLE.UNSIGNED_SHORT) return BOOLEAN
    renames ORACLE."=";

ONAME           : constant STRING := "SCOTT";
OPASSWORD       : constant STRING := "TIGER";
ENAME           : STRING(1..10);
JOB             : STRING(1..9);
SAL             : STRING(1..6);
BOSS            : ORACLE.VARCHAR(10);
EXCLUDE         : ORACLE.VARCHAR(10);
SEL_STATEMENT   : STRING(1..142);
SEL_LEN         : integer;
SQL_ERROR       : exception;

-- This precompiler directive is used because the PREPARE is
-- done after the DECLARE CURSOR (forward reference).
-- Since the DECLARE statements are directives (no code is
-- generated), they may be placed with variable declarations
-- but must be present within the same physical file as the
-- remaining cursor operations (i.e. OPEN, FETCH, etc.).
EXEC SQL DECLARE S STATEMENT;

EXEC SQL DECLARE C1 CURSOR FOR S;
EXEC SQL WHENEVER SQLERROR raise SQL_ERROR;

begin           -- now start main code section

    EXEC SQL CONNECT :ONAME IDENTIFIED BY :OPASSWORD;

    SEL_STATEMENT(1..142) := "SELECT ename, job, sal " &
                             "FROM emp " &
                             "WHERE ename != UPPER(:EXCLUDE) " &
                             "CONNECT BY PRIOR empno = mgr " &
                             "START WITH ename = UPPER(:BOSS) " &
                             "ORDER BY          ";
    loop
        PUT("List information about employees who work for " &
            "(CR to exit): ");
        VARCHAR_TEXT_IO.GET_LINE(BOSS);

        -- Note that this is using ORACLE."=" (See above).
        exit when BOSS.LENGTH = 0;

        -- Let's exclude the boss from the list.
        EXCLUDE := BOSS;

        PUT("Order by which column in the emp table ? ");
        GET_LINE(SEL_STATEMENT(136..142), SEL_LEN);

        EXEC SQL PREPARE S FROM :SEL_STATEMENT(1..SEL_LEN);
        EXEC SQL OPEN C1 USING :EXCLUDE, :BOSS;

        PUT("These people work for ");
        VARCHAR_TEXT_IO.PUT_LINE(BOSS);
        NEW_LINE;
        PUT_LINE("      NAME   SALARY   JOB");
        PUT_LINE("---------- --------   ---------");

        FETCH_LOOP : loop
        declare DONE_FETCHING : exception;
        begin
            EXEC SQL WHENEVER NOT FOUND raise DONE_FETCHING;
            EXEC SQL FETCH C1 INTO :ENAME, :JOB, :SAL;
            PUT_LINE("  " & ENAME & SAL & "    " & JOB);

            exception
                when DONE_FETCHING =>
                    NEW_LINE;
                    PUT(ORACLE.SQLROWS);
                    PUT(" employee");
                    if ORACLE.SQLROWS /= 1 then
                        PUT("s");
                    end if;
                    PUT_LINE(" returned.");
                    NEW_LINE;
                    exit;
                end;
        end loop FETCH_LOOP;
        EXEC SQL CLOSE C1;
    end loop;

    exception
        EXEC SQL WHENEVER SQLERROR CONTINUE;
        when SQL_ERROR =>
            PUT_LINE("** ORACLE ERROR OCCURRED **");
            PUT_LINE(ORACLE.ERROR.MESSAGE);
            EXEC SQL ROLLBACK RELEASE;
end DYN3;


Using PL/SQL

The Oracle Precompilers treat a PL/SQL block like a single SQL statement. So, like a SQL statement, a PL/SQL block can be stored in a string host variable or literal. When you store the PL/SQL block in the string, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the statement terminator.

However, there are two differences in the way the precompiler handles SQL and PL/SQL:

With Method 1

If the PL/SQL block contains no host variables, you can use Method 1 to EXECUTE the PL/SQL string in the usual way.

With Method 2

If the PL/SQL block contains a known number of input and output host variables, you can use Method 2 to PREPARE and EXECUTE the PL/SQL string in the usual way.

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 3

Methods 2 and 3 are the same except that Method 3 allows FETCHing. Since you cannot FETCH from a PL/SQL block, use Method 2 instead.




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