DEFINE_COLUMN v14
The DEFINE_COLUMN
procedure defines a column or expression in the SELECT
list to be returned and retrieved in a cursor.
DEFINE_COLUMN(<c> NUMBER, <position> NUMBER, <column> { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 } [, <column_size> NUMBER ])
Parameters
c
Cursor id of the cursor associated with the SELECT
command.
position
Position of the column or expression in the SELECT
list that's being defined.
column
A variable 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. Specify column_size
only if column
is VARCHAR2
. Returned data exceeding column_size
is truncated to column_size
characters.
Examples
This example shows how the empno
, ename
, hiredate
, sal
, and comm
columns of the emp
table are defined with the DEFINE_COLUMN
procedure.
DECLARE curid NUMBER; 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); v_status := DBMS_SQL.EXECUTE(curid); LOOP v_status := DBMS_SQL.FETCH_ROWS(curid); EXIT WHEN v_status = 0; DBMS_SQL.COLUMN_VALUE(curid,1,v_empno); DBMS_SQL.COLUMN_VALUE(curid,2,v_ename); DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate); DBMS_SQL.COLUMN_VALUE(curid,4,v_sal); DBMS_SQL.COLUMN_VALUE(curid,5,v_comm); DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||v_ename|| ' ' || TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' || TO_CHAR(v_sal,'9,999.99') || ' ' || TO_CHAR(NVL(v_comm,0),'9,999.99')); END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END;
The following shows an alternative that produces the same results. The lengths of the data types are irrelevant. The empno
, sal
, and comm
columns 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 returns data up to 10 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 NUMBER; 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); v_status := DBMS_SQL.EXECUTE(curid); LOOP v_status := DBMS_SQL.FETCH_ROWS(curid); EXIT WHEN v_status = 0; DBMS_SQL.COLUMN_VALUE(curid,1,v_empno); DBMS_SQL.COLUMN_VALUE(curid,2,v_ename); DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate); DBMS_SQL.COLUMN_VALUE(curid,4,v_sal); DBMS_SQL.COLUMN_VALUE(curid,5,v_comm); DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||v_ename|| ' ' || TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' || TO_CHAR(v_sal,'9,999.99') || ' ' || TO_CHAR(NVL(v_comm,0),'9,999.99')); END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END;
- On this page
- Parameters
- Examples