EXEC_SQL.EXECUTE
buit-in functionThis procedure executes the SQL statement at a specified cursor.
FUNCTION EXEC_SQL.EXECUTE
([connid IN CONNTYPE],curs_id IN CURSTYPE)
RETURN 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 to the SQL statement you want to execute. |
The number of rows processed.
INSERT
, UPDATE
and
DELETE
statements. For other statements, including DDL, ignore the
return value because it is undefined.
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;
...
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);
--
-- after parsing, and calling BIND_VARIABLE and DEFINE_COLUMN, if necessary, you
-- are ready to execute the statement. Note that all information about the
-- statement and its result set is encapsulated in the cursor referenced as cursorID.
--
nIgn := EXEC_SQL.EXECUTE(connection_id, cursorID);
...
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.