How to load an external script file

Question

Let's say I've got a large script and want to cut it into pieces and then load the pieces from a main script file.

The question is how to load and execute an external script using plain SQL in Oracle DBMS or PL/SQL from another script file?

Answer 1

For SQL*plus, you can use:

@filename.sql

or

@@filename.sql

Please realize that @ is a SQL*plus command - not a SQL or PL/SQL command. SO you cannot use this from inside a PL/SQL stored procedure - it would not make much sense either, as essential context like current working direcory is absent in that case.

Form inside a stored procedure, you could in principle load external code using dynamic sql, but a better way to break up a stored procedure is to break it into several smaller stored procedures. If you like you can group those togehter in a package (see http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/packages.htm#CIHIJECJ)