Advance Table

SQL> CREATE TABLE SampDept

2 AS

3 SELECT * FROM Dept

4 /

Table created.

SQL> DESC Dept

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> DESC SampDept

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept1(

2 DeptID,

3 DeptName,

4 Place

5 )

6 AS

7 SELECT * FROM Dept

8 /

Table created.

SQL> DESC SampDept1

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTID NOT NULL NUMBER(2)

DEPTNAME VARCHAR2(14)

PLACE VARCHAR2(13)

SQL> SELECT * FROM SampDept1;

DEPTID DEPTNAME PLACE

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept2

2 AS

3 SELECT Deptno, Dname

4 FROM Dept

5 /

Table created.

SQL> DESC SampDept2

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

SQL> CREATE TABLE SampDept3

2 AS

3 SELECT * FROM Dept

4 WHERE 1 = 2

5 /

Table created.

SQL> DESC SampDept3

Name Null? Type

----------------------------------------- -------- ----------------------------

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept3;

no rows selected

SQL> CREATE TABLE SampleTablePart

2 (

3 SampID NUMBER(4)

4 CONSTRAINT SampIDPK PRIMARY KEY,

5 SampName VARCHAR2(20)

6 CONSTRAINT SampNameNN NOT NULL

7 CONSTRAINT SampNameCHK

8 CHECK(SampName = UPPER(SampName)),

9 SampDate DATE DEFAULT SYSDATE

10 CONSTRAINT SampdateNN NOT NULL,

11 SampDesc VARCHAR2(4000)

12 DEFAULT 'NOT PROVIDED'

13 )

14 PARTITION BY RANGE(SampID)

15 (

16 PARTITION SampIDPart1

17 VALUES LESS THAN (500),

18 PARTITION SampIDPart2

19 VALUES LESS THAN (1000),

20 PARTITION SampIDPart3

21 VALUES LESS THAN (2000),

22 PARTITION SampIDPart4

23 VALUES LESS THAN (3000),

24 PARTITION SampIDPart5

25 VALUES LESS THAN (4000),

26 PARTITION SampIDPart6

27 VALUES LESS THAN (MAXVALUE)

28 );

Table created.

SQL> cl scr

CREATE TABLE EmpTableHash

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10, 20, 30, 40, 50, 60, 70, 80, 90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpTableHash(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY HASH(DeptNO)

PARTITIONS 9

CREATE TABLE EmpSampleList1

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK1 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN1 NOT NULL

CONSTRAINT ENameCHK1

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN1 NOT NULL

CONSTRAINT JobCHKUpper1

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList1 CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange1

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList1

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf1

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN1 NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN1 NOT NULL

CONSTRAINT SalChkRange1

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN1 NOT NULL

CONSTRAINT CommChkRange1

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH1

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK1

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPresident

VALUES('PRESIDENT'),

PARTITION JAnalyst

VALUES('ANALYST'),

PARTITION JManager

VALUES('MANAGER'),

PARTITION JSalesman

VALUES('SALESMAN'),

PARTITION JClerk

VALUES('CLERK')

);

CREATE TABLE EmpSampleList

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPartList1

VALUES(

'PRESIDENT',

'ANALYST'

),

PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN',

'CLERK'

)

);

CREATE TABLE EmpSampleSubPart

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK2 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN2 NOT NULL

CONSTRAINT ENameCHK2

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN2 NOT NULL

CONSTRAINT JobCHKUpper2

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList2

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2) CONSTRAINT

DeptnoCHKRange2 CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList2

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf2

REFERENCES EmpSampleSubPart(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN2 NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN2 NOT NULL

CONSTRAINT SalChkRange2

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN2 NOT NULL

CONSTRAINT CommChkRange2

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKSubPart2

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK2

CHECK(Sal + Comm <= 175000)

)

PARTITION BY RANGE(Ename)

SUBPARTITION BY HASH(Job)

SUBPARTITIONS 5

(

PARTITION NameP1

VALUES LESS THAN('G'),

PARTITION NAMEP2

VALUES LESS THAN('N'),

PARTITION NAMEP3

VALUES LESS THAN('T'),

PARTITION NAMEP4

VALUES LESS THAN(MAXVALUE)

)

/

ALTER TABLE SampleTablePart

SPLIT PARTITION SampIDPart6

AT(5000)

INTO(

PARTITION SampIDPart6,

PARTITION SampIDPart7

)

/

ALTER TABLE EmpSampleList

SPLIT PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN'

)

INTO(

PARTITION MANAGER,

PARTITION SALESMAN

)

/

ALTER TABLE EmpSampleList

MERGE

PARTITIONS

MANAGER,

SALESMAN

INTO

PARTITION

JPARTLIST2

/

ALTER TABLE SampleTablePart

MERGE

PARTITIONS

SampIDPart6,

SampIDPart7

INTO

PARTITION

SampIDPart7

/

SYS_REFCURSOR Vs REF CURSOR

How to Use the SYS_REFCURSOR and REF CURSOR in Oracle Packages and Procedures

SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it and can be used in 9i or higher. SYS_REFCURSOR can be used to:

• Delcare a cursor variable in an Oracle stored procedure/function;
• Pass cursors from and to an Oracle stored procedure/function.

Exampel 1: Getting a cursor out from an Oracle Package/ProcedureThe following Oracle stored procedure demonstrates how to get a REFCURSOR cursor variable out from a package/procedure:

Package Specification Definition:

CREATE OR REPLACE PACKAGE XX_BI_PKG AS
PROCEDURE xx_ar_inv_proc(p_recordset OUT SYS_REFCURSOR);
END;
Package body definition:CREATE OR REPLACE PACKAGE BODY XX_BI_PKG IS
PROCEDURE xxnsn_ar_inv_proc(p_recordset OUT sys_refcursor)
AS
BEGIN
OPEN p_recordset FOR SELECT RCT.trx_date
,COUNT(RCT.customer_trx_id)
,SUM(APS.amount_due_original)
FROM ra_customer_trx_all RCT
,ar_payment_schedules_all APS
WHERE RCT.customer_trx_id = APS.customer_trx_id
AND APS.class = 'INV'
AND APS.status = 'OP'
GROUP BY RCT.trx_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END xx_ar_inv_proc;
END;

The following PL/SQL statement is used to harness the above Oracle stored procedure:

DECLARE
v_cursor sys_refcursor;
v_date varchar2(100);
v_count number(12);
v_amount number(12);
BEGIN
XX_BI_PKG.xx_ar_inv_proc(p_recordset =>v_cursor);
LOOP
FETCH v_cursor
INTO v_date, v_count, v_amount;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_date ' ' v_count ' ' v_amount);
END LOOP;
CLOSE v_cursor;
END;

STORED PROCEDURE & USER DEFINE FUNCTION

Stored Procedure                                          User define Function

1.Can return zero or n value                           1.Can return one value which is mandatory.
2.Can have input output parameter                  2.Can have only input parameter.
3.We can't call procedure in select             3.We can call functions in select statement.
    statement.
4.Procedures are normally used for           4.Functions are normally used for computations.
   executing business logic.
5.We can call Stored Procedure within      5.We can not call function within stored procedure. 
   function


                                   

EXISTS & IN in SQL

• When a subquery returns a NULL value then the whole statement become NULL. In that cases we are using the EXITS keyword. If we want to compare particular values in subqueries then we are using the IN keyword.

    Ex:-

   select *
   from emp
   where empno not in(select mgr from emp);   /*it through no rows selected because the inner query
                                                                          return NULL value*/

• If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.

•   When your inner query fetching thousand of rows then EXIST would be better choice.When your inner query fetching few rows, then IN will be faster
• EXIST evaluate on true or false but IN compare multiple value. When you don't know the record is exist or not, your should choose EXIST.

FORMAT_ERROR_BACKTRACE

FORMAT_ERROR_BACKTRACE in the DBMS_UTILITY package has been introduced in the Oracle version 10g. Format error backtrace is use find the exact position where the exception has occurred.
 When an exception is raised, one of the most important piece of information that a developer would like to know is the line of code that raised that exception  for that we are using .

FORMAT_ERROR_BACKTRACE
FORMAT_ERROR_STACK
FORMAT_CALL_STACK

DECLARE
BEGIN
   RAISE NO_DATA_FOUND;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('Error occured at...');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
      RAISE;
END;

Error occured at...
ORA-06512: at line 3
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8