Problem solve Get help with specific problems with your technologies, process and projects.

Executing external .sql files from stored procedures

How can I execute an external .sql file (eg, tab.sql) from a Oracle stored procedure? My tab.sql file contains CREATE objects, grants, etc scripts.

As far as I know, there is no way to call a .sql file from within a PL/SQL procedure. What you might want to do is to write the commands included in your .sql file in a stored procedure and then call that procedure. Since PL/SQL can't directly support DDL (such as CREATE and GRANT statements), you would have to use the dynamic SQL capability (NDS - Native Dynamic SQL). If you're using 8i, you're in luck, as the process has been much simplified over previous versions which used the DBMS_SQL package. In 8i, all the commands you want to run would simply be enclosed in an EXECUTE IMMEDIATE statement. For example: EXECUTE IMMEDIATE 'GRANT SELECT ON EMP TO scott' ;

By creating your current tab.sql script as a procedure, you can call it from any other procedure you wish and you could still run it from SQL*Plus by executing the script (e.g. EXECUTE tab_sql_proc) instead of trying to execute it as a SQL*Plus script using @ or START.

Dig Deeper on Oracle and SQL