0

My query:

declare
v_sql varchar2(4000);

cursor c_ppqa_tables is
    select 
        job_id 
    from 
        jobs 
    where 
        INPUT_DOCUMENT_TYPE = 'test' 
        and to_char(PRE_PRINTING_END, 'MM/DD/YYYY') = '06/26/2023'; --to_char(sysdate, 'MM/DD/YYYY');
        
begin
    for REC in c_ppqa_tables LOOP
        v_sql := 'select * from PPQA_'||REC.job_id||'_O01_0 where REMOVAL_MARK = ''Y'' ';
        execute IMMEDIATE v_sql;
    end LOOP;
end;

When I run the query from c_ppqa_tables by itself, I can get good output, I receive back 3 records. And then when I plug in the job_id from that into my v_sql, and run it manually, I can pull back data, too.

Am I doing something wrong on how to display the results for v_sql?

etm124
  • 2,100
  • 4
  • 41
  • 77
  • You need to define a rowtype for the ppqa_% tables (assuming they all have the same structure) and bulk collect all rows into a collection. – Martin Schapendonk Jul 05 '23 at 13:30
  • 3
    `execute immediate` doesn't return anything without `into`. You need to create a collection type, fetch everything into it and then return that collection to the consumer (via bind variables or pipelined function or using dynamic cursor and [`DBMS_SQL.RETURN_RESULT`](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-87562BF3-682C-48A7-B0C1-61075F19382A)). Please describe what will consume this result. – astentx Jul 05 '23 at 14:10
  • 1
    This is a common question, esp for users coming from MS T-SQL. See [this similar question](https://stackoverflow.com/questions/43249410/output-the-result-of-a-select-executed-using-dynamic-pl-sql) and also [this one](https://stackoverflow.com/questions/57869570/how-to-print-select-statment-in-a-plsql-script) – kfinity Jul 05 '23 at 14:59

0 Answers0