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

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? — Cee McSharpface
– Cee McSharpface, Commented Feb 8, 2017 at 8:57

Aleksej · Accepted Answer · 2017-02-08 08:57:18Z

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;
/

Collectives™ on Stack Overflow

Dynamic SQL - ORACLE

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related