Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Note: You should be familiar with the material about dynamic SQL Methods 1, 2, and 3, and an overview of Method 4
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;
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:
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 .
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;
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";
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.
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.
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;
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.
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.
oracle.describe.bind_variable_length(
d : oracle.descriptor_def,
vn : integer) return integer;
oracle.describe.indicator_variable ( d : oracle.descriptor_def, vn : integer) return string;
oracle.describe.indicator_variable_length ( d : oracle.descriptor_def, vn : integer) return integer;
If there is no indicator variable, this function returns zero.
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.
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.
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;
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.
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;
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.
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.
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.
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.
The syntax for this function is:
oracle.describe.precision (d : oracle.descriptor_def,
i : integer) return integer;
oracle.describe.scale (d : oracle.descriptor_def,
i : integer) return integer;
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;
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;
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.
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 |
function error_code ( d : oracle.descriptor_def, i : integer) return integer
This function simply returns the SQLCODE value.
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;
EXEC SQL CLOSE cursor_name;
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;
-- 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;
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |