DEFINE_COLUMN_ROWID v16

Suggest edits

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

Note

In EDB Postgres Advanced Server, ROWID is a domain rather than a bigint.

When you use this procedure the column you are defining is identified by its relative position in the SELECT list of the statement in the specified cursor. The COLUMN value type determines the type of column being defined.

DEFINE_COLUMN_ROWID(<c> IN NUMBER, <position> IN NUMBER, <column> IN NUMBER);

Parameters

c

Cursor ID of the cursor from which a row is selected.

position

Relative position of the column in the row being defined. The first column in a statement has position 1.

column

Value of the column. The type of this value determines the type for the column being defined.

Examples

This example shows how the empno, ename, hiredate, sal, and comm columns of the emp table are defined with the DEFINE_COLUMN_ROWID procedure.

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

DEFINE_COLUMN_LONG

Next →

DESCRIBE_COLUMNS