how to execute .sql file on server directory using plsql

Question

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.

Jeff Holt · Accepted Answer · 2017-04-27 17:37:55Z

1

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.

answered Apr 27, 2017 at 17:37

Jeff Holt

3,2623 gold badges27 silver badges33 bronze badges

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

1 Comment

Arvind Toorpu Over a year ago

Thanks jeff. This helps.

Renato Afonso · Accepted Answer · 2017-04-28 15:10:14Z

0

@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.

answered Apr 28, 2017 at 15:10

Renato Afonso

6546 silver badges13 bronze badges

1 Comment

Arvind Toorpu Over a year ago

yes, may be I have to read each line from file and then execute it.

Collectives™ on Stack Overflow

how to execute .sql file on server directory using plsql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related