DataBase

How to check grants on database objects in oracle

SELECT * FROM ALL_TAB_PRIVS
WHERE TABLE_NAME LIKE ‘TABLE_OR_PACKAGE_OR_PROCEDURE_OR_FUNCTION’;

Advertisements

Trigger Example

Trigger:-    Trigger is a pl/sql block or procedure that implicitly execute when some event occur.

Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or shutdown) occur on a schema or database

Trigger Timing
For table:- Before, After
For View:- Instead of

Trigger Event:-
Insert, Update or Delete

Trigger Name:-
On Table, View

Trigger Type:-
Row Level
Statement Level

Trigger Body:-
What Action perform

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,’DY’) = ‘SAT’
THEN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20500,’YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF UPDATING
THEN
RAISE_APPLICATION_ERROR(-20501,’YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE’);
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20502,’YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE’);
END IF;
END IF;
END XXC05_TRIGGER_TEST;

 

 

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENCT OF clause in an UPDATE or DELETE statement states that most recent row fetched from the table should be update or deleted. We must declare the cursor with the CURRENCT OF clause to use this feature.

Oracle provide the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of transactions.

The syntax of using the WHERE CURRENT OF clause is UPDATE OR DELETE statement.

Syntax:-

WHERE[CURRENT OF cursor_name | search_condition]

Example:-

DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL
FOR UPDATE OF COMMISSION_PCT;
BEGIN
FOR I IN C1
LOOP
UPDATE EMPLOYEES
SET COMMISSION_PCT= 0.19
WHERE CURRENT OF C1;
END LOOP;
END;

Ref Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but  it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week    ref cursor
Example 1-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_ID,L_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_ID||’ and ‘||’LAST_NAME IS  ‘||L_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_ID,DEPT_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example 2-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example of Strong ref cursor

DECLARE
TYPE XXC_EMP_REF_CUR IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
XXC_EMP_REF_CUR_C1 XXC_EMP_REF_CUR;
TYPE XXC_DEPT_REF_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
XXC_DEPT_REF_CUR_C1 XXC_DEPT_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_EMP_REF_CUR_C1;
OPEN XXC_DEPT_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_DEPT_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_DEPT_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_DEPT_REF_CUR_C1;
END;

SYS Ref Cursor:-

DECLARE
XXC_EMP_REF_CUR SYS_REFCURSOR;
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);
END LOOP;
END;

 

————————- normal corsor ———————
declare
cursor c1 is select * from employees;
v1 employees%rowtype;
begin
open c1;
for i in 1..3
loop
fetch c1 into v1;
dbms_output.put_line(v1.employee_id);
end loop;
close c1;
end;

—————– week ref cursor ——————–
declare
type week_ref_cursor is ref cursor;
wrf week_ref_cursor;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open wrf for select * from employees;
loop
fetch wrf into v1;
exit when wrf%notfound;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
close wrf;
open wrf for select * from departments;
loop
fetch wrf into v2;
exit when wrf%notfound;
dbms_output.put_line(v2.department_id||chr(9)||v2.department_name);
end loop;
close wrf;
end;

——————- strong ref cursor ————–

declare
type strong_ref_cursor is ref cursor return employees%rowtype;
src strong_ref_cursor;
v1 employees%rowtype;
begin
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id);
end loop;
close src;
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id||’    ‘||v1.last_name);
end loop;
close src;
end;

 

***************************************REF CURSOR *******************************

declare
emp_id number;
emp_name varchar2(20);
type test_cur is ref cursor;
v_ref test_cur;
begin
if(no=1)then
open v_ref for select employee_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
elsif(no=2)then
open v_ref for select department_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
else
open v_ref for select last_name from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_name;
dbms_output.put_line((emp_name));
end loop;
close v_ref;
end;
/

***************************************************** PARAMETERIZED CURSOR *************************************

declare
cursor c1(no number)is select employee_id from employees where employee_id<no;
id number;
begin
open c1(198);
for i in 1..10
loop
fetch c1 into id;
dbms_output.put_line(id);
end loop;
close c1;
end;

declare
type p1 is ref cursor;
t1 p1;
v1 varchar2(10);
begin
open t1 for select employee_id from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
open t1 for select last_name from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
end;
/

create or replace procedure p1 is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name);
end loop;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.salary||chr(9)||v1.manager_id);
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.manager_id);
end loop;
end if;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
type v1_table is table of employees%rowtype;
v1 v1_table;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).employee_id||chr(9)||v1(i).last_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id||chr(9)||v1(i).first_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
end if;
end;
/

 

Thanks

Sajal Agarwal

Set operators with examples

Set Operator:-
Set Operators combine the result of two or more component queries into a single result. Queries containing set operators are called compound queries.

1. UNION
2. MINUS
3. UNION ALL
4. INTERSECT

Table EMP

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000

Table EMP_DETAIL

ID    NAME    SALARY
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000

UNION:-
All distinct rows selected by either query. In UNION operator data is sorted in ascending order according to first column of first query.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
2    NAVAL    10000
3    SEEMA    50000
4    SUSHIL    90000
4    ANKIT    60000
5   AYUSH    30000

MINUS:-
All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
minus
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
2    SAJAL    20000
4    SUSHIL    90000

UNION ALL:-
All rows selected by either queries, including all duplicates.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
union all
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
2    SAJAL    20000
3    SEEMA    50000
4    SUSHIL    90000
1    AMAL    80000
2    NAVAL    10000
3    SEEMA    50000
4    ANKIT    60000
5   AYUSH    30000

INTERSECT:-
All distinct rows selected by both queries.

Example:-
SELECT ID,NAME,SALARY
FROM EMP
intersect
SELECT ID,NAME,SALARY
FROM EMP_DETAIL;

Output:-

ID    NAME    SALARY
1    AMAL    80000
3    SEEMA    50000

 

 

What is Sequence and examples

Sequence:-
Sequence is a database object, it use to generate a numeric value only.

SYNTAX:-

CREATE SEQUENCE sequence_name
[INCREAMENT BY n]
[{START WITH n}]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{NOCYCLE | CYCLE}]
[{NOCACHE | CACHE n}]

INCREAMENT BY n:- Specifies the interval between sequence of number(n is interger).By default in sequence number increment by 1.

START WITH n:- Specific the first sequence of number.By default in sequence number start with 1.

MAXVALUE n:- Specifies the maximum value sequence can be generate.

NOMAXVALUE:- Specifies a maximum value is 10^27.

MINVALUE n:- Specific the minimum value.

NOMINVALUE:- Specifies a minimum value is -10^26.

CYCLE:- Specifies whether the sequence continue to generate value after reaching maximum or minimum.

NOCYCLE:- After reaching maximum or minimum, sequence may not generate value and it give error. NOCYCLE is default option.

CACHE n | NOCACHE:- :- Specifies how many value the oracle preallocates and keeps in memory.(BY default it is 20).

NEXTVAL:- It returns the next value of the sequence.

CURVAL:- It returns the current value of the sequence.

Example:-

CREATE SEQUENCE XXC05_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100
MINVALUE 1
NOCYCLE
NOCACHE;

Modify Sequence:- We can modify sequence.

Syntax:-

ALTER SEQUENCE sequence_name
INCREAMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE | NOCACHE;

Note:- we can not modify starting value of sequence. Maximum value cannot less current value.

Example:-

ALTER SEQUENCE XXC05_SEQ
INCREAMENT BY 10
MAXVALUE 1000
MINVALUE -1000
CYCLE
CACHE;

Drop Sequence:- We can drop a sequence.

Syntax:-

DROP SEQUENCE sequence_name;

Example:-

DROP SEQUENCE XXC05_SEQ;

—————————————————–

SYNONYM:-
Synonym is the another or alternate name of the database object.
We can create synonym of TABLE, VIEW, SEQUENCE, INDEX, PROCEDURE, FUNCTION, TRIGGER, PACKAGE etc.

Syntax:-

CREATE [PUBLIC] S
SYNONYM synonym_name
FOR object_name;

Example:-

CREATE SYNONYM XXC05_SEQ_SYN
FOR XXC05_SEQ;

Note:- XXC05_SEQ is the name of sequence.

Drop Synonym:-

DROP SYNONYM synonym_name;

Example:-

DROP SYNONYM XXC05_SEQ_SYN;

——————————————————-

Control File:-

The LOAD DATA statement is required at the beginning of the control file.

INFILE * specifies that the data is found in the control file and not in an external file.

The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks.
Data types for all fields default to CHAR.

Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is specified, the default is a character of length 255.

BEGINDATA specifies the beginning of the data.

put control file in that path———>    /u02/oracle/visappl/wip/11.5.0/bin

Attach those ctl file in concurrent program

Program name———->    ANY CONCURRENT PROGRAM NAME

Execute that command:-

sqlldr user=apps/apps@vis control= control_file_name.ctl

Example:-

We create a control file XXC05_CONTROL_EMP.ctl

sqlldr user=apps/apps@vis control=XXC05_CONTROL_EMP.ctl

Note:- When we execute sqlloder command 3 more file created i.e log file, bad file, discard file with the same name of ctl file name.

Log File:- In that file we see the log details of control file. File extension is “file_name.log”.

Bad File:- Which date have logical error (for example:- datatype mismatch, length, etc) goes in that file. File extension is “file_name.bad”.

Discard File:- Which data are not satisfy in when condition goes in that file.

******************************************** 1 ****************************

LOad data
infile *
INSERT/APPEND/DELETE/TRUNCATE
into table XXC05_EMP
when dept_name=’HR’
fields terminated by ‘ ‘
(emp_id position(1:2),last_name position(4:5),salary position(7:9),dept_name position(11:12))
into table XXC04_emp2
when dept_name=’FF’
(emp_id position(1:2),dept_name position(11:12))
begindata
10 AA 200 HR
20 BB 300 HR
30 CC 400 MG
40 DD 500 FF

******************************************** 2 ****************************

Option (skip=1)
load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘ ‘
(emp_id,last_name,dept_name FILLER POSITION(1),salary)
begindata
10 HR 200
20 GG 300
30 AA 400

******************************************** 3 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 4 ****************************

load data
infile *
insert into table XXC05_EMPLOYEES
when (1:1)=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 5 ****************************

load data
infile *
replace into table XXC05_EMPLOYEES
when (1:1)!=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name,dept_name,rec_no recnum)
begindata
H,A
HR,B
AA,C
BB,D

******************************************** 6 ****************************

options(skip=2)– skip first 2 lines from the top
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ‘,’
(
image_id   INTEGER(5),
file_name  CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

******************************************** 7 ****************************

LOAD DATA
INFILE ‘/u02/oracle/visappl/wip/11.5.0/data/XXC05_DATA_FILE’
TRUNCATE
INTO TABLE XXC05_EMPLOYEES
FIELDS TERMINATED BY “,”  trailing nullcols
(
c1,
field2 BOUNDFILLER,
field3 BOUNDFILLER,
field4 BOUNDFILLER,
field5 BOUNDFILLER,
c2     “:field2 || :field3”,
c3     “:field4 + :field5”
)

******************************************** 8 ****************************

LOAD DATA
INFILE *
INSERT
INTO TABLE XXC05_EMPLOYEES
(
LAST_NAME  position(1:7)  CHAR “UPPER(:LAST_NAME)”,
FIRST_NAME position(8:15) CHAR “LOWER(:FIRST_NAME)”
)

BEGINDATA
Locke Phil
Gorman Tim

————————————————————————–

Multiple Insert Statement:-
In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.

Type of Multiple Insert Statement:

1.  Unconditional INSERT
2.  Conditional INSERT
3.  Conditional FIRST INSERT
4.  Pivoting INSERT

Syntax:-

INSERT [ALL] [CONDITION_INSERT_CLAUSE]
[insert_into_clause values_clause ] (subquery);

=> Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[else] [insert_into_clause values_clause];

Unconditional INSERT ALL:-
This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.

Conditional INSERT ALL:-
This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
WHEN EMPLOYEE_ID 200
THEN
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
VALUE(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Conditional FIRST INSERT:-
This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= 24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = 2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= -24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = -2388
THENINTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.

Pivoting INSERT:-
Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.

SAVE EXCEPTIONS with example

SAVE EXCEPTIONS:-

Since version 9i it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows – the exceptions – are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.

DECLARE
USER_EXCE EXCEPTION;
PRAGMA EXCEPTION_INIT(USER_EXCE,-24381);
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN USER_EXCE
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

DECLARE
TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
T2 T1;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
INSERT INTO XXC05_OBJECT VALUES T2(I);
EXIT WHEN T2.COUNT=0;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;