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:
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. |