Oracle Dynamic SQL Multiple Row Return

Question

Don't have much experience with Oracle, and am not even sure this can be done. I am trying to create dynamic SQL and execute it, the SQL in question resulting in multiple rows returned with 3 columns in each row. I am able to create the SQL statement just fine, its the executing part that I'm stuck on. I'm using an example from here, but am getting this error:

Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 41
00942. 00000 -  "table or view does not exist"

From this code:

DECLARE thecount number;
 thesql varchar2(8000);
 suffix VARCHAR2(20);
 finalsql varchar2(8000);
 TYPE results IS TABLE OF EDW_HPM.DYNAMIC_TEMP%ROWTYPE;
 results_tbl results;

  CURSOR c1 is
       select cast(cast(substr(p.TABLE_SUFFIX,2,20) as Number(19))as varchar2(50))  
         from support.data_set_phys p
        where p.name not like '%UPD' and p.name like 'FY%' and p.name like '%Encounters'
        and p.is_active = 1 and p.owner_id in ('001441631324','000001666805')
        and NAME not like '%TEST%' and NAME not like 'old%' and NAME not like '%new%' and
        NAME not like '%NEW%' and name not like '%BACK%' and name not like '%back%'
        and cast(replace(substr(p.name, 1, 4),'FY', '20')as int) between 2018 and
        2018 union
                  select cast(cast(substr(p.TABLE_SUFFIX,2,20) as Number(19))as varchar2(50))
         from support.data_set_phys p
        where p.name not like '%UPD' and p.name like 'EPIC HB %' and p.name like '%Encounters'
        and p.is_active = 1 and p.owner_id in ('001441631324','000001666805')
        and NAME not like '%TEST%' and NAME not like 'old%' and NAME not like '%new%' and
        NAME not like '%NEW%' and name not like '%BACK%' and name not like '%back%'
        and cast(replace(substr(p.name, 9, 4),'FY', '20')as int) between 2018 and
      2018;

BEGIN

   OPEN c1;
   LOOP
      FETCH c1 INTO suffix;
      EXIT WHEN c1%NOTFOUND;
    thesql := thesql ||'SELECT facility_id,patient_type_id ,count(pat_account_no) FROM encounter_' ||suffix|| ' e group by facility_id, patient_type_id union ';
   END LOOP;
   CLOSE c1;

   finalsql :=SUBSTR(thesql,1,length(thesql)-7);

   EXECUTE IMMEDIATE finalsql BULK COLLECT INTO results_tbl; 


END allrows_by;

The error triggered by this line:

 EXECUTE IMMEDIATE finalsql BULK COLLECT INTO results_tbl;

I can ensure you that table EDW_HPM.DYNAMIC_TEMP does exist and this must be some kind of syntax error, or I'm attempting something that is just not possible in Oracle. Any help would be much appreciated.

Answer 1

Your code is considerably more complicated than the example you link to. Not only is the driving query extremely complex but your dynamic statement is a union with generated table names.

"this must be some kind of syntax error, or I'm attempting something that is just not possible in Oracle."

Dynamic SQL is hard because it turns compilation errors into runtime errors.

"I can ensure you that table EDW_HPM.DYNAMIC_TEMP does exist "

In this case it is probably the generated table name which is hurling ORA-00942.

This is simple to debug: instead of executing the statement display it.

dbms_output.put_line(finalsql);

Now you can verify the statement, by running it in a SQL client if you can't spot the bloomer immediately.

Possible causes

• the table names are malformed
• the generated SQL assembles names for tables which don't exist
• the generated names reference tables in different schemas and there are no synonyms
• the generated names reference tables in different schemas, there are synonyms but your user doesn't have rights on the tables.