2.7 Scripts


You can always save the contents of the SQL*Plus buffer to a file for reuse ”just type the SAVE command and a filename. If the file already exists, SQL*Plus tells you. You can do a "SAVE filename REPLACE," which will replace the contents of an existing file. You also have the option to do a "SAVE filename APPEND," which adds your SQL*Plus buffer statement to the end of the filename specified.

Where does the saved file go? In UNIX the save file goes into the directory from which you launched SQL*Plus. In Windows, it goes to the Oracle "bin" directory, but, if you set up a shortcut and changed the start-in directory, as recommended in Section 2.1, the file is saved to a dedicated directory. The following illustrates how we save a select statement into a script file.

 
  SQL>  SELECT * FROM students;  STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS   ---------- ------- -------------------- ------   A101       John    Biology              Degree   A102       Mary    Math/Science         Degree   SQL>  save students_select  Created file students_select  SQL> 

We can also save a script using a full pathname.

 
  SQL>  save D:\sqlplus_scripts\students\students_select  Created file D:\sqlplus_scripts\students\students_select   SQL>  

The file extension of a SQL*Plus saved file is always ".SQL."

The file text will be identical to what you type during your session ”it is the exact text from the SQL*Plus buffer. The semicolon is dropped and a forward slash is added as a last and final line in the saved file. To illustrate , type a select statement, but do not type a semicolon. Then type forward slash to execute the SQL*Plus buffer.

 
  SQL>  SELECT * FROM students  2  /  STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS   ---------- ------- -------------------- ------   A101       John       Biology              Degree   A102       Mary       Math/Science         Degree   SQL>  

The aforementioned is identical to typing "SELECT * FROM students" and adding a semicolon. Either way, the actual text in a script file, from a SAVE command, will be two lines: the select statement minus the semicolon and a second line with a forward slash.

 
 SELECT * FROM students   ".SQL" file line 1   /   ".SQL" file line 2   

The three options with the SQL*Plus SAVE command are:

  • SAVE filename

  • SAVE filename REPLACE

  • SAVE filename APPEND

You execute a SQL script from SQL*Plus with the syntax: @filename. For example:

 
  SQL>  @students_select  STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS   ---------- ------- -------------------- ------   A101       John    Biology              Degree   A102       Mary    Math/Science         Degree   SQL>  

During development, SQL*Plus scripts are used to drop and recreate all the tables of an application. Also for development, scripts are used to load test data. Scripts can be embedded within UNIX Korn Shell scripts to perform daily Oracle backups . They are used for ad hoc troubleshooting, (i.e., to show existing locks on database objects, to show performance of memory usage, and many other database metrics).

You can create a SQL script from SQL*Plus with a SAVE command, but scripts of length are usually created with a host editor. The contents of a SQL script can include one or more SQL statements, plus SQL*Plus commands. You can comment within a SQL*Plus script with a double dash. The following is a SQL*Plus script that contains two SQL SELECT statements plus two comment lines ”the file is five lines.

 
  --  Filename: SELECT_STUDENTS.SQL  --  Script to select STUDENTS data. SELECT student_name, college_major FROM students; SELECT student_id, student_name, college_major FROM students WHERE status = 'Degree'; 

The aforementioned script would execute from SQL*Plus with:

 
  SQL>  @select_students 

SQL*Plus scripts can contain SQL*Plus commands. This includes SQL*Plus formatting commands. A common SQL*Plus command often embedded in a SQL*Plus script is the COLUMN command. The SQL*Plus COLUMN command is used to restrict the number of characters , for a single column, used in the SQL output as well as designate a specific heading.

 
  --  Filename: SELECT_STUDENTS.SQL  --  Script to select STUDENTS data. COLUMN college_major FORMAT A12 HEADING 'CollegeMajor' COLUMN student_name  FORMAT A7  HEADING 'StudentName' COLUMN student_id    FORMAT A7  HEADING 'StudentID' SELECT student_name, college_major FROM students; SELECT student_id, student_name, college_major FROM STUDENTS WHERE status = 'Degree';  SQL>  @select_students  Student Student College   ID      Name    Major   ------- ------- ------------   A101    John    Biology   A102    Mary    Math/Science  

A SQL statement always ends with a semicolon. A SQL*Plus command does not need to end with a semicolon. The next section illustrates other SQL*Plus commands: HEADING, PAGESIZE, TERM, and FEEDBACK.



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net