CODEWITHSUNDEEP
oracle-databaseplsql
Arvind Toorpu
Arvind Toorpu

Reputation: 21

how to execute .sql file on server directory using plsql

I have a TEST.sql file that has below lines in it.

$ cat TEST.sql

create table AB (name varchar2(20));

insert into AB values ('AAA');
insert into AB values ('BBB');
commit;

I know I can execute this script by loging into DB and doing 

sql> @TEST.sql

But I want to know ho to execute the script using a procedure/plsql. Provided that oracle has read access to that directory.

Upvotes: 0

Views: 429

Answers (2)

Renato Afonso
Renato Afonso

Reputation: 654

@jeff6times7 showed you a way. Another way would be to use the UTL_FILE package and read the contents of the file, and then perform an execute immediate with those contents.

Upvotes: 0

Jeff Holt
Jeff Holt

Reputation: 3190

If you must do it that way, then create a dbms_scheduler job whose job_type is 'SQL_SCRIPT'. Your watchdog dbms_scheduler job can create child jobs whose job_type is 'SQL_SCRIPT'.

You can also create a dbms_scheduler job whose job_type is 'EXTERNAL_SCRIPT' and have it scour the directory for new files and launch sqlplus commands for each new script that it finds.

Upvotes: 1

Related Questions