Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed
Or perhaps just use scheduler to run them, job_type = SQL_SCRIPT
— Connor loves SQL , 🍸and ☕️ (@connor_mc_d) December 21, 2016
If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus
If you run
sqlplus -L -s -R 3 scott/tiger@db01
lot’s of OS-command are disabled
SQL> get /etc/passwd SP2-0738: Restricted command "get" not available SQL> spool /etc/oratab SP2-0738: Restricted command "spool" not available SQL> host reboot SP2-0738: Restricted command "host" not available
There is also a Product User Profile. I never saw any customer using this. I doubt it is very safe.
You could disable some commands
SQL> insert into system.PRODUCT_USER_PROFILE
(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE)
values
('SQL*Plus','SCOTT','ROLLBACK','DISABLED');
Which prevents SCOTT from doing rollabck
SQL> rollback;
SP2-0544: Command "rollback" disabled in Product User Profile
but it doesn’t resist Rock & Roll
SQL> roll & rock
Rollback complete.
If you have 12c, go have a look at the doc for SQL_SCRIPT jobs