PL/SQL compile conditionally on existence of database object

Question

Is it possible to have conditional compilation in Oracle, where the condition is the existence of a database object (specifically, a table or view or synonym)? I'd like to be able 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

Yes it is. Here a sample where the first stored procedure wants to select from XALL_TABLES, but if this table doesn't exist, select from dual. Finally, because I haven't got an XALL_TABLES object, the first stored procedure selects from dual. The second one, does the same thing on the ALL_TABLES object. Because the ALL_TABLES exists, the second stored procedure selects from all_tables but not from DUAL.

This kind of construction is useful where the package have to be deployed on all your database and use tables that are not deployed everywhere ... (ok, perhaps there is a conceptual problem, but it happens).

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

the test :

select my_pkg.test_xall_tables from dual;

give

VIEW XALL_TABLES D'ONT EXISTS

select my_pkg.test_all_tables from dual;

give

VIEW ALL_TABLES EXISTS

Answer 2

I would use 'EXECUTE IMMEDIATE' and a EXCEPTION clause.

Answer 3

Use dynamic SQL to create package constants to track which objects exist, and then use those constants in conditional compilation.

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

Mixing dynamic SQL, dynamic PL/SQL, and conditional compilation is usually a very evil idea. But it will allow you to put all of your ugly dynamic SQL in one installation package, and maintain real dependency tracking.

This may work well in a semi-dynamic environment; for example a program that is installed with different sets of objects but does not frequently change between them.

(Also, if the whole point of this is just to replace scary error messages with friendly warnings, in my opinion that is a very bad idea. If your system is going to fail, the failure should be obvious so it can be immediately fixed. Most people ignore anything that starts with "Reminder...".)

Answer 4

No - that is not possible... but if you create a stored procedure referencing a non-existent DB object and try to compile it the compilation will show errors... the stored procedure will be there but "invalid"... and the compilation errors are accessible for the DBA whenever he looks at it... so I would just go ahead and create all needed stored procedures, if any compilation errors arise ask the DBA (sometimes the object exists but the stored procedure need permissions to access it...)... after the reason for the error(s) is fixed you can just recompile the stored procedure (via ALTER PROCEDURE MySchema.MyProcName COMPILE;) and all is fine...

IF you don't want code to be there you can just DROP the strored procedure and/or replace is via CREATE OR REPLACE... with dbms_output.put_line('Reminder: ask DBA to create A!') in the body.

The only other alternative is as kevin points out EXECUTE IMMEDIATE with proper EXCEPTION handling...