Re: Dynamic SQL Challenging problem

Thanks very much for that Sybrand, it helps me out of a big jam. It was obviously not as challenging as you had hoped. PS. Minor Syntax error in your reply, re last fetch, but all is now well.

Sybrand Bakker wrote in message
<955389395.6677.0.pluto.d4ee154e_at_news.demon.nl>...
>
>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