EXEC_SQL.COLUMN_VALUE
built-in procedureThis 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 <datatype>,
[Column_Error OUT NUMBER],
[Actual_Length OUT PLS_INTEGER]);
where <datatype>
is one of the following:
NUMBER
DATE
VARCHAR2
Parameter |
Mode |
Description |
|
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
|
Is the cursor handle to the row from which you want to get the column value. |
|
|
Is the relative position of the column in the specified cursor. Starting from the left, the first column is position 1. |
|
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
|
Returns the error code for the specified column value (Oracle data sources only). |
|
|
Returns the actual length of the column value before truncation. |
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
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.