DESCRIBE_COLUMNS2 v16

Suggest edits

The DESCRIBE_COLUMNS2 procedure describes specified columns returned by a cursor. This procedure provides an alternative to DESCRIBE_COLUMN.

DESCRIBE_COLUMNS2(<c> IN NUMBER, <col_cnt> OUT NUMBER, <desc_tab2> OUT
  DESC_TAB2);

Parameters

c

The cursor ID of the cursor.

col_cnt

The number of columns in the cursor result set.

desc_tab2

The table that contains a description of each column returned by the cursor. The descriptions are of type DESC_REC2 and contain the following values:

Column nameType
col_typeINTEGER
col_max_lenINTEGER
col_nameVARCHAR2(128)
col_name_lenINTEGER
col_schema_nameVARCHAR2(128)
col_schema_name_lenINTEGER
col_precisionINTEGER
col_scaleINTEGER
col_charsetidINTEGER
col_charsetformINTEGER
col_null_okBOOLEAN

Examples

DECLARE
    c INTEGER := DBMS_SQL.OPEN_CURSOR;
    l_columns DBMS_SQL.desc_tab2;
    l_numcols INTEGER;
    dbms_return INTEGER;
BEGIN
    DBMS_SQL.parse ( c, 'SELECT * FROM emp', DBMS_SQL.native );
    dbms_return := DBMS_SQL.EXECUTE ( c );
    DBMS_SQL.describe_columns2( c, l_numcols, l_columns );
   DBMS_OUTPUT.PUT_LINE( 'Total # of Columns     :    '   || l_numcols);
     DBMS_OUTPUT.put_line ( '--------------------------------------');
    FOR colind IN 1 .. l_numcols
    LOOP
    DBMS_OUTPUT.PUT_LINE( 'Columns # :                 '   || colind);
    DBMS_OUTPUT.put_line ( 'Column Name :               '   || l_columns ( colind ).col_name);
    DBMS_OUTPUT.put_line ( 'Column Type :               '   || l_columns ( colind ).col_type);
    DBMS_OUTPUT.put_line ( 'Column Max Length :         '   || l_columns ( colind ).col_max_len);
    DBMS_OUTPUT.put_line ( 'Column Name Length :        '   || l_columns ( colind ).col_name_len);
    DBMS_OUTPUT.put_line ( 'Column Schema Name :        '   || l_columns ( colind ).col_schema_name);
    DBMS_OUTPUT.put_line ( 'Column Schema Name Length : '   || l_columns ( colind ).col_schema_name_len);
    DBMS_OUTPUT.put_line ( 'Column Precision :          '   || l_columns ( colind ).col_precision);
    DBMS_OUTPUT.put_line ( 'Column Scale :              '   || l_columns ( colind ).col_scale);
    DBMS_OUTPUT.put_line ( 'Character Set ID :          '   || l_columns ( colind ).col_charsetid);
    DBMS_OUTPUT.put_line ( 'Character Set Form :        '   || l_columns ( colind ).col_charsetform);
    DBMS_OUTPUT.put ( 'Column Can be NULL :        ');
    IF (l_columns ( colind ).col_null_ok) THEN
            DBMS_OUTPUT.put_line ( 'NULL');
    ELSE
            DBMS_OUTPUT.put_line ( 'NOT NULL');
    END IF;
    DBMS_OUTPUT.put_line ( '--------------------------------------');
    END LOOP;
    DBMS_SQL.close_cursor ( c );
END;
← Prev

DESCRIBE_COLUMNS

Next →

DESCRIBE_COLUMNS3