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.
Tip: To obtain the number of columns in a result set, loop through the columns from 1 until the EXEC_SQL.Invalid_Column_Number exceptions 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 |
---|---|
Connid | Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection retrieves the primary Oracle Developer connection handle from the cache. |
Curs_Id | Is the cursor handle associated to the column you want to describe. |
Position | 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. |
Name | Contains the name of the column, on output. |
Collen | Contains the maximum length of the column in bytes, on output. |
Type |
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;