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

EXEC_SQL.Define_Column

Description

This procedure is used only with SELECT statements or calls to non-Oracle stored procedures that return a result set. It defines a column to be fetched from a specified cursor. The column is identified by its relative position in the result set; the first relative position is identified by the integer 1. The PL/SQL type of the Column parameter determines the type of the column being defined.

Syntax


PROCEDURE EXEC_SQL.Define_Column
    ([Connid     IN CONNTYPE],
      Curs_Id    IN CURSTYPE,
      Position   IN PLS_INTEGER,
      Column     IN );

where  can be one of the following: 

      NUMBER
      DATE
      VARCHAR2

PROCEDURE EXEC_SQL.Define_Column
    ([Connid       IN CONNTYPE],
      Curs_Id      IN CURSTYPE,
      Position     IN PLS_INTEGER,
      Column       IN VARCHAR2,
      Column_Size  IN PLS_INTEGER);

Parameters

Parameter Description
Connid 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 Is the cursor handle you want to define the column for.
Position s the relative position of the column in the row or result set. The first column in the statement has a relative position of 1.
Column Is the maximum expected size of the column value in bytes (for column type VARCHAR2 only)
Column_Size The maximum OUT value size in bytes expected for the VARCHAR2 OUT or IN/OUT variables. If no size is specified, the current length of the Value parameter is used.

Usage Notes

For a query, you must define the column before retrieving its data by EXEC_SQL.Column_Value.

Example


PROCEDURE getData IS
  connection_id EXEC_SQL.CONNTYPE; 
  cursorID EXEC_SQL.CURSTYPE;
  sqlstr VARCHAR2(1000);  
  loc_ename VARCHAR2(30);   -- these are variables local to the procedure; 
  loc_eno NUMBER;           -- used to store the return values from our desired
  loc_hiredate DATE;        -- query

 ...

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);
  --
  -- we make one call to DEFINE_COLUMN per item in the select list. We must use local 
  -- variables to store the returned values. For a result value that is a VARCHAR, it 
  -- is important to specify the maximumn length. For a result value that is a number
  -- or a date, there is no need to specify the maximum length. We obtain the 
  -- relative positions of the columns being returned from the select statement, 
  -- sql_str.
  --
  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);   

 ...

END;