A script-enabled browser is required for this page to function properly.

EXEC_SQL.DESCRIBE_COLUMN built-in procedure

This 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.

Syntax


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 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:

EXEC_SQL.VARCHAR2_TYPE EXEC_SQL.NUMBER_TYPE
EXEC_SQL.FLOAT_TYPE
EXEC_SQL.LONG_TYPE
EXEC_SQL.ROWID_TYPE
EXEC_SQL.DATE_TYPE
EXEC_SQL.RAW_TYPE
EXEC_SQL.LONG_RAW_TYPE
EXEC_SQL.CHAR_TYPE (ANSI fixed CHAR)
EXEC_SQL.MLSLABLE_TYPE (Trusted Oracle only)

Example


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;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package