EXEC_SQL.DEFINE_COLUMN
built-in procedureThis 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 <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 |
|
Is the handle to the connection you want to use. If you do not specify
a connection, |
|
Is the cursor handle you want to define the column for. |
|
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. |
|
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. |
|
Is the maximum expected size of the column value in bytes (for column
type |
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;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.