How to execute all pl/sql files in a directory (install_db script)

Question

I'm trying to implement what is said in this paper: http://www.rwijk.nl/AboutOracle/psdua.pdf

(in a few words, I've sql files creating database objects such as tables, priviledges, sequences, indexes etc. in directories 'myApp/tables/', 'myApp/priviledges/', 'myApp/sequences/'... and I want to execute these files to install my database).

There is an example of what should look like the install_db.sql script p11-12 but the execution of the sql files is hard coded:

@@&tables_path.SCA_OPNAMES.sql 
@@&tables_path.SCA_METERSTANDEN.sql
@@&sequences_path.SCA_MSD_SEQ1.sql
@@&sequences_path.SCA_ONE_SEQ1.sql
@@&indexes_path.MSD_ONE_FK1_I.sql
@@&privs_path.privileges.sql &SCHEMAPREFIX

I would prefer to execute all the files in a given directory but it seems to be tricky.

There is a nice post to get a list of files in a directory: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584 which is working well.

So at this point, I've a list of files in a temporary table and now I want to execute these files.

Suprisingly I'm stuck with this step. I would like to do something like this:

for tab in (select filename from dir_list) loop
    @tab.filename;
end loop;

but it's not working since I'm not in a pl/sql procedure. Can I execute files from a pl/sql procedure? The answer is not very clear to me and I read opposite answers to this question.

Thank you for your help,

No, you can't mix an SQLPlus @ with a PL/SQL block. IMO you should be maintaining a master script of the files to run; for a start you have to run some things in order, like creating tables with primary keys before those that reference them, and that would be hard to do dynamically. If you could work around that with a file naming convention, say, and really didn't want to maintain the master script, then you could build the master script from a shell script that then submits it to SQLPlus. I don't think you can do it from within SQL*Plus. — Alex Poole
– Alex Poole, Commented Jun 13, 2014 at 16:58
If you are already using java to build the list, can't you use java to build and execute a command line to run? I've never tried it from java within oracle, but I know it works with "normal" java. — Bob
– Bob, Commented Jun 14, 2014 at 2:13
Oracle doesn't have tools to run database install/update scripts. Either you write your own or use a ready made solution like Flyway. — user272735
– user272735, Commented Jun 14, 2014 at 8:00
After a few months, the solution is clear to me: use the right tool for the right job! The aim of SQL and PLSQL is not to loop through files in a directory... if you are stuck doing simple things with a tool, maybe it's not the good one.I chose to use ANT to build my installation script and I think it is much more appropriate. — Nicolas Girault
– Nicolas Girault, Commented Sep 23, 2014 at 14:45

user1847974 · Accepted Answer · 2014-06-26 09:30:33Z

0

You could try using dynamic PL/SQL.
For example, write a PL/SQL procedure that would - read the contents of the file into a variable and then - execute the PL/SQL using the EXECUTE IMMEDIATE dynamic SQL construct.

answered Jun 26, 2014 at 9:30

user1847974

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute all pl/sql files in a directory (install_db script)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related