DEFINE_COLUMN v16

Suggest edits

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;
← Prev

DEFINE_ARRAY

Next →

DEFINE_COLUMN_CHAR