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

EXEC_SQL.Column_Value

Description

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 ,
     [Column_Error  OUT NUMBER],
     [Actual_Length OUT PLS_INTEGER]);

where <datatype> is one of the following:


NUMBER
DATE
VARCHAR2

Parameters

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.

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