SQL Extensions   «Prev 

Saving and Starting SQL*Plus a Script File

  1. You have created the query you see in the SQL*Plus window here. Now you want to save it in a file named PR_CUST.sql. To do this, type SAVE PR_CUST at the SQL> prompt and press Enter. SQL*Plus automatically adds the .SQL suffix to the file.
  2. SQL*Plus tells you that it has created the file. Next, go to the operating system to verify that the file was created. Type HOST and press Enter to open a new window where you can type operating system commands.
  3. SQL*Plus opens a window. In this example, you are now in an MS-DOS window, so you will type the MS-DOS directory listing command. Type dir PR* and press Enter to list all files that begin with PR.
  4. As you can see, the file PR_CUST.sql was created and is listed in the current directory. Close the MS-DOS window now by typing EXIT and then pressing Enter.
  5. You are now returned to the SQL*Plus window. Try executing the query that you saved in the file by typing START PR_CUST and pressing Enter. SQL*Plus assumes that the suffix will be .sql because you did not specify any other suffix.
  6. The file is read and executed by SQL*Plus. Notice that the query text is not displayed but the query results are displayed. This is the default behavior for SQL*Plus. This is the end of the simulation. Click Exit.

Saving Commands

As explained earlier in the module, the SQL buffer is overwritten with every new SQL command you enter in SQL*Plus. If you want to save the contents of the SQL buffer, you can use the SQL*Plus SAVE command. The SAVE command creates a script file containing the contents of the SQL buffer. 
PMT  Process modeling techniques DSG 1
RSD  Relational system design DSG 2
PRO  Prototyping DSG 5
GEN  System generation DSG 4
10 rows selected.

SQL> save result1
SP2-0540: File "result1.sql" already exists.
Use "SAVE filename[.ext] REPLACE".

SQL>	save result1 replace
Created file result1.sql

SQL>

Note the error message after the second SAVE RESULT1 attempt; REPLACE (or APPEND) is mandatory if a file already exists. Since the SQL buffer is overwritten with each new SQL command issued, using the SAVE APPEND syntax when creating a script file can be useful if you would like any new SQL commands you issue to be added to your script file. We have created two script files.
These script files are assigned the extension .SQL by default.

If you prefer to use a different file name extension, you can change it with the SQL*Plus SUFFIX setting.