DESCRIBE_COLUMNS3 v16

Suggest edits

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

DESCRIBE_COLUMNS3(<c> IN NUMBER, <col_cnt> OUT NUMBER, <desc_tab3> OUT
  DESC_TAB3);

Parameters

c

The cursor ID of the cursor.

col_cnt

The number of columns in the cursor result set.

desc_tab3

The table that contains a description of each column returned by the cursor. The descriptions are of type DESC_REC3 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
col_type_nameVARCHAR2(32767)
col_type_name_lenINTEGER

Examples

CREATE TYPE PROJECT_DETAILS AS OBJECT
      ( projname          VARCHAR2(20),
        mgr               VARCHAR2(20));
CREATE TABLE projecttab (deptno NUMBER, project PROJECT_DETAILS);

DECLARE
  curid      NUMBER;
  desctab    DBMS_SQL.DESC_TAB3;
  colcnt     NUMBER;
  sql_stmt   VARCHAR2(200) := 'SELECT * FROM projecttab';
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS3(curid, colcnt, desctab);
    FOR i IN 1 .. colcnt LOOP
      IF desctab(i).col_type = 109 THEN
        DBMS_OUTPUT.PUT(desctab(i).col_name || ' is user-defined type: ');
        DBMS_OUTPUT.PUT_LINE('COL_TYPE_NAME = ' || desctab(i).col_type_name
       || ' COL_TYPE_NAME_LEN = ' || desctab(i).col_type_name_len);
      ELSE
       DBMS_OUTPUT.PUT(desctab(i).col_name || ' is not user-defined type: ');
        DBMS_OUTPUT.PUT_LINE('COL_TYPE_NAME is NULL and '
       || 'COL_TYPE_NAME_LEN = ' || desctab(i).col_type_name_len);
      END IF;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
← Prev

DESCRIBE_COLUMNS2

Next →

EXECUTE