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

EXEC_SQL.DEFINE_COLUMN built-in procedure

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 <datatype>);

where <datatype> 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);

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 connection handle from the cache.

curs_id

Is the cursor handle you want to define the column for.

position

Is 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 value of the column being defined. The value type determines the column type being defined. The actual value stored in the variable is ignored.

column_size

Is the maximum expected size of the column value in bytes (for column type VARCHAR2 only)

 

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;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package