Re: Dynamic SQL Challenging problem

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/04/10
Message-ID: <955389395.6677.0.pluto.d4ee154e@news.demon.nl>#1/1

Keith Jamieson <jamiesonk_at_phoenix.ie> schreef in berichtnieuws 8ct3r9$beh$1_at_kermit.esat.net...
> 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;
> /
>
>
>

change the last bit into
fdbk := DBMS_SQL.EXECUTE(cur);
fdbk := DBMS_SQL.FETCH_ROWS(cur);
while fdbk > 0 loop

    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.FETCH_ROWS(cur);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur);

Should be all,

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Apr 10 2000 - 00:00:00 CDT