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

EXEC_SQL.Execute_And_Fetch

Description

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

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;