从 PL/SQL 块运行 SCRIPT

2021-12-24 00:00:00 prompt oracle plsql

如何在pl/sql块中使用START SCRIPT"?

How to use "START SCRIPT" in pl/sql block ?

我想用这样的东西

declare
begin
   proc(para1,para2);
   execute immediate 'start prompt1' ; 
end;
/

我还想知道，我可以从 prompt1 获取一个值到我调用脚本的 PL/SQL 块中吗?因为我需要使用该值在PL/SQL块中执行一些操作.

Also I want to know , can i get a value from prompt1 into my PL/SQL block where am calling the script ? Because I need to use the value to perform some operations in the PL/SQL block.

推荐答案

现在是 2012 2017 年.脚本是上个千年的笨拙而脆弱的残留物.Oracle 具有我们可以在 PL/SQL 中执行的大量功能，此外还有 Java 存储过程和用于启动作业的调度.除了运行 DDL 来创建或修改模式之外，在 Oracle 数据库环境中几乎不需要脚本；甚至 DDL 脚本也应该从外部客户端触发，可能是诸如 TeamCity 之类的构建工具.

It is 2012 2017. Scripts are a clunky and brittle hangover from the last millennium. Oracle has a fantastic range of functionality we can execute in PL/SQL, plus there's Java Stored Procedures, and there's scheduling for starting jobs. Other than running DDL to create or amend schemas there is hardly any need for scripts in an Oracle database environment; even DDL scripts should be triggered from an external client, probably a build tool such as TeamCity.

特别是，我认为尝试从 PL/SQL 程序运行 SQL 脚本是架构失败.你用存储过程不能做的脚本做什么?

In particular I would regard attempting to run a SQL script from a PL/SQL program as an architectural failure. What are you doing with the script which you cannot do with a stored procedure?

至于将输入传递给存储过程，这就是参数的用途.PL/SQL 不是交互式的，我们需要一个客户端来输入值.根据场景，这可以异步(文件或表中的值)或同步(从 SQL*Plus、SQL Developer 或定制前端调用存储过程)完成.

As for passing input to a stored procedure, that's what parameters are for. PL/SQL isn't interactive, we need a client to enter the values. Depending on the scenario this can be done asynchronously (values in a file or a table) or synchronously (calling the stored procedure from SQL*Plus, SQL Developer or a bespoke front end).

话虽如此，在现实世界中，我们使用杂乱的架构，数据库和外部操作系统之间存在相互依赖关系.那我们能做什么?

Having said all that, in the real world we work with messy architectures with inter-dependencies between the database and the external OS. So what can we do?

  1. 我们可以编写一个 Java 存储过程来执行 shell 命令.这是一个古老的解决方案，自 Oracle 8i 以来一直存在.了解更多.
  2. 在 10g 中，Oracle 将 DBMS_JOB 替换为 DBMS_SCHEDULER.该工具的一个增强功能是它能够运行外部作业，即 shell 脚本.查找更多.
  3. 由于 Oracle 11g R1 外部表支持预处理器脚本，该脚本在查询表之前运行 shell 命令.了解更多.

请注意，所有这些选项都需要提升访问权限(授予目录对象、安全凭据等).这些只能由特权用户(即 DBA)授予.除非我们的数据库有一个惊人的宽松的安全配置，否则我们无法从 PL/SQL 运行任意的 shell 脚本.

Note that all these options demand elevated access (grants on DIRECTORY objects, security credentials, etc). These can only be granted by privileged users (i.e. DBAs). Unless our database has an astonishingly lax security configuration there is no way for us to run an arbitrary shell script from PL/SQL.

最后，不清楚在 PL/SQL 中运行 SQL 脚本有什么好处.请记住，PL/SQL 在数据库服务器上运行，因此它无法在客户端机器上看到脚本.鉴于接受用户输入的要求，这似乎很重要.

Finally, it is not clear what benefit you expect from running a SQL script in PL/SQL. Remember that PL/SQL runs on the database server, so it can't see scripts on the client machine. This seems relevant in the light of the requirement to accept user input.

也许最简单的解决方案是重新配置原始脚本.将必要的 PL/SQL 调用拆分成一个块，然后只调用命名脚本:

Perhaps the simplest solution is reconfiguration of the original script. Split out the necessary PL/SQL call into a block and then just call the named script:

begin
   proc(para1,para2);
end;
/   
@prompt1.sql

相关文章