SQL 脚本中的条件

发布于 2024-08-25 20:14:48 字数 733 浏览 7 评论 0原文

我有一个由 SQL*Plus 执行的 SQL 脚本，需要与 Oracle 10g 和 Oracle 11g 一起运行。

该脚本为 11g 之前不存在的包提供授权：

GRANT EXECUTE ON sys.dbms_result_cache TO my_user;

我想避免 10g 上的异常，因为我想对脚本中的其他异常做出反应。

一种方法是使用条件编译 和 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;
/

是否还有其他方法，最好不使用 PL/SQL？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放血 2024-09-01 20:14:48

您的问题和评论之一表明您希望避免 PL/SQL 块并立即执行。我还假设“对其他异常做出反应”是指在遇到异常时中止脚本的执行。

如果是这样，我认为在纯 SQL/SQL*Plus 中你能做的最好的事情就是忽略 grant 语句的异常退出：

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

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

回复收藏 0 原文

○闲身 2024-09-01 20:14:48

您可以事先检查该对象是否存在：

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;

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;

回复收藏 0 原文

夕色琉璃 2024-09-01 20:14:48

您可以通过编写生成 SQL 并将其假脱机到 SQL 脚本的 SQL 来模拟分支。然后运行sql脚本：

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

感谢@Vincent的数据字典查询。

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.

回复收藏 0 原文

~没有更多了~