Dynamic SQL - ORACLE

Question

I have the following procedure, which does not compile correctly, because it refers to non existing objects (table does not exist) Here is only a section of the code (i used generic names for tables and columns):

 DECLARE
 C INTEGER := 0;
 BEGIN
 SELECT COUNT(1) INTO C FROM USER_TABLES WHERE TABLE_NAME = 'MY_TABLE';
 IF C > 0 THEN
 DECLARE
 CURSOR c_maps IS SELECT COLUM_NAME1, COLUM_NAME2 FROM MY_TABLE WHERE ACTIVE = 1;
 BEGIN
 FOR prec IN c_maps LOOP
 some code...;
 END LOOP;
 EXECUTE IMMEDIATE 'some code..';
 END;
 END IF;
END;
/

I don't know how to write this statement dynamically, since the table "MY_TABLE" does not exist:

 CURSOR c_maps IS SELECT COLUM_NAME1, COLUM_NAME2 FROM MY_TABLE WHERE ACTIVE =1;

I also tried to write it like:

CURSOR c_maps IS SELECT COLUM_NAME1, COLUM_NAME2 FROM (Select 'MY_TABLE' from dual) WHERE ACTIVE = 1;

However, than it refers to the column "ACTIVE" which also does not exist at compile time...It is possible to write the whole procedure inside "execute immediate" - block? I have tried different variants, however without success

• Cee McSharpface: the cursor that builds the column list would have to be part of the dynamic execution. two layers of dynamic execution nested... a nightmare to understand and to debug. is there really no way to code against an existing schema?

Answer 1

You may need to open the cursor in a different way, so that the non existing table is only referred in dynamic SQL; for example:

declare
 c integer := 0;
 curs sys_refcursor;
 v1 number;
 v2 number;
begin
 select count(1)
 into c
 from user_tables
 where table_name = 'MY_TABLE';
 if c > 0
 then
 open curs for 'select column_name1, column_name2 from my_table where active = 1';
 loop
 fetch curs into v1, v2;
 exit when curs%NOTFOUND;
 dbms_output.put_line(v1 || ' - ' || v2);
 end loop;
 else
 dbms_output.put_line('The table does not exist');
 end if;
end;
/