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

EXEC_SQL.Describe_Column

Description

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.

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]);

Parameters

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