COLUMN_VALUE_ROWID v17

Suggest edits

The COLUMN_VALUE_ROWID procedure defines a column of the type ROWID to be returned and retrieved in a cursor. 

COLUMN_VALUE_ROWID(<c> NUMBER, <position> NUMBER, <value> OUT ROWID
  [, <column_error> OUT NUMBER [, <actual_length> OUT INTEGER ]]);

Parameters

c

Cursor ID of the cursor returning data to the variable being defined.

position

Position of the returned data in the cursor. The first value in the cursor is position 1.

value

The ROWID 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

DECLARE
    curid           NUMBER;
    v_rid           ROWID;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(55) := 'SELECT rid, 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_rowid(curid,1, v_rid);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,6,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_rowid(curid, 1, v_rid);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_empno);
DBMS_SQL.COLUMN_VALUE(curid,3,v_ename);
DBMS_SQL.COLUMN_VALUE(curid,4,v_hiredate);
DBMS_SQL.COLUMN_VALUE(curid,5,v_sal);
DBMS_SQL.COLUMN_VALUE(curid,6,v_comm);
DBMS_OUTPUT.PUT_LINE(v_rid || ' ' || 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

COLUMN_VALUE_LONG

↑ Up

DBMS_SQL

Next →

DEFINE_ARRAY