Home » SQL & PL/SQL » SQL & PL/SQL » Display result of sql on PL-SQL For loop (Oracle 11.2.0.3,Linux x86_64)
Display result of sql on PL-SQL For loop [message #583948] Tue, 07 May 2013 05:00 Go to next message
preet_kumar
Messages: 178
Registered: March 2007
Senior Member
I have the below PL/SQL Block in which it iterates through the id stored in local table and passes this value to the select statement which runs on a remote DB.
In the loop the select statement is executed for each id in local table.Now i am trying to display the output of the execute statement which does not work.
Could anyone let me know how can i show the output of this and store in a file or display on screen.

server output on
DECLARE
v_sql VARCHAR2(9000) := NULL;
BEGIN

for i in ( select id from abc) loop
v_sql := 'select * from xyz@dblink where id='||i.id|| 'and order=N';
execute immediate v_sql;
end loop;

END;
/
Re: Display result of sql on PL-SQL For loop [message #583954 is a reply to message #583948] Tue, 07 May 2013 05:41 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member


SET serveroutput ON
DECLARE
  v_sql VARCHAR2(9000) := NULL;
BEGIN
  FOR i IN
  ( SELECT deptno FROM dept
  )
  LOOP
  dbms_output.put_line('department '||i.deptno||' data');
    FOR j IN
    (SELECT * FROM emp WHERE deptno=i.deptno
    )
    LOOP
      dbms_output.put_line(j.ename);
    END LOOP;
  END LOOP;
END;
Re: Display result of sql on PL-SQL For loop [message #583955 is a reply to message #583954] Tue, 07 May 2013 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you using dynamic sql? There's no need with the code you posted, why is it not simply:
select * from xyz@dblink where id in (select id from abc) and order='N';

Re: Display result of sql on PL-SQL For loop [message #583956 is a reply to message #583955] Tue, 07 May 2013 06:05 Go to previous message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Yes CookiMonster that is right the select statment is enough to get the information .
Previous Topic: Using VArray For Known Column Names?
Next Topic: help in this guyz (merged 3)
Goto Forum:
  


Current Time: Fri Dec 26 08:25:03 CST 2014

Total time taken to generate the page: 0.04075 seconds