PL / SQL is compiled conditionally on the existence of a database object

Question

Is it possible to have conditional compilation in Oracle, where the condition is the existence of a database object (in particular, a table or view or synonym)? I would like to do something like this:

sp_some_procedure is
    $IF /*check if A exists.*/ then 
        /* read from and write to A as well as other A-related non-DML stuff...*/
    $ELSE /*A doesn't exist yet, so avoid compiler errors*/
        dbms_output.put_line('Reminder: ask DBA to create A!')
    $ENDIF
end;

Answer 1

- ... , , , ... , ""... DBA , ... , - , ( , ...)... () ( ALTER PROCEDURE MySchema.MyProcName COMPILE;) ...

, , DROP / CREATE OR REPLACE... dbms_output.put_line('Reminder: ask DBA to create A!') .

- EXECUTE IMMEDIATE EXCEPTION...

Answer 2

, . , XALL_TABLES, , . , XALL_TABLES, . , ALL_TABLES. ALL_TABLES, all_tables, DUAL.

, , ... (, , , ).

--conditionals compilation instructions accept only static condition (just with constants)
--passing sql bind variable doesn't work 
--To pass a value to a conditional compilation instruction, I bypasses the use of input parameters of the script
--these 4 next lines affect a value to the first and the second input parameter of the script
--If your originally script use input script parameter, use the next free parameter ...
column param_1 new_value 1 noprint
select nvl(max(1), 0) param_1 from all_views where owner = 'SYS' and view_name = 'XALL_TABLES';
column param_2 new_value 2 noprint
select nvl(max(1), 0) param_2 from all_views where owner = 'SYS' and view_name = 'ALL_TABLES';

CREATE or replace PACKAGE my_pkg AS
  function test_xall_tables return varchar2;
  function test_all_tables return varchar2;
END my_pkg;
/

CREATE or replace PACKAGE BODY my_pkg AS

  function test_xall_tables return varchar2 is
    vch varchar2(50);
  begin
    $IF (&1 = 0) $THEN
      select 'VIEW XALL_TABLES D''ONT EXISTS' into vch from dual;
    $ELSE
      select max('VIEW XALL_TABLES EXISTS') into vch from XALL_TABLES;
    $END        
    return vch;      
  end test_xall_tables;

  function test_all_tables return varchar2 is
    vch varchar2(50);
  begin
    $IF (&2 = 0) $THEN
      select 'VIEW ALL_TABLES D''ONT EXISTS' into vch from dual;
    $ELSE
      select max('VIEW ALL_TABLES EXISTS') into vch from ALL_TABLES;
    $END
    return vch;
  end test_all_tables;             
END my_pkg;
/

:

select my_pkg.test_xall_tables from dual;

XALL_TABLES D'ONT EXISTS

select my_pkg.test_all_tables from dual;

_

Answer 3

"EXECUTE IMMEDIATE" EXCEPTION.

Answer 4

SQL , .

--E.g., say there are two possible tables, but only one of them exists.
--create table table1(a number);
create table table2(a number);


--Create a package with boolean constants to track the objects.
--(Another way to do this is to use ALTER SESSION SET PLSQL_CCFLAGS)
declare
  table1_exists_string varchar2(10) := 'true';
  table2_exists_string varchar2(10) := 'true';
  temp number;
begin
  begin
    execute immediate 'select max(1) from table1 where rownum <= 1' into temp;
  exception when others then
    table1_exists_string := 'false';
  end;

  begin
    execute immediate 'select max(1) from table2 where rownum <= 1' into temp;
  exception when others then
    table2_exists_string := 'false';
  end;

  execute immediate '
    create or replace package objects is
      table1_exists constant boolean := '||table1_exists_string||';
      table2_exists constant boolean := '||table2_exists_string||';
    end;
  ';
end;
/

--Look at the results in the source:
select * from user_source where name = 'OBJECTS';


--Create the object that refers to the tables.
create or replace function compile_test return varchar2 is
    v_test number;
begin
    $if objects.table1_exists $then
        select max(1) into v_test from table1;
        return 'table1 exists';
    $elsif objects.table2_exists $then
        select max(1) into v_test from table2;
        return 'table 2 exists';
    $else
    return 'neither table exists';
    $end
end;
/

--Check the dependencies - only TABLE2 is dependent.
select * from user_dependencies where name = 'COMPILE_TEST';

--Returns 'table 2 exists'.
select compile_test from dual;

SQL, PL/SQL . SQL .

; , , , .

(Also, if all of this means simply replacing scary error messages with friendly warnings, in my opinion this is a very bad idea. If your system fails, the error should be obvious, so that it can be fixed right away. Most people ignore everything that starts with "Reminder ...".)

Answer 5

What I would do is check existence through all_objects, something like:

declare

l_check_sql varchar2(4000);
l_cnt number;

begin

l_check_sql := q'{
select count(1)
from all_objects
where object_name = 'MY_OBJ'
and owner = 'MY_OWNER'
}';

execute immediate l_check_sql into l_cnt;

if (l_cnt > 0) then
  -- do something referring to MY_OBJ
else
  -- don't refer to MY_OBJ
end if;

end;