set the SQLPLUS Environment

SQL>SET SERVEROUTPUT ON

define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname


select lower(user) || '@' || substr( global_name, 1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

• DEFINE _EDITOR=VI: This sets up the default editor SQL*Plus will use. You may set the default editor to be your favorite text editor (not a word processor) such as Notepad or emacs.
• SET SERVEROUTPUT ON SIZE 1000000: This enables DBMS_OUTPUT to be on by default (hence you don’t have to type it in each and every time). It also sets the default buffer size as large as possible.
• SET TRIMSPOOL ON: When spooling text, lines will be blank-trimmed and not fixed width. If this is set to OFF (the default), spooled lines will be as wide as your LINESIZE setting.
• SET LONG 5000: This sets the default number of bytes displayed when selecting LONG and CLOB columns.
• SET LINESIZE 100: This sets the width of the lines displayed by SQL*Plus to be 100 characters.
• SET PAGESIZE 9999: This sets the PAGESIZE, which controls how frequently SQL*Plus prints out headings, to a large number (you get one set of headings per page).
• COLUMN PLAN_PLUS_EXP FORMAT A80: This sets the default width of the explain plan output you receive with AUTOTRACE. A80 is generally wide enough to hold the full plan.

define gname=idle

column global_name new_value gname

select lower(user) || '@' || substr( global_name,1,

decode( dot, 0, length(global_name), dot-1) ) global_name

from (select global_name, instr(global_name,'.') dot from global_name );

set sqlprompt '&gname> '

• The directive COLUMN GLOBAL_NAME NEW_VALUE GNAME tells SQL*Plus to take the last value it retrieves for any column named GLOBAL_NAME and place it into the substitution variable GNAME. I then select the GLOBAL_NAME out of the database and concatenate this with the username I am logged in with. That makes my prompt look like this:

ops$tkyte@ora10g>

so I know who I am as well as where I am.

Setting up SCOTT/TIGER Schema

Sample Table Structures for Practice:-


The SCOTT/TIGER schema may already exist in your database. It is generally included during a
typical installation, but it is not a mandatory component of the database.


In 9i

1. $ cd $ORACLE_HOME/sqlplus/demo
2. Run "demobld.sql" when connected as any user

In 10g on wards.

1. $ cd $ORACLE_HOME/rdbms/admin
2. $ cp utlsampl.sql  /prod/ravi10/admin/scripts/table.sql
3. connect to the any user and run "table.sql".

              SQL> @?/prod/ravi10/admin/scripts/table.sql

demobld.sql (or) utlsampl.sql will create and populate five tables.

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);


INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
xxvi ■SETTING UP YOUR ENVIRONMENT
5300FM.qxd 8/18/05 10:18 AM Page xxvi
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');


CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');