SQL Plus is an Oracle Product Used to Interface with Database Server:

In the Exercise 3.5 on page 36, you were able to login Oracle database through SQL Plus. SQL Plus is an interface designed specifically by Oracle to provide database administrators to interact with the database server. Once you are in SQL Plus, you will use both SQL Plus commands and SQL commands to work with the database. We will discuss more details about SQL in the following sections.

 

Useful Tips for SQL Plus:

1. Copy: You may use your mouse to highlight the SQL command you have input. Use ctrl+c to copy the highlighted content into your buffer.

You may use the Copy function in Edit menu to copy as well.

2. Paste: You may paste your copied content to where you like, generally at SQL> prompt, to save repetitive typing. You may paste the contact by pressing ctrl+v.

If you practice and use the above copy and paste functions, you will save yourself a lot of time for retyping the same thing again and again.

3. Once you typed several lines in SQL, you may find some typos or mistakes in the command. Of course, you do not want to retype everything over again. You may edit the lines. There are many ways to edit your SQL commands. The easiest way for a new Oracle user is to invoke your text editor as follows:

Once you have typed in any SQL command, you can invoke your text editor by issuing "ed" command:

SQL>Ed

This command will invoke your default text editor (Notepad in Windows, and vi in UNIX). You may edit the SQL commands as you debug the code.

After you complete the changes or editing, you may SAVE the changes and EXIT from the text editor. You will be back to the SQL> prompt after exit from text editor.

Then, you may use "/" to execute what you have in SQL buffer.

SQL>/

If you do not want SQL in the buffer to be executed, (for example, if you see error in the input, ) you may use:

SQL>.

(Type a period and return.)

If you like to see what you have in the SQL buffer, you may use list command:

SQL>L

 

SQL Script:

You may save your SQL commands into a script file and retrieve/use it later. The script will significantly improve your efficiency and consistency. It is a necessary tool for database administration.

If you like to save your commands in SQL Plus buffer and reuse it later, you may issue the following command:

SQL>SAVE d:\tec5323\ exmaple.sql

You may save your file in different directory other than d:\tec5323 directory. However, you will need to create the directory before you use it. The default file extension is sql if you do not specify it. It is a good idea to specify the SQL file extension.

If the file already exists and you want to replace the file, you may use:

SQL>SAVE d:\tec5323\ exmaple.sql REPLACE

If you like to retrieve your saved SQL script file into your SQL Plus buffer, use the following command:

SQL>GET d:\tec5323\example1.sql

After the file is retrieved into the SQL Plus buffer, you may edit, list or execute it as described on tips above.

Another way to use the script is to use the following command:

SQL>@d:\tec5323\example1.sql

In this way, you will be able to execute all the actions with one command and run them consistently over time.

 

How to Catch Output of SQL Plus on Screen onto a File?

On many occasions, you will need to copy the output of SQL Plus to a file. For example, you will need to show the instructor what your output and script are for your project assignment in this chapter. To catch the screen output from SQL Plus, you may use SPOOL command, as follows:

SQL> SPOOL d:\tec5323\sample2.txt

After you execute the above command under SQL Plus, your input commands and their output on SQL Plus screen will be recorded in the file d:\tec5323\sample.txt. You may save your file in different directory other than d:\tec5323 directory. However, you will need to create the directory before you use it.

You may use text editor to view and edit your spooled file as you wish.

If you like to stop spooling, you may issue the following command:

SQL> SPOOL OFF

This command terminates spooling for your SQL Plus session.

A great teacher never strives to explain his vision. He simply invites you to stand beside him and see for yourself.

--R. Inman

 

Teacher: "Tommy, where was the Declaration of Independence signed?"

Tommy: "At the bottom, I guess."