Khaled Zaghloul

1
ORACLE SQL

 Type of relations :
1. One to One : Student -- School
2. One to Many : Department -- Employee
3. Many to One : Employee -- Department
4. Many to Many : Students -- Courses

 ORACLE :
1. PL/SQL
 PL (Procedural Language) Triggers & Blocks and it is a Set of
Commands are executed one statement as block (at once)
 SQL (Structure Query Language) : Set of Commands are executed one by
one
2. Developer : Forms, Reports, Graphics
3. Designer
4. DBA Data Base Administration
 Tuning DB
 Recovering DB

 Data Type:
1. Number : 1-38 digits
 Number takes the Max. digits
 Number(no)
 Number(no,decimal)
2. Char(no) : 1-255 character
3. Varchar2(no) : 1–2000
4. Date : Number or Julian, it takes 7 bytes
5. Raw : 2 KB saved in binary
6. Longrow : 4 GB for emages and sounds, saved in binary
7. Long : 2 GB for notes, only one Long in table is allowed

 Oracle Commands :
1. DDL (Data Define Language) : Create (for tables), Rename, Drop, Alter
2. DML (Data Manipulation Language) : Select, Alter, Delete, Update, Insert
3. DCL (Data Control Language) : Create, Drop for users

 Users of Oracle :
1. Sys/sys : owner of database
2. System/manager : Local for database (All privilege)
3. Scott/tiger : user of database (Certain Privilege)
4. Demo/demo : user of database

SQL> SPOOL path:\>filename

Used to save All SQL statements written during our work.

SQL> SPOOL OFF or EXIT

It will close our session and The file name will have the extension .LST

SQL> Edit it brings the last SQL statement written using

2
Notepad SQL> / it execute that statement

3
SQL> describ deptOR SQL> describ
dept; Name Null? Type

DEPTNO NOT NULL

NUMBER(2) DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> desc emp;

Name Null? Type

EMPNO NOT NULL

NUMBER(4) ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select * from dept;

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from dept;

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

We can save the last SQL statement written using the SQL+
command : SQL> Save c:\kaz it will be saved with
extension .SQL
Created file c:\kaz

To run the [Link] file

: SQL> @c:\kaz
OR
SQL> start kaz

4
 DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5 rows selected.

SQL> get [Link] it is good only if KAZ contains one

statment 1* select * from dept
SQL> /

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5 rows
selected.

SQL> get kaz

1* select * from dept

SQL> ed
Wrote file

[Link] 1*

select * from

dept

SQL> run kaz

1* select * from dept

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5 rows selected.

SQL> select EMPNO from

emp; EMPNO

5
7369
7499
7521
7566

6
SQL> select empno no1,ename name from emp;
$ used to change the default header (Aliases)

NO1 NAME

7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES

SQL> select empno, sal*12 annual from

emp; EMPNO ANNUAL

7369 9600
7499 19200
7521 15000
7566 35700

SQL> select empno,sal,comm,sal+comm from emp;

EMPNO SAL COMM SAL+COMM

7369 800
7499 1600 300 1900
7521 1250 500 1750
7566 2975

if any of sal or comm fields has a null value the result will be null so we use
NVL(fieldname,0) where 0 means: replace the null field with 0, as:
SQL> select empno,sal,comm,sal+nvl(comm,0) from emp;

EMPNO SAL COMM SAL+NVL(COMM,0)

7369 800 800

7499 1600 300 1900
7521 1250 500 1750
7566 2975 2975

SQL> select ename||' earns '||sal from

emp; ENAME||'EARNS'||SAL

SMITH earns 800

ALLEN earns 1600
WARD earns 1250
JONES earns 2975

7
SQL> select ename||' earns '||sal "Employee Information" from emp;

Employee Information

SMITH earns 800

ALLEN earns 1600
WARD earns 1250
JONES earns 2975

SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7782 CLARK MANAGER 7839 09-JUN-1981 2450 10

7839 KING PRESIDENT 17-NOV-1981 5000 10
7934 MILLER CLERK 7782 23-JAN-1982 1300 10

BETWEEN:
SQL> select * from emp where sal BETWEEN 1000 and
2000; The 1000 & 2000 values are included

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-1987 1100 20
7934 MILLER CLERK 7782 23-JAN-1982 1300 10

6 rows selected.

SQL> select * from emp where hiredate between '1-jan-1980' and '1-jan-1982';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-1980 800 20

7499 ALLEN SALESMAN 7698 20-FEB- 1600 300 30
1981
7521 WARD SALESMAN 7698 22-FEB- 1250 500 30
1981
7566 JONES MANAGER 7839 02-APR- 2975 20
1981
7654 SALESMAN 7698 28-SEP- 1250 1400 30
MARTIN 1981
7698 BLAKE MANAGER 7839 01-MAY- 2850 30
1981
7782 CLARK MANAGER 7839 09-JUN- 2450 10
1981
7839 KING PRESIDENT 17-NOV-1981 5000 10
7844 TURNER SALESMAN 7698 08-SEP- 1500 0 30
1981
7900 JAMES CLERK 7698 03-DEC-1981 950 30
7902 FORD ANALYST 7566 03-DEC-1981 3000 20

11 rows selected.

8
LIKE & NOT LIKE:
= : exactly the same
LIKE : some part of it using % part, _ for one

Character SQL> select * from emp where ename =

'SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

SQL> select * from emp where ename LIKE '%COTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

SQL> select * from emp where ename LIKE '%RD';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7521 WARD SALESMAN 7698 22-FEB- 1250 500 30

1981
7902 FORD ANALYST 7566 03-DEC-1981 3000 20

SQL> select * from emp where ename LIKE '_C%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

IN & NOT IN :
SQL> select ENAME,JOB FRom emp where JOB IN ('CLERK','MANAGER');

ENAME JOB

SMITH CLERK
JONES MANAGER
BLAKE
MANAGER CLARK
MANAGER
ADAMS CLERK
JAMES CLERK
MILLER CLERK

7 rows selected.

9
IS NULL & IS NOT NULL :
SQL> SELECT * FROM EMP WHERE COMM IS NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-1980 800 20

7566 JONES MANAGER 7839 02-APR-1981 2975 20
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 2450 1
0
7788 SCOTT ANALYST 7566 19-APR- 3000 20
1987
7839 KING PRESIDENT 17-NOV-1981 5000 10
7876 ADAMS CLERK 7788 23-MAY-1987 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 950 30
7902 FORD ANALYST 7566 03-DEC- 3000 20
1981
7934 MILLER CLERK 7782 23-JAN-1982 1300 10

10 rows
selected.

ORDER BY :
The defaul ordering is ASC but we can make it DESC
SQL> select * from emp order by sal;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC- 800 20

1980
7900 JAMES CLERK 7698 03-DEC- 950 30
1981
7876 ADAMS CLERK 7788 23-MAY- 1100 20
1987
7521 WARD SALESMAN 7698 22-FEB- 1250 500 30
1981
7654 MARTIN SALESMAN 7698 28-SEP- 1250 1400 30
1981
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7788 SCOTT ANALYST 7566 19-APR-1987 3000 20
7902 FORD ANALYST 7566 03-DEC-1981 3000 20
7839 KING PRESIDENT 17-NOV-1981 5000 10

14 rows
selected.

SQL> select * from emp order by sal desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-NOV-1981 5000 10

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20
7902 FORD ANALYST 7566 03-DEC-1981 3000 20
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30

1
0
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
7844 SALESMAN 7698 08-SEP-1981 1500 0 30
TURNER
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
7876 ADAMS CLERK 7788 23-MAY-1987 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 950 30
7369 SMITH CLERK 7902 17-DEC-1980 800 20

1
1
SQL> select * from emp order by sal,job;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-1980 800 20

7900 JAMES CLERK 7698 03-DEC-1981 950 30
7876 ADAMS CLERK 7788 23-MAY-1987 1100 20
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7788 SCOTT ANALYST 7566 19-APR-1987 3000 20
7902 FORD ANALYST 7566 03-DEC-1981 3000 20
7839 KING PRESIDENT 17-NOV-1981 5000 10

14 rows
selected.

SQL> select * from emp order by sal desc,job;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-NOV-1981 5000 10

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20
7902 FORD ANALYST 7566 03-DEC-1981 3000 20
7566 JONES MANAGER 7839 02-APR-1981 2975 20
7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30
7934 MILLER CLERK 7782 23-JAN-1982 1300 10
7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30
7876 ADAMS CLERK 7788 23-MAY-1987 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 950 30
7369 SMITH CLERK 7902 17-DEC-1980 800 20

14 rows selected.

SQL> select ename,job,sal from emp order

by 2; Where 1 for ename & 2 for job & 3 for
sal

ENAME JOB SAL

SCOTT ANALYST 3000

FORD ANALYST 3000
SMITH CLERK 800
ADAMS CLERK 1100
MILLER CLERK 1300
JAMES CLERK 950
JONES MANAGER 2975
CLARK MANAGER 2450
BLAKE MANAGER 2850
1
2
KING PRESIDENT 5000
ALLEN SALESMAN 1600
MARTIN SALESMAN 1250
TURNER SALESMAN 150
0
WARD SALESMAN 1250

14 rows selected.

SQL> select ename name,job jobs,sal salary from emp order

by jobs; NAME JOBS SALARY

SCOTT ANALYST 3000

FORD ANALYST 3000
SMITH CLERK 800
ADAMS CLERK 1100
MILLER CLERK 1300
JAMES CLERK 950
JONES MANAGER 2975
CLARK MANAGER 2450
BLAKE MANAGER 2850
KING PRESIDENT 5000
ALLEN SALESMAN 1600
MARTIN SALESMAN 1250
TURNER SALESMAN 150
0
WARD SALESMAN 1250

14 rows selected.

DISTINCT :
SQL> select distinct deptno,job from emp;

DEPTNO JOB

10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

9 rows selected.
********************************************************************

1
3
 SINGLE ROW FUNCTIONS FOR CHARACTERS
DUAL :
SQL> select 5*12 from emp;
It will show 60 as many records in emp so we use a dummy table called DUAL
in these cases, it contain one record used for calculation :

SQL> select 5*12 from

dual; 5*12

60

SQL> select 'khaled' from

dual; 'KHALE

khaled

sysdate,user :
The date will be read from the computer where ORACLE
installed SQL> select sysdate,user from dual;

SYSDATE USER

19- DEC-1999 SCOTT

LOWER & UPPER :

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

SQL> select * from emp where

upper(ename)='scott'; no rows selected

SQL> select * from emp where upper(ename)='SCOTT';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-1987 3000 20

INICAP :
SQL> SELECT 'KHALED ZAGHLOUL' FROM DUAL;

'KHALEDZAGHLOUL

KHALED ZAGHLOUL

SQL> SELECT initcap('khaled zaghloul') from dual;

1
4
Change the first Character to capital letter

INITCAP('KHALED

Khaled Zaghloul

SUBSTR : (Fieldname,Start Col.,No. of Char.)

SQL> select dname,substr(dname,1,3) from
dept;

DNAME SUB

ACCOUNTING ACC
RESEARCH RES
SALES SAL
OPERATIONS OPE

INSTR : (Fieldname, String, From Col., No. of

occurrence) SQL> select dname,instr(dname,'A',1,1)
from dept;

DNAME INSTR(DNAME,'A',1,1)

ACCOUNTING 1
RESEARCH 5
SALES 2
OPERATIONS 5

LTRIM : (Fieldname, Sring) Cancels The string from left of

field RTRIM : (Fieldname, Sring) Cancels The string from
right of field SQL> select dname,ltrim(dname,'a') from
dept;
Because it is case sensitive it dos nothing

SQL> select dname,ltrim(dname,'A')

from dept; DNAME LTRIM(DNAME,'A

ACCOUNTING CCOUNTING
RESEARCH RESEARCH
SALES SALES
OPERATIONS OPERATIONS

LPAD : (Fieldname,Field length,String)

RPAD : (Fieldname,Field length,String)
SQL> select dname,lpad(dname,20,'*') from dept;

DNAME LPAD(DNAME,20,'*')

ACCOUNTING
**********ACCOUNTING
1
5
RESEARCH ************RESEARCH
SALES ***************SALES
OPERATIONS
**********OPERATIONS

1
6
SQL> select dname,rpad(dname,20,'*') from dept

DNAME RPAD(DNAME,20,'*')

ACCOUNTING
ACCOUNTING**********
RESEARCH RESEARCH************
SALES SALES***************
OPERATIONS
OPERATIONS**********

SQL> select dname,rpad(lpad(dname,15,'$'),20,'*')

from dept; DNAME RPAD(LPAD(DNAME,15,'

ACCOUNTING $$$$
$ACCOUNTING***** RESEARCH
$$$$$$$RESEARCH*****
SALES $$$$$$$$$$SALES*****
OPERATIONS $$$$
$OPERATIONS*****

TRANSLATE : (Fieldname,Character to be replaced,Replace

with) SQL> select dname,translate(dname,'AL','*%') from
dept;
Replace A with * and replace L with %

DNAME TRANSLATE(DNAM

ACCOUNTING
*CCOUNTING
RESEARCH RESE*RCH
SALES S*%ES
OPERATIONS OPER*TIONS

REPLACE : (Fieldname,String to be replaced,Replace

with) SQL> select dname,replace(dname,'AC','XYZ')
from dept;

DNAME REPLACE(DNAME,'AC','XYZ')

ACCOUNTING XYZCOUNTING
RESEARCH RESEARCH
SALES SALES
OPERATIONS OPERATIONS

LENGTH :
If the field is of type CHAR, it gives the Max. length
VARCHAR2, it gives the Exact length
SQL> select dname,length(dname) from dept;

1
7
DNAME LENGTH(DNAME)

ACCOUNTING 10
RESEARCH 8
SALES 5
OPERATIONS 10

1
8
Example :

How many ‘C’ in the field Dname in Dept table ?

SQL> select dname,length(dname)-length(translate(dname,'XC','X')) from dept;

DNAME LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'XC','X'))

ACCOUNTING 2
RESEARCH 1
SALES 0
OPERATIONS 0

1. Change X with X and delete C from the field

2. The Length after the change
3. The original length – the result from 2

ASCII & CHR :

SQL> select ascii('A') from dual; it gives:
65 SQL> select chr(65) from Dual; it gives :
A
SQL> select chr(10) from dual; it gives Newline (Enter)

1
9
 SINGLE ROW FUNCTIONS FOR NUMBERS

ROUND :
SQL> select round(97.89),round(97.89,1),round(97.89,-1) from dual;

ROUND(97.89) ROUND(97.89,1) ROUND(97.89,-1)

98 97.9 100

TRUNC :
SQL> select trunc(97.89),trunc(97.89,1),trunc(97.89,-1) from dual;

TRUNC(97.89) TRUNC(97.89,1) TRUNC(97.89,-1)

97 97.8 90

SIGN :
SQL> select sign(67),sign(0),sign(-34) from dual;

SIGN(67) SIGN(0) SIGN(-34)

1 0 -1

CEIL & FLOOR :

SQL> select ceil(5.89),floor(5.89) from dual;

CEIL(5.89) FLOOR(5.89)

6 5

MOD & ABS :

SQL> select mod(3,2) from dual;

MOD(3,2)

SQL> select abs(23),abs(-45) from dual;

ABS(23) ABS(-45)

23 45

2
0
 SINGLE ROW FUNCTIONS FOR DATES

SYSDATE :
SQL> select sysdate from dual;

SYSDATE

19-DEC-1999

SQL> select sysdate+2 from dual;

SYSDATE+2

21-DEC-1999

SQL> select sysdate+4/24 from dual; #

Add 4 hours SYSDATE+4/2

19-DEC-1999

SQL> select ename,sysdate,hiredate,sysdate-hiredate from

emp; The result will be No. of days and apart of day

ENAME SYSDATE HIREDATE SYSDATE-HIREDATE

SMITH 19-DEC-1999 17-DEC-1980 6941.6183

ALLEN 19-DEC-1999 20-FEB-1981 6876.6183
WARD 19-DEC-1999 22-FEB-1981 6874.6183
JONES 19-DEC-1999 02-APR-1981 6835.6183

MONTHS_BETWEEN :
SQL> select ename,sysdate,hiredate,months_between(sysdate,hiredate) from emp;
The result will be No. of months and apart of month

ENAME SYSDATE HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)

SMITH 19-DEC-1999 17-DEC-1980 228.08451

ALLEN 19-DEC-1999 20-FEB-1981 225.98774
WARD 19-DEC-1999 22-FEB-1981 225.92322
SCOTT 19-DEC-1999 19-APR-1987 152

ADD_MONTHS :
SQL> select add_months('1-jan-2000',1) from
dual; Add a No. of months to the Date given
ADD_MONTHS(

01-FEB-2000

SQL> select add_months('31-jan-2000',1) from dual;

2
1
ADD_MONTHS(

29-FEB-2000

LAST_DAY :
SQL> select last_day('1-feb-1996') from
dual; Gives the date of the last day in a
month

LAST_DAY('1

29-FEB-1996

NEXT_DAY :
SQL> select next_day('19-jul-2000','friday') from
dual; Gives the date of the next first day given

NEXT_DAY('1

21-JUL-2000

ROUND :
SQL> select
round(sysdate),round(sysdate,'month'),round(sysdate,'year')
from dual;

ROUND(SYSDA ROUND(SYSDA ROUND(SYSDA

20- JUL-2000 01-AUG-2000 01-JAN-2001

TRUNC :
SQL> select
trunc(sysdate),trunc(sysdate,'month'),trunc(sysdate,'year')
from dual;

TRUNC(SYSDA TRUNC(SYSDA TRUNC(SYSDA

19-JUL-2000 01-JUL-2000 01-JAN-2000

SQL> select trunc(sysdate - trunc(sysdate,'year'))

from dual; TRUNC(SYSDATE-

TRUNC(SYSDATE,'YEAR'))

200

SET STATISTICS TIME ON|OFF:

There are two methods to gauge how much time it takes to complete a particular
query. The easiest is to wrap each statement in the GETDATE() function.
2
2
SELECT GETDATE()
SELECT * FROM authors
SELECT GETDATE()
SELECT * FROM titleauthor
SELECT GETDATE()

2
3
This would obviously print out the begin- and end-time for each SQL statement if
they were sent as a batch. The alternative technique is just as simple but provides a
bit more information about the query itself. By using SET STATISTICS TIME ON, you
obtain information about CPU time and elapsed time for both parse and execution of
the query.
SET STATISTICS TIME ON
SELECT * FROM authors
SELECT * FROM titleauthor

The above query would return results similar to the following:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 243 ms. <-parse and compile

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 10 ms. <-execution
<data>

SQL Server parse and compile time:

CPU time = 10 ms, elapsed time = 32 ms.

SQL Server parse and compile time:

CPU time = 10 ms, elapsed time = 32 ms. <data>

The first reading is the CPU and elapsed time to parse and compile
the query. The second reading is the CPU and elapsed time to
execute the query.
Note: that SET STATISTICS TIME does not take into account time spent waiting for
locks or resources. Thus, the times may differ from that of the GETDATE() function if
there is any kind of resource contention.
Using the GETDATE() method may be all you need to gauge your query time(s), but
if you are interested in compile and optimize time(s), you will need to use SET
STATISTICS TIME ON|OFF.

DECODE :
It Means (IF THEN ELSE)
SQL> select ename,sal,decode(sal,2450,'ok','bad') from
emp; Means (If sal=2450 Then ‘OK’ Else ‘Bad’)
ENAME SAL DEC

SMITH 800 bad

ALLEN 1600 bad
CLARK 2450 ok
SCOTT 3000 bad

2
4
SQL> select ename,sal,decode(sign(sal-2450),0,'ok',1,'good','bad') from emp;
Means (If sal<2450 Then ‘bad’ Else
If Sal = 2450 Then ‘ok’ Else ‘good’)

ENAME SAL DECO

SMITH 800 bad

ALLEN 1600 bad
CLARK 2450 ok
SCOTT 3000 good

Functions to handle the format of date (TO_CHAR & TO_DATE & TO_NUMBER)

TO_CHAR :
SQL> select ename,to_char(hiredate,'day dd,month year') from emp;

ENAME TO_CHAR(HIREDATE,'DAYDD,MONTHYEAR')

SMITH wednesday 17,december nineteen

eighty ALLEN friday 20,february
nineteen eighty-one WARD sunday
22,february nineteen eighty-one
JONES thursday 02,april nineteen eighty-
one

SQL> select ename,hiredate,to_char(hiredate,'day dd,month year') from emp;

ENAME HIREDATE TO_CHAR(HIREDATE,'DAYDD,MONTHYEAR')

SMITH 17-DEC-1980 wednesday 17,december nineteen

eighty ALLEN 20-FEB-1981 friday
20,february nineteen eighty-one WARD
22-FEB-1981 sunday 22,february
nineteen eighty-one JONES 02-APR-1981 thursday
02,april nineteen eighty-one

SQL> select ename,hiredate,to_char(hiredate,'Day dd,Month Year') from emp;

ENAME HIREDATE TO_CHAR(HIREDATE,'DAYDD,MONTHYEAR')

SMITH 17-DEC-1980 Wednesday 17,December Nineteen

Eighty ALLEN 20-FEB-1981 Friday
20,February Nineteen Eighty-One WARD
22-FEB-1981 Sunday 22,February
Nineteen Eighty-One JONES 02-APR-1981 Thursday
02,April Nineteen Eighty-One

SQL> select ename,hiredate,to_char(hiredate,'Dy dd,Month YYYY')

from emp; ENAME HIREDATE TO_CHAR(HIREDATE,'DYD

2
5
SMITH 17-DEC-1980 Wed 17,December
1980 ALLEN 20-
FEB-1981 Fri 20,February 1981 WARD
22-FEB-1981 Sun 22,February
1981 JONES
02-APR-1981 Thu 02,April 1981

2
6
SQL> select ename,hiredate,to_char(hiredate,'Dy dd,MM YYYY') from emp;

ENAME HIREDATE TO_CHAR(HIREDA

SMITH 17-DEC-1980 Wed 17,12 1980

ALLEN 20-FEB-1981 Fri 20,02 1981
WARD 22-FEB-1981 Sun 22,02 1981
JONES 02-APR-1981 Thu 02,04 1981

SQL> select ename,hiredate,to_char(hiredate,'Dy ddd,MM YYYY')

from emp; ENAME HIREDATE TO_CHAR(HIREDAT

SMITH 17-DEC-1980 Wed 352,12 1980

ALLEN 20-FEB-1981 Fri 051,02 1981
WARD 22-FEB-1981 Sun 053,02 1981
JONES 02-APR-1981 Thu 092,04 1981

SQL> select ename,sal,to_char(sal,'999,999.999')

from emp; ENAME SAL TO_CHAR(SAL,

SMITH 800 800.000

ALLEN 1600

1,600.000
WARD 1250

1,250.000
JONES 2975

2,975.000

SQL> select ename,sal,to_char(sal,'009,999.999')

from emp; ENAME SAL TO_CHAR(SAL,

SMITH 800
000,800.000
ALLEN 1600
001,600.000
WARD 1250
001,250.000
JONES 2975
002,975.000

SQL> select to_char(sysdate,'dd month,yyyy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'DDMONTH,Y

19 july ,2000 03:44:26

2
7
SQL> select to_char(sysdate,'dd month,yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DDMONTH,Y

19 july ,2000 15:44:39

2
8
SQL> select to_char(sysdate,'dd month,yyyy hh24:mi:ss am') from dual;

TO_CHAR(SYSDATE,'DDMONTH,YYYY

19 july ,2000 15:45:00 pm

TO_DATE :
SQL> select to_char(to_date(‘19-07-2000’,’DD MM YYYY’),’DAY’)
from dual; Gives the name of the day of a date

FORMAT CHANGE :
The date is in format of : 19-JUL-
2000 To change it to a format :
19/07/2000 We start from windows
:
1. START
2. RUN : Regedit
3. HKEY_LOCAL_MACHINE
4. SOFTWARE
5. ORACLE
6. NLS_DATE_FORMAT : double click on it, then write the new format
7. EXIT
To validate the new format we have to reconnect to SQL :
SQL> Connect Scott/Tiger

The solution for Y2K was by putting a new format for year it is RR so if we write the
format in : DD MM RR : if RR (Year) between 0 & 49 it consider it as 20RR and
If it was between 50 & 99 it consider it as 19RR

2
9
 JOINS
There are 3 kinds of Join:
1- Equi-Join:
SQL> select ename,dname from emp,dept where [Link] = [Link];

ENAME DNAME

SMITH RESEARCH
ALLEN SALES
CLARK ACCOUNTING
SCOTT RESEARCH

2- Non Equi-Join:
SQL> select ename,dname,[Link] from emp,dept where [Link] =
[Link];

ENAME DNAME DEPTNO

SMITH RESEARCH 20
ALLEN SALES 30
CLARK ACCOUNTING 10
SCOTT RESEARCH 20
KING ACCOUNTING 10

The number of join conditions are at least equal to the number of tables in select
statement - 1

3- Self Join:
SQL> select ename,dname,[Link] from emp e,dept d where [Link] = [Link];

ENAME DNAME DEPTNO

SMITH RESEARCH 20
ALLEN SALES 30
CLARK ACCOUNTING 10
SCOTT RESEARCH 20
KING ACCOUNTING 10

SQL> select [Link],[Link],[Link] from emp e,salgrade s where [Link] between

[Link] and [Link];

ENAME SAL GRADE

SMITH 800 1
MILLER 1300 2
ALLEN 1600 3
CLARK 2450 4
SCOTT 3000 4
KING 5000 5

3
0
SQL> select [Link], [Link] from emp e, emp m where [Link] = [Link];

ENAME ENAME

SMITH FORD
ALLEN BLAKE
CLARK KING
SCOTT JONES

4- Outer Join:
SQL> select [Link],[Link] from emp e,dept d where [Link] =[Link];

ENAME DNAME

CLARK ACCOUNTING
KING ACCOUNTING
SMITH RESEARCH
SCOTT RESEARCH
ALLEN SALES
This statement did not bring the OPERATION department, Becouse it has no
employees in it, so
:

SQL> select [Link],[Link] from emp e,dept d where [Link]

(+)=[Link]; ENAME DNAME

CLARK ACCOUNTING
KING ACCOUNTING
SMITH RESEARCH
SCOTT RESEARCH
ALLEN SALES
OPERATIONS

SQL>SELECT [Link], [Link], [Link] FROM

Customer, Orders WHERE [Link] = [Link]

Alias the same statement to make it easier to read.

SQL>SELECT [Link], [Link], [Link] FROM Customer a,
Orders b WHERE [Link] = [Link]

The second and newer style of inner join is expressed as follows.

SQL>SELECT [Link], [Link], [Link]
FROM Customer INNER JOIN Orders ON [Link] =
[Link]

SQL>SELECT [Link], [Link], [Link] FROM

Customer AS a INNER JOIN Orders AS b ON [Link] = [Link]

3
1
GRASP THE SYNTAX OF OUTER JOINS
Starting with Oracle9i, the confusion of the outer join syntax using the (+) notation
has been superseded by the ISO 99 outer join syntax. There are three types of
outer joins: left, right, and the full outer. The purpose of an outer join is to include
nonmatching rows, which the outer join returns as NULL values.
Let's review the syntax differences between these variations in joins:

 Left outer join--Oracle8i:

Select last_name,
department_name From
employees e, departments
d
Where e.department_id = d.department_id(+);

 Left outer join--Oracle9i:

Select last_name,
department_name From
employees e
left outer join departments d
on e.department_id = d.department_id;

 Right outer join--Oracle8i:

Select last_name,
department_name From
employees e, departments d
Where e.department_id(+) = d.department_id;

 Right outer join--Oracle9i:

Select last_name,
department_name From
employees e
right outer join departments d
on e.department_id = d.department_id;

The full outer join has no direct equivalent in Oracle8i, but it's very handy to find
missing rows in both tables being joined in Oracle9i. In the example below, we
include employees with departments as well as departments without employees:

Select last_name, department_name

From employees e
full outer join departments d
on e.department_id =

d.department_id; SQL> select

sum(sal) from emp; SUM(SAL)

29025

SQL> select avg(sal) from

3
2
emp; AVG(SAL)

2073.2143

3
3
GROUP BY :
SQL> select deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)

10 8750
20 10875
30 9400

SQL> select deptno,sum(sal) from emp group by

deptno,job; DEPTNO SUM(SAL)

10 1300
10 2450
10 5000
20 6000
20 1900
20 2975
30 950
30 2850
30 5600

9 rows selected.

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

DEPTNO JOB SUM(SAL)

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

9 rows selected.

SQL> select avg(comm),sum(comm)/count(comm) from

emp; AVG(COMM) SUM(COMM)/COUNT(COMM)

550 550

3
4
SQL> select count(*),avg(comm),sum(comm)/count(comm) from emp;

COUNT(*) AVG(COMM) SUM(COMM)/COUNT(COMM)

14 550 550

SQL> select
count(*),count(comm),avg(comm),sum(comm)/count(comm),sum(comm)/count(*)
from emp;

COUNT(*) COUNT(COMM) AVG(COMM) SUM(COMM)/COUNT(COMM)

SUM(COMM)/COUNT(*)

14 4 550 550 157.14286

The GROUP BY clause in queries has become more complex recently. Until
A few years ago, the only option for aggregate functions was the standard

GROUP BY: select deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)

10 8750
20 10875
30 9400

This would calculate the aggregate functions for a group of

rows based on a particular column. In this case, a row would
be produced for each distinct value in the DEPTNO column.
In the past, if you wanted to generate a grand total for all departments, you had
to use either a programming language to accumulate
it outside the query, or a reporting tool, or a union:

select deptno,sum(sal) from emp group by

deptno union
select null,sum(sal) from emp;

DEPTNO SUM(SAL)

10 8750
20 1087
5
30 9400
29025

With the ROLLUP clause, you can now do the same thing as the
union example did, but more easily and accurately. The ROLLUP
clause simply
adds a row to the result that applies the aggregate function to all the rows in the
current rollup. For example:

3
5
select deptno,sum(sal) from emp group by rollup(deptno);

DEPTNO SUM(SAL)

10 8750
20 1087
5
30 9400
29025

In a ROLLUP grouping, the extra aggregate row is returned for

each column listed in order. For example, if you use ROLLUP on
both DEPTNO
and JOB, you get an extra row for each department, and an extra row to indicate the
grand total for all departments:

select deptno,job,sum(sal) from emp group by

rollup(deptno,job) DEPTNO JOB SUM(SAL)

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT

5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025

Notice that we didn't get extra rows for JOB. These rows are
called super-aggregate rows. The ROLLUP clause only adds
rows at the end of each column in its clause. If you want a
summary for every possible combination of grouping column,
use the CUBE clause:

select deptno,job,sum(sal) from emp group by

cube(deptno,job) DEPTNO JOB SUM(SAL)

29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
3
6
 PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

We now have our aggregate rows plus a summary row for each
column in The group by expression. We know the total salary for
PRESIDENT and department 10.
You can use a CUBE grouping to create a cross-tabulation
report, or matrix report, from the SQL results. But when
generating a report, how
do you tell the difference between a row that is normally part of the GROUP BY and
those that have been generated by ROLLUP or CUBE?
The GROUPING function returns 0 if its argument is not part of a
ROLLUP or CUBE in the current row, and 1 if it is:

select
grouping(deptno),grouping(job),deptno,job,sum(s
al) from emp group by cube(deptno,job)

GROUPING(DEPTNO) GROUPING(JOB) DEPTNO JOB SUM(SAL)

1 1 29025
1 0 CLERK 4150
1 0 ANALYST 6000
1 0 MANAGER 8275
1 0 SALESMAN 5600
1 0 PRESIDENT 5000
0 1 10 875
0
0 0 10 CLERK 1300
0 0 10 MANAGER 2450
0 0 10 PRESIDENT

5000
0 1 20 10875
0 0 20 CLERK 1900
0 0 20 ANALYST 6000
0 0 20 MANAGER 2975
0 1 30 9400
0 0 30 CLERK 950
0 0 30 MANAGER 2850
3
7
 0 0 30 SALESMAN

5600

The first row is the CUBE row for both DEPTNO and JOB, so
GROUPING returns 1 for both. The second row is the CUBE row for
the JOB CLERK,
so GROUPING(DEPTNO) returns 1, but GROUPING(JOB) returns 0. These values can be
used by external utilities to detect whether it's a super-aggregate row or a normal
aggregate row.
Oracle9i introduces some new syntax with the GROUPING SETS
clause. Rather than grouping results on a single column, a
GROUPING SETS clause

3
8
Allows a query to group by a combination of data as if it were a single GROUP
BY column. Before Oracle9i, such grouping would have to be done through an
awkward hack. Here's a simple example of grouping before Oracle 9i:

select lpad(deptno,10)||' '||job,sum(sal)

from emp group by rollup(rpad(deptno,10)||'

'||job); RPAD(DEPTNO,10)||JOB

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT
SUM(SAL) 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

29025

With the GROUPING SETS clause, you can do something similar

with: select deptno,job,sum(sal)

from emp group by grouping sets (rollup(deptno,job));

DEPTNO JOB SUM(SAL)

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025

Also with Oracle9i are the functions GROUP_ID and GROUPING_ID,

which return values that indicate which grouping set the particular
row is
part of:

select grouping_id(deptno) deptgrp,

3
9
 grouping_id(job) jobgrp,
deptno,job,sum(sal)
from emp
group by grouping sets (rollup(deptno,job))

4
0
 DEPTGRP JOBGRP DEPTNO JOB SUM(SAL)

0 0 10 CLERK 1300
0 0 10 MANAGER 2450
0 0 10 PRESIDENT

5000
0 1 10 8750
0 0 20 CLERK 1900
0 0 20 ANALYST 6000
0 0 20 MANAGER 2975
0 1 20 10875
0 0 30 CLERK 950
0 0 30 MANAGER 2850
0 0 30 SALESMAN

5600
0 1 30 9400
1 1 29025

Note:
There is a system table that you can query to get a rowcount for every table with a
defined clustered index. By using the following query, you can determine the
number of rows in the table based upon the value stored in the sysindexes table.

SQL> SELECT rows FROM sysindexes WHERE id = OBJECT_ID('authors') AND indid < 2

The indid portion of the query identifies the type of index that is defined upon the
table. An indid of 1 is a clustered index; greater than 1 is a nonclustered index
(except for 255, which is an entry for tables that have text or image data).
Obviously, you cannot apply a WHERE clause to this query based upon some criteria
within the target table. Additionally, this technique does not work with views. If you
were counting only the rows where Fname = 'John', then you would have to resort to
the regular method of SELECT COUNT(*) FROM the actual table.

SQL> select avg(comm),avg(nvl(comm,0))

from emp; AVG(COMM) AVG(NVL(COMM,0))

550 157.14286

Examples:
1- give the average of salary for each department where it is greater
than 2000 ? SQL> select deptno,avg(sal) from emp group by deptno
having avg(sal) > 2000;

DEPTNO AVG(SAL)

10 2916.6667
20 2175

4
1
2- Give the minimum average salary between
departments ? SQL> select min(avg(sal)) from emp
group by deptno;

MIN(AVG(SAL))

1566.6667

4
2
SUB QUERIES:
** bring out the employees in SCOTT department ?
SQL> select * from emp where deptno=(select deptno from emp where
ename='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17/12/1980 800 2

0
7566 JONES MANAGER 7839 2975 20
02/04/1981
7788 SCOTT ANALYST 7566 3000 20
19/04/1987
7876 ADAMS CLERK 7788 23/05/1987 1100 20
7902 FORD ANALYST 7566 3000 20
03/12/1981

** bring out the employees who's Manager is KING ?

SQL> select * from emp where mgr=(select empno from emp where ename='KING');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7566 JONES MANAGER 7839 02/04/1981 2975 20

7698 MANAGER 7839 01/05/1981 2850 30
BLAKE
7782 CLARK MANAGER 7839 09/06/1981 2450 10

** Bring the department number of the least salary average ?

SQL> select deptno,avg(sal) from emp having avg(sal)=(select min(avg(sal)) from
emp group by deptno) group by deptno;

DEPTNO AVG(SAL)

30 1566.6667

** Bring out the employees who's salary is greater than the Max. salary of All
CLERCKS ? SQL> select * from emp where sal > all (select sal from emp where
job='CLERK');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 1600 300 30

20/02/1981
7566 JONES MANAGER 7839 2975 20
02/04/1981
7698 BLAKE MANAGER 7839 2850 30
01/05/1981
7782 CLARK MANAGER 7839 2450 10
09/06/1981
7788 SCOTT ANALYST 7566 3000 20
19/04/1987
7839 KING PRESIDENT 17/11/1981 5000 1
0
7844 TURNER SALESMAN 7698 1500 0 30
08/09/1981
7902 FORD ANALYST 7566 03/12/1981 3000 20

8 rows selected.

4
3
** Bring out the employees who's salaries is greater than any CLERCK
salary? SQL> select * from emp where sal > any(select sal from emp
where job='CLERK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7499 ALLEN SALESMAN 7698 1600 300 30

20/02/1981
7521 WARD SALESMAN 7698 1250 500 30
22/02/1981
7566 JONES MANAGER 7839 2975 20
02/04/1981
7654 MARTIN SALESMAN 7698 1250 140 30
28/09/1981 0
7698 BLAKE MANAGER 7839 2850 30
01/05/1981
7782 CLARK MANAGER 7839 2450 10
09/06/1981
7788 SCOTT ANALYST 7566 3000 20
19/04/1987
7839 KING PRESIDENT 17/11/1981 5000 10
7844 SALESMAN 7698 08/09/1981 1500 0 30
TURNER
7876 ADAMS CLERK 7788 23/05/1987 1100 20
7900 JAMES CLERK 7698 03/12/1981 950 30
7902 FORD ANALYST 7566 03/12/1981 3000 20
7934 MILLER CLERK 7782 23/01/1982 1300 10

13 rows selected.

** Bring out the employees who's salaries is equal any CLERCK salary?
SQL> select * from emp where sal in (select sal from emp where job='CLERK');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17/12/1980 800 20

7900 JAMES CLERK 7698 03/12/1981 950 30
7876 ADAMS CLERK 7788 23/05/1987 1100 20
7934 MILLER CLERK 7782 23/01/1982 1300 10

** Bring out the employees who's salaries and job are the same as SCOTT?
SQL> select * from emp where sal = (select sal from emp where ename='SCOTT')
and job = (select job from emp where ename='SCOTT');

OR

SQL> select * from emp where (sal,job) = (select sal,job from emp where
ename='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19/04/1987 3000 20

7902 FORD ANALYST 7566 3000 20
03/12/1981

SQL> select deptno,avg(sal) from emp group by

deptno; DEPTNO AVG(SAL)

4
4
10 2916.6667
20 2175
30 1566.6667

4
5
SQL> select [Link],v.avg_sal from dept d,(select deptno,avg(sal) avg_sal from emp
group by deptno) v where [Link]=[Link];

DNAME AVG_SAL

ACCOUNTING 2916.6667
RESEARCH 2175
SALES 1566.6667

4
6
 DML (Data Manipulation Language)

SQL> insert INTO EMP VALUES

(8000,'ZAGHLOUL','MANAGER',NULL,SYSDATE,2000,'',40);
SQL> insert INTO EMP (EMPNO,ENAME,DEPTNO) VALUES (8001,'moh',30);
SQL> insert INTO EMP (EMPNO,ENAME,DEPTNO) select
empno+1000,ename,deptno from emp;
SQL> update emp set sal=sal+50 where empno=7788;
SQL> update emp set sal=(select sal from emp where
empno=7499) where Empno = 7788;

e.g. Change the salary of not null comm. with comm., and comm.
With sal ? SQL> update emp set sal=comm,comm=sal where
comm is not null; Errors :
1. Unique constraint (username,,,,) Violated : When we insert a record with
duplicate primary key
2. If we insert or update foreign key and it is not exist in the attached table.

SQL> delete from emp; it deletes all records in

emp SQL> delete from emp where hiredate is null;

CASCADE OPTIONS
v2k Cascading of updated or deleted key data via constraints, which has been in
Access and Oracle for some time, has finally arrived in SQL Server 2000. Before this
new functionality, cascading of changes in key data usually had to be performed via
application triggers. While triggers are still going to be around, there will likely be
fewer of them. Consider the following scenario:

CREATE TABLE Parent (Lname VARCHAR(10) NOT NULL PRIMARY

KEY) GO
CREATE TABLE Child (Id INT IDENTITY(1,1),
Lname VARCHAR(10) NOT NULL
CONSTRAINT fk1 REFERENCES
Parent(Lname) ON DELETE NO ACTION
ON UPDATE CASCADE)
GO
INSERT INTO Parent VALUES
('Smith') INSERT INTO Child
VALUES ('Smith') GO

The ON UPDATE CASCADE portion of the foreign key definition tells SQL Server to
cascade or "domino" the updates made to the primary key in Parent to the Child
table.

UPDATE Parent SET Lname = 'Johnson'

The row in both tables would now be updated from 'Smith' to 'Johnson.' The ON
DELETE NO ACTION clause tells SQL Server to do just that, take no action if a
primary key is deleted from the Parent table. This is the default behavior of SQL
Server. So if you left this clause out of your table/foreign key definitions or kept it in,
4
7
then SQL would raise an error stating something like the following and would not let
you delete the row.

4
8
DELETE FROM Parent WHERE Lname = 'Johnson'
Server: Msg 547, Level 16, State 1, Line 1

DELETE statement conflicted with COLUMN REFERENCE constraint 'fk1'. The conflict
occurred in database 'test', table 'Child', column 'Lname'. The statement has been
terminated. These options can be useful for managing your application's data, but
this functionality is also a double-edged sword. If you were to use both ON DELETE
CASCADE and ON UPDATE CASCADE, you could potentially invalidate all your data
within your database if you started updating or deleting top-level keys.

NOTE : Until now non of the previous statements are really executed, to save the
result we have to write : COMMIT and to not save the result we use ROLLBACK, so
where our work was ?
It was on a logical space (table space) called users ([Link]).
When we use UPDATE statement, the previous values will be saved in ROLLBACK
SEQMENT until we make COMMIT the this segment will be free.
Before we make COMMIT non of the users can see any changes we did on the
database. ORACLE While updating, deleting or inserting will LOCK automatically the
table until the user make COMMIT or ROLLBACK
1. Share Lock : for tables
2. Exclusive Lock : for records under updating
If we write EXIT (normal exit) with out commit or rollback then it will be understood
as commit But in case of Not normal exit it will be understood as rollback.
If we have multi statements like :
SQL> insert ………..
SQL> update
………….
We can save these statement as an indicator to make rollBack step
by step SQL> SavePoint A
another

statements

SQL> SavePoint

Now we Have multi choices using the Rollback

commant : SQL> Rollback to A # it will delete all
jobs done after A SQL> Rollback # Delete
every thing
SQL> Commit # For the rest or All

4
9
 DDL (Data Definition Language)

Note: Any DDL command will make a Commit for all previous written statements and
the DDL command it self.
The table name must be :
1. Maximum length 30 Char.
2. Start with Char.
3. The special char. Allowed (#,$,_)

SQL> create table test1 (f1 number,f2 varchar2(10),f3

date); SQL> desc test1;
Name Null? Type

F1 NUMBER
F2 VARCHAR2(10)
F3 DATE

SQL> create table test2 (f1 number not null,f2 varchar2(10),f3

date); SQL> desc test2
Name Null? Type

F1 NOT NULL NUMBER

F2 VARCHAR2(10)
F3 DATE

SQL> alter table test2 add (f4 number); # Add Field to

table SQL> desc test2;
Name Null? Type

F1 NOT NULL NUMBER

F2 VARCHAR2(10)
F3 DATE
F4 NUMBER

SQL> alter table test2 modify(f2 varchar2(30)

not null); # Modify a Field of a table

Note: Logically we can’t do the following :

1. change a field to NOT NULL if the table has some NULL data
2. Minimize the length of a field
3. Drop Column using ORACLE version less than 8.1.5 But in the version 8.1.5I
(8I) or after : SQL> Alter table test2 drop(f4); #if table Test2 has Children’s or
constraints we have to CASCADE CONSTRAINTS at the end of statement

SQL> desc test2;

Name Null? Type

F1 NOT NULL NUMBER

F2 NOT NULL
VARCHAR2(30) F3 DATE

5
0
Note: The table is an Object for the user saved in data dictionary in capital letter. The
name of Data Dictionary for table is : USER_TABLES

SQL> select * from user_tables where table_name='EMP';

SQL> select * from user_tab_Columns where table_name='EMP';
SQL> select * from user_tab_Columns where
table_name='EMP' and column_name='ENAME';

CONSTRAINTS:
1. Primary key :
 Not null, Unique (No Duplicate)
 Only one PK for a table
 We can make composite PK e.g. : PK(f1,f2,,,,)
2. Unique key :
 Can be Null
 No Duplicate
 We can make composite UK
 We can have more than one UK in one table
3. Foreign key :
 Can be Null
 Must be attached to PK or UK
 Can’t be attached to composite PK or UK
 The constraints in PK or UK must be the same as FK
4. Check key : Just one of two values e.g. : Male or Female

SQL> CREATE TABLE CTZN(NAT_NO NUMBER,PASS_NO NUMBER,F_NAME

VARCHAR2(20),GENDER NUMBER(1),
CONSTRAINT CTZN_PK PRIMARY KEY (NAT_NO),
CONSTRAINT CTZN_UK UNIQUE (PASS_NO),
CONSTRAINT CTZN_CK CHECK (GENDER IN (-1,1)));

SQL> DESC CTZN

Name Null? Type

NAT_NO NOT NULL

NUMBER PASS_NO NUMBER
F_NAME VARCHAR2(20)
GENDER NUMBER(1)

SQL> SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='CTZN';

PARENT:
SQL> CREATE TABLE ACC_MST(ACC_NO NUMBER,ACC_NAME
VARCHAR2(20),BALANCE
NUMBER, CONSTRAINT ACC_MST_PK PRIMARY KEY (ACC_NO));
CHILD:
SQL> CREATE TABLE ACC_TRN(TRN_ACC_NO NUMBER,TRN_TYPE
NUMBER(1),AMOUNT
NUMBER, CONSTRAINT ACC_MST_TRN_FK FOREIGN KEY (TRN_ACC_NO)
REFERENCES ACC_MST(ACC_NO));
5
1
Note: We can’t Delete or Modify constraint but we can Add or
Drop only, but we can disable a constraint.

SQL> ALTER TABLE ACC_TRN ADD CONSTRAINT ACC_TRN_CK CHECK (TRN_TYPE IN (-1,1));
SQL> ALTER TABLE ACC_TRN DISABLE CONSTRAINT ACC_MST_TRN_FK;
SQL> DESC ACC_TRN
Name Null? Type

TRN_ACC_NO NUMBER
TRN_TYPE

NUMBER(1)
AMOUNT NUMBER

Note: If the FK built on a PK we can’t drop the PK unless :

1. Drop the FK first
2. At the end of the Drop statement we put CASCADE CONSTRAINTS, This will
drop the PK & FK
3. An error will be If there are data on the table so when we write the
constraints we add : ON DELETE CASCADE this will delete all data but it will
give (One record deleted)

The constraint table fields are :

1. Constraint_name
2. Constraint_type
3. Search_condition
4. R_constraint_name

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='CTZN';

CONSTRAINT_NAME

CTZN_PK
CTZN_UK
CTZN_CK

SQL> SELECT CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='CTZN';

C
-
P
U
C

SQL> SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME ='CTZN';

SEARCH_CONDITION

GENDER IN (-1,1)
5
2
SQL> SELECT R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME
='CTZN';

5
3
R_CONSTRAINT_NAME

SQL> select COLUMN_NAME,CONSTRAINT_NAME,POSITION from user_cons_COLUMNS

where TABLE_NAME = &X;

Enter value for x: CTZN

old 2: from user_cons_COLUMNS where TABLE_NAME = &X
new 2: from user_cons_COLUMNS where TABLE_NAME =
CTZN from user_cons_COLUMNS where TABLE_NAME =
CTZN
*
ERROR at line 2:
ORA-00904: invalid column
name SQL> /
Enter value for x: 'CTZN'
old 2: from user_cons_COLUMNS where TABLE_NAME = &X
new 2: from user_cons_COLUMNS where TABLE_NAME = 'CTZN'

COLUMN_NAME CONSTRAINT_NAME POSITION

GENDER CTZN_CK
NAT_NO CTZN_PK 1
PASS_NO CTZN_UK 1

SQL> SET VERIFY OFF

SQL> select COLUMN_NAME,CONSTRAINT_NAME,POSITION from user_cons_COLUMNS
where TABLE_NAME = &X;
Enter value for x: 'CTZN'
COLUMN_NAME CONSTRAINT_NAME POSITION

GENDER CTZN_CK
NAT_NO CTZN_PK 1
PASS_NO CTZN_UK 1

SQL> SET VERIFY ON

SQL> /
Enter value for x: 'CTZN'
old 2: from user_cons_COLUMNS where TABLE_NAME = &X
new 2: from user_cons_COLUMNS where TABLE_NAME = 'CTZN'

COLUMN_NAME CONSTRAINT_NAME POSITION

GENDER CTZN_CK
NAT_NO CTZN_PK 1
PASS_NO CTZN_UK 1

SQL> SET VERIFY OFF

5
4
SQL> SELECT ROWID,DEPTNO,DNAME FROM
DEPT; ROWID DEPTNO DNAME

AAAAe/AACAAAAEgAAA 10 ACCOUNTING
AAAAe/AACAAAAEgAAB 20 RESEARCH
AAAAe/AACAAAAEgAAC 30 SALES
AAAAe/AACAAAAEgAAD 40 OPERATIONS

SQL> CREATE INDEX EMP_DEPTNO_INDEX ON EMP (DEPTNO);

Index created.

SQL> DESC USER_INDEXES;

Name Null? Type

INDEX_NAME NOT NULL VARCHAR2(30)

INDEX_TYPE VARCHAR2(12)
TABLE_OWNER NOT NULL
VARCHAR2(30)
TABLE_NAME NOT NULL
VARCHAR2(30) TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
TABLESPACE_NAME
VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS
NUMBER PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE

VARCHAR2(40)
INSTANCES VARCHAR2(40)

5
5
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)

5
6
SQL> SELECT INDEX_NAME,INDEX_TYPE,UNIQUENESS FROM USER_INDEXES;

INDEX_NAME INDEX_TYPE UNIQUENES

ACC_MST_PK NORMAL UNIQUE

CTZN_PK NORMAL UNIQUE
CTZN_UK NORMAL UNIQUE
EMP_DEPTNO_INDEX NORMAL
NONUNIQUE PK_DEPT
NORMAL UNIQUE
PK_EMP NORMAL UNIQUE

VIEW:

If we create a View from more than one table, then we can insert on one table only
at a time. And we can end the statement with (WITH READ ONLY).

SQL> CREATE VIEW V1 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP;

View created.

V1 doesn't contain any data but it brings data from the original
table SQL> SELECT * FROM V1;

EMPNO ENAME DEPTNO

7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
8000 ZAGHLOUL 40

15 rows selected.

SQL> INSERT INTO V1 VALUES (8888,'SUL',40);

1 row created.

5
7
SQL> SELECT * FROM EMP WHERE EMPNO = 8888;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

8888 SUL 40

SQL> CREATE OR REPLACE VIEW V1 AS SELECT EMPNO,ENAME,SAL,DEPTNO FROM

EMP;

View created.

SQL> CREATE OR REPLACE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO = 10;

View created.

SQL> SELECT * FROM V2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7782 CLARK MANAGER 7839 2450 10

09/06/1981
7839 KING PRESIDENT 17/11/1981 5000 10
7934 MILLER CLERK 7782 23/01/1982 1300 1
0

SQL> INSERT INTO V2 (EMPNO,ENAME,DEPTNO) VALUES (8989,'KIL',30);

1 row created.

SQL> SELECT * FROM V2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7782 CLARK MANAGER 7839 2450 10

09/06/1981
7839 KING PRESIDENT 17/11/1981 5000 10
7934 MILLER CLERK 7782 23/01/1982 1300 1
0

SQL> SELECT * FROM EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17/12/1980 800 20

7499 ALLEN SALESMAN 7698 300 1600 30
20/02/1981
7521 WARD SALESMAN 7698 500 1250 30
22/02/1981
7566 JONES MANAGER 7839 2975 20
02/04/1981
7654 MARTIN SALESMAN 7698 1400 1250 30
28/09/1981
7698 BLAKE MANAGER 7839 2850 3
01/05/1981 0
7782 CLARK MANAGER 7839 2450 10
09/06/1981
7788 SCOTT ANALYST 7566 1600 20
19/04/1987
7839 KING PRESIDENT 17/11/1981 5000 10
7844 SALESMAN 7698 08/09/1981 0 1500 30
TURNER
7876 ADAMS CLERK 7788 23/05/1987 110 20
5
8
 0
7900 JAMES CLERK 7698 03/12/1981 950 30
7902 FORD ANALYST 7566 03/12/1981 300 20
0
7934 MILLER CLERK 7782 23/01/1982 1300 10
8000 ZAGHLOUL 23/07/2000 2000 40
MANAGER
8888 SUL 40
8989 KIL 30

17 rows selected.

5
9
SQL> CREATE OR REPLACE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO = 10
WITH CHECK OPTION;

View created.

SQL> INSERT INTO V2 (EMPNO,ENAME,DEPTNO) VALUES (6666,'GIS',10);

1 row created.

SQL> CREATE OR REPLACE VIEW V3 AS SELECT

EMPNO,ENAME,[Link],DNAME,
LOC FROM EMP,DEPT WHERE [Link] = [Link];

View created.

SQL> SELECT * FROM V3;

EMPNO ENAME DEPTNO DNAME LOC

7782 CLARK 10 ACCOUNTING NEW YORK

7839 KING 10 ACCOUNTING NEW YORK
7934 MILLER 10 ACCOUNTING NEW YORK
6666 GIS 10 ACCOUNTING NEW YORK
8954 GIS 10 ACCOUNTING NEW YORK
7369 SMITH 20 RESEARCH DALLAS
7566 JONES 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALLAS
7876 ADAMS 20 RESEARCH DALLAS
7902 FORD 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7844 TURNER 30 SALES CHICAGO
7900 JAMES 30 SALES CHICAGO
8989 KIL 30 SALES CHICAGO
8000 ZAGHLOUL 40 OPERATIONS BOSTON
8888 SUL 40 OPERATIONS BOSTON

19 rows selected.

NOT SIMPLE VIEW:

SQL> CREATE OR REPLACE VIEW V4 AS SELECT DEPTNO,AVG(SAL) AVG_SAL,
COUNT(EMPNO) EMP_COUNT FROM EMP GROUP BY DEPTNO;

View created.

6
0
SQL> SELECT * FROM V4;

DEPTNO AVG_SAL EMP_COUNT

10 2916.666 5
7
20 1895 5
30 1000 7
40 2000 2

OR :

SQL> SELECT [Link] ,V.* FROM DEPT D,V4 V WHERE [Link] =

[Link]; DNAME DEPTNO AVG_SAL EMP_COUNT

ACCOUNTING 10 5
2916.6667
RESEARCH 20 1895 5
SALES 30 1000 7
OPERATIONS 40 2000 2

SQL> SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = 'V4';

TEXT

SELECT DEPTNO,AVG(SAL) AVG_SAL,COUNT(EMPNO) EMP_COUNT

FROM EMP GROUP BY DEPTNO

NOTE : The previous result rows are too long so we can

use : SQL> SET LONG 1000;

SQL> DROP VIEW V4;

SQL> DESC

USER_VIEWS;
Name Null? Type

VIEW_NAME NOT NULL

VARCHAR2(30) TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER
VARCHAR2(30) VIEW_TYPE VARCHAR2(30)

6
1
SEQUENCES: Auto generated
Numbers The Sequence Format :
CREATE SEQUENCE seq_name
START WITH n1 the default is 1
INCREMENT BY n2 the default
is 1 MAXVALUE n3
MINVALUE n4
CYCLE / NOCYCLE After arriving to Max., Back to
biginning CACHE / NOCACHE the default is 20

SQL> CREATE SEQUENCE EMP_SEQ MAXVALUE 150;

Sequence created.

SQL> SELECT EMP_SEQ.NEXTVAL FROM DUAL;

NEXTVAL

SQL> SELECT EMP_SEQ.NEXTVAL FROM DUAL;

NEXTVAL

SQL> INSERT INTO EMP(empno,ename,deptno) VALUES

(emp_seq.nextval, 'KHALED',30);

NEXVAL : Incrementing the value on the level of database (all users).

CURRVAL : Incrementing the value on the level of session.

SQL> CREATE SYNONYM EE FOR EMP;

Synonym created.

SQL> SELECT * FROM EE;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-12-1980 800 20

7499 ALLEN SALESMAN 7698 20-02- 1600 300 30
1981
7521 WARD SALESMAN 7698 22-02- 1250 500 30
1981
7566 JONES MANAGER 7839 02-04- 2975 20
1981
7654 MARTIN SALESMAN 7698 28-09- 1250 1400 30
1981
7698 BLAKE MANAGER 7839 01-05- 2850 30
1981
7782 CLARK MANAGER 7839 09-06- 2450 10
1981
7788 SCOTT ANALYST 7566 19-04- 3000 20
1987
7839 KING PRESIDENT 17-11-1981 5000 10
7844 TURNER SALESMAN 7698 08-09-1981 1500 0 30
7876 ADAMS CLERK 7788 23-05-1987 1100 20
7900 JAMES CLERK 7698 03-12-1981 950 30
7902 FORD ANALYST 7566 03-12-1981 3000 20

6
2
7934 MILLER CLERK 7782 23-01-1982 1300 10

14 rows
selected.

6
3
ASSIGN EXPLICIT DEFAULTS IN A TABLE
In Oracle's data definition language (DDL), the Oracle DBA has the ability to assign
default values to any data column. This feature removes the tedium of naming all
of the column values for every INSERT statement.

Create
table
Student
(
student_id number,
student_rank varchar2(3) DEFAULT 'FRESHMAN'
);

When using a default value, the developer would simply omit the

default column: Insert into student (student_id) values 'Jones';

In the above SQL, it's not clear that the default value of 'FRESHMAN' was inserted
into the table.
Starting in Oracle9i, the DEFAULT keyword can be used to explicitly assign the
column's default value during any INSERT or UPDATE statement:

insert into
student
values
('jones', DEFAULT);

update
stude
nt
set
student_rank = DEFAULT
where
student_id = 12345;

GAIN EXPLICIT CONTROL WITH SQL USING AND ON CLAUSES

As part of the Oracle9i enhancements to ISO 99 SQL, Oracle has added new USING
and ON clauses for performing natural joins. This gives additional flexibility to the
developer, since these clauses are able to explicitly control the columns that are used
in the table join.
The USING clause is called on if several columns can be used to join the tables. Here
are examples showing the syntax differences:

 Oracle8i:
Select last_name,
department_name From
employees e, departments d
Where e.department_id = d.department_id;

 Oracle9i:
6
4
Select last_name,
department_name From
employees e, departments d
USING (department_id);

The ON clause is used to join tables where the column names do not match in both
tables:

6
5
  Oracle8i:
Select last_name,
department_name From
employees e, departments d
Where e.department_id =
d.dept_id;

 Oracle9i
Select last_name,
department_name From
employees e, departments d ON
(department_id = dept_id);

It's important to note that the old-fashioned Oracle SQL:join syntax continues to be
supported, and that this new enhancement is most important for porting non-Oracle
systems into an Oracle database.

6
6
 RENAME A COLUMN WITHIN A TABLE IN ORACLE8i
Last time, we told you that Oracle8i offers a new way to rename a column in an
Oracle table without having to copy the table, which was an update from a recent tip
that focused on Oracle7 and Oracle8 ("Rename a column within an Oracle table," July
11, 2001).
We received several reader requests for the procedure, so here's how you can use
this Oracle8i technique. Starting in Oracle8i, you can use the Add Column and Drop
Column options to rename a column within a table. While the position of the column
within the table moves, the column is effectively renamed with the existing values.

Here's an example of this procedure:

drop table temp_don;
create table temp_don (old_first_column char(20), old_second_column
char(20) ); insert into temp_don values ( 'string1','string2' );
desc temp_don;
alter table temp_don add (new_first_column
char(20)); desc temp_don;
select * from temp_don;
update temp_don set new_first_column =
old_first_column; select * from temp_don;
alter table temp_don drop column
old_first_column; desc temp_don;
select * from temp_don;

6
7
Here's the output from the script:
SQL> create table temp_don (old_first_column char(20),old_second_column
char(20)); SQL> insert into temp_don values ('string1','string2');
SQL> desc temp_don;
Name Null? Type

OLD_FIRST_COLUMN CHAR(20)
OLD_SECOND_COLUMN
CHAR(20)

SQL> alter table temp_don add (new_first_column

char(20)); SQL> desc temp_don;
Name Null? Type

OLD_FIRST_COLUMN CHAR(20)
OLD_SECOND_COLUMN
CHAR(20)
NEW_FIRST_COLUMN CHAR(20)

SQL> select * from temp_don;

OLD_FIRST_COLUMN OLD_SECOND_COLUMN NEW_FIRST_COLUMN

string1 string2

SQL> update temp_don set new_first_column =

old_first_column; SQL> select * from temp_don;
OLD_FIRST_COLUMN OLD_SECOND_COLUMN NEW_FIRST_COLUMN

string1 string2 string1

SQL> alter table temp_don drop column

old_first_column; SQL> desc temp_don;
Name Null? Type

OLD_SECOND_COLUMN
CHAR(20)
NEW_FIRST_COLUMN CHAR(20)

SQL> select * from temp_don;

OLD_SECOND_COLUMN NEW_FIRST_COLUMN

string2 string1

6
8
 DCL (Data Control Language )

ROLES: IT IS A PRIVILEGE OF THE DBA

We have tow kinds of privileges :
1. Object Privilege:
2. System Privilege: Building Users, Views, Revoke,,,,,

SQL> CONN SYSTEM/MANAGER

Connected.

SQL> CREATE USER X IDENTIFIED BY X;

User created.

SQL> GRANT CREATE SESSION TO X;

Grant succeeded.

SQL> CONN X/X

Connected.

SQL> CREATE TABLE YY (N NUMBER);

CREATE TABLE YY (N NUMBER)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> CONN SYSTEM/MANAGER

Connected.

SQL> GRANT CREATE TABLE TO X;

Grant succeeded.

SQL> CREATE TABLE YY (N NUMBER);

Table created.

SQL> CONN X/X

Connected.

SQL> CREATE TABLE YY (N NUMBER);

CREATE TABLE YY (N NUMBER)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> CONN SYSTEM/MANAGER

Connected.

SQL> ALTER USER X QUOTA 5M ON USER_DATA;

User altered.

SQL> CONN X/X

6
9
Connected.

7
0
SQL> CREATE TABLE YY (N NUMBER);
CREATE TABLE YY (N NUMBER)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> CONN SYSTEM/MANAGER

Connected.

SQL> ALTER USER X DEFAULT TABLESPACE USER_DATA;

User altered.

SQL> CREATE TABLE Y(N NUMBER);

Table created.

TYPES OF ROLES:
1. Connect : Privilege to Enter a database
2. Resource : Privilege to deal with a database
3. DBA :

SQL> REVOKE SELECT ON YY FROM X;

SQL> DROP USER X;

IT WILL NOT WORK IF X GAVE SOME PRIVILEGES TO ZZ USER, SO TO CONTINUE DROP

WE HAVE TO ADD "CASCADE"

SQL> DROP USER X CASCADE;

it will drop X and all users Privileges they created

7
1
 WHAT IS A BATCH v7.0?
A batch is a set of commands sent to SQL Server as one unit of work. The following
will outline what happens when SQL Server receives a batch. The batch separator is
the GO keyword.
The examples below should assist in this concept. Assume the following table
structure. CREATE TABLE TableX (Col1 INT NOT NULL).

Batch #1
INSERT INTO TableX (Col1) VALUES
(1) INSERT INTO TableX (Col1)
VALUES (2) GO

Batch #2
INSERT INTO TableX (Col1) VALUES
(3) INSERT INTO TableX (Col1) VALU
(4) GO

Batch #3
INSERT INTO TableX (Col1) VALUES (NULL)
INSERT INTO TableX (Col1) VALUES (5)
GO

Upon submitting a batch to SQL, the server takes it as a string of T- SQL commands
and parses the string for syntax. If no syntax error(s) are found, as in Batch #1, then
all of the statement(s) are executed. If a syntax error is found, as in Batch #2, then
none of the statements are executed and SQL Server returns an error to the client.
Note that Batch #3 is syntactically correct but will violate the NOT NULL constraint.
This is still considered a syntax-valid batch so, even though the INSERT NULL
command will fail, the second command is valid and will be executed/inserted into
the table. Values 1, 2, and 5 will be inserted. Note that this behavior applies for
constraint violations. Some run-time errors stop the current statement as well as
subsequent statements within the batch.
The following rules for batches are reprinted from MSDN Books Online:
1. CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and
CREATE VIEW statements cannot be combined with other statements in a batch.
2. A table cannot be altered and then the new columns referenced in the same
batch.
3. If an EXECUTE statement is the first statement in a batch, the EXECUTE
keyword is not required. The EXECUTE keyword is required if the EXECUTE
statement is not the first statement in the batch.

7
2
Oracle9i'S NEW BUILT-IN FUNCTIONS
Oracle9i is continuing the company's commitment to extending the SQL language by
providing numerous new built-in functions. As you may recall, Oracle started allowing
end users to create their own functions beginning with Oracle8.
A user can take just about any function imaginable and encapsulate and use it
directly inside an Oracle SQL statement. Continuing with Oracle's commitment
toward e-commerce systems, Oracle9i introduces more than 30 different built-in
functions, extending the functionality of all the Oracle SQL to provide a wealth of
business-related functions.
Several of these new functions are built-in datetime functions, which allow worldwide
systems to properly manage times.
The primary purpose of these built-in functions is to translate datetimes for both local
and global processing. For example, in a worldwide e-commerce system, it's
important to understand the local time where the customer placed the order,
especially when the company makes shipping commitments based on the local
processing time. But the centralized headquarters still needs to be able to extract the
dates based on a global time, such as Greenwich Mean Time.
Here are some of the new datetime built-in functions:
 CURRENT_DATE
 CURRENT_TIMESTAMP
 LOCALTIMESTAMP
 DBTIMEZONE
 SESSIONTIMEZONE
 EXTRACT (datetime)
 FROM_TZ
 TO_TIMESTAMP
 TO_TIMESTAMP_TZ
 TO_YMINTERVAL
 TZ_OFFSET

CREATE EXTERNAL TABLES TO ACCESS NON-ORACLE FILES

Oracle9i has the ability to take data directly from operating system files and make it
appear to Oracle as if it were a table inside the database.
Oracle9i has extended its interfaces with the operating system to allow any type of
flat file to behave as if it were a relational table, allowing you to write virtually any
kind of SQL against a house standard relational table. In fact, you can even take
Microsoft Excel spreadsheet files (.xls files) and make them appear to be tables inside
Oracle9i.
This external table functionality is especially useful for Oracle data warehouses where
metadata comes in frequently. Rather than taking the time to use the Oracle
SQL*Loader utility to put the data into the database, you can leave the information in
the flat file and create an external table upon the flat file. Using this technique, the
information will behave as if it were part of the Oracle database, when in reality, the
information is external to Oracle.
Let's look at a simple example. We'll start with a comma-delimited flat file, as shown
below.

7
3
7369,SMITH,CLERK,7902,12-JAN-98,800,20
7499,ALLEN,SALESMAN,7698,03-MAR-96,1600,300,30
7521,WARD,SALESMAN,7698,27-APR-97,1250,500,30
7566,JONES,MANAGER,7839,20-OCT-97,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-98,1250,1400,30
7698,BLAKE,MANAGER,7839,11-NOV-98,2850,,30
7782,CLARK,MANAGER,7839,29-DEC-97,2450,,10
7788,SCOTT,ANALYST,7566,21-SEP-96,3000,,20
7839,KING,PRESIDENT,,27-MAY-98,5000,,10
7844,TURNER,SALESMAN,7698,24-OCT-98,1500,0,30
7876,ADAMS,CLERK,7788,12-JUN-97,1100,,20

Using the following commands, we can define this file as an Oracle

table. Create directory blah as '/home4/teach17'
create table
external_emp ( EMPNO
NUMBER(4), ENAME
VARCHAR2(10), JOB
VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM
NUMBER(7,2),
DEPTNO
NUMBER(2))
Organization external
(type oracle_loader default directory BLAH access
parameters (records delimited by newline fields
terminated by ',')
location
('[Link]'))
reject limit 1000;

Now, we can issue any SQL against the table. However, there are a couple of
important limitations to external tables: You can't use DML operations, and you
can't create indexes on external tables. Also, external tables have a processing
overhead, and they aren't suitable for large tables.

SPEED UP QUERIES WITH INDEX SKIP SCANS

The index skip scan is a new execution plan in Oracle9i, where an Oracle query can
bypass the leading edge of a concatenated index and access the inside keys of a
multivalues index. Let's look at an example.
Consider the following concatenated index:
create index sex_emp_id on emp (sex, emp_id) ;

Prior to Oracle9i, we could only use this index when both sex and emp_id were
present in the SQL query or when the query specified the sex column. The following
query wouldn't be able to use the concatenated index:

7
4
select emp_id from emp where emp_id = 123;

he Oracle9i skip scan execution plan allows for the use of the concatenated index,
even though the SQL query doesn't specify sex. This feature promises that there's
no need to provide a second index on the emp_id column. Oracle acknowledges that
the index skip scan is not as fast as a direct index lookup, but it states that the
index skip scan is faster than a full table scan.

7
5
What Oracle doesn't mention is that the cardinality of the leading column has a direct
impact on the speed of the index skip scan. In our example, the first column, sex, has
only two columns. While Oracle doesn't publish the internals of the index skip scan,
we can infer from the execution plans that Oracle is internally generating multiple
queries, thus satisfying the query with multiple subqueries.

SELECT STATEMENT Optimizer=CHOOSE

(Cost=6 Card=1 Bytes=5)
0 SORT (AGGREGATE)
1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE) Internally,

Oracle is probably generating two queries and joining the resulting

ROWID lists. select emp_name from emp_where sex = 'F' and emp_id
= 123 UNION
select emp_name from emp_where sex = 'M' and emp_id = 123;

The implications of using the index skip scan are clear:

 Oracle skip scan execution plan performance will decrease according to the
number of unique values in the high order key. If the leading column were
"state" with 50 values, Oracle would be issuing 50 index probes to retrieve the
result set.
 The index skip scan is only useful in shops where disk space savings are
critical. Shops that can afford the disk space to build a second index will
always get faster performance.

UNDERSTAND THE NEW NULLIF AND COALESCE SQL FUNCTIONS

Before Oracle9i, handling a NULL value meant either using NVL or a complex
DECODE statement. NVL's function is to default a NULL expression to an alternative
expression. For example, NVL(col,'N/A') means "if col is NULL, then replace this value
with 'N/A'."

If you wanted something logically reversed, say "if col = 'N/A' then return NULL," you
had to use some kind of DECODE statement:
DECODE(col,'N/A',null,col).

Oracle9i introduces a new SQL function that accomplishes the same thing more
succinctly: NULLIF(col,'N/A'). That is, "if col = 'N/A' then return NULL, otherwise return
the column."

One difference between the NULLIF function and NVL is that NVL is actually
comparing its first argument to NULL and returning the second, so the first and
second arguments can be any datatype. The NULLIF command does a comparison
between its two arguments, so the datatype of its arguments must be compatible.

For example, NULLIF(null,'N/A') or NULLIF('a',1) will return an ORA-932 "inconsistent

datatypes" error. It's important to make sure that both arguments are the same
datatype, preferably a string. The previous two examples will work if you write them
as:
NULLIF('','N/A') and NULLIF('a',to_char(1)).

7
6
COALESCE
Oracle9i also introduces a related function for handling NULL values,called
COALESCE, that can take any number of arguments and return the first expression
in its list that is not NULL--or that is NULL, if all of the expressions evaluate to null.
It's actually an NVL with an unlimited number of arguments.

7
7
COALESCE(col,'N/A') is logically identical to NVL(col,'N/A'). The only way to do a
longer version, such as COALESCE(col1,col2,col3,'N/A'), would e to use
ECODE(col1,null,col2,null,col3,null,'N/A').

COALESCE has a different limitation. It must have at least two arguments, one of
which is not the literal NULL. COALESCE('a') will issue an ORA-938 error, "not
enough arguments for function," but so will COALESCE(NULL,NULL).

Like NULLIF, COALESCE arguments must have compatible datatypes. For example,
COALESCE('A',1) will get an
ORA-932 error, "inconsistent datatypes: expected CHAR got NUMBER."
So all the datatypes in the expression have to be cast to an appropriate datatype:
COALESCE('A',to_char(1)).

EXECUTE THE NEW INDEX SKIP SCAN

The index skip scan is an execution plan in Oracle9i whereby an Oracle query can
bypass the leading edge of a concatenated index and access the inside keys of a
multivalues index. For example, consider the following concatenated index:

create index
sex_emp_id
on
emp (sex, emp_id)
;

Prior to Oracle9i, this index could only be used when both sex and EMP_ID were
present in the SQL query, or when the sex column was specified.
The following query would not be able to use the

concatenated index: Select emp_id from emp where

emp_id = 123;

The Oracle9i skip scan execution plan allows for the concatenated index to be used,
even though sex is not specified in the SQL query. This feature means that there is
no need to provide a second index on the EMP_ID column. Oracle acknowledges
that the index skip scan is not as fast as a direct index lookup, but states that the
index skip scan is faster than a full-table scan.

What Oracle does not mention is that the cardinality of the leading column has a
direct impact on the speed of the index skip scan. While Oracle does not publish the
internals of the index skip scan, we can infer from the execution plans that Oracle is
internally generating multiple queries, thereby satisfying the query with multiple
subqueries:

7
8
SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
0 SORT (AGGREGATE)
1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE)

Internally, Oracle9i is probably generating two queries and joining the resulting

Row ID lists: select emp_name from emp_where sex = 'F' and emp_id = 123
UNION
select emp_name from emp_where sex = 'M' and emp_id = 123;

Oracle skip scan execution plan performance will decrease according to the number
of unique values in the high order key. If the leading column were "state" with 50
values, Oracle would be issuing 50 index probes to retrieve the result set.

The index skip scan is only useful in shops where disk space savings are critical.
Shops that can afford the disk space to build a second index will always get faster
performance.

ISSUE SERVER MANAGER COMMANDS WITH SQL*PLUS

Over the past five years, Oracle has been slowly phasing out support for the old
server manager interface. The old Oracle server manager provided a way to enter
special database administration functions such as startup and shutdown commands
for the Oracle server, as well as special DBA
commands like oradebug and the display of internal block
structures. For example:

svrmgrl
connect
internal
startup

Starting with Oracle9i, the server manager has been removed, and you must now use
server manager commands within the Oracle SQL*Plus product.
Oracle allows you to connect to SQL*Plus with AS SYSDBA or AS SYSOPER
commands. Once connected to the database, the Oracle DBA can startup the
database, shutdown the database, and perform other administrative tasks such as
providing trace files and using the oradebug utility.
In the following examples, you can see how Oracle is used to connect to the database
and issue startup and shutdown commands:

sqlplus /nolog
connect / as
sysdba startup

sqlplus /nolog
connect system/manager as sysdba
oradebug ipc

These commands can also easily be placed inside shell script to perform routine
database functions:
7
9
#!/bin/ksh

8
0
# First, we must set the environment . . . .
ORACLE_SID=mon1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -
d':'` export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus /nolog<<!

connect system/manager as
sysdba select * from v\
$database; shutdown
immediate

exit
!

SHOW PROCESS DETAIL WITH THE V$SESSION VIEW

A little-known feature of the v$session view is the ability to decode the actual
command that's being executed by an individual session. The information is coded in
a special column called v$[Link].
As we select from the v$session view, we decode each command according to its
internal representation. This will allow us to create a data dictionary query that
shows each individual user, the program they're executing, and the current
command within that program that is being executed in their system.
This is extremely valuable when you need to find out quickly what is going on within
their Oracle systems.
This simple script illustrates the v$[Link]:

select
substr([Link],1,18) username,
substr([Link],1,15) program,
decode([Link],
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate
Index', 35,'Alter
Database',
39,'Create
Tablespace', 41,'Drop
Tablespace', 40,'Alter
Tablespace', 53,'Drop
8
1
User', 62,'Analyze
Table', 63,'Analyze
Index',
[Link]||': Other') command

8
2
from v$session s, v$process p, v$transaction t, v$rollstat
r, v$rollname n where [Link] = [Link] and
[Link] = [Link] (+)
and [Link] = [Link] (+) and [Link] =
[Link] (+) order by 1;
Here is a sample of the output, showing the individual command for each session:

USERNAME PROGRAM COMMAND

APPS f45runm@corp-hp Select

APPS S:\ORANT\BIN\F5 Insert
APPS S:\ORANT\BIN\R3 No
Command
APPS f45runm@corp-hp Select
APPS S:\ORANT\BIN\R3 Select
APPS S:\ORANT\BIN\R3 No
Command
APPS f45runm@corp-hp Update
APPS S:\ORANT\BIN\R3 No
Command
MWCEDI [Link] No
Command
PERFSTAT [Link] Select
PERFSTAT [Link] No
Command

USE ESCAPE CHARACTERS IN SQL FOR LITERAL INTERPRETATION

Oracle allows the assignment of special escape characters to tell the database that
the character in a SQL query is to be interpreted literally.
This ability is required because certain characters with special meaning, such as the
underscore "_", are not interpreted literally.
In the example below, we want to find all Oracle parameters that contain or relate
to I/O, so we're tempted to use the filter LIKE '%_io_%'.
Below we'll select from the x$ksppi fixed table, filtering with the

LIKE clause: SQL> select ksppinm from x$ksppi where ksppinm like

'%_io_%';

KSPPINM

sessions
license_max_sessions
license_sessions_warni
ng
_session_idle_bit_latches
_enable_NUMA_optimizati
on
java_soft_sessionspace_li
mit
java_max_sessionspace_si
8
3
ze
_trace_options
_io_slaves_disabled
dbwr_io_slaves
_lgwr_io_slaves

As you can see above, we didn't get the answer we expected. The SQL displayed all
values that contained "io", and not just those with an underscore.
To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that
the character is to be interpreted literally:

8
4
SQL> select ksppinm from x$ksppi where ksppinm like '%\_io\_%' ESCAPE'\';

KSPPINM

_io_slaves_disabled
dbwr_io_slaves
_lgwr_io_slaves
_arch_io_slaves
_backup_disk_io_slaves
backup_tape_io_slaves
_backup_io_pool_size
_db_file_direct_io_count
_log_io_size
fast_start_io_target
_hash_multiblock_io_count
_smm_auto_min_io_size
_smm_auto_max_io_size
_ldr_io_size

AVOID WAITING WITH FOR UPDATE WAIT

The FOR UPDATE clause is available in the Oracle SQL syntax to allow developers to
lock a set of Oracle rows for the duration of a transaction.
The FOR UPDATE clause is generally used in cases where an online system needs to
display a set of row data on a screen, and it needs to ensure that the data doesn't
change before the end user has an opportunity to update the data.
However, the FOR UPDATE clause in SQL will cause the system to hang if another
user locks one of the requested rows. If you try to access the rows with the NOWAIT
clause, you will get an error message.
Essentially, the options prior to Oracle9i were either "wait forever" or "don't wait."
Oracle9i has added additional flexibility to the syntax by allowing the SQL to wait
for a predefined amount of time for locked rows to release before aborting.
In this example, we select a student row and wait up to 15 seconds for
another session to release its lock:

Select
student_last_name
From student
where student_id =
12345 FOR UPDATE
WAIT 15;

In the real world, many large online systems do not use the FOR UPDATE clause.
Rather, they ensure data integrity by rereading the data when the end user requests
a change. If the data has changed since it was initially displayed to the user, an error
message is displayed.

8
5
COMBINE TABLES WITH THE SQL MERGE STATEMENT

Oracle9i introduces a new SQL statement that is intended to allow you to merge two
tables in a single statement. Rather than write two SQL statements, an UPDATE to
update rows that exist in both tables, and an INSERT to add rows that don't exist in
the merging table, you can now write a single SQL MERGE statement that will do
both.

create table inventory (part_no integer,part_count

integer); insert into inventory values(1,5);
insert into inventory values(3,6);

create table shipment (part_no integer,part_count

integer); insert into shipment values(1,2);
insert into shipment values(2,2);

MERGE INTO inventory

USING shipment
ON (inventory.part_no =
shipment.part_no) WHEN MATCHED
THEN
UPDATE SET part_count = part_count +
shipment.part_count WHEN NOT MATCHED THEN
INSERT VALUES (shipment.part_no,shipment.part_count);

commit;
select * from inventory;

PART_NO PART_COUNT

1 4
3 6
2 2

The result is that the shipment data has been merged into the inventory data, so
that any shipment that matched something in inventory was added to the count,
while anything that didn't match was added to the inventory.

You must specify both a WHEN MATCHED and WHEN NOT MATCHED statement. If
you have a case for excluding either one of them, you probably should use a
regular INSERT or UPDATE statement.

Another point is that you can't use the MERGE statement to modify a row more
than once, and you can't modify the column being referenced in the ON clause.

The target table of the MERGE statement (which is inventory in the example) must
be a table or view into which the user can INSERT and UPDATE.
The source table (which is shipment in the example) can be from any query table
source, such as external tables or pipelined table functions.

8
6
PERFORM CASE-INSENSITIVE QUERIES WITH FUNCTION-BASED INDEXES
One of the fundamental rules of optimizing an Oracle database is that applying a
function to an index will disable the use of that index, such as in this query:

create table surnames (name

varcahr2(200)); create index surnames_idx
on surnames(name);
...
select name from surnames where upper(name) = 'SCOTT';
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'SURNAMES'

Internally, the index has stored a B-Tree algorithm to the name in mixed case. It
probably wouldn't be useful to try to scan the index with a function applied to the
value, so access to the index is disabled.
To work around this restriction, many applications would build tables with a mixed-
case column and a forced-uppercase column:

create table surnames2 (name varchar2(200),name_upper

varchar2(200)); create index surnames2_idx on
surnames2(name_upper);
create or replace trigger surnames2_trg
before update or insert on surnames2 for
each row begin
:new.name_upper :=
upper(:[Link]); end
surnames2_trg;
/
...
select name from surnames2 where name_upper = 'SCOTT';
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACESS (BY INDEX ROWID) OF 'SURNAMES2'
2 1 INDEX (RANGE SCAN) OF 'SURNAMES2_IDX' (NON-UNIQUE)

This plan requires storage of every case-insensitive column twice, in addition to the
original column. What is really needed is some way of organizing the index B-Tree so
it stores the uppercase version of the column.
Oracle8 introduced the function-based index to do just that. It stores the result of a
function as the key used by the index to locate rows.
If the same function is being applied to the same column, the optimizer will use the
index to locate the value.
Therefore, you could take the first table and apply a function-based index on the
column:

create index surnames_upidx on

surnames(upper(name)); analyze table surnames
compute statistics;
select name from surnames where upper(name) = 'SCOTT';

0 SELECT STATEMENT Optimizer=CHOOSE (Const=2 Card=1 Bytes=8)

8
7
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SURNAMES' (Cost=2 Card=1
Bytes=8)
2 1 INDEX (RANGE SCAN) OF 'SURNAMES_UPIDX' (NON-UNIQUE) (Cos
t=1 Card=1)

In order for a schema to use function-based indexes, it needs to be granted query

rewrite status:

8
8
GRANT QUERY REWRITE TO <user>;

Also, notice that function-based indexes are only used in cost-based optimizations, so
the table must be analyzed before the index will be used.

8
9
GENERATE HTML REPORTS FROM SQL*PLUS RESULTS

SQL*Plus is primarily a character mode terminal application. However, since version

8.1.6, SQL*Plus has the ability to format its output in HTML format. To enable HTML
output, use the command:

SQL> set markup html on

After it's turned on, several characters, such as >, <, and & are automatically
changed to HTML entities. You can disable this feature by adding the option ENTMAP
OFF.

SQL&gt; set markup html entmap

off; SQL>

When you issue a query, the results will be generated as an HTML TABLE, TR, and
TD instead of as the usual formatted ASCII output:

SQL> select sysdate from dual;

<br>
<p>
<table border="1" width="90%">
<tr>
<th>
SYSDATE
</th>
</tr>
<tr>
<td>
30-MAR-03
</td>
</tr>
</table>
<p>

If you want to see the results as a preformatted HTML PRE element, use the option
PREFORMAT ON:

SQL> set markup html preformat

on; SQL> select sysdate from dual;
<br>
<pre>

SYSDATE

30-MAR-03
</pre>

If you specify SPOOL ON, SQL*Plus will generate headers to the spool file when you
issue the SPOOL command.
9
0
You can change the HEAD tag contents, BODY tag, and TABLE tag attributes in the
output by specifying those tag names as options:

9
1
SQL> set markup html head '<style type="text/css">body
{background: white}</style>' -
>body 'bgcolor="#FFFFFF"' -
>table 'width="100%"
border=0' SQL> select
sysdate from dual;
<br>
<p>
<table width="100%" border=0>
<tr>
<th>
SYSDATE
</th>
</tr>
<tr>
<td>
30-MAR-03
</td>
</tr>
</table>
<p>

With MARKUP HTML ON enabled, the PAGESIZE, LINESIZE, TTITLE, BTITLE, and
COLUMN commands are reflected in the HTML output.
You can enable MARKUP HTML commands and options from the command line, like
this:

REM -- [Link]
select * from emp order by
empno; exit;

sqlplus -s -m "html on" scott/tiger @report > [Link]

Using this method, you can run an old character mode SQL*Plus report and generate
HTML without modifying the script.

9
2
USE @URL AND @@URL IN SQL*PLUS

Oracle9i's SQL*Plus introduces some new functionality with the @<script>

command. Previously, the @ command would run a SQL script from a file.
The @@ would also run a script, but relative to the same directory of The current
script.
In Oracle9i, SQL*Plus can now accept either an HTTP or FTP URL instead of a
filename to run the script. For example, @ [Link] will
load the SQL script from the URL and run it under the current environment. The
@@URL command understands that it's currently running from a URL and will load
the script from the same location. For example, if the script loaded above contained
the following code, each SQL script would be loaded relative to the given URL.

REM - setup script

@@[Link]
@@[Link]
@@[Link]

This is valuable for a DBA wishing to centralize maintenance scripts at a remote

location that can be run against databases in different locations. This is especially
valuable if the client doesn't have SQL*Net access to the database, but may have
HTTP access to the scripts.
Over the HTTP protocol, a script may be dynamically generated based on some
input parameters by a Web server. For example,
[Link] passes the
parameter host=bart to the CGI-BIN
script. SQL*Plus has a separate parameter passing facility.

These parameters are bound to DEFINE parameters after the script is loaded. The
@@[Link] mytablespace will load the script [Link] from the same relative
directory, then mytablespace will be bound to the SQL*Plus parameter &1. One
difference in the @URL functionality is that the .sql extension is not optional.

9
3
SIMULATING @URL IN ORACLE8i

One drawback to this feature is that it doesn't seem to support HTTP proxy. Scripts
must be loaded from direct connections. One workaround for access via a proxy or
simulating this functionality in Oracle8i is this script:

REM --
http_at.sql set
feedback off set
linesize 255
set serveroutput on size
64000 set trimspool on
spool
[Link]
declare
l_pieces
utl_http.html_pieces;
l_piece varchar2(2000);
l_url varchar2(2048) :=
'&1'; l_maxline integer :=
255; l_index integer;
l_stub varchar2(255);
l_proxy varchar2(2048) :=
'[Link]'; begin
l_pieces :=
utl_http.request_pieces(l_url,1000,l_proxy); for i
in l_pieces.first .. l_pieces.last loop
l_piece := l_pieces(i);
while length(l_piece) > l_maxline
loop l_stub :=
substr(l_piece,1,l_maxline);
l_index := length(l_stub);
while l_index > 0 and substr(l_stub,l_index,1) != chr(10)
loo
p l_index := l_index
- 1; end loop;
l_stub :=
substr(l_piece,1,l_index-1);
l_piece :=
substr(l_piece,l_index+1);
dbms_output.put_line(l_stub);
end loop;
dbms_output.put_line(l_piece);
end
loop; end;
/
spool off;

Here is an example of using the above script on some real

9
4
data: sqlplus scott/tiger
@http_at
[Link]
@test

9
5