Oracle SQL - Query multiple schemas

Question

Oracle SQL - Query multiple schemas

Navigation

#1 by (0 votes)

1

I was able to assemble the result, but now I want to create the view with this result. What is the best way?

This query:

"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, can you execute this result and create a view so that both the result and the permanent view remain? What is the best way? Thankful.

sql oracle

asked by anonymous 13.11.2015 / 10:58

1 answer

Problems in a SQL query [closed] Admin View Ruby on Rails

score 0 · Accepted Answer

Tip: Instead of CONCAT(p1,p2) , use the concatenation operator || . So:

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

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

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 run 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;