SQL Plus: A Command Line DOS-like Interface Which Can Provide Users An Environment To Execute

SQL*Plus: A command line DOS-like interface which can provide users an environment to execute
SQL statements and PL/SQL statements to Oracle database server. Users can work under
SQL*PLUS environment to:
 create database objects -- tables, views, functions, procedures, ..etc

 query and manipulate data
 format database outputs
SQL*Plus commands can assist to:
 Create, edit, save, and execute SQL and PL/SQL statements

 Format columns for better report display purpose
Table-1 lists a group of SQL*Plus commands and their features:
Command name Description Abbre
/ Execute the current SQL statement in the buffer - same as RUN
ACCEPT Accept a value from the user and place it into a variable ACC
APPEND Add text to the end of the current line of the SQL statement in the buffer APP
CHANGE Replace text on the current line of the SQL statement with new text C
CLEAR Clear the buffer or clear screen CL
COLUMN Change the appearance of an output column from a query
CONNECT Connect to Oracle database or to the same database under a different user CONN
COPY Copy data from one table to another in the same or different databases
DEL Delete the current line in the buffer
DESCRIBE List the columns with data types of a table DESC
EDIT Edit the current SQL statement in the buffer using an external editor ED
EXIT Exit the SQL*Plus program
GET Load a SQL statement into the buffer but do not execute it
HELP Obtain help for a SQL*Plus command (In some installations)
Description
Command
name Abbre
INPUT Add one or more lines to the SQL statement in the buffer I
LIST List the current SQL statement in the buffer L
QUIT Exit the SQL*Plus program
REMARK Place a comment following the REMARK keyword REM
RUN Execute the current SQL statement in the buffer R
SAVE Save the current SQL statement to a script file SAV
SET Set a variable to a new value
SHOW Show the current value of a variable
SPOOL Send the output from a SQL statement to a file SP
START Load a SQL statement located in a script file and run that SQL statement STA
TIMING Used to time the execution of SQL statements for performance analysis TIMI
UNDEFINE Delete a user defined variable
 SQL*Plus is the primary interface to the Oracle Database server, which provides a powerful yet
easy-to-use environment for user to query, define, and control data.
 You can logon SQL*PLUS from DOS environment or from SQL*PLUS Dialog box
[From DOS Environment]
Click on "Start" -> "Run" and enter "cmd"

C:> sqlplus scott/lion2@waketech
SQL> show user
SQL> connect peter/chen@waketech
SQL> show user
SQL> select * from tab;
SQL> disconnect
SQL> exit
Or
[SQL*PLUS Dialog box]

Click on "Start" -> "all programs" -> double click "Oracle 10h home" -
> "Application Development" -> "SQL Plus"
SQL> show user
SQL> connect peter/chen@waketech
SQL> show user
SQL> select * from tab;
SQL> disconnect
SQL> exit
SQL*PLUS Command:
SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command
buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have
to be terminated with a semicolon (;) (as is the case with SQL commands).
Examples:
SQL> PROMPT << Database is being processing>>
--- Display a text string on the screen
SQL> SPOOL c:\24-aug-2009.txt
--- Output a series of statement execution history to a file
Following examples are the most common SQL*PLUS commands:
SQL> SHOW USER

SQL> SELECT * FROM emp;
SQL> SELECT * FROM dept;
SQL> SELECT * FROM salgrade;
SQL> SET LINESIZE 150
SQL> SET PAGESIZE 100
SQL> SHOW LINESIZE
SQL> SHOW PAGESIZE
SQL> SELECT * FROM emp;
SQL> LIST
Or
SQL> L
SQL> SELECT * FROM dept;
SQL> SELECT * FROM salgrade;
SQL> ED
or
SQL> EDIT
SQL> SAVE C:\test.sql
SQL> START C:\test.sql
Or
SQL> @ C:\test.sql
Or
SQL> GET C:\test.sql
SQL> /
SQL> GET C:\test.sql
SQL> ED
Or
SQL> EDIT C:\test.sql
SQL> LIST
SQL> LIST LAST
SQL> APPEND WHERE deptno = 10
SQL> LIST
SQL> 1
SQL> CHANGE/*/ename, deptno
SQL> LIST
SQL> 1
SQL> C/ename, deptno/empno, sal, comm
SQL> LIST
SQL> LIST LAST
SQL> INPUT AND sal > 1000
SQL> LIST
SQL> DEL 3
SQL> LIST
SQL> CLEAR BUFF
SQL> LIST
SQL> SET SQLPROMPT “PETER>”
PETER> SET TIME ON
19:15:00 PETER> SET TIME OFF
PETER> SET SQLPROMPT “SQL>”

SQL> SELECT ename, sal, deptno
FROM emp;
SQL> CREATE TABLE test(
empno NUMBER,
sal NUMBER);
SQL> INSERT INTO test VALUES(1000, 5000.333);
SQL> INSERT INTO test VALUES(1000, .333);
SQL> INSERT INTO test VALUES(1000, 500.333);
SQL> SELECT *
FROM test;
SQL> COLUMN sal FPORMAT $999,999.99
SQL> /
Or
SQL> RUN
SQL> COLUMN sal FPORMAT $999,990.99
SQL> RUN
SQL> COLUMN sal FPORMAT $USD 999,990.99
SQL> RUN
SQL> CLEAR SCREEN

SQL Plus: A Command Line DOS-like Interface Which Can Provide Users An Environment To Execute

Uploaded by

Copyright:

Available Formats

SQL Plus: A Command Line DOS-like Interface Which Can Provide Users An Environment To Execute

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Plus: A Command Line DOS-like Interface Which Can Provide Users An Environment To Execute

Uploaded by

Copyright:

Available Formats

SQL*Plus: A command line DOS-like interface which can provide users an environment to execute

 create database objects -- tables, views, functions, procedures, ..etc

SQL*Plus commands can assist to:

 Create, edit, save, and execute SQL and PL/SQL statements

Table-1 lists a group of SQL*Plus commands and their features:

Command name Description Abbre

/ Execute the current SQL statement in the buffer - same as RUN

CLEAR Clear the buffer or clear screen CL

COLUMN Change the appearance of an output column from a query

DEL Delete the current line in the buffer

DESCRIBE List the columns with data types of a table DESC

EXIT Exit the SQL*Plus program

HELP Obtain help for a SQL*Plus command (In some installations)

LIST List the current SQL statement in the buffer L

QUIT Exit the SQL*Plus program

REMARK Place a comment following the REMARK keyword REM

RUN Execute the current SQL statement in the buffer R

SAVE Save the current SQL statement to a script file SAV

SET Set a variable to a new value

SHOW Show the current value of a variable

SPOOL Send the output from a SQL statement to a file SP

UNDEFINE Delete a user defined variable

[From DOS Environment]

Click on "Start" -> "Run" and enter "cmd"

[SQL*PLUS Dialog box]

SQL> PROMPT << Database is being processing>>

--- Display a text string on the screen

SQL> SPOOL c:\24-aug-2009.txt

--- Output a series of statement execution history to a file

Following examples are the most common SQL*PLUS commands:

SQL> SHOW USER

SQL> START C:\test.sql

SQL> GET C:\test.sql

SQL> GET C:\test.sql

SQL> EDIT C:\test.sql

SQL> LIST LAST

SQL> APPEND WHERE deptno = 10

SQL> CHANGE/*/ename, deptno

SQL> C/ename, deptno/empno, sal, comm

SQL> LIST LAST

SQL> INPUT AND sal > 1000

SQL> CLEAR BUFF

SQL> SET SQLPROMPT “PETER>”

PETER> SET TIME ON

19:15:00 PETER> SET TIME OFF

PETER> SET SQLPROMPT “SQL>”

SQL> CREATE TABLE test(

SQL> INSERT INTO test VALUES(1000, 5000.333);

SQL> INSERT INTO test VALUES(1000, .333);

SQL> INSERT INTO test VALUES(1000, 500.333);

SQL> COLUMN sal FPORMAT $999,999.99

SQL> COLUMN sal FPORMAT $999,990.99

SQL> COLUMN sal FPORMAT $USD 999,990.99

SQL> CLEAR SCREEN

You might also like