DEFINE_COLUMN v11
The DEFINE_COLUMN
procedure defines a column or expression in the SELECT
list that is to be returned and retrieved in a cursor.
DEFINE_COLUMN(<c> INTEGER, <position> INTEGER, <column> { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 } [, <column_size> INTEGER ])
Parameters
c
Cursor id of the cursor associated with the SELECT
command.
position
Position of the column or expression in the SELECT
list that is being defined.
column
A variable that is of the same data type as the column or expression in position position
of the SELECT
list.
column_size
The maximum length of the returned data. column_size
must be specified only if column
is VARCHAR2
. Returned data exceeding column_size
is truncated to column_size
characters.
Examples
The following shows how the empno, ename, hiredate, sal
, and comm
columns of the emp
table are defined with the DEFINE_COLUMN
procedure.
DECLARE curid INTEGER; v_empno NUMBER(4); v_ename VARCHAR2(10); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp'; v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native); DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno); DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10); DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate); DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal); DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm); . . . END;
The following shows an alternative to the prior example that produces the exact same results. Note that the lengths of the data types are irrelevant – the empno, sal
, and comm
columns will still return data equivalent to NUMBER(4)
and NUMBER(7,2)
, respectively, even though v_num
is defined as NUMBER(1)
(assuming the declarations in the COLUMN_VALUE
procedure are of the appropriate maximum sizes). The ename
column will return data up to ten characters in length as defined by the length
parameter in the DEFINE_COLUMN
call, not by the data type declaration, VARCHAR2(1)
declared for v_varchar
. The actual size of the returned data is dictated by the COLUMN_VALUE
procedure.
DECLARE curid INTEGER; v_num NUMBER(1); v_varchar VARCHAR2(1); v_date DATE; v_sql VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp'; v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native); DBMS_SQL.DEFINE_COLUMN(curid,1,v_num); DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10); DBMS_SQL.DEFINE_COLUMN(curid,3,v_date); DBMS_SQL.DEFINE_COLUMN(curid,4,v_num); DBMS_SQL.DEFINE_COLUMN(curid,5,v_num); . . . END;