How to run sql scripts from a pl sql procedure

Question

I have a procedure like :

CREATE OR REPLACE PROCEDURE test is 
BEGIN

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

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;

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

Answer 1

Actually, you can do this in SQL*Plus - you just need to ensure the @ is the first character on the line, e.g.:

CREATE OR REPLACE PROCEDURE test is 
BEGIN

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

END;

SQL*Plus will read the entire contents of the script and insert it at that point in the procedure, then create the procedure as it is given. That means you can't have SQL*Plus commands in scriptname.sql. Also, there won't be any reference to @scriptname.sql in the actual procedure created on the database.

Answer 2

You could execute an OS command to start SQLPlus and have that execute the scripts. You can pass a filename into SQLplus at start up and it will execute it.

Google External Procedures and extproc or this article. Or something like call OS command with Java

Answer 3

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.