10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> spool off
LIST
This
will give the current SQL
statement.
Syntax:
List or li [start_line_number]
[end_line_number]
Ex:
SQL> select
2 *
3 from
4 dept;
SQL> list
1 select
2 *
3 from
4* dept
SQL> list 1
1* select
SQL> list 3
3* from
SQL> list 1 3
1
select
2
*
3* from
INPUT
This
will insert the new line to the current SQL statement.
Syntax:
Input or in <string>
Ex:
SQL> select *
SQL> list
1* select *
SQL> input from dept
SQL> list
1 select *
2* from dept
APPEND
This
will adds a new string to the existing string in the SQL statement without any space.
Syntax:
Append or app <string>
Ex:
SQL> select *
SQL> list
1* select *
SQL> append from dept
1* select * from
dept
SQL> list
1* select * from
dept
DELETE
This
will delete the current SQL
statement lines.
Syntax:
Delete or del <start_line_number>
[<end_line_number>]
Ex:
SQL> select
2 *
3 from
4 dept
5 where
6 deptno
7 >10;
SQL> list
1 select
2 *
3 from
4 dept
5 where
6 deptno
7* >10
SQL> del 1
SQL> list
1 *
2 from
3 dept
4 where
5 deptno
6* >10
SQL> del 2
SQL> list
1 *
2 dept
3 where
4 deptno
5* >10
SQL> del 2 4
SQL> list
1
*
2* >10
SQL> del
SQL> list
1
*
VARIABLE
This
will be used to declare a variable.
Syntax:
Variable or var <variable_name>
<variable_type>
Ex:
SQL> var dept_name varchar(15)
SQL> select dname into dept_name from
dept where deptno = 10;
PRINT
This
will be used to print the output of the variables that will be declared at SQL level.
Syntax:
Print <variable_name>
Ex:
SQL> print dept_name
DEPT_NAME
--------------
ACCOUNTING
START
This
will be used to execute SQL
scripts.
Syntax:
start <filename_name>.sql
Ex:
SQL> start ss.sql
SQL> @ss.sql -- this will execute sql script files
only.
HOST
This
will be used to interact with the OS
level from SQL.
Syntax:
Host [operation]
Ex:
SQL> host
SQL> host dir
SHOW
Using
this, you can see several commands that use the set command and status.
Syntax:
Show all | <set_command>
Ex:
SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next
SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
SQL> sho verify
verify OFF
RUN
This
will runs the command in the buffer.
Syntax:
Run | /
Ex:
SQL> run
SQL> /
STORE
This
will save all the set command statuses in a file.
Syntax:
Store set <filename>.[extension] [create] | [replace]
| [append]
Ex:
SQL> store set my_settings.scmd
Created file my_settings.scmd
SQL> store set my_settings.cmd replace
Wrote file my_settings.cmd
SQL> store set my_settings.cmd append
Appended file to my_settings.cmd
FOLD_AFTER
This
will fold the columns one after the other.
Syntax:
Column <column_name> fold_after [no_of_lines]
Ex:
SQL> col deptno fold_after 1
SQL> col dname fold_after 1
SQL> col loc fold_after 1
SQL> set heading off
SQL> select * from dept;
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
FOLD_BEFORE
This
will fold the columns one before the other.
Syntax:
Column <column_name> fold_before [no_of_lines]
DEFINE
This
will give the list of all the variables currently defined.
Syntax:
Define [variable_name]
Ex:
SQL> define
DEFINE _DATE
= "16-MAY-07" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oracle" (CHAR)
DEFINE _USER
= "SCOTT" (CHAR)
DEFINE _PRIVILEGE
= "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)
DEFINE _EDITOR
= "Notepad" (CHAR)
DEFINE _O_VERSION
= "Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 – Production With the Partitioning, OLAP and
Data Mining options" (CHAR)
DEFINE _O_RELEASE
= "1001000200" (CHAR)
SET COMMANDS
These
commands does not require statement terminator and applicable to the sessions ,
those will be automatically cleared when session was closed.
LINESIZE
This
will be used to set the linesize. Default linesize is 80.
Syntax:
Set linesize <value>
Ex:
SQL> set linesize 100
PAGESIZE
This
will be used to set the pagesize. Default pagesize is 14.
Syntax:
Set pagesize <value>
Ex:
SQL> set pagesize 30
DESCRIBE
This
will be used to see the object’s structure.
Syntax:
Describe or desc <object_name>
Ex:
SQL> desc dept
Name
Null? Type
-----------------------------------------------------------------
---------------------
DEPTNO
NOT NULL NUMBER(2)
DNAME
VARCHAR2(14)
LOC
VARCHAR2(13)
PAUSE
When the displayed data contains hundreds or thousands of
lines, when you select it then it will automatically scrolls and displays the
last page data. To prevent this you can use this pause option. By using this it
will display the data correspoinding to the pagesize with a break which will
continue by hitting the return key. By default this will be off.
Syntax:
Set pause on | off
Ex:
SQL> set pause on
FEEDBACK
This will give the information regarding howmany rows you
selected the object. By default the feedback message will be displayed, only
when the object contains more than 5 rows.
Syntax:
Set feedback <value>
Ex:
SQL> set feedback 4
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
4 rows selected.
HEADING
If
you want to display data without headings, then you can achieve with this. By
default heading is on.
Syntax:
Set heading on | off
Ex:
SQL> set heading off
SQL> select * from dept;
10
ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
SERVEROUTPUT
This
will be used to display the output of the PL/SQL programs. By default this will
be off.
Syntax:
Set serveroutput on | off
Ex:
SQL> set serveroutput on
TIME
This
will be used to display the time. By default this will be off.
Syntax:
Set time on | off
Ex:
SQL> set time on
19:56:33 SQL>
TIMING
This
will give the time taken to execute the current SQL statement. By default this will be
off.
Syntax:
Set timing on | off
Ex:
SQL> set timing on
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
Elapsed: 00:00:00.06
SQLPROMPT
This
will be used to change the SQL
prompt.
Syntax:
Set sqlprompt <prompt>
Ex:
SQL> set sqlprompt 'ORACLE>'
ORACLE>
SQLCASE
This
will be used to change the case of the SQL statements. By default the case is mixed.
Syntax:
Set sqlcase upper | mixed | lower
Ex:
SQL> set sqlcase upper
SQLTERMINATOR
This
will be used to change the terminator of the SQL statements. By default the terminator
is ;.
Syntax:
Set sqlterminator <termination_character>
Ex:
SQL> set sqlterminator :
SQL> select * from dept:
DEFINE
By default if the & character finds then it will treat
as bind variable and ask for the input. Suppose your want to treat it as a
normal character while inserting data, then you can prevent this by using the
define option. By default this will be on
Syntax:
Set define on | off
Ex:
SQL>insert into dept
values(50,'R&D','HYD');
Enter value for d:
old 1: insert into
dept values(50,'R&D','HYD')
new 1: INSERT INTO
DEPT VALUES(50,'R','HYD')
SQL> set define off
SQL>insert into dept
values(50,'R&D','HYD'); -- here it won’t ask for value
NEWPAGE
This
will shows how many blank lines will be left before the report. By default it
will leave one blank line.
Syntax:
Set newpage <value>
Ex:
SQL> set newpage 10
The zero value for newpage does not produce zero blank
lines instead it switches to a special property which produces a top-of-form
character (hex 13) just before the date on each page. Most modern printers
respond to this by moving immediately to the top of the next page, where the
priting of the report will begin.
HEADSEP
This
allow you to indicate where you want to break a page title or a column heading
that runs longer than one line. The default heading separator is vertical bar
(|).
Syntax:
Set headsep <separation_char>
Ex:
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
SQL> set headsetp !
SQL> col dname heading 'DEPARTMENT ! NAME'
SQL> /
DEPARTMENT
DEPTNO NAME LOC
---------- ----------------- ----------
10 ACCOUNTING
NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON
ECHO
When
using a bind variable, the SQL
statement is maintained by echo. By default this is off.
Syntax:
Set echo on | off
VERIFY
When
using a bind variable, the old and new statements will be maintained by verify.
By default this is on.
Syntax:
Set verify on | off
Ex:
SQL> select * from dept where deptno =
&dno;
Enter value for dno: 10
old 1: select *
from dept where deptno = &dno
new 1: select *
from dept where deptno = 10
DEPTNO DNAME LOC
----------
---------------- -----------
10 ACCOUNTING NEW YORK
SQL> set verify off
SQL> select * from dept where deptno = &dno;
Enter value for dno: 20
DEPTNO DNAME
LOC
----------
------------- -----------
20 RESEARCH DALLAS
PNO
This
will give displays the page numbers. By default the value would be zero.
Ex:
SQL> col hiredate new_value xtoday noprint format a1 trunc
SQL> ttitle left xtoday right 'page'
sql.pno
SQL> select * from emp where deptno = 10;
09-JUN-81
page 1
EMPNO ENAME
JOB MGR SAL
COMM DEPTNO
---------- ---------- --------------- --------- -----
---------- ----------
7782 CLARK
MANAGER 7839 2450 10
7839 KING
PRESIDENT 5000 10
7934 MILLER
CLERK 7782 1300 10
In the above noprint tells SQLPLUS not to display this column when it
prints the results of the SQL
statement. Dates that have been reformatted by TO_CHAR get a default width of about 100
characters. By changing the format to a1 trunc, you minimize this effect.
NEW_VALUE inserts contents of the column retrieved by the SQL statement into a
variable called xtoday.
No comments:
Post a Comment