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

Implementing Dynamic SQL Method 4


This chapter provides the information you need to implement dynamic SQL Method 4 in your Pro*Ada application. This chapter covers the following topics:

Note: You should be familiar with the material about dynamic SQL Methods 1, 2, and 3, and an overview of Method 4[*], "Introduction to Dynamic SQL."


Dynamic SQL with Descriptors

Use Method 4 when your program must process and execute any legal SQL statement. This includes queries that contain an unknown number of select-list items that cannot be processed using Method 3. For example:

SELECT * FROM emp;

Method 4 also allows you to process and execute SQL statements in which the number of input variables is not known at compile time. For example, a SQL statement that might have one or more placeholders in a WHERE clause can be entered by the user at run time. For example:

SELECT * FROM emp WHERE deptno = :DEPT_NUMBER 
    AND sal > :SALARY_MINIMUM;

To process all SQL statements using Method 4, you must declare a bind descriptor. The bind descriptor is defined in the package Oracle. It is an Ada record and is completely transparent to the Pro*Ada programmer. You do not need to be aware of the internal structure of the record; you access elements of the descriptor using predefined Ada functions.

To process a SQL statement that has a dynamic select list, you declare a select descriptor for the statement. The select descriptor is defined in the Oracle package, and has its own set of predefined functions.

You do not use host variables with Method 4 to handle input and output data. Instead, you bind the names of Ada scalar or array variables to placeholders in the SQL statement, and you define Ada scalar or array variables to hold the output values for select-list items.

The same type-checking constraints that are in effect for host variables in static SQL apply to dynamic SQL Method 4. You can only bind or define Ada variables of the correct type for input or output. For a list of the host variables that can be used with different Oracle internal types, see "Declaring and Referencing Host Variables" [*]. If the wrong datatype is bound or defined, an Oracle "inconsistent datatypes" error occurs at runtime.

Most dynamic SQL Method 4 applications are designed to process any arbitrary SQL statement, so both select and bind descriptors are declared. For each SQL statement, only one descriptor is needed for the select list, and one for the bind variables. The following example shows how the descriptors are declared:

bind_descriptor   : oracle.descriptor_def; 
select_descriptor : oracle.descriptor_def; 


Basic Steps

This section outlines the steps that you follow to develop a dynamic SQL Method 4 application. Later sections in this chapter go into each step in greater detail. Note that not all steps need to be followed in all cases. For example, if the SQL statement is not a query, you do not need to perform Steps 12 to 16.

Note: In the descriptions of the steps below, command refers to an embedded SQL command, such as FETCH or OPEN, and procedure or function refers to a procedure supplied in the package ORACLE.DESCRIBE.

The steps are:


A Closer Look at Each Step

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

EXEC SQL PREPARE statement_name 
    FROM { :HOST_STRING | STRING_LITERAL }; 
 
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; 
 
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name 
    INTO BIND_DESCRIPTOR; 
 
EXEC SQL OPEN cursor_name 
    [ USING DESCRIPTOR descriptor_name ]; 
 
EXEC SQL DESCRIBE [ SELECT LIST FOR ] statement_name 
    INTO SELECT_DESCRIPTOR; 
 
EXEC SQL FETCH cursor_name 
    USING DESCRIPTOR SELECT_DESCRIPTOR; 
 
EXEC SQL CLOSE cursor_name; 

The following sections show how these statements allow your host program to accept and process a dynamic query using descriptors. A query is used in these examples to illustrate all the steps. These steps are brought together in a complete demonstration program [*].

Declare Program Variables

You must declare Ada variables to handle the SQL statement, the bind and select descriptors, and variables for both input (bind) and output variables.

In these examples, the VARCHAR type from the Oracle package defines arrays of variable-length strings. This is a convenient type to use when select-list items of unknown length will be retrieved and converted to character data.

-- declare the descriptors 
BIND_DESCRIPTOR       : ORACLE.descriptor_def; 
SELECT_DESCRIPTOR     : ORACLE.descriptor_def; 
-- declare a string to hold the SQL statement 
COMMAND_LINE          : string(1..512); 
COMM_LEN              : integer; 
-- declare the bind variables 
-- the assumption is no more than 10 bind variables with 
-- no more than 20 bytes per value 
FROM_CHAR_VAR         : array(1..10) of ORACLE.VARCHAR(20); 
-- declare the output variables for character, 
-- integer, and float select-list items 
INTO_CHAR_VAR         : array(1..10) of ORACLE.VARCHAR(20); 
INTO_INT_VAR          : array(1..10) of integer; 
INTO_LF_VAR           : array(1..10) of float; 
-- declare a global exception for SQL errors 
SQL_ERROR             : exception; 

Obtain the SQL Statement

In the example program, the SQL command is obtained interactively from the user, as follows:

 PUT_LINE
        ("Enter a SQL statement (or type ""exit"" to quit)--"); 
    PUT("> "); 
    GET_LINE(STATEMENT_LINE, STMT_LENGTH); 
    exit when 
      STATEMENT_LINE(1..4) = "exit" 
      or STATEMENT_LINE(1..4) = "EXIT"; 

PREPARE the Query from the Host String

The PREPARE command parses the query and gives it a name. In this example, the host string COMMAND_LINE is given the name sql_statement. The name is an identifier used by the precompiler. It is not an Ada variable and is not declared in the program unit.

EXEC SQL PREPARE EXAMPLE_STMT FROM :COMMAND_LINE; 

Data Definition Language statements are executed when the PREPARE command is issued. No further processing is required.

DECLARE a Cursor

For each SQL statement to be processed using Method 4, a cursor must be defined, using the DECLARE ... CURSOR command. A cursor is an internal object; it is not declared in the host program.

When declaring a cursor for static queries, you use the syntax

EXEC SQL DECLARE <cursor_name> CURSOR FOR SELECT ... 

completing the query in the DECLARE ... CURSOR statement. But when declaring a cursor for dynamic queries, the statement name given to the SQL statement by the PREPARE command is substituted for the static query. In our example, DECLARE CURSOR defines a cursor named EMP_CURSOR and associates it with EXAMPLE_STMT as follows:

EXEC SQL DECLARE EMP_CURSOR CURSOR FOR EXAMPLE_STMT; 

where EMP_CURSOR and EXAMPLE_STMT are identifiers used by Pro*Ada. They are not Ada host variables, and are not prefixed with colons.

You must declare a cursor for all Data Manipulation Language SQL statements in Method 4, not just statements with queries. When you OPEN the cursor, the statement is executed. If the statement is a query, you can then use the FETCH command to retrieve the rows in the query result set, if any.

DESCRIBE the Statement into the Bind Descriptor

When you issue the command DESCRIBE BIND VARIABLES. . . you initialize the bind descriptor. After this command has been issued, you can use any of the functions or procedures in the ORACLE package that apply to bind descriptors. These procedures let you determine

The procedures also let you bind input variables to the SQL statement. For a Pro*Ada code fragment that demonstrates how to use these functions, see "OPEN the Cursor" [*].

BIND_LIST_SIZE

This function returns the number of bind variables required for the statement that was DESCRIBEd. Each instance of a placeholder requires one bind variable. A SQL statement such as

INSERT INTO emp (ename, hiredate, mgr, empno) 
    VALUES (:NAME, :DATE, :NAME, :N) 

requires only three bind variables because the placeholder :NAME occurs twice, and the same input value is bound at both occurrences of the placeholder.

Note: Placeholders for indicator variables are not counted in the value returned by BIND_LIST_SIZE. The statement

INSERT INTO emp (ename, sal, comm) 
                 VALUES (:N, :S, :C INDICATOR :CI);

contains three placeholders, and BIND_LIST_SIZE returns 3 when called after this statement is DESCRIBEd. You must use the INDICATOR_VARIABLE_LENGTH function to determine if there is an indicator associated with a regular placeholder (see page 9 - 8).

If there are no placeholders in the SQL statement, the BIND_LIST_SIZE function returns zero.

The syntax for this function is:

oracle.describe.bind_list_size ( 
                d : oracle.descriptor_def)
       return integer;

BIND_VARIABLE Function

The BIND_VARIABLE function returns the name of the placeholder as a string. A placeholder in the SQL statement must be preceded by a colon. The returned name does not contain the colon. The syntax is

oracle.describe.bind_variable( 
                           d  : oracle.descriptor_def, 
                           vn : integer)
       return <return_type>;

where the <return_type> may be string or ORACLE.STRING_DEF (string access type).

The index parameter vn indicates the sequential position of the placeholder in the SQL statement, where the first position has the value 1. Repeated placeholder names are skipped. The statement

INSERT INTO my_table (col1, col2, col3, col4) 
                      VALUES (:C1, :C2, :C1, :C3);

has three placeholders, C1, C2, and C3, and the index position of placeholder C3 is 3. The input value bound to C1 will be inserted in COL1 and COL3.

BIND_VARIABLE Procedure

As a procedure, BIND_VARIABLE returns the placeholder name as a VARCHAR OUT parameter. The syntax is:

oracle.describe.bind_variable ( 
                        d  : oracle.descriptor_def, 
                        vn : integer, 
                        hv : in out oracle.varchar);

The index parameter vn behaves in the same way as the index parameter in the BIND_VARIABLE function.

BIND_ VARIABLE_LENGTH

This function returns the length of the name of the placeholder in position vn, where vn is the positional index variable. The syntax is

oracle.describe.bind_variable_length( 
                                 d  : oracle.descriptor_def, 
                                 vn : integer)
       return integer;

INDICATOR_VARIABLE

This function returns the name of a placeholder for an indicator variable. The position index variable, vn, has the same value as the associated host variable placeholder. The returned name does not contain the colon, or if present, the INDICATOR keyword. The syntax is:

oracle.describe.indicator_variable (
                                  d  : oracle.descriptor_def, 
                                  vn : integer)
       return string;

INDICATOR_ VARIABLE_LENGTH

This function returns the length of the placeholder name for the indicator variable in the indexed position. The syntax is:

oracle.describe.indicator_variable_length ( 
                                  d  : oracle.descriptor_def,
                                  vn : integer)
       return integer;

If there is no indicator variable, this function returns zero.

Obtain Values for the Placeholders

You can use the BIND_LIST_SIZE function to determine if there are any placeholders in the SQL statement that was DESCRIBEd. If there are, values for the placeholders must be obtained interactively from the user, or must otherwise be generated by the program. This can be done using a for ... loop statement. In the example below, the values for the input variables are obtained as character strings; they will be converted to the appropriate Oracle internal type when the statement is executed.

for INDEX in 1..ORACLE.DESCRIBE.BIND_LIST_SIZE(BIND_DESC) loop 
    -- write the name 
    PUT(ORACLE.DESCRIBE.BIND_VARIABLE(BIND_DESC, INDEX)); 
    PUT(" : "); 
    GET(CHAR_VAR(INDEX)); 
    -- bind the values (see later example) 
end loop; 

For a complete example of this loop, see "OPEN the Cursor" on page 9 - 10.

Bind the Input Values

After you obtain the input values, you use the SQL_BIND procedure to bind the program variables containing the input values to the SQL statement. The syntax for this procedure is

procedure sql_bind (d    : oracle.descriptor_def, 
                    vn   : integer, 
                    hv   : host_var 
                   [hi   : indicator_var]) 

where vn is the position of the variable in the SQL statement, hv is the host variable that contains the input value, and hi contains the value for the associated indicator variable, if any. The hi parameter can be omitted if there is no indicator variable.

SQL_BIND is overloaded. Pro*Ada supplies versions of this procedure for each of the datatypes that can be used for host variables.

Note: If MODE={ANSI | ANSI14) when Pro*Ada is invoked, the SQL_BIND procedure may only be called with the <input_value> parameter having one of the datatypes defined in the SQL_STANDARD package.

OPEN the Cursor

When the SQL statement and the cursor have been declared, and the input variables have been bound, you can OPEN the cursor using the OPEN command. The OPEN statement used for dynamic statements is similar to that used for static statements, except that the cursor is associated with a bind descriptor. Values determined at runtime and stored in the bind descriptor are used to evaluate the statement. For queries, these values determine the active set for the query.

In our example, OPEN associates the cursor cursor_name with BIND_DESC as follows:

EXEC SQL OPEN cursor_name USING DESCRIPTOR BIND_DESC; 

Note that the bind descriptor BIND_DESC is not prefixed with a colon in the OPEN CURSOR statement.

The OPEN executes the statement, identifies its active set if it is a query, and positions the cursor at the first row.

Steps 4 through 11 of Method 4 are illustrated in the code fragment listed below. This example shows the program statements only. A complete program illustrating dynamic SQL Method 4, with appropriate declarations, can be found at the end of this chapter.

... 
-- <4> get SQL statement interactively 
PUT(" ");                                   -- prompt user 
GET_LINE(COMMAND_LINE, CL_LENGTH);           -- get statement 
-- <5> declare the statement (optional in this example) 
EXEC SQL DECLARE example_statement STATEMENT; 
-- <6> prepare (parse) the statement 
EXEC SQL PREPARE example_statement FROM :COMMAND(1..CL_LENGTH); 
-- <7> declare the cursor (mandatory, even if not a query) 
EXEC SQL DECLARE example_cursor CURSOR FOR example_statement; 
-- (note that both example_cursor and example_statement are 
--  Pro*Ada identifiers, not Ada variables) 
-- <8> initialize the bind descriptor 
EXEC SQL DESCRIBE BIND VARIABLES 
    FOR example_statement INTO BIND_DESC; 
-- <9> loop through placeholder list, and obtain values for each 
-- input variable 
-- if there are no placeholders for input variables, 
-- the loop is just skipped 
for INDEX in 1..ORACLE.DESCRIBE.BIND_LIST_SIZE(BIND_DESC) loop 
-- write out placeholder name for user 
PUT(ORACLE.DESCRIBE.BIND_VARIABLE(BIND_DESC, INDEX)); 
PUT(" : "); 
-- get the input value as a character string 
GET(INPUT_CHAR_VAR(INDEX)); 
if ORACLE.DESCRIBE.INDICATOR_VARIABLE_LENGTH( 
    BIND_DESC, INDEX)) /= 0 then 
    PUT("Indicator "); 
    PUT(ORACLE.DESCRIBE.INDICATOR_VARIABLE(BIND_DESC, INDEX)); 
    PUT(" : "); 
    GET(integer(INPUT_INDS(INDEX))); 
    SKIP_LINE; 
else 
    INPUT_INDS(INDEX) := 0; 
end if; 
-- <10> bind input variables to statement placeholders 
SQL_BIND(BIND_DESC, INDEX, INPUT_CHAR_VAR(INDEX), INPUT_INDS(INDEX)); 
end loop; 
-- <11> open the cursor, which executes the statement 
EXEC SQL OPEN example_cursor USING DESCRIPTOR BIND_DESC; 

Initialize the Select Descriptor

For a dynamic SQL Method 4 query, you declare a cursor and use the FETCH command, just as with Method 3. However, the FETCH command is different in Method 4. Rather than FETCHing INTO host variables, you specify the FETCH command with the USING DESCRIPTOR clause. This command causes a row to be FETCHed into a descriptor. The data obtained is then retrieved from the descriptor using the SQL_INTO and SQL_STORE functions, described later in this section.

Before FETCHing the select-list items returned by the query, you must initialize the select descriptor using the DESCRIBE command with the SELECT LIST FOR clause, as follows,

EXEC SQL DESCRIBE SELECT LIST FOR sql_statement INTO SELECT_DESC; 

where SELECT_DESC is the name of a select descriptor declared in the same program unit. The select descriptor name is not prefixed with a colon, as it does not serve as a host variable.

When you DESCRIBE a SQL statement into a select descriptor, Oracle sets the length and datatype of each element in the select-list. For a dynamic SQL Method 4 query, you must process the select-list items using the SQL_INTO procedure to bind the select-list items to program variables before FETCHing the select-list values.

Obtain Information about Select-list Items

After the program has executed the DESCRIBE SELECT LIST ... command, information about the select-list is available. You can obtain this information using a set of functions that access the select descriptor. The description of these functions follow.

SELECT_LIST_SIZE

This function returns the number of select-list items in the referenced select descriptor, or zero if there are no select-list items (the statement is not a query). For example, if you process the statement

SELECT * FROM emp;

using the standard EMP table, SELECT_LIST_SIZE returns the value 8; there are 8 columns in that table. The strange but legal SQL statement

SELECT sal, sal, sal, sal FROM emp;

contains four select-list items, and SELECT_LIST_SIZE returns the value 4 after this statement is DESCRIBEd. You must define four output variables.

The syntax for this function is:

oracle.describe.select_list_size ( 
                      d : oracle.descriptor_def) return integer;

SELECT_ENTRY

There are two functions and one procedure named SELECT_ENTRY in the ORACLE.DESCRIBE package. The syntax for these is

oracle.describe.select_entry ( 
                      d : oracle.descriptor_def, 
                      i : integer)
       return string;
 
oracle.describe.select_entry ( 
                      d : oracle.descriptor_def, 
                      i : integer)
       return string_def;
 
oracle.describe.select_entry ( 
                      d : oracle.descriptor_def, 
                      i : integer, 
                      v : in out varchar);

Each returns the name of the select list item in the i position of the select list. For example, if the query

SELECT ename, sal, empno FROM emp;

is DESCRIBEd into the select descriptor SEL_DESC, the following statement

TEXT_IO.PUT(ORACLE.DESCRIBE.SELECT_ENTRY(SEL_DESC, 2)); 

will print the string "SAL".

The i parameter in each procedure indicates the position in the select list, starting at 1.

SELECT_ENTRY_LENGTH

This function returns the length of the string that would be returned by SELECT_ENTRY. The syntax for this function is:

oracle.describe.select_entry_length ( 
                           d : oracle.descriptor_def, 
                           i : integer)
       return integer;

The i parameter indicates the position in the select list, starting at 1.

MAX_DISPLAY_LENGTH

This function returns the maximum display length of the select list item at the position referenced by the index parameter. The syntax for this function is:

oracle.describe.max_display_length ( 
                          d : oracle.descriptor_def, 
                          i : integer)
       return integer;

This function is especially useful when SQL routines like SUBSTR or TO_CHAR are used to modify the representation of a column.

SELECT_DATA_TYPE

This function returns a number that indicates the datatype of the select-list item at the position referenced by the index parameter. The syntax for this function is:

oracle.describe.select_data_type ( 
                         d : oracle.descriptor_def, 
                         i : integer)
       return oracle.unsigned_byte;

The datatype codes returned by SELECT_DATA_TYPE are listed in Table 9 - 1 on the next page. Each datatype can be referenced by the symbolic name, also listed. See the program DESCRIBE_SAMPLE at the end of this chapter for example that uses the SELECT_DATA_TYPE function, and the symbolic datatype codes. The datatypes and codes are in the package ORACLE.

Datatype Code Type Symbolic Label
1 CHAR SQLT_CHR
2 NUMBER SQLT_NUM
3 integer SQLT_INT
4 float SQLT_FLT
8 LONG SQLT_LNG
9 VARCHAR SQLT_VCS
10 (none, unknown) SQLT_NON
11 ROWID SQLT_RID
12 DATE SQLT_DAT
15 VARRAW SQLT_VBI
23 RAW SQLT_BIN
24 LONG RAW SQLT_LBI
Table 9 - 1. Supported Datatypes

PRECISION

The PRECISION function returns the precision of the select-list item referenced by the index parameter i. It is only applicable to NUMBER datatypes.

The syntax for this function is:

oracle.describe.precision (d : oracle.descriptor_def, 
                           i : integer)
       return integer;

SCALE

The SCALE function returns the scale of the select-list item referenced by the index parameter i. It is only applicable to NUMBER datatypes. The syntax for this function is:

oracle.describe.scale (d : oracle.descriptor_def, 
                       i : integer)
       return integer;

NULL_PERMITTED

The NULL_PERMITTED function returns a boolean value that indicates whether the column of the select-list item referenced by the i parameter permits null values. True means that nulls are permitted in the column. The syntax for this function is:

oracle.describe.null_permitted (d : oracle.descriptor_def, 
                                i : integer)
       return boolean;

For example, if the statement

SELECT ename, deptno, sal FROM emp;

has been DESCRIBEd into SELECT_DESC, the following code would print the string "FALSE":

if NULL_PERMITTED(SELECT_DESC, 2) then 
    PUT("TRUE"); 
else 
    PUT("FALSE"); 
end if; 

Output Variables

For each item in the select-list of a query, you must define the address of an output variable in your Ada program that will receive the data after the FETCH is executed.

SQL_INTO

Defining the output variable address is done using the SQL_INTO procedure. SQL_INTO associates the output variable with a component in the descriptor. The syntax for SQL_INTO is

oracle.describe.sql_into (d  : oracle.descriptor_def, 
                          vn : integer, 
                          hv : host_var 
                         [hi : indicator_var ]) 

The hv parameter is the name of the variable that is bound to the select-list item referenced by the vn parameter. You may use any supported host variable type for the hv parameter.

Remember, the SQL_INTO operation must be done after the SQL statement is DESCRIBEd into the select descriptor, but before the FETCH. The following code fragment continues the previous examples in this chapter, and illustrates how you use SQL_INTO to name output variables for a query.

with ORACLE.DESCRIBE; 
use ORACLE, ORACLE.DESCRIBE; 
... 
EXEC SQL DESCRIBE SELECT LIST FOR EXAMPLE_STMT INTO SELECT_DESC; 
... 
for I in 1..SELECT_LIST_SIZE(SELECT_DESC) loop 
    case SELECT_DATA_TYPE(SELECT_DESC,I) is 
        when SQLT_NUM  => 
            SQL_INTO(SELECT_DESC, I ,FLOAT_VAR(I),INTO_INDS(I)); 
        when others           => 
            SQL_INTO(SELECT_DESC, I, CHAR_VAR(I),INTO_INDS(I)); 
    end case; 
end loop; 

FETCH the Select-list Items

After defining the output variables for the select-list items, you issue the FETCH command to retrieve each row in the table that satisfies the result set of the query. The process of retrieving data is usually done using two nested loops. The outer loop performs the FETCH for each row of the table, and an inner loop is used to retrieve each item in the select list. In Pro*Ada, you can set up a local exception handling mechanism to test for the "no data found" condition that indicates the end of the outer loop. See the complete Method 4 program [*] for an example that shows how to do this.

The FETCH command is performed as follows

EXEC SQL FETCH example_cursor USING DESCRIPTOR SELECT_DESC; 

where EXAMPLE_CURSOR is the cursor for the statement that was used in the previous "DECLARE example_cursor CURSOR FOR example_statement" statement.

After you have executed the FETCH, there are four functions in the ORACLE.DESCRIBE package that you can call to obtain information about the items in the select list and to retrieve the select-list items into Ada variables.

The functions that store data are described in the next section. The two functions that provide information are described below.

RETURN_SIZE

The RETURN_SIZE function returns the display size of a select-list item. The syntax for this function is:

function oracle.describe.return_size ( 
                         d : oracle.descriptor_def, 
                         i : integer)
       return integer 

The display size is the maximum length of the internal representation of the column, or the maximum display length of the expression. Table 9 - 2 shows RETURN_SIZE values for Oracle datatypes, and for some common expressions.

Type or Expression RETURN_SIZE Value
CHAR(N) N
NUMBER 22
NUMBER(P,S) 22 (not P)
DATE 7
TO_CHAR(DATE) 75
LONG n/a
RAW(N) N
LONG RAW n/a
Table 9 - 2. RETURN_SIZE Values

ERROR_CODE

The ERROR_CODE function returns the error code after the FETCH. The syntax for this function is:

function error_code ( d : oracle.descriptor_def, 
                      i : integer)
       return integer 

This function simply returns the SQLCODE value.

Store the Select-List Items

You use the SQL_STORE procedure to store the select-list items whose addresses were defined earlier using the SQL_INTO procedure. Call SQL_STORE for each item in the select list.

SQL_STORE

The syntax for SQL_STORE is:

procedure sql_store (d  : oracle.descriptor_def;
                     vn : integer;
                     hv : host_var;
                    [hi : indicator_var])

where d is the previously DESCRIBEd select descriptor, vn is the position of the select-list item being retrieved, starting from one, hv is the output variable for the referenced item previously defined using SQL_INTO, and the optional hi is the indicator variable also defined using SQL_INTO.

The FETCH and store operations are demonstrated in the following code fragment, which continues from the previous examples in this chapter:

ROW_FETCH_LOOP:         -- outer FETCH loop 
loop  -- until not found 
declare 
    NOT_FOUND : exception; 
    EXEC SQL WHENEVER NOT FOUND raise NOT_FOUND; 
begin 
    EXEC SQL FETCH EXAMPLE_CURSOR 
        USING DESCRIPTOR SELECT_DESC; 
 
    COLUMN_LOOP:     -- inner store loop 
    for I in 1..N_VARS loop 
      case SELECT_DATA_TYPE(SELECT_DESC,I) is 
        when SQLT_NUM => 
          SQL_STORE( 
            SELECT_DESC,I,LF_VAR(I),INTO_INDS(I)); 
            PREC := PRECISION(SELECT_DESC,I); 
            SCALE := SCALE(SELECT_DESC,I); 
        when SQLT_DAT  => 
          SQL_STORE( 
            SELECT_DESC,I,INTO_CHAR_VAR(I),INTO_INDS(I)); 
        when others           => 
            SQL_STORE(SELECT_DESC,I,INTO_CHAR_VAR(I)); 
      end case; 
    end loop COLUMN_LOOP; 
  exception 
    when NOT_FOUND => 
      NEW_LINE; 
      exit; 
end;   -- end begin 
end loop ROW_FETCH_LOOP; 

CLOSE the Cursor

After you finish processing the SQL statement, you should CLOSE the cursor. CLOSE disables the cursor, and frees storage used by the cursor. You do this as follows:

EXEC SQL CLOSE cursor_name; 


A Method 4 Program

The sample program in this section demonstrates all aspects of processing a dynamic SQL statement using Method 4. The program accepts a SQL statement interactively from the user, and performs each of the steps listed in the section "The Basic Steps" [*].

This program illustrates dynamic SQL. It does not handle all possible Data Manipulation Language statements on all possible tables. This is so that the error checking and output text formatting can be very simple. This program is available on line as dyn4.pad, in the Pro*Ada demo directory.

-- Copyright (c) 1994 by Oracle Corporation 
--  DYN4 :  
--         
--         1) Connect to Oracle.
--         2) Prepare a "dynamic" statement.
--         3) Describe and bind the "where" clause host variables.
--         4) Open the cursor. (Execute)
--         5) Describe the select list and bind the select variables.
--         6) Fetch the rows.
--         7) Close the cursor and logoff.
--

with text_io, 
     varchar_text_io, 
     integer_text_io, 
     float_io; 

procedure DYN4 is 
use text_io, 
    varchar_text_io, 
    integer_text_io, 
    float_io; 

USERID          : constant string := "SCOTT/TIGER"; 
-- <Step 1> declare string to hold SQL statement 
STATEMENT_LINE  : string(1..512); 
SPACES          : string(1..80) := (1..80 => ' '); 
DASHES          : constant string(1..79) := (1..79 => '-'); 
STMT_LENGTH     : integer; 
N_VARS          : integer; 
N               : integer; 
PREC, SCALE     : integer; 

--<2> declare select and bind descriptors 
BIND_DESC       : ORACLE.DESCRIPTOR_DEF; 
SELECT_DESC     : ORACLE.DESCRIPTOR_DEF; 

--<3> declare input and output variables 
WHERE_CHAR_VAR  : array(1..10) of ORACLE.VARCHAR(20); 
INTO_CHAR_VAR   : array(1..10) of ORACLE.VARCHAR(20); 
INTO_LF_VAR     : array(1..10) of FLOAT; 
INTO_INDS       : array(1..10) of ORACLE.INDICATOR; 
CONNECT_ERROR   : exception; 
PL_EXCEP        : exception;

begin 
  EXEC SQL WHENEVER SQLERROR raise CONNECT_ERROR; 
  EXEC SQL CONNECT :USERID; 
  PUT("Connected to ORACLE as "); PUT_LINE(USERID); 

  PROGRAM_LOOP: loop 
  declare 
    TOO_MANY_VARS : exception; 

  begin 
    EXEC SQL WHENEVER SQLERROR raise PL_EXCEP; 
-- <4> obtain the SQL statement 
    PUT_LINE
        ("Enter a SQL statement (or type ""exit"" to quit)--"); 
    PUT("> "); 
    GET_LINE(STATEMENT_LINE, STMT_LENGTH); 
    exit when 
      STATEMENT_LINE(1..4) = "exit" 
      or STATEMENT_LINE(1..4) = "EXIT"; 
    if STMT_LENGTH > 0 then      -- there is a statement 
-- <6> parse the statement 
      EXEC SQL PREPARE EXAMPLE_STMT FROM
        :STATEMENT_LINE(1..STMT_LENGTH); 
-- <7> declare the cursor 
      EXEC SQL DECLARE EXAMPLE_CURSOR CURSOR FOR EXAMPLE_STMT; 
-- <8> initialize the bind descriptor 
      EXEC SQL DESCRIBE BIND VARIABLES 
        FOR EXAMPLE_STMT INTO BIND_DESC; 
      N_VARS := ORACLE.DESCRIBE.BIND_LIST_SIZE(BIND_DESC); 
-- <9> obtain values for the input variables 
      if N_VARS > 0 then 
        if N_VARS > 10 then raise TOO_MANY_VARS; end if; 
          PUT_LINE("Enter bind values: "); 
          for I in 1..N_VARS loop 
-- get the bind variable 
            NEW_LINE; 
            PUT(ORACLE.DESCRIBE.BIND_VARIABLE(BIND_DESC,I)); 
            PUT(" : "); 
            GET(WHERE_CHAR_VAR(I)); 
-- <10> bind the input values 
            SQL_BIND(BIND_DESC,I,WHERE_CHAR_VAR(I)); 
          end loop; 
          NEW_LINE; 
      end if; 
-- <11> open the cursor 
      EXEC SQL OPEN EXAMPLE_CURSOR USING DESCRIPTOR BIND_DESC; 
-- <12> initialize the select descriptor 
      EXEC SQL DESCRIBE SELECT LIST FOR EXAMPLE_STMT 
        INTO SELECT_DESC; 
      N_VARS := ORACLE.DESCRIBE.SELECT_LIST_SIZE(SELECT_DESC); 
-- <13> obtain information about the select-list items 
      if N_VARS > 0 then 
        if N_VARS > 10 then raise TOO_MANY_VARS; end if; 
          for I in 1..N_VARS loop 
             N := ORACLE.DESCRIBE.SELECT_ENTRY_LENGTH
                  (SELECT_DESC,I); 
             PUT(ORACLE.DESCRIBE.SELECT_ENTRY(SELECT_DESC, I)); 
             PUT(SPACES(1..10-N)); 
-- <14> define output variables for the select-list items 
             case ORACLE.DESCRIBE.SELECT_DATA_TYPE(SELECT_DESC,I) is
                 when ORACLE.SQLT_NUM  => 
                   SQL_INTO 
                     (SELECT_DESC,I,INTO_LF_VAR(I),INTO_INDS(I)); 
                 when others           => 
                   SQL_INTO 
                       (SELECT_DESC,I,INTO_CHAR_VAR(I),INTO_INDS(I)); 
               end case; 
          end loop; 
          NEW_LINE; 
          PUT_LINE(DASHES); 
-- <15> FETCH the rows 
          ROW_FETCH_LOOP: loop  -- until not found 
          declare NOT_FOUND : exception; 
          EXEC SQL WHENEVER NOT FOUND raise NOT_FOUND; 
          begin 
            EXEC SQL FETCH EXAMPLE_CURSOR 
              USING DESCRIPTOR SELECT_DESC; 
            COLUMN_LOOP: 
            for I in 1..N_VARS loop 
              case ORACLE.DESCRIBE.SELECT_DATA_TYPE
                (SELECT_DESC,I) is 
                 when ORACLE.SQLT_NUM => 
-- <16> store the select-list items 
                   SQL_STORE( 
                     SELECT_DESC,I,INTO_LF_VAR(I),INTO_INDS(I)); 
                   PREC := 
                     ORACLE.DESCRIBE.PRECISION(SELECT_DESC,I); 
                   SCALE :=
                     ORACLE.DESCRIBE.SCALE(SELECT_DESC,I); 
                   if INTO_INDS(I) < 0 then 
                     PUT(SPACES(1..10)); 
                   elsif PREC > 0 and SCALE = 0 then 
                     PUT(INTEGER(INTO_LF_VAR(I)), PREC); 
                     PUT(SPACES(1..10-PREC)); 
                   else 
                     PUT(INTO_LF_VAR(I),FORE=>4,AFT=>2,EXP => 0); 
                     PUT(SPACES(1..3)); 
                   end if; 
                 when ORACLE.SQLT_DAT  => 
                   SQL_STORE( 
                     SELECT_DESC,I,INTO_CHAR_VAR(I),INTO_INDS(I));
                   if INTO_INDS(I) < 0 then 
                     PUT(SPACES(1..10)); 
                   else 
                     PUT(INTO_CHAR_VAR(I)); 
                     PUT(" ");   -- dates are 9 bytes 
                   end if; 
                 when others           => 
                   SQL_STORE(SELECT_DESC,I,INTO_CHAR_VAR(I)); 
                   if INTO_INDS(I) < 0 then 
                     PUT(SPACES(1..10)); 
                   else 
                     N := INTEGER(INTO_CHAR_VAR(I).LENGTH); 
                     PUT(INTO_CHAR_VAR(I)); 
                     PUT(SPACES(1..10-N)); 
                   end if; 
              end case; 
            end loop COLUMN_LOOP; 
            NEW_LINE; 
            exception 
              when NOT_FOUND => 
                NEW_LINE; 
              exit; 
            end; 
          end loop ROW_FETCH_LOOP; 
          NEW_LINE; 
      end if; -- N_VARS > 0 (select) 
    end if; -- STMT_LENGTH > 0 

    exception 
      when PL_EXCEP       => 
        PUT_LINE(ORACLE.ERROR.MESSAGE); 
      when TOO_MANY_VARS  => 
        PUT("Too many bind variables "); 
        PUT("or select-list items in statement ("); 
        PUT(N_VARS, 3); PUT_LINE(")"); 
    end; 
  end loop PROGRAM_LOOP; 
-- <17> close the cursor 
         EXEC SQL CLOSE EXAMPLE_CURSOR; 
    exception 
      EXEC SQL WHENEVER SQLERROR CONTINUE; 
      EXEC SQL WHENEVER NOT FOUND CONTINUE; 
      when CONNECT_ERROR  => 
        PUT_LINE(ORACLE.ERROR.MESSAGE); 
        EXEC SQL ROLLBACK WORK RELEASE; 

end DYN4; 


A Describe Program

The following program demonstrates additional features of dynamic SQL Method 4. This program accepts a SQL query from the user, then displays information about each select-list item, such as the datatype and display length. The information is obtained using the functions on the select descriptor defined in the package ORACLE.DESCRIBE. This program is available as desc.pad in the precompiler demo directory.

-- Copyright (c) 1994 by Oracle Corporation 
--  DESC_SAMPLE :
--      1)  Prompts for user name and password, then logs
--          on to ORACLE
--      2)  The user enters a SELECT statement
--      3)  Calls DISPLAY_COLUMN to print the attributes of each
--          of the select-list items

with system, text_io, 
     integer_text_io, varchar_text_io;

procedure DESC is

use system, text_io,
     integer_text_io, varchar_text_io;

USERNAME                : ORACLE.VARCHAR(40);
STATEMENT               : ORACLE.VARCHAR(512);
STATEMENT_DESCRIPTOR    : ORACLE.DESCRIPTOR_DEF;
COLUMN_NUMBER           : INTEGER;
SIZE                    : INTEGER;
SPACES                  : STRING(1..80) := (1..80 => ' ');
CONNECT_ERROR           : exception;
SQL_ERROR               : exception;

HEADING1                : constant STRING :=
"Item/Column                    Length                  Indicator";

    procedure DISPLAY_COLUMN (
        STATEMENT_DESCRIPTOR    : ORACLE.DESCRIPTOR_DEF;
        COLUMN_NUMBER           : INTEGER) is
    use integer_text_io, text_io;

    COLUMN_NAME_LENGTH  : INTEGER;
    COLUMN_TYPE         : ORACLE.UNSIGNED_BYTE;
    DISPLAY_LENGTH      : INTEGER;
    INTERNAL_LENGTH     : INTEGER;
    PRECISION           : INTEGER;
    SCALE               : INTEGER;
    NULL_OK             : BOOLEAN;
    OUTPUT_LINE         : STRING(1..80) := (1..80 => ' ');
    SQL_TYPE            : STRING(1..8);
    NULL_OK_STRING      : STRING(1..8);     -- "not null" or ""

    begin
        COLUMN_NAME_LENGTH  :=
            ORACLE.DESCRIBE.SELECT_ENTRY_LENGTH (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
        COLUMN_TYPE         :=
            ORACLE.DESCRIBE.SELECT_DATA_TYPE (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
        DISPLAY_LENGTH      :=
            ORACLE.DESCRIBE.MAX_DISPLAY_LENGTH (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
        INTERNAL_LENGTH     :=
            ORACLE.DESCRIBE.ORACLE_SIZE (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
        NULL_OK             :=
            ORACLE.DESCRIBE.NULL_PERMITTED (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
        OUTPUT_LINE(1..COLUMN_NAME_LENGTH) :=
            ORACLE.DESCRIBE.SELECT_ENTRY (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);

        if COLUMN_NAME_LENGTH < 23 then
            PUT_LINE(OUTPUT_LINE(1..COLUMN_NAME_LENGTH));
            OUTPUT_LINE := SPACES;
        end if;

        PUT(OUTPUT_LINE(25..26), INTEGER(COLUMN_TYPE));
        PUT(OUTPUT_LINE(30..34), INTERNAL_LENGTH);
        PUT(OUTPUT_LINE(38..44), DISPLAY_LENGTH);
        PUT(OUTPUT_LINE(1..45));

        case COLUMN_TYPE is
            when ORACLE.SQLT_CHR => SQL_TYPE := "CHAR    ";
            when ORACLE.SQLT_DAT => SQL_TYPE := "DATE    ";
            when ORACLE.SQLT_LNG => SQL_TYPE := "LONG    ";
            when ORACLE.SQLT_LBI => SQL_TYPE := "LONG RAW";
            when ORACLE.SQLT_NUM => SQL_TYPE := "NUMBER  ";
            when ORACLE.SQLT_BIN => SQL_TYPE := "RAW     ";
            when ORACLE.SQLT_RID => SQL_TYPE := "ROWID   ";
            when others          => SQL_TYPE := "Other = ";
                PUT(INTEGER(COLUMN_TYPE), 4);
        end case;

        PUT(SQL_TYPE);


        if (COLUMN_TYPE = ORACLE.SQLT_NUM) then
            PRECISION := ORACLE.DESCRIBE.PRECISION (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
            SCALE     := ORACLE.DESCRIBE.SCALE (
                STATEMENT_DESCRIPTOR, COLUMN_NUMBER);

            PUT("(");
            PUT(PRECISION, 2); PUT(",");
            PUT(SCALE, 2); PUT(")");
        elsif (DISPLAY_LENGTH /= 0) then
            PUT("("); PUT(DISPLAY_LENGTH,5); PUT(")");
        end if;

        if not NULL_OK then
            NULL_OK_STRING := "NOT NULL";
        else
            NULL_OK_STRING := "        ";
        end if;

        PUT(" "); PUT(NULL_OK_STRING);
        NEW_LINE;

    end DISPLAY_COLUMN;

    begin           -- main

        PUT("Enter username/password: ");
        GET_LINE(USERNAME);

        EXEC SQL WHENEVER SQLERROR raise CONNECT_ERROR;

        EXEC SQL CONNECT :USERNAME;

        -- statement loop
        --  1)  Get the statement from user
        --  2)  Parse it
        --  3)  Describe the parsed statement
        --  4)  Print the description of each column

        STATEMENT_LOOP :
        loop
        declare STATEMENT_ERROR : exception;
        begin
            EXEC SQL WHENEVER SQLERROR raise STATEMENT_ERROR;
            -- get the statement text
            NEW_LINE;
            PUT_LINE("Enter a SELECT statement (CR to exit)-- ");
            GET_LINE(STATEMENT);

            if STATEMENT.LENGTH = 0 then
                PUT("Logging off ORACLE...");
                EXEC SQL WHENEVER SQLERROR CONTINUE;
                EXEC SQL ROLLBACK RELEASE;
                exit STATEMENT_LOOP;
            end if;
            EXEC SQL WHENEVER SQLERROR raise STATEMENT_ERROR;
            -- parse the statement
            EXEC SQL PREPARE SQL_STATEMENT FROM :STATEMENT;
            -- describe
            EXEC SQL DESCRIBE SQL_STATEMENT INTO STATEMENT_DESCRIPTOR;
            -- print
            NEW_LINE;
            PUT_LINE(STATEMENT);
            NEW_LINE;
            PUT_LINE(HEADING1);
            NEW_LINE;

            for COLUMN_NUMBER in 1..ORACLE.DESCRIBE.SELECT_LIST_SIZE (
                STATEMENT_DESCRIPTOR)
            loop
                DISPLAY_COLUMN(STATEMENT_DESCRIPTOR, COLUMN_NUMBER);
            end loop;

        exception
            when STATEMENT_ERROR =>
                PUT_LINE("** ORACLE ERROR OCCURRED **");
                NEW_LINE;
                PUT_LINE(ORACLE.ERROR.MESSAGE);
            end;

    end loop STATEMENT_LOOP;

    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL COMMIT RELEASE;

    exception
        when SQL_ERROR | CONNECT_ERROR =>
            PUT_LINE("** ORACLE ERROR OCCURRED **");
            NEW_LINE;
            PUT_LINE(ORACLE.ERROR.MESSAGE);
            EXEC SQL ROLLBACK RELEASE;

end DESC;




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