How to return result of many select statements as one custom table

Question

I have a table (let's name it source_tab) where I store list of all database tables that meet some criteria.

tab_name:     description:
table1        some_desc1
table2        some_desc2

Now I need to execute a select statement on each of these tables and return a result as a table (I created custom TYPE). However I have a problem - when using bulk collect, only the last select statement is returned. The same issue was with open cursor. Is there any possibility to achieve this goal, another then concatenating all select statements using union all and executing it as one statement? And because I'm the begginer in sql, my second question is, is it ok to use this dynamic sql in terms of sql injection issues? Below is simplified version of my code:

CREATE OR REPLACE FUNCTION my_function RETURN newly_created_table_type IS
   ret_tab_type newly_created_table_type;
BEGIN
     for r in (select * from source_tab)
     loop
        execute immediate 'select value1, value2,''' || r.tab_name || ''' from ' || r.tab_name bulk collect into ret_tab_type; 
     end loop;
   return ret_tab_type;
END;

I'm using Oracle 11.

Maybe take a look at pipelined functions. – OldProgrammer Jan 08 '18 at 01:49 — OldProgrammer, Jan 08 '18 at 01:49

score 0 · Answer 1 · answered Jan 08 '18 at 07:33

May be you can combine all the queries into one using UNION ALL before execution, if the number and type of columns to be retrieved from all the tables are identical.

CREATE OR REPLACE FUNCTION my_function
   RETURN newly_created_table_type
IS
   ret_tab_type   newly_created_table_type;
   v_query        VARCHAR2 (4000);
BEGIN
   SELECT LISTAGG (' select VALUE1,VALUE2 FROM ' || tab_name, ' UNION ALL ')
             WITHIN GROUP (ORDER BY tab_name)
     INTO v_query
     FROM source_tab;

   EXECUTE IMMEDIATE v_query BULK COLLECT INTO ret_tab_type;

   RETURN ret_tab_type;
END;

You could then use a single select statement to get all the values.

select * FROM TABLE ( my_function );

Thanks Kaushik for your response. However I was trying to avoid this option, because it doesn't seem to be good to concatenate thousands of select statements — adolzi, Jan 08 '18 at 08:40

score 0 · Accepted Answer · answered Jan 08 '18 at 07:45

In your case you are trying to populate a collection dynamically and wanted result in a single collection. In your case its not possible to do that in a single loop. Also as mentioned by @OldProgrammer, piperow would be a better solution from performance point. See below demo:

--Tables and Values:

CREATE TABLE SOURCE_TAB(TAB_NAME VARCHAR2(100),   DESCRIPTION  VARCHAR2(100));
/

SELECT * FROM SOURCE_TAB;
/

INSERT INTO SOURCE_TAB VALUES('table1','some_desc1');
INSERT INTO SOURCE_TAB VALUES('table2','some_desc2');
/

CREATE TABLE TABLE1(COL1 NUMBER, COL2 NUMBER);
/

INSERT INTO TABLE1 VALUES(1,2);
INSERT INTO TABLE1 VALUES(3,4);
INSERT INTO TABLE1 VALUES(5,6);
/

Select * from TABLE1;
/

CREATE TABLE TABLE2(COL1 NUMBER, COL2 NUMBER);
/

INSERT INTO TABLE2 VALUES(7,8);
INSERT INTO TABLE2 VALUES(9,10);
INSERT INTO TABLE2 VALUES(11,12);
/

Select * from TABLE2;
/

--Object Created

--UDT 
CREATE OR REPLACE TYPE NEWLY_CREATED_TABLE_TYPE IS OBJECT (
     VALUE1                        NUMBER,
     VALUE2                        NUMBER
);
/

--Type of UDT
CREATE OR  TYPE NEWLY_CRTD_TYP AS TABLE OF NEWLY_CREATED_TABLE_TYPE;
/

--Function:

--Function
CREATE OR REPLACE FUNCTION MY_FUNCTION
     RETURN NEWLY_CRTD_TYP PIPELINED
AS
     CURSOR CUR_TAB
     IS
          SELECT *
            FROM SOURCE_TAB;

     RET_TAB_TYPE                  NEWLY_CRTD_TYP;
BEGIN
     FOR I IN CUR_TAB
     LOOP
          --Here i made sure that all the tables have col1 & col2 columns since you are using dynamic sql. 
          EXECUTE IMMEDIATE    'select  NEWLY_CREATED_TABLE_TYPE(COL1, COL2)  from '|| I.TAB_NAME
          BULK COLLECT INTO RET_TAB_TYPE;

          EXIT WHEN CUR_TAB%NOTFOUND;

          FOR REC IN 1 .. RET_TAB_TYPE.COUNT
          LOOP
               PIPE ROW (RET_TAB_TYPE (REC) );
          END LOOP;
     END LOOP;

     RETURN;
END;
/

Output:

    SQL>  Select *  from table(MY_FUNCTION);

    VALUE1     VALUE2
---------- ----------
         1          2
         3          4
         5          6
         7          8
         9         10
        11         12

6 rows selected.

I was thinking about it, but I wasn't sure whether it is a good practise, is it? — adolzi, Jan 08 '18 at 08:16
@adolzi If its serving your requirement, you should go with it. I saw your question about sql injection but SQL inject coming when you trying to bind some variable. In your case you are not binding anything so there is no threat for SQL injection, Also piperowing the result brings in additional benefit which you can explore. — XING, Jan 08 '18 at 08:18
@adolzi Comment sections are for clarification. Please read this https://stackoverflow.com/help/someone-answers to understand you should do incase solution provided helped you. — XING, Jan 08 '18 at 10:00

How to return result of many select statements as one custom table

2 Answers2

Linked