This procedure returns the value of the cursor for a given position in a given cursor. It is used to access the data fetched by calling EXEC_SQL.Fetch_Rows.
PROCEDURE EXEC_SQL.Column_Value
([Connid IN CONNTYPE],
Curs_Id IN CURSTYPE,
Position IN PLS_INTEGER,
Value OUT ,
[Column_Error OUT NUMBER],
[Actual_Length OUT PLS_INTEGER]);
where <datatype> is one of the following:
NUMBER
DATE
VARCHAR2
Name | Mode | Description |
---|---|---|
Connid | IN | Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection retrieves the primary Oracle Developer connection handle from the cache. |
Curs_Id | IN | Is the cursor handle to the row from which you want to get the column value. |
Position | IN | Is the relative position of the column in the specified cursor. Starting from the left, the first column is position 1. |
Value | OUT | Returns the value of the specified column and row. |
Column_Error | OUT | Returns the error code for the specified column value (Oracle data sources only). |
Actual_Length | OUT | Returns the actual length of the column value before truncation. |
You must use EXEC_SQL.Define_Column to define the column data characteristics before using EXEC_SQL.Column_Value to retrieve the value. If you specify a value which has a PL/SQL type that is different from what was specified by EXEC_SQL.Define_Column, the exception EXEC_SQL.Value_Error is raised.
PROCEDURE getData IS
connection_id EXEC_SQL.CONNTYPE;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
loc_ename VARCHAR2(30);
loc_eno NUMBER;
loc_hiredate DATE;
nIgn PLS_INTEGER;
nRows PLS_INTEGER := 0;
...
BEGIN
connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str);
cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
sqlstr := 'select ename, empno, hiredate from emp ';
EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7);
EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn', input_empno);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1, loc_ename, 30);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 2, loc_eno);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 3, loc_hiredate);
nIgn := EXEC_SQL.EXECUTE(connection_id, cursorID);
--
-- You must have used DEFINE_COLUMN to define the column data characteristics before
-- using COLUMN_VALUE to retrieve the value. Assign the row's first value to the