如何从pl sql过程运行sql脚本 [英] How to run sql scripts from a pl sql procedure

问题描述

我有一个类似的过程:

CREATE OR REPLACE PROCEDURE test is 
BEGIN

  DBMS_OUTPUT.PUT_LINE('This is a Test');
END;

我想运行一些存储在当前目录中的sql脚本. 我可以使用'@ scriptname.sql'从sqlplus运行它们，但是如何从过程内部进行操作呢?例如:

I want to run some sql scripts stored in the current directory. I could run them from sqlplus with '@scriptname.sql' but how can i do it from inside the procedure ? For ex:

CREATE OR REPLACE PROCEDURE test is 
BEGIN

  DBMS_OUTPUT.PUT_LINE('This is a Test');
  @scriptname.sql

END;

这似乎不起作用！是否有从pl/sql过程运行sql脚本的特定命令?

This doesn't seem to work ! Is there a specific to run sql scripts from pl/sql procedures ?

推荐答案

通常不能，因为pl/sql在数据库中的服务器上运行，而sqlplus是客户端进程.服务器甚至不能依赖于与客户端及其文件位于同一系统上，更不用说有关如何查找客户端所指文件的知识了.即使支持语法(也不支持语法)，您的sql脚本也必须位于服务器上服务器所知道且有权访问的位置.

You can't, in general, because the pl/sql is run in the database, on the server, and sqlplus is a client process. The server can't rely on even being on the same system as the client and its files, much less knowing anything about how to find the file the client is referring to. Even if the syntax were supported (and it isn't), your sql script would have to be on the server, in a location the server knew about and had access to.

这篇关于如何从pl sql过程运行sql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！