Dynamic SQL Challenging problem

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: 2000/04/10
Message-ID: <8ct3r9$beh$1@kermit.esat.net>#1/1

I have some dynamic procedures which need to be modified. Currently the statements are parsed and execution takes place via a cursor for loop.
The parsed statements have a variable which holds the database link.

Now, what I need to do is to modify the cursor for loop so that it can have the database link appended when necessary, ie I want it to select from either the remote or the local database depending on the current operation.

I cannot have the database link hardcoded as I do not know what the link is going to be.

I am on Oracle 7.3.4 for NT, so Any Oracle 8 Solutions are out.

So far I have been able to perform a single row select and that works okay, I just can't seem to fathom out how to translate it to a multi-row select.

The current code is posted below;

declare
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk integer;

v_string varchar2(4);
v_serverid number(38);
v_length integer := 4;
v_error integer;

BEGIN
   DBMS_SQL.PARSE(cur,'SELECT serverid,servertype from server',DBMS_SQL.NATIVE);

   DBMS_SQL.DEFINE_COLUMN(cur,1,1);
   DBMS_SQL.DEFINE_COLUMN(cur,2,v_string,4);    fdbk := DBMS_SQL.EXECUTE_AND_FETCH(cur);

   DBMS_SQL.COLUMN_VALUE(cur,1,v_serverid);
   DBMS_SQL.COLUMN_VALUE(cur,2,v_string);
   DBMS_OUTPUT.PUT_LINE('Serverid is '||v_serverid||' Servertype is

'||v_string);

   DBMS_SQL.CLOSE_CURSOR(cur);
END;
/ Received on Mon Apr 10 2000 - 00:00:00 CDT