Oracle replaced DBMS_LOCK.SLEEP() with DBMS_SESSION.SLEEP() from Oracle 18.

Some of our customers are still on Oracle 12 whilst others have moved on to Oracle 19.

Those who have moved on to Oracle 19 are looking to replace DBMS_LOCK with DBMS_SESSION for all sorts of reasons.

So we need our PL/SQL package to be able to choose the correct call based on Oracle version.

Is this doable? I assume it will have to incorporate some sort of EXECUTE IMMEDIATE. Will it work on these DBMS calls? Will it have any unexpected permission or security issues?

CodePudding user response：

You could use conditional compilation, either based on the major database version:

create or replace procedure sleep_test as
begin
  $IF DBMS_DB_VERSION.VERSION < 18 $THEN
  dbms_output.put_line('Version ' || DBMS_DB_VERSION.VERSION || ' - using dbms_lock');
  dbms_lock.sleep(1);
  $ELSE
  dbms_output.put_line('Version ' || DBMS_DB_VERSION.VERSION || ' - using dbms_session');
  dbms_session.sleep(1);
  $END
end;
/

begin
  dbms_output.enable;
  sleep_test;
end;
/

1 rows affected

dbms_output:
Version 18 - using dbms_session

Or using a compiler flag you set yourself, which might be more flexible if you want to manage privileges separately (though as @Jeff says there isn't much benefit in sticking with dbms_lock in later versions):

create or replace procedure sleep_test as
begin
  $IF $$use_dbms_lock $THEN
  dbms_output.put_line('Using dbms_lock');
  dbms_lock.sleep(1);
  $ELSIF $$use_dbms_session $THEN
  dbms_output.put_line('Using dbms_session');
  dbms_session.sleep(1);
  $ELSE
  null;
  $END
end;
/

When first compiled both of the preprocessor flags will be unset (unless you set them in your session first), so the complied code will just have the dummy null; statement:

begin
  dbms_output.enable;
  dbms_preprocessor.print_post_processed_source (
    object_type => 'PROCEDURE',
    schema_name => user,
    object_name => 'SLEEP_TEST');
end;
/

1 rows affected

dbms_output:
procedure sleep_test as
begin
  






  null;
  
end;

and calling the procedure won't do anything:

begin
  dbms_output.enable;
  sleep_test;
end;
/

1 rows affected

But then you can recompile it with whichever flag you want to turn on:

alter procedure sleep_test compile PLSQL_CCFLAGS='use_dbms_session:true' reuse settings;

begin
  dbms_output.enable;
  sleep_test;
end;
/

1 rows affected

dbms_output:
Using dbms_session

fiddle

db<>fiddle doesn't have dbms_lock available and the 11g instance doesn't seem happy at the moment, so that can't currently demonstrate the alternative paths. But note that the conditional compilation means that it doesn't complain about that not being there - unless I try to compile with the flag that tries to use it:

alter procedure sleep_test compile PLSQL_CCFLAGS='use_dbms_lock:true' reuse settings;

ORA-24344: success with compilation error

select * from user_errors