Assuming that you know the filename of a SQL or PL/SQL script, the easiest way to run it is to use the SQL*Plus "at-sign" (@) command.[3] For example, to create the prototype error-handling package discussed in Chapter 6, you can run it from SQL*Plus by entering:
[3] START, @, and @@ commands are available in the non-browser versions of SQL*Plus. In iSQL*Plus, you can use the "Browse" and "Load Script" buttons for a similar result.
SQL> @errpkg.pkgOr, if you prefer words to at-signs, you can use the equivalent START command:
SQL> START errpkg.pkgand you will get identical results. Either way, this command causes SQL*Plus to do the following:
1. Open the file named errpkg.pkg.
2. Attempt to execute all of the SQL, PL/SQL, and SQL*Plus statements in the file, one by one.
3. When complete, close the file and return you to the SQL*Plus prompt (unless the file invokes the EXIT statement, which will cause SQL*Plus to quit).
For example:
SQL> @errpkg.pkg Package created. Package body created. SQL>As you can see, the results of running the script appear on the screen.
In my example, I've used a filename extension of pkg. By default, though, SQL*Plus assumes a file extension of sql. So if I were using the defaults and left off the.pkg, as below, SQL*Plus would have searched for a file named errpkg.sql and given me an error:
SQL> @errpkg SP2-0310: unable to open file "errpkg.sql"SP2-0310 is the Oracle-supplied error number, and "SP2" means that it is unique to SQL*Plus. (For more details about SQL*Plus error messages, refer to Oracle's SQL*Plus User's Guide and Reference).
As you might expect, if your script file is in another directory, you can precede the filename with the path:[4]
[4] As a pleasant surprise, you can use forward slashes as directory delimiters on both Unix and Microsoft operating systems, at least as of Oracle8i. This allows your scripts to port more easily between operating systems.
SQL> @/files/src/release/1.0/errpkg.pkgThe idea of running scripts in other directories raises an interesting question. What if errpkg.pkg is located in this other directory and, in turn, calls other scripts? It might contain the lines:
REM Filename: errpkg.pkg@errpkg.pks@errpkg.pkb(Any line beginning with REM is acomment or "remark" that SQL*Plus ignores.) Executing the errpkg.pkg script is supposed to run errpkg.pks and errpkg.pkb. But because I have not included path information, where will SQL*Plus look for these other files? The answer: it looks only in the current directory (see the upcoming sidebar). And that's probably not where they are.
To address this problem, Oracle created a variation on the @ command: the @@ command. This double at-sign means "look for the file relative to the directory of the currently executing file." So, the preferred way of writing the calls in the errpkg.pkg script would be:
REM Filename: errpkg.pkg@@errpkg.pks@@errpkg.pkb2.1.5 Other SQL*Plus Tasks
There are dozens of commands specific to SQL*Plus, but I only have space to mention a few more that are particularly important or particularly confusing. For a thorough treatment of this venerable product, you might want to get a copy of Jonathan Gennick's book Oracle SQL*Plus: The Definitive Guide (O'Reilly) or, for quick reference, his Oracle SQL*Plus Pocket Reference.
