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