Append the output of Oracle PL/SQL block Loop into one table

Question

I have a very beginner knowledge on SQL and trying to get this work for a couple days but no luck unfortunately.

I have a PL/SQL block code like this..

BEGIN
For i in (SELECT 'SELECT * FROM ' || ACTIVE_POD || '.userservice' AS QUERY
FROM active_tables) loop
dbms_output.put_line(i.query);
end loop;
END;

This will output

SELECT * FROM DB1.userservice
SELECT * FROM DB2.userservice
....
SELECT * FROM DB99.userservice

What I want to get is union of all above query outputs into one table. For example) Let's say the union of above output will be three integer columns

    ID |item_code|type
    10 | 2       |1001
    91 | 2       |1005
    10 | 2       |1011

I would appreciate any guidance on this. thank you!

Probably you come from MSSQL, in Oracle it doesn't work, you will have to invent a workaround which suits best your needs, @wolfrevokcats answer is one option. — Sergiu Velescu
– Sergiu Velescu, Commented Aug 29, 2017 at 7:51

wolfrevokcats · Accepted Answer · 2017-08-29 05:56:00Z

2

Declare a variable for the combined statement:

declare l_stmt varchar2(32767);
begin
...

Insided the loop concatenate statements like this:

l_stmt:=l_stmt|| case when l_stmt is not null then chr(10)||' union all ' end||i.query;

Then execute immediate:

execute immediate 'insert into your_table select * from ('||l_stmt||')';

The resulting statement will look like

insert into your_table select * from (
SELECT * FROM DB1.userservice 
union all SELECT * FROM DB2.userservice
...
union all SELECT * FROM DB99.userservice)

Another way is to use a pipelined function.

edited Aug 29, 2017 at 5:56

answered Aug 29, 2017 at 5:48

wolfrevokcats

2,1101 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1486507 Over a year ago

I was totally new and wasn't sure the exact order but ended up having this work as well. Thank you!

Hasan Alizada · Accepted Answer · 2017-08-31 08:42:31Z

1

If you need just generator for this, you can use this generator, written by me special for this task (don't forget to change SELECTs):

DECLARE
   qry   VARCHAR2 (1000);
   chk   NUMBER := 0;
BEGIN
   qry := 'insert into your_table ';

   FOR rec
      IN (SELECT 'SELECT * FROM ' || ACTIVE_POD || '.userservice' AS stmnt
            FROM active_tables)
   LOOP
      IF chk <> 0
      THEN
         qry := qry || ' union all' || rec.stmnt;
      ELSE
         qry := qry || rec.stmnt;
      END IF;

      chk := chk + 1;
   END LOOP;

   qry := qry || ';';
   DBMS_OUTPUT.put_line (qry);
END;

But, note, that there are some more cool features and techniques in PL/SQL, that could do this work faster and better, based on task.

edited Aug 31, 2017 at 8:42

answered Aug 29, 2017 at 9:10

Hasan Alizada

5914 silver badges13 bronze badges

5 Comments

user1486507 Over a year ago

thanks for the help! just one more Q, I replaced ' IN (SELECT ~ DUAL)' with 'in (SELECT 'SELECT * FROM ' || ACTIVE_POD || '.userservice' AS QUERY FROM active_tables)' from my original query. Did I miss DUAL? also I get 'PLS-00302: component 'STMNT' must be declared' error even though I added 'stmnt varchar2(32767);' before BEGIN.

user1486507 Over a year ago

I feel like it's really getting close but can't get this work even though all sort of different attempts -.-;

Hasan Alizada Over a year ago

No. Problem was in 'QUERY' alias. I've edited script. Try it again.

user1486507 Over a year ago

oh, I see..Thank you! I changed the alias and I don't see any error but the table is empty and i don't see anything after running the query though.. (I added 'SET serveroutput ON;'too, but no changes.) Can you suggest where i can look? I really appreciate your help -)

user1486507 Over a year ago

it's very late but I tried different things and ended up finding out that the problem was in my DB setting (it worked after I cleared up a bunch of table). Thank you so much for your help and i learned alot!

Tomek Krasuski · Accepted Answer · 2017-08-31 08:22:20Z

1

Oracle has special package for handling dynamic SQL. Maybe this link would be helpful https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68199

answered Aug 31, 2017 at 8:22

Tomek Krasuski

517 bronze badges

Collectives™ on Stack Overflow

Append the output of Oracle PL/SQL block Loop into one table

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related