Returning table results from a dynamic PL/SQL query

Question

Trying to dynamically generate and execute a PL/SQL statement. It doesn't return results, but executing okay. What I am trying is to get the table name from the schema in first statement (got it right!), and append that to a select statement, and then execute it to return the table results.

DECLARE
  LATEST_TABLE VARCHAR2(256);
  PostalCode  ADM.POSTAL_CODE_201801%ROWTYPE;
BEGIN
SELECT TO_CHAR(max(table_name)) INTO LATEST_TABLE FROM all_tables WHERE owner = 'ADM' AND  table_name LIKE 'POSTAL_CODE_%';
LATEST_TABLE := 'begin Select POSTALCODE,LONGITUDE,LATITUDE,MUNICIPALITY_FULL_NAME,LOCAL_NAME,SZONE_NAME,ZONE_NAME,RHA_CODE,RHA_NAME,URBAN,ZONE_RURAL from ADM.'||LATEST_TABLE||' ;end;';
execute immediate LATEST_TABLE into PostalCode;
Exception
When others then
 Null; 
END;

Why am I not getting any results? Adding dbms_output.put_line(PostalCode.LONGITUDE || PostalCode.LATITUDE); after execute immediate is also not generating results!

Possible duplicate of [Using bind variables with dynamic SELECT INTO clause in PL/SQL](https://stackoverflow.com/questions/7816402/using-bind-variables-with-dynamic-select-into-clause-in-pl-sql) — OldProgrammer, Jul 21 '18 at 02:27

Aleksej · Answer 1 · 2018-07-21T06:08:15.573

I see a couple of issues here; your code is something like:

declare
    vSQL        varchar2(1000);
    vTabName    varchar2(30);
    vResult     number;
begin
    select table_name into vTabName from user_tables;
    vSQL := 'begin select a from ' || vTabName || '; end;';
    execute immediate vSQL into vResult;
    dbms_output.put_line('REsult: ' || vResult);
exception
when others then
  null
end;

If you run this, you see nothing, because the dynamic part gives error, but the (dangerous) exception handling hides it; if you would edit the null; into something like

dbms_output.put_line('Error: ' || sqlerrm);

you would get:

Error: ORA-06550: line 1, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement

In fact you dynamic code is like

begin select a from someTable; end;

and this gives error. A way to do what you need could be:

...
vSQL := 'select a from ' || vTabName;
execute immediate vSQL into vResult;
...

Returning table results from a dynamic PL/SQL query

1 Answers1