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

EXEC_SQL.EXECUTE_AND_FETCH built-in function

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.

Syntax


FUNCTION EXEC_SQL.EXECUTE_AND_FETCH
([connid IN CONNTYPE],
curs_id IN CURSTYPE,
exact IN BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;

Parameters

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 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.

Returns

The number of rows fetched (either 0 or 1).

Example


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; 

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package