Executing Queries

The difference between queries and nonqueries is that queries retrieve a result set from a SELECT statement. The result set is retrieved by using a cursor.

Figure 3-2 illustrates the steps in a passthrough SQL query. After the system parses the SELECT statement, each row of the result set can be retrieved with the FETCH_ROW procedure. After the row is retrieved, use the GET_VALUE procedure to retrieve the selected list of items into program variables. After all rows are retrieved, you can close the cursor.

Figure 3-2 Passthrough SQL for Queries


Description of
Description of "Figure 3-2 Passthrough SQL for Queries"

You do not have to retrieve all the rows. You can close the cursor at any time after opening the cursor.

Note:

Although you are retrieving one row at a time, Heterogeneous Services optimizes the round-trips between Oracle Database and the non-Oracle system by buffering multiple rows and fetching from the non-Oracle data system in one round-trip.

The following example executes a query:

DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb; 
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c, 
    'select ENAME
     from   EMP
     where  DEPTNO=10');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
    EXIT WHEN nr = 0;
    DBMS_HS_PASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;  
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c); 
END;

After the SELECT statement has been parsed, the rows are fetched and printed in a loop until the FETCH_ROW function returns the value 0.