CODEWITHSUNDEEP
sqloracle-databaseplsql
Aniket
Aniket

Reputation: 163

How to run sql scripts from a pl sql procedure

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 ?

Upvotes: 9

Views: 31085

Answers (5)

DCookie
DCookie

Reputation: 43533

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.

Upvotes: 11

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

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.

Upvotes: 8

Wolfram
Wolfram

Reputation: 11

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.

Upvotes: 1

Karl
Karl

Reputation: 3372

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

Upvotes: 4

Bryan
Bryan

Reputation: 963

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.

Upvotes: 1

Related Questions