SQL*Plus file commands
SQL*Plus edit commands
Run SQL statements in a batch
Output results to a file
DUAL and select the current time
Simple formatting query results
SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user. Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process.
After you login into SQL*Plus, at the SQL prompt, you can begin typing any SQL command. Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. When you are finished typing a command, type / or RUN to execute the SQL command. Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. In addition to SQL commands, /, and RUN, you can also executes SQL*Plus file commands.
SQL*Plus file command allow you to execute commands (or programs) stored in an external file, input or output data from/to a file, and save SQL commands typed during current session.
Some SQL*Plus file commands are:
Recall that the previously executed commands (in current SQL*Plus session) are stored in the local buffer. One way to change an SQL statement in the buffer is by using the line editor. The following are a list of line edit commands.
Besides line editor, you can also use the vi editor if you are a
fan of Unix editor!.
To invoke the vi editor, type Edit at the SQL Prompt. Multiple SQL commands can be typed in vi editor. End each SQL command (except the last one) with a semicolon. After exiting notepad, type Start to run all of the commands.
To run SQL commands in a batch, you can put all your SQL commands into a text file and execute these commands in this file in SQL*PLUS.
wanghao@sf3:~/cs6203$ more table.sql
DROP TABLE employee
CREATE TABLE employee (
empno INTEGER NOT NULL,
name VARCHAR2(50) NOT NULL,
sal REAL NOT NULL,
primary key (empno));
INSERT INTO employee VALUES (1, 'Jack', 6000);
INSERT INTO employee VALUES (2, 'Tom', 6000);
INSERT INTO employee VALUES (3, 'John', 6000);
INSERT INTO employee VALUES (4, 'Jane', 6000);
UPDATE employee SET sal=500 WHERE name='Jack'
CREATE INDEX test_index on employee(sal)
SQL> START table.sql;
SQL> SPOOL <your file name> ;
SQL> SPOOL myoutput.out ;
All SQL commands and their outputs after this command are written into the file myoutput.out that by default is stored in the current working directory where you invoked SQL*Plus.
The built-in function SYSDATE returns a DATE value containing the current date and time on your system. (Note Oracle is a client-server architecture and SQL*Plus is the client. SYSDATE gives you the time of the Unix system which you telnet in. It may NOT be the time of Oracle server unless you telnet into the machine running Oracle server.)
SQL> SELECT TO_CHAR(SYSDATE , 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Date/Time" FROM DUAL;;
Current Time --------------------------------------------------------------------------- Mon 15-July-2002 10:01:29
SQL> COLUMN salary FORMAT $999,999
SQL> COLUMN name FORMAT A8If a name is longer than 8 characters, the remaining is displayed at the second line (or several lines)
For example, to set the number of lines per page to 60, use the following command:
SQL> SET PAGESIZE 60
SQL> CLEAR COLUMN
SQL> HELP <the SQL command>;
You could also find out all commands by entering:
SQL> HELP menu;
SQL> SET SERVEROUTPUT ON
SQL> SET ARRAYSIZE 1