Oracle SQL - Query multiple schemas

Question

I was able to assemble the result, now missing create the view with this result. Which is the best way?

This consultation:

"SELECT CONCAT(CONCAT(CONCAT('SELECT * FROM ',a.OWNER),'.TAB2'),CASE WHEN a.OWNER = UPPER(b.TESTE1) THEN '' ELSE ' UNION ALL' END) TESTE 
FROM ALL_TABLES a LEFT JOIN (SELECT MAX(b.DB_NAME) TESTE1 FROM INST.TAB1 b ORDER BY b.DB_NAME) b ON a.OWNER = UPPER(b.TESTE1) 
WHERE a.TABLE_NAME = 'TAB2' AND a.OWNER IN(SELECT UPPER(c.DB_NAME) FROM INST.TAB1 c WHERE c.COL2 = 'Y' GROUP BY c.DB_NAME)
ORDER BY a.OWNER"

Give me this result:

SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 UNION ALL
SELECT * FROM INST1.TAB2 

And now, you have how to execute this result and create a view so that you get both the result and the permanent view? What is the best way? Grateful.

Answer 1

Tip: instead of CONCAT(p1,p2), use the concatenation operator ||. Thus:

SELECT 'SELECT * FROM ' || a.owner || '.TAB2' TESTE 

Your first operation will be to aggregate all lines into one. You can do this using listagg(). Thus:

SELECT   Listagg(teste, ' UNION ALL ') within GROUP (ORDER BY ROWNUM)

The second parameter of Listagg indicates the term to be inserted between the concatenated lines.

You can then use a variable to accumulate the result,

DECLARE 
  v_cmd VARCHAR2(32767); 
BEGIN 
  SELECT   listagg(TESTE, ' UNION ALL ') within GROUP (ORDER BY ROWNUM) 
  INTO     v_cmd

And at the end execute the variable:

EXECUTE IMMEDIATE v_cmd;

Your final sequence will look like this:

DECLARE 
    v_cmd VARCHAR2(32767); 
BEGIN 
    SELECT Listagg(TESTE, ' UNION ALL ') 
             within GROUP (ORDER BY ROWNUM) 
    INTO   v_cmd 
    FROM   (SELECT 'SELECT * FROM ' 
                   || a.owner 
                   || '.TAB2' TESTE 
            FROM   all_tables a 
                   left join (SELECT Max(b.db_name) TESTE1 
                              FROM   inst.tab1 b 
                              ORDER  BY b.db_name) b 
                          ON a.owner = Upper(b.teste1) 
            WHERE  a.table_name = 'TAB2' 
                   AND a.owner IN(SELECT Upper(c.db_name) 
                                  FROM   inst.tab1 c 
                                  WHERE  c.col2 = 'Y' 
                                  GROUP  BY c.db_name) 
            ORDER  BY a.owner); 

    EXECUTE IMMEDIATE v_cmd;
END;