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
SQL PROMPT
This will be used to change the SQL
prompt.
Syntax:
Set sqlprompt
<prompt>
Ex:
SQL> set sqlprompt 'ORACLE>'
ORACLE>
SQL CASE
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
SQL TERMINATOR
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 no print tells SQL PLUS 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 x today.
No comments:
Post a Comment