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.
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);
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. |
For a query, you must define the column before retrieving its data by EXEC_SQL.Column_Value.
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;