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 ?
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.
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.
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
You could write a Java Stored Procedure to open the file and return its contents as a String and then call Execute Immediate on the String.
Be VERY CAREFUL doing this though as any malicious sql in those files can do pretty much whatever it wants.
Even if there should be a solution, I would not recommend to to this. A PL/SQL procedure basically is a SQL script. Either 1. run your SQL scripts from outside the database, e.g. via shell script or 2. move the SQL code inside your procedure.
