Condition in SQL script

Question

I've got an SQL-script executed by SQL*Plus, that needs to run with Oracle 10g and Oracle 11g.

That script gives grants on a package that does not exist before 11g:

GRANT EXECUTE ON sys.dbms_result_cache TO my_user;

I would like to avoid the exception on 10g, since I want to react to other exceptions in the script.

---

One way is to use Conditional Compilation and dbms_db_version:

BEGIN
  $IF dbms_db_version.ver_le_10 $THEN NULL; $ELSE
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON sys.dbms_result_cache TO my_user';
  $END
END;
/

Is there any other way, preferable without using PL/SQL?

Answer 1

Hi Peter,

you could check if the object exists beforehand:

BEGIN
   FOR cc IN (SELECT NULL
                FROM all_objects
               WHERE owner = 'SYS'
                 AND object_name = 'DBMS_RESULT_CACHE'
                 AND ROWNUM = 1) LOOP
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON sys.dbms_result_cache TO my_user';
   END LOOP;
END;

Answer 2

Your question and one of the comments indicate that you want to avoid PL/SQL blocks and EXECUTE IMMEDIATE. I also assume that by "react to other exceptions" you mean abort execution of the script when an exception is encountered.

If so, I think the best you can do in pure SQL/SQL*Plus is to ignore the exception exit for the grant statement:

... first part of script (with exit on sqlerror in effect)
WHENEVER SQLERROR CONTINUE
GRANT EXECUTE ON sys.dbms_result_cache TO my_user;
WHENEVER SQLERROR EXIT SQL.SQLCODE
... remaining part of script

Answer 3

You can simulate branching by writing SQL that generates SQL and spools it to a sql script. Then run the sql script:

define temp_file='somefile.sql'

set heading off
set feedback off
spool &&temp_file

SELECT 'GRANT EXECUTE ON sys.dbms_result_cache TO my_user;'
  FROM all_objects
 WHERE owner = 'SYS'
   AND object_name = 'DBMS_RESULT_CACHE';

spool off
@&&temp_file
host rm &&temp_file

Thanks to @Vincent for the data dictionary query.