Condition in SQL script

Question

I have an SQL script executed by SQL * Plus that should run with Oracle 10g and Oracle 11g.

What the script gives grants for a package that does not exist before 11g:

GRANT EXECUTE ON sys.dbms_result_cache TO my_user; 

I would like to avoid the 10g exception, as I want to respond 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 preferred without using PL / SQL?

Answer 1

Your question and one of the comments indicate that you want to avoid the PL / SQL and EXECUTE IMMEDIATE blocks. I also assume that by “responding to other exceptions,” you mean interrupting script execution when an exception occurs.

If so, I think the best you can do in pure SQL / SQL * Plus is to ignore the exception output 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 2

you can check if the object exists in advance:

 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 3

You can model the branch by creating SQL that generates SQL and associates it with 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 querying the data dictionary.