COLUMN_VALUE v12
The COLUMN_VALUE
procedure defines a variable to receive a value from a cursor.
COLUMN_VALUE(<c> INTEGER, <position> INTEGER, <value> OUT { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 } [, <column_error> OUT NUMBER [, <actual_length> OUT INTEGER ]])
Parameters
c
Cursor id of the cursor returning data to the variable being defined.
position
Position within the cursor of the returned data. The first value in the cursor is position 1.
value
Variable receiving the data returned in the cursor by a prior fetch call.
column_error
Error number associated with the column, if any.
actual_length
Actual length of the data prior to any truncation.
Examples
The following example shows the portion of an anonymous block that receives the values from a cursor using the COLUMN_VALUE
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 . . . 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,4,v_sal); DBMS_SQL.COLUMN_VALUE(curid,5,v_comm); DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,10) || ' ' || 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;