A script-enabled browser is required for this page to function properly.

EXEC_SQL.COLUMN_VALUE built-in procedure

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.

Syntax


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

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

Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.DEFAULT_CONNECTION retrieves the primary connection handle from the cache.

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.

Usage notes

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.

Example


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 

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package