Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Call plsql script within another plsql script

I have a directory full of PLSQL scripts that I want to run, the problem is that the content of that directory is dynamic, and I have no way to know what the names of those scripts will be.

I have to write some stuff to run all of the sql files in that directory, but I can't find a way in PLSQL of call a script which file name is unknown until runtime.

I tried some stuff like load the .sql file content into a VARCHAR2 and then do

EXECUTE IMMEDIATE l_Script_Content;

But for some reason this just doesn't work, I guess there has to be a easier way to do that, like suddenly @ command accepting varchar2 instead a full path.

Can anyone point me in the right direction? Maybe running the scripts from java?

Thanks!

like image 386
im8bit Avatar asked Dec 07 '22 00:12

im8bit


1 Answers

PL/SQL is not the right tool for the job. The easiest way would be to use some kind of shell scripting (BASH for example) to build an SQL file and run that. like this:


bash> EXPORT IFS="
"
bash> for FILE in `ls -1 *.pls``;  # single backtick here -_-
  do echo "start "$FILE >> run.sql;
done;

sqlplus> start run.sql

like image 78
milan Avatar answered Dec 14 '22 23:12

milan