This function calls EXEC_SQL.Execute and then EXEC_SQL.Fetch_Rows. It executes a SQL statement at a specified cursor and retrieves the first row that satisfies the query. Calling EXEC_SQL.Execute_And_Fetch may reduce the number of round-trips when used against a remote database.
FUNCTION EXEC_SQL.Execute_And_Fetch
([Connid IN CONNTYPE],
Curs_Id IN CURSTYPE,
Exact IN BOOLEAN DEFAULT FALSE)
RETURN 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 to the SQL statement you want to execute. |
Exact | The default is FALSE. Set to TRUE to raise the exception EXEC_SQL.Package_Error. The row is retrieved even if the exception is raised. |
PROCEDURE getData(input_empno NUMBER) 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);
--
-- assuming that empno is a primary key of the table emp, the where clause guarantees
-- that 0 or 1 row is returned
--
sqlstr := 'select ename, empno, hiredate from emp '
sqlstr := sqlstr || ' where empno = ' || input_empno;
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);
--
-- do execute_and_fetch after parsing the statement, and calling bind_variable and
-- define_column if necessary
--
nIgn := EXEC_SQL.EXECUTE_AND_FETCH (connection_id, cursorID);
IF (nIgn = 0 ) THEN
TEXT_IO.PUT_LINE (' No employee has empno = ' || input_empno);
ELSE IF (nIgn = 1) THEN
TEXT_IO.PUT_LINE (' Found one employee with empno ' || input_empno);
--
-- obtain the values in this row
--
EXEC_SQL.column_value(connection_id, cursorID, 1, loc_ename);
EXEC_SQL.column_value(connection_id, cursorID, 2, loc_eno);
EXEC_SQL.column_value(connection_id, cursorID, 3, loc_hiredate);
...
END IF;
...
END;