EXEC_SQL.DESCRIBE_COLUMN
built-in procedureThis procedure obtains information about the columns in a result set of a parsed
SQL statement. If you try to describe a column number that does not exist in
the result set, the EXEC_SQL.INVALID_COLUMN_NUMBER
exception is
raised.
PROCEDURE EXEC_SQL.DESCRIBE_COLUMN
([connid IN CONNTYPE,
curs_id IN CURSTYPE,
position IN PLS_INTEGER,
name OUT VARCHAR2,
collen OUT PLS_INTEGER,
type OUT PLS_INTEGER]);
Parameter |
Description |
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
Is the cursor handle associated to the column you want to describe. |
|
Is the position in the result set of the column you want to describe. The positions are numbered from left to right, starting at 1. |
|
Contains the name of the column, on output. |
|
Contains the maximum length of the column in bytes, on output. |
|
Contains the type of the column, on output. The valid values are one the following:
|
PROCEDURE esdesccol(tablename VARCHAR2) IS
connection_id EXEC_SQL.CONNTYPE;
cursor_number EXEC_SQL.CURSTYPE;
sql_str VARCHAR2(256);
nIgnore PLS_INTEGER;
nColumns PLS_INTEGER := 0;--count of number of columns returned
colName VARCHAR2(30);
colLen PLS_INTEGER;
colType PLS_INTEGER;
BEGIN
connection_id := EXEC_SQL.OPEN_CONNECTION('connection_string');
--
-- when you do a "select *..." from a table which is known only at runtime,
-- you cannot know what the columns are a priori. EXEC_SQL.Describe_Column becomes
-- very usefule then
--
sql_str := 'select * from ' || tablename;
cursor_number := EXEC_SQL.OPEN_CURSOR(connection_id);
EXEC_SQL.PARSE(connection_id, cursor_number, sql_str, exec_sql.V7);
nIgnore := EXEC_SQL.EXECUTE(connection_id, cursor_number);
LOOP
nColumns := nColumns + 1; --used as column index into result set
--
-- describe_column is in general used within a PL/SQL block with an exception
-- block included to catch the EXEC_SQL.invalid_column_number exception.
-- when no more columns are found, we can store the returned column names
-- and column lengths in a PL/SQL table of records and do further queries
-- to obtain rows from the table. In this example, colName, colLen and colType
-- are used to store the returned column characteristics.
--
BEGIN
EXEC_SQL.DESCRIBE_COLUMN(connection_id, cursor_number, nColumns, colName, colLen, colType);
TEXT_IO.PUT_LINE(' col= ' || nColumns || ' name ' || colName ||' len= ' || colLen || ' type ' || colType );
EXCEPTION
WHEN EXEC_SQL.INVALID_COLUMN_NUMBER THEN
EXIT;
END;
END LOOP;
nColumns := nColumns - 1;
IF (nColumns <= 0) THEN
TEXT_IO.PUT_LINE('No columns returned in query');
END IF;
...
EXEC_SQL.CLOSE_CURSOR(connection_id, cursor_number);
EXEC_SQL.CLOSE_CONNECTION(connection_id);
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.