Call plsql script within another plsql script

Question

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!

Answer 1

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

Answer 2

Save this install script in the same folder eg-"current_folder_location/" where all your plsql file exits- in .sql format.
You can trigger this in unnixbox, sqlplus, sql Developer:

set define off;
spool install_scripts_logs.log;

Prompt plsql_file_1.sql
@current_folder_location/plsql_file_1.sql
show errors;

Prompt plsql_file_2.sql
@current_folder_location/plsql_file_2.sql
show errors;

Prompt plsql_file_1.sql
@current_folder_location/plsql_file_2.sql
show errors;

spool off;