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 - 20:53:58 CEST