从PL/SQL块运行SCRIPT [英] Run SCRIPT from PL/SQL Block

问题描述

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

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

我想使用这样的东西

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

我也想知道，我可以从提示符1中获取一个值到调用脚本的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).

说了这么多，在现实世界中，我们使用的是杂乱的架构，数据库和外部OS之间具有相互依赖性.那我们该怎么办?

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_SCHEDULER替换DBMS_JOB.该工具的一项增强功能是它可以运行外部作业(即Shell脚本)的功能. 找到更多.
3. 由于Oracle 11g R1外部表支持预处理器脚本，因此该脚本会在查询表之前运行shell命令. 了解更多信息.

1. We can write a Java Stored Procedure to execute shell commands. This is the venerable solution, having been around since Oracle 8i. Find out more.
2. In 10g Oracle replace DBMS_JOB with DBMS_SCHEDULER. Once of the enhancements of this tool is its ability to run external jobs i.e. shell scripts. Find out more.
3. Since Oracle 11g R1 external tables support pre-processor scripts, which run shell commands before querying the table. Find out more.

请注意，所有这些选项均要求提升访问权限(对DIRECTORY对象，安全凭据等的授予).这些只能由特权用户(即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

这篇关于从PL/SQL块运行SCRIPT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！