Escape Sequence in Oracle

Escape special characters when writing SQL queries

--to include single '

SELECT 'Steven's salary is more than 50k INR' AS "SAL_DETAILS" 
FROM Dual;

ORA-01756 : quoted string not properly terminated
 

SELECT 'Steven''s salary is more than 50k INR' AS "SAL_DETAILS" 
FROM   Dual;

SAL_DETAILS
---------------------------------------
Steven's salary is more than 50k INR


--to include double '



SELECT 'You can print double quot ('''') in oracle' "Info"  
FROM Dual;

Info
---------------------------------------
You can print double quot ('') in oracle


SELECT q'[some test ' some test ' some text ']' AS "In 10g" 
FROM dual;

In 10g
------------------------------------
some test ' some test ' some text '



--Escape wild card characters ( _ and % )

           The LIKE keyword allows for string searches.
           The '_' wild card character is used to match exactly one character
           While '%' is used to match zero or more occurrences of any characters.
           These characters can be escaped in SQL as follows.
          
WITH mail_ids AS
   (
     SELECT 'an.murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an_murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an%murugappan@gmail.com'  mail FROM Dual
    )
    SELECT * FROM mail_ids
    WHERE mail LIKE '__$_%' ESCAPE '$';

mail
----------------------------------   
an_murugappan@gmail.com

   

WITH mail_ids AS
   (
     SELECT 'an.murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an_murugappan@gmail.com'  mail FROM Dual
     UNION
     SELECT 'an%murugappan@gmail.com'  mail FROM Dual
    )
    SELECT * FROM mail_ids
    WHERE mail LIKE '__/%%' ESCAPE '/';
   
mail
----------------------------------   
an%murugappan@gmail.com

   
   
Escape ampersand (&) characters in SQL*Plus

SQL> select '&a' FROM dual;

'23'
----
23

SQL> SET ESCAPE '\'
SQL> select '\&a' FROM dual;

'&A'
----
&a

SQL> SET SCAN OFF;
SQL> select '&a' FROM dual;

'&A'
----
&a

SQL> SET SCAN ON;
SQL> select '&a' FROM dual;

'45'
----
45

Data Manipulation Language

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

    INSERT - insert data into a table
    UPDATE - updates existing data within a table
    DELETE - deletes all records from a table, the space for the records remain
    MERGE  - UPSERT operation (insert or update)


CREATE TABLE prod_details
       (
          prod_id      NUMBER(4)              ,
          prod_name    VARCHAR2(30)           ,
          order_dt     DATE DEFAULT SYSDATE   ,
          Deliver_dt   DATE DEFAULT SYSDATE+3 ,
          comments     VARCHAR2(300)
       );
      

SELECT * FROM prod_details;

no_data_found
        
INSERT

INSERT INTO prod_details(prod_id,prod_name,order_dt,deliver_dt,comments)
       VALUES(100,'Apple iphone 5s','21-May-14','24-May-14','Color : Black');
       
SELECT * FROM prod_details;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
---------------------------------------------------------------------------
100         Apple iphone 5s      5/21/2014      5/24/2014        Color : Black
---------------------------------------------------------------------------

--Inserting records with out mentioning column name

INSERT INTO prod_details
       VALUES(101,'Samsung Galaxy III','20-Aug-14','23-Aug-14','Color : White');

SELECT * FROM prod_details;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
---------------------------------------------------------------------------
100       Apple iphone 5s    5/21/2014  5/24/2014     Color : Black
101      Samsung Galaxy III 8/20/2014   8/23/2014     Color : White
---------------------------------------------------------------------------

--Inserting selective number of values

INSERT INTO prod_details
       VALUES(103,'Moto X','11-May-14','13-May-14');
      
ORA-00947 : not enough values

--While inserting selective number of values mentioning column name is compulsory.

INSERT INTO prod_details (prod_id,prod_name,order_dt,deliver_dt)
       VALUES(103,'Moto X','11-May-14','13-May-14');
      

SELECT * FROM prod_details;

--------------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
--------------------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     Color : Black
101      Samsung Galaxy III 8/20/2014   8/23/2014     Color : White
103      Moto X             5/11/2014   5/24/2014
--------------------------------------------------------------------------------

--Inserting NULL value.
--If you want to insert NULL value you can ignore that column at the time of inserting
--or we can use NULL keyword to insert NULL.

INSERT INTO prod_details
       VALUES(104,'Moto G','19-May-14','22-May-14',NULL);
      

SELECT * FROM prod_details;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
---------------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     Color : Black
101      Samsung Galaxy III 8/20/2014   8/23/2014     Color : White
103      Moto X             5/11/2014   5/24/2014
104      Noto G             5/19/2014   5/22/2014    
---------------------------------------------------------------------------

--if you are not providing values for order_dt and deliver_dt column default value can be taken.

INSERT INTO prod_details(prod_id,prod_name,comments)
       VALUES(105,'Nokia Lumis 720p','Color : Red');
      
      
SELECT * FROM prod_details;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
----------------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     Color : Black
101      Samsung Galaxy III 8/20/2014   8/23/2014     Color : White
103      Moto X             5/11/2014   5/24/2014
104      Moto G             5/19/2014   5/22/2014
105      Nokia Lumis 720p   5/26/2014   5/29/2014     Color : Red
---------------------------------------------------------------------------

--Inserting data by using sub query

CREATE TABLE test_tab (id NUMBER, Name VARCHAR2(30));


INSERT INTO test_tab VALUES(1,'Name1');
INSERT INTO test_tab VALUES(2,'Name2');
INSERT INTO test_tab VALUES(3,'Name3');

SELECT COUNT(*) FROM test_tab;

COUNT(*)
-------
      3

--creating table by using sub query (with out data)

 
CREATE TABLE ins_chk
 
 
SELECT * FROM test_tab
WHERE id = 900;

 
SELECT COUNT(*) FROM ins_chk;

COUNT(*)
-------
      0
           
--Inserting data by using sub query
--copying data from test_tab to ins_chk

 
INSERT INTO ins_chk (SELECT * FROM test_tab);

3 rows inserted in 0.047 seconds.

SELECT COUNT(*) FROM ins_chk;

COUNT(*)
-------
      3
 
    
DROP TABLE test_tab;

DROP TABLE ins_chk;


UPDATE

Syntax :

     UPDATE table_name
        SET column1_name = column1_value,
            column2_name = column2_value,
            column2_name = column3_value,
            columnn_name = columnn_value
      WHERE condition(s);
     

SELECT * FROM prod_details;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
----------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     Color : Black
101      Samsung Galaxy III 8/20/2014   8/23/2014     Color : White
103      Moto X             5/11/2014   5/24/2014
104      Moto G             5/19/2014   5/22/2014
105      Nokia Lumis 720p   5/26/2014   5/29/2014     Color : Red
---------------------------------------------------------------------------

UPDATE prod_details ps
   SET ps.prod_name = 'iphone 5s'
 WHERE ps.prod_id = 100;

1 row updated in 0.031 seconds

SELECT *
  FROM prod_details ps
 WHERE ps.prod_id = 100;

--------------------------------------------------------------------
PROD_ID     PROD_NAME      ORDER_DT      DELIVER_DT      COMMENTS
--------------------------------------------------------------------
100         iphone 5s      5/21/2014      5/24/2014    Color : Black
--------------------------------------------------------------------

--update statement with out condition
--If you try to execute update statement without condition it'll update all the records inside the table.

UPDATE prod_details ps
   SET ps.comments = 'None';

5 row updated in 0.031 seconds

SELECT *
  FROM prod_details ps;

---------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
---------------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     None
101      Samsung Galaxy III 8/20/2014   8/23/2014     None
103      Moto X             5/11/2014   5/24/2014     None
104      Moto G             5/19/2014   5/22/2014     None
105      Nokia Lumis 720p   5/26/2014   5/29/2014     None
----------------------------------------------------------------------


--if your update text contain ' means you can use following metnod (use '')

UPDATE prod_details ps
SET    ps.comments = 'Some product''s are not available'
WHERE  ps.prod_id = 100;

1 row updated in 0.031 seconds


SELECT *
  FROM prod_details ps
 WHERE ps.prod_id = 100;

------------------------------------------------------------------------------------
PROD_ID     PROD_NAME      ORDER_DT      DELIVER_DT      COMMENTS
------------------------------------------------------------------------------------
100         iphone 5s      5/21/2014      5/24/2014        Some product's are not available
------------------------------------------------------------------------------------


DELETE

Syntax:

     DELETE FROM table_name
     WHERE condition(s);
    
DELETE FROM prod_details
 WHERE prod_id IN (104, 105);

2 row(S) deleted in 0.032 seconds


SELECT *
  FROM prod_details ps;

---------------------------------------------------------------------------------------
PROD_ID     PROD_NAME            ORDER_DT      DELIVER_DT      COMMENTS
---------------------------------------------------------------------------------------
100      Apple iphone 5s    5/21/2014   5/24/2014     Some product's are not available
101      Samsung Galaxy III 8/20/2014   8/23/2014     None
103      Moto X             5/11/2014   5/24/2014     None
---------------------------------------------------------------------------------------


DELETE FROM prod_details;

3 row(s) deleted in 0.062 seconds.

SELECT * FROM prod_details;

no rows selected.

DROP TABLE prod_details;

MERGE = Insert + Update

      -- will update soon.

Data Define Language

Data Definition Language (DDL) statements are used to define the database structure or schema. 

Some examples:

    CREATE - to create objects in the database.
    ALTER  - alters the structure of the database.
        ADD
        RENAME
        MODIFY
        DROP
    DROP - delete objects from the database
    TRUNCATE - remove all records from a table, 
               including all spaces allocated for the records are removed.
    COMMENT - add comments to the data dictionary.
    RENAME - rename an object.
   

Creating Table

--------------------------------------------------------------------------
Data Types     Size            Default Size    Explanation
--------------------------------------------------------------------------
NUMBER(P,S)     P:   1 to 38              we can store number between 0-9
                S: -84 to 127
VARCHAR2        4000 Bytes                we can store 0-9, a-z, A-Z and special characters
CHAR            2000 Bytes          1           
DATE                                7     Used to store Date
TIMESTAMP                                 Includes year, month, day, hour, minute, and seconds.
LONG            2 GB                      Only one long column is allowed in a table.
                                          We can't use this column in ORDER BY clause.
CLOB            4 GB                      Character Large Object
BLOB                                      Binary Larger Object
--------------------------------------------------------------------------
   
  CREATE
   ALTER  :  ADD   RENAME   MODIFY   DROP   RENAME
  RENAME
    DROP
TRUNCATE


--creating table

CREATE TABLE friends_details_tb
    (
    Name               VARCHAR2(30) ,
    Phone              NUMBER(10)   ,
    Gender             CHAR         ,
    dob                DATE         ,
    other_details      LONG         ,
    make_dtm           TIMESTAMP
    );
   
DESC friends_details_tb;

   -----------------------------
   NAME           TYPE
   -----------------------------
   NAME           VARCHAR2(30)
   PHONE          NUMBER(10)
   GENDER         CHAR(1)
   DOB            DATE
   OTHER_DETAILS  LONG
   MAKE_DTM       TIMESTAMP(6)
   -----------------------------
   
--maximum you can keep 255 columns in a table



ALTER  :  Colum level operations
          ADD   RENAME   MODIFY   DROP  

ADD    :  Used to add a column after the table hase been created


    ALTER TABLE friends_details_tb
    ADD   email VARCHAR2(30);

    DESC friends_details_tb;

   -----------------------------
   NAME           TYPE
   -----------------------------
   NAME           VARCHAR2(30)
   PHONE          NUMBER(10)
   GENDER         CHAR(1)
   DOB            DATE
   OTHER_DETAILS  LONG
   MAKE_DTM       TIMESTAMP(6)
   EMAIL          VARCHAR2(30)
   -----------------------------
  
--adding multiple columns by using single ALTER statement

SYNTAX

ALTER TABLE friends_details_tb
ADD   (
        column_1   Data_type(size),
        column_2   Data_type(size),
        column_3   Data_type(size),
        column_4   Data_type(size)
      );




RENAME   :  Used to Rename a column

    ALTER TABLE friends_details_tb
    RENAME COLUMN email TO email_id;

    DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   OTHER_DETAILS    LONG
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------



MODIFY  :  Used to change the data type or size of the data type.

    ALTER TABLE friends_details_tb
    MODIFY other_details CLOB;

    DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   OTHER_DETAILS    CLOB
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------

--Modifying  multiple columns at a time


    ALTER TABLE friends_details_tb
    MODIFY (            column_name_1  old_datatype(new_size),
            column_name_2  new_datatype(old_size),
            column_name_3  new_datatype(new_size)
            );


DROP  :  Used to drop the column after the table has been created.

    ALTER TABLE friends_details_tb
    DROP COLUMN other_details;

DESC friends_details_tb;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------
  

RENAME  :  Used to Rename a particular table.

SYNTAX 

    RENAME old_table_name TO new_table_name;

    RENAME friends_details_tb TO fnd_det;

DESCRIBE fnd_det;

   -----------------------------
   NAME             TYPE
   -----------------------------
   NAME             VARCHAR2(30)
   PHONE            NUMBER(10)
   GENDER           CHAR(1)
   DOB              DATE
   MAKE_DTM         TIMESTAMP(6)
   EMAIL_ID         VARCHAR2(30)
   -----------------------------

TRUNCATE   :  Used to remove the entire content of the table (not a structure)

SYNTAX 

    TRUNCATE TABLE table_name;

    TRUNCATE TABLE fnd_det;




DROP  :  Used to drop the tabel (Data + stucture of the table get removed from the database )

    DROP TABLE fnd_det;
  
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
   
   

Statements in Oracle

Data Definition Language (DDL) 

               DDL statements are used to define the database structure or schema. Some examples:

    CREATE - to create objects in the database
    ALTER - alters the structure of the database
        ADD
        RENAME
        MODIFY
        DROP
    DROP - delete objects from the database
    TRUNCATE - remove all records from a table,       
               including all spaces allocated for the records are removed
    RENAME - rename an object
   
   
Data Manipulation Language (DML) 

            DML statements are used for managing data within schema objects. Some examples:

    INSERT - insert data into a table
    UPDATE - updates existing data within a table
    DELETE - deletes all records from a table, the space for the records remain
    MERGE  - UPSERT operation (insert or update)
  
   
Data Control Language (DCL) 

            DCL statements. Some examples:

    GRANT  - gives users access privileges to database
    REVOKE - withdraw access privileges given with the GRANT command
   
   
Transaction Control (TCL) 

            TCL statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

    COMMIT    - save work done
    SAVEPOINT - identify a point in a transaction to which you can later roll back
    ROLLBACK  - restore database to original since the last COMMIT


   

DECODE and CASE statement

CREATE TABLE students (
                         roll_no     NUMBER(4)    ,
                         Name        VARCHAR2(30) ,
                         dept_cd     VARCHAR2(20) ,
                         total_marks NUMBER(3)
                      );

BEGIN                    
     INSERT INTO students VALUES (2000,'Rahul','CSE',480);
     INSERT INTO students VALUES (2001,'Bala','IT', 390);
     INSERT INTO students VALUES (2002,'Ramesh','CSE',250);
     INSERT INTO students VALUES (2003,'Karthi','EEE',185);
     INSERT INTO students VALUES (2004,'Ravi','IT',345);
END;
/

SELECT * FROM students;

DECODE
    Decode is a function. Its a Oracle one.
    Works like IF-THEN-ELSE.
    You can use DECODE only in SELECT clause.
    In DECODE you can include 255 things include Exep, search , result and default value. 

Example

    SELECT roll_no,
           NAME,
           dept_cd,
           DECODE(dept_cd,
                  'CSE',
                  'Computer Science and Engineering',
                  'IT',
                  'Information Technology',
                  'Default Value') AS "DEPARTMENT NAME"
      FROM students;
     
Sample Output

------------------------------------------------------------
ROLL_NO    NAME       DEPT_CD  DEPARTMENT NAME
------------------------------------------------------------
   2000      Rahul    CSE      Computer Science and Engineering
   2001      Bala     IT       Information Technology
   2002      Ramesh   CSE      Computer Science and Engineering
   2003      Karthi   EEE      Default Value
   2004      Ravi     IT       Information Technology
------------------------------------------------------------

CASE
   CASE is an expression. Its a ANSI standard.
   Compare to DECODE case will be more faster.

Example

    SELECT roll_no,
           NAME,
           dept_cd,
           total_marks,
           CASE
               WHEN total_marks > 500 THEN
                'A Grade'
               WHEN total_marks > 400 THEN
                'B Grade'
               WHEN total_marks > 300 THEN
                'C Grade'
               ELSE
                'U Grade'
           END AS "GRADE"
      FROM students;
     
-----------------------------------------------
ROLL_NO  NAME     DEPT_CD  TOTAL_MARKS GRADE
-----------------------------------------------    
   2000  Rahul    CSE        480       B Grade
   2001  Bala     IT         390       C Grade
   2002  Ramesh   CSE        250       U Grade
   2003  Karthi   EEE        185       U Grade
   2004  Ravi     IT         345       C Grade   
-----------------------------------------------

Interview Questions:

What is DECODE?
What is CASE?
Difference between CASE and DECODE? Which one is faster?

Sub Query

Query with in another query
     
      1. Single Row sub query
      2. Multi row Sub query


select * from employees;

Want to fetch the person Who are all getting salary more than Neena's salary ?

Neena's Salary  :  17000


Select First_name, salary   
From   Employees
Where  salary > 17000;

--change in Neena's salary won't work for the above query.

Select salary From Employees
Where First_name = 'Neena';


Sub Query :

Select First_name, salary
From   Employees
Where  salary > (
                     Select salary From Employees
                     Where First_name = 'Neena'
                );



Select * from employees;

Select salary From Employees
Where First_name = 'Alexander';


Sub Query Returns one value  it is called as Single row sub query

Sub Query Returns more than one value  it is called as Multi row sub query


Single row sub query operators :

     >   <   >=  <=  =  <>

Multiple Row Sub query operators :

     IN   ALL  ANY

Who are all getting salary more than the average salary?

Select
       First_name,
       Salary
From  
       Employees
WHERE
       Salary >  (
                    SELECT Round(avg(salary),0)
                    FROM   Employees
                 );


Multiple Row Sub query  (In ANY ALL)

Select First_name,Department_id, salary
From Employees
Where salary in(
                 Select Salary From Employees
                 Where First_name = 'Alexander'
               );


Any : Minimum Value will be taken  (3100)
Select First_name,Department_id, salary
From Employees
Where salary > ANY(
                     Select Salary From Employees
                     Where First_name = 'Alexander'
                  );


All : Maximum Value will be taken (9000)
Select First_name,Department_id, salary
From Employees
Where salary > All(
                     Select Salary From Employees
                     Where First_name = 'Alexander'
                  );


We can use sub query in all the classes except Group by and order by class.

Select
From
Where
-----------Group by
having
-----------Order by


SELECT
    ( Select Count(*) from Employees Where Department_id = 90 )Executive,
    ( Select Count(*) from Employees Where Department_id = 60 ) IT,
    ( Select Count(*) from Employees Where Department_id = 10 ) Administration,
    ( Select Count(*) from Employees Where Department_id = 100) Finance
FROM
    Dual;

Select Min(salary) From Employees;

Select Min(salary) from
                        (
                          Select * from Employees
                          Where Department_id = 60
                        );

What is INLINE VIEW?
        If u use sub query in From it is call it is as INLINE VIEW.
 Intermediate result set


Correlated sub query

SELECT employee_number, name
FROM employees  Bob
WHERE salary >  (
       SELECT AVG(salary)
       FROM employees
       WHERE department = Bob.department
  );

LAG, LEAD, FIRST_VALUE and LAST_VALUE Function in oracle

LAG( )

LAG is an analytic function which provides access to a row following Nth position of current row within window of ordered set of rows.

CREATE TABLE employee_data
       (
       eid        NUMBER(4),
       ename      VARCHAR2(30),
       depno      NUMBER(3),
       salary     NUMBER(8)
       );
      
BEGIN      
    INSERT INTO employee_data VALUES(1000,'Name01',90,30000);
    INSERT INTO employee_data VALUES(1001,'Name02',90,6000);
    INSERT INTO employee_data VALUES(1002,'Name03',90,23000);
    INSERT INTO employee_data VALUES(1003,'Name04',60,35000);
    INSERT INTO employee_data VALUES(1004,'Name05',60,60000);
    INSERT INTO employee_data VALUES(1005,'Name06',60,30000);
    INSERT INTO employee_data VALUES(1006,'Name07',80,36000);
    INSERT INTO employee_data VALUES(1007,'Name08',80,29000);
    INSERT INTO employee_data VALUES(1008,'Name09',80,37000);
    INSERT INTO employee_data VALUES(1009,'Name10',80,41000);
    COMMIT;
END;
/

SELECT * FROM employee_data;

SYNTAX  :  LAG( arg1,arg2,arg3) 

           arg1  = column name
           arg2  = offset value
           arg2  = Default value

SELECT ed.eid,
       ed.ename,
       ed.depno,
       ed.salary,
       LAG(ed.salary,1,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAG"
  FROM employee_data ed;
 

Output

----------------------------------------------
Eid     Ename    Depno      salary       LAG
----------------------------------------------
1005    Name06      60       30000         0
1003    Name04      60       35000     30000
1004    Name05      60       60000     35000
1007    Name08      80       29000         0
1006    Name07      80       36000     29000
1008    Name09      80       37000     36000
1009    Name10      80       41000     37000
1001    Name02      90       6000          0
1002    Name03      90       23000      6000
1000    Name01      90       30000     23000
----------------------------------------------

SELECT ed.eid,
       ed.ename,
       ed.depno,
       ed.salary,
       LAG(ed.salary,2,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAG"
  FROM employee_data ed;
 
Output

----------------------------------------------
Eid     Ename     Depno     salary     LAG
----------------------------------------------
1005    Name06      60       30000         0
1003    Name04      60       35000         0
1004    Name05      60       60000     30000
1007    Name08      80       29000         0
1006    Name07      80       36000         0
1008    Name09      80       37000     29000
1009    Name10      80       41000     36000
1001    Name02      90       6000          0
1002    Name03      90       23000         0
1000    Name01      90       30000      6000
----------------------------------------------

LEAD( )

LEAD is same as LAG analytic function but it provides access to a row prior to Nth position of current row within window of ordered set of rows.

 SELECT ed.eid,
       ed.ename,
       ed.depno,
       ed.salary,
       LEAD(ed.salary,1,0) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LEAD"
  FROM employee_data ed;

Output

----------------------------------------------
Eid     Ename    Depno       salary   LEAD
----------------------------------------------
1005    Name06      60       30000     35000
1003    Name04      60       35000     60000
1004    Name05      60       60000         0
1007    Name08      80       29000     36000
1006    Name07      80       36000     37000
1008    Name09      80       37000     41000
1009    Name10      80       41000         0
1001    Name02      90       6000      23000
1002    Name03      90       23000     30000
1000    Name01      90       30000         0
----------------------------------------------

FIRST_VALUE( ) and LAST_VALUE( )

FIRST_VALUE( )

The FIRST_VALUE returns the first result of an ordered set from a window.

SELECT ed.eid,
       ed.ename,
       ed.depno,
       ed.salary,
       FIRST_VALUE(ed.salary) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "FIRST_VALUE"
  FROM employee_data ed;
 

Output

----------------------------------------------
Eid      Ename   Depno     salary   FIRST_VALUE
----------------------------------------------
1005    Name06      60       30000     30000
1003    Name04      60       35000     30000
1004    Name05      60       60000     30000
1007    Name08      80       29000     29000
1006    Name07      80       36000     29000
1008    Name09      80       37000     29000
1009    Name10      80       41000     29000
1001    Name02      90       6000       6000
1002    Name03      90       23000      6000
1000    Name01      90       30000      6000
----------------------------------------------


LAST_VALUE( )

The LAST_VALUE returns the last result of an ordered set from a window

SELECT ed.eid,
       ed.ename,
       ed.depno,
       ed.salary,
       LAST_VALUE(ed.salary) OVER (PARTITION BY ed.depno ORDER BY ed.salary) AS "LAST_VALUE"
  FROM employee_data ed;
 
Output

----------------------------------------------
Eid      Ename   Depno     salary   LAST_VALUE
----------------------------------------------
1005    Name06      60       30000     60000
1003    Name04      60       35000     60000
1004    Name05      60       60000     60000
1007    Name08      80       29000     41000
1006    Name07      80       36000     41000
1008    Name09      80       37000     41000
1009    Name10      80       41000     41000
1001    Name02      90       6000      30000
1002    Name03      90       23000     30000
1000    Name01      90       30000     30000
----------------------------------------------

DROP TABLE employee_data;


Example:

--Transpose data in rows in to column

create table test_insert (data varchar2(30));

create table ins_data(col1 varchar2(30), col2 varchar2(30), col3 varchar2(30));


BEGIN
   insert into test_insert values('Revision 1');
   insert into test_insert values('txt1');
   insert into test_insert values('col1');
   insert into test_insert values('txt2');
   insert into test_insert values('col3');
   insert into test_insert values('col4');
   insert into test_insert values(NULL);
   insert into test_insert values('Revision 1');
   insert into test_insert values('txt1');
   insert into test_insert values('col1');
   insert into test_insert values('txt2');
   insert into test_insert values('col3');
   insert into test_insert values('col4');
   Commit;
END;
/

SELECT * FROM ins_data;




DECLARE
    v_data      VARCHAR2(30);
    v_required1 VARCHAR2(30);
    v_required2 VARCHAR2(30);
    v_required3 VARCHAR2(30);
    v_required4 VARCHAR2(30);
    v_required5 VARCHAR2(30);

    CURSOR all_data
    IS
        SELECT *
          FROM (SELECT data,
                       lead(data,
                            1,
                            6) over(ORDER BY 1) AS required1,
                       lead(data,
                            2,
                            6) over(ORDER BY 1) AS required2,
                       lead(data,
                            3,
                            6) over(ORDER BY 1) AS required3,
                       lead(data,
                            4,
                            6) over(ORDER BY 1) AS required4,
                       lead(data,
                           5,
                            6) over(ORDER BY 1) AS required5
                  FROM test_insert)
         WHERE data LIKE 'Revision%';

BEGIN
    OPEN all_data;
    LOOP
        FETCH all_data
            INTO v_data,v_required1, v_required2, v_required3, v_required4, v_required5;
        EXIT WHEN all_data%NOTFOUND;
        INSERT INTO ins_data
        VALUES
            (v_required1,
             v_required3,
             v_required5);
    END LOOP;
    CLOSE all_data;
    Commit;
EXCEPTION

WHEN OTHERS THEN
            dbms_output.put_line(SQLERRM);
END;


SELECT * FROM ins_data;

DROP TABLE ins_data;

DROP TABLE test_insert;
 

 

DML Error Logging (dbms_errlog.create_error_log package)

    It a Feature of Oracle 10g R2.

    The CREATE_ERROR_LOG procedure of DBMS_ERRLOG package makes you enables to create an error logging table so that
    DML operations can continue after encountering errors rather than abort and roll back.


CREATE TABLE students
       (
             stud_id        NUMBER      ,
             f_name         VARCHAR2(30),
             l_name         VARCHAR2(30),
             salary         NUMBER(8)   ,
             department_id  NUMBER CHECK(department_id IN(10,20,30))
       );
      

BEGIN
   dbms_errlog.create_error_log('students');
END;
/

SELECT * FROM user_tables ut
WHERE ut.table_name LIKE '%STU%';


DESCRIBE ERR$_STUDENTS;

-------------------------------------------------------
NAME                   TYPE                  NULLABLE
-------------------------------------------------------
ORA_ERR_NUMBER$          NUMBER                   Y
ORA_ERR_MESG$            VARCHAR2(2000)           Y
ORA_ERR_ROWID$           UROWID(4000)             Y
ORA_ERR_OPTYP$           VARCHAR2(2)              Y
ORA_ERR_TAG$             VARCHAR2(2000)           Y
STUD_ID                  VARCHAR2(4000)           Y
F_NAME                   VARCHAR2(4000)           Y
L_NAME                   VARCHAR2(4000)           Y
SALARY                   VARCHAR2(4000)           Y
DEPARTMENT_ID            VARCHAR2(4000)           Y
-------------------------------------------------------


CREATE TABLE old_students
AS SELECT * FROM students;

BEGIN
     INSERT INTO old_students VALUES (100,'Fname1','lname1',20000,10);
     INSERT INTO old_students VALUES (101,'Fname1','lname1',20000,50);
     INSERT INTO old_students VALUES (102,'Fname1','lname1',20000,20);
     INSERT INTO old_students VALUES (103,'Fname1','lname1',20000,40);
     INSERT INTO old_students VALUES (104,'Fname1','lname1',20000,30);
     Commit;
END;
/

SELECT * FROM old_students;

INSERT bstudents
SELECT * FROM old_students
LOG ERRORS INTO ERR$_STUDENTS REJECT LIMIT UNLIMITED;

3 row(S) inserted in 0.078 seconds.

SELECT * FROM ERR$_STUDENTS;

Inserting Data into Array Type

 
Creating Type Object

create type project_type as object
(
name varchar2(50),
role varchar2(20)
);

 
Creating Type Array

create type projectlist as VARRAY2(5) of project_Type;
commit;

 
Creating table with array type

create table emp
(   empno         number(5),
    ename         varchar2(30),
    projects     projectlist
);

Inserting data in to varray

insert into emp
 values(1,'Ellison', projectlist(
                                    project_type('Telephone Billing', 'System Analyst'),
                                    project_type('Housing Loans','Oracle DBA')
                                )
        );

select * from emp where empno = 1;

DROP TYPE project_type;
DROP TYPE projectlist;
DROP TABLE emp;

CONNECT BY PRIOR - Hierarchical queries

CREATE TABLE emp (eid NUMBER(3), ename VARCHAR2(20), mgr NUMBER(3));

        BEGIN
        INSERT INTO emp VALUES(100 , 'Murugappan', NULL);
        INSERT INTO emp VALUES(101 , 'Senthil'   , 100);
        INSERT INTO emp VALUES(102 , 'Karthic'   , 101);
        INSERT INTO emp VALUES(103 , 'John'      , 102);
        INSERT INTO emp VALUES(104 , 'Nagaraj'   , 100);
        INSERT INTO emp VALUES(105 , 'Selva'     , 104);
        END;
        /

Sample 1:

        SELECT eid, ename, mgr, PRIOR ename, LEVEl
        FROM emp
        CONNECT BY PRIOR eid = mgr
        START WITH mgr IS NULL;   
                
Sample Output

      
              
Sample 2:
       
        SELECT lpad(' ',5*level-1)||ename as ename
        FROM    emp
        START WITH mgr IS NULL
        CONNECT BY NOCYCLE PRIOR eid = mgr;

Sample output

    -----------------------
           ENAME        
    -----------------------
     Murugappan         
        Senthil               
             Karthic         
                  John        
        Nagaraj             
             Selva            
    -----------------------         
            
Sample 3: 

        SELECT eid, LPAD(' ', LEVEL*5, ' ')||ename NAME , mgr, PRIOR ename, LEVEL
        FROM emp
        CONNECT BY PRIOR eid = mgr
        START WITH mgr IS NULL;

Sample output

        
       
        DROP TABLE emp;

Dynamic SQL

    Its allowed you to built SQL statements whose contents are not known until runtime.
    Here we are building the string and passed it to the server for execution.
      
Disadvantage

    slower than static SQL.
    since syntax checking and object validation cannot be done until runtime.

        CREATE TABLE trans_jan_to_dec_12 (  cust_id NUMBER(3), bill_date DATE, bill_amt NUMBER(6,2) );
        CREATE TABLE trans_jan_to_dec_13 (  cust_id NUMBER(3), bill_date DATE, bill_amt NUMBER(6,2) );

        BEGIN
        --inserting data into trans_jan_to_dec_12
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'12-MAR-12',4000);
        INSERT INTO trans_jan_to_dec_12 VALUES(115,'21-JAN-12',5700);
        INSERT INTO trans_jan_to_dec_12 VALUES(116,'19-JUN-12',3300);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'19-JUN-12',1200);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'27-AUG-12',1300);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'16-MAR-12',1000);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'14-APR-12',2000);
        INSERT INTO trans_jan_to_dec_12 VALUES(114,'12-SEP-12',7000);
        --inserting data into trans_jan_to_dec_13
        INSERT INTO trans_jan_to_dec_13 VALUES(123,'13-FEB-13',5600);
        INSERT INTO trans_jan_to_dec_13 VALUES(143,'24-APR-13',2300);
        INSERT INTO trans_jan_to_dec_13 VALUES(152,'15-JUL-13',7600);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'30-MAR-13',1000);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'02-APR-13',2000);
        INSERT INTO trans_jan_to_dec_13 VALUES(114,'09-SEP-13',7000);
        END;
        /

        SELECT * FROM trans_jan_to_dec_12;
        SELECT * FROM trans_jan_to_dec_13;

        --simple example 1

        DECLARE
               v_cust_id   number(3);
               v_bill_date date;
               v_bill_amt  number(6,2);
               Query_str VARCHAR2(200) := 'SELECT * FROM trans_jan_to_dec_12 WHERE cust_id = 115';
        BEGIN
           EXECUTE IMMEDIATE Query_str INTO v_cust_id, v_bill_date, v_bill_amt;
           dbms_output.put_line('customer id :  ' || v_cust_id);
           dbms_output.put_line('Bill date   :  ' || v_bill_date);
           dbms_output.put_line('Bill Amount :  ' || v_bill_amt);
        END;
        /
 
sample output
       
        customer id :  115
        Bill date   :  21-JAN-12
        Bill Amount :  5700
       
        --simple example 2 (with bind variable)
       
USING PLACEHOLDERS
 
    Placeholders can be used to replace a missing value at runtime. A placeholder is a name preceded by : (colon).
    The placeholder is to be replaced with a value through USING clause of EXECUTE IMMEDIATE statement.

        DECLARE
               v_cust_id   number(3);
               v_bill_date date;
               v_bill_amt  number(6,2);
               Query_str VARCHAR2(200) := 'SELECT * FROM trans_jan_to_dec_12 WHERE cust_id = :idval';
        BEGIN
           EXECUTE IMMEDIATE Query_str INTO v_cust_id, v_bill_date, v_bill_amt USING 115;
           dbms_output.put_line('customer id :  ' || v_cust_id);
           dbms_output.put_line('Bill date   :  ' || v_bill_date);
           dbms_output.put_line('Bill Amount :  ' || v_bill_amt);
        END;

--sample output        
        customer id :  115
        Bill date   :  21-JAN-12
        Bill Amount :  5700
       

        CREATE OR REPLACE PROCEDURE total_purchase_amt_sp (p_year IN NUMBER, p_cust_id IN NUMBER)
        AS
          v_total_amt   NUMBER(8);
          sql_query   VARCHAR2(3000) := 'SELECT SUM(bill_amt) FROM trans_jan_to_dec_' || p_year || ' WHERE cust_id = :1';
        BEGIN
             EXECUTE IMMEDIATE sql_query INTO v_total_amt USING p_cust_id ;
             dbms_output.put_line('Total purchase amount of the customer with id '||p_cust_id ||' in 20'|| p_year ||' is ' || v_total_amt ||' INR');
        END total_purchase_amt_sp;
        /


        BEGIN
           total_purchase_amt_sp(12,114);
           total_purchase_amt_sp(13,114);
        END;
        /

--sample output
 
    Total purchase amount of the customer with id 114 in 2012 is 16500 INR
    Total purchase amount of the customer with id 114 in 2013 is 10000 INR


DDL statements using Dynamic Sql

        DECLARE
           Query_str VARCHAR2(200) := 'TRUNCATE TABLE trans_jan_to_dec_12';
        BEGIN
           EXECUTE IMMEDIATE Query_str;
        END;
        /

SELECT * FROM trans_jan_to_dec_12;


    Fetching row from cursor

            CREATE OR REPLACE PROCEDURE dynamic_sql_with_cursor_sp         
(p_year IN NUMBER)
            AS
              v_cust_id   NUMBER(3);
              v_tot_amt  NUMBER;
              TYPE all_data_rc IS REF CURSOR;
              all_rec all_data_rc;
              sql_query   CLOB := 'SELECT cust_id,SUM(bill_amt) FROM trans_jan_to_dec_' || p_year || ' GROUP BY cust_id';
              v_sum NUMBER(20) := 0;
            BEGIN
                             dbms_output.put_line('***************************************');
                             dbms_output.put_line('Customer total purchace record in 20'|| p_year);
                             dbms_output.put_line('***************************************');
                 OPEN all_rec FOR sql_query;
                      LOOP
                          FETCH all_rec INTO v_cust_id,v_tot_amt;
                          EXIT WHEN all_rec%NOTFOUND;
                          v_sum := v_sum + v_tot_amt;
                             dbms_output.put_line('customer id :  ' || v_cust_id);
                             dbms_output.put_line('Bill Amount :  ' || v_tot_amt);
                             dbms_output.put_line('--------------------');
                      END LOOP;
                     
                             dbms_output.put_line('***************************************');
                             dbms_output.put_line('Total amount    :         '|| v_sum);
                             dbms_output.put_line('***************************************');
                           
            END dynamic_sql_with_cursor_sp;
            /


        BEGIN
           dynamic_sql_with_cursor_sp(12);
        END;       

--sample output
       
        ***************************************
        Customer total purchase record in 2012
        ***************************************
        customer id :  114
        Bill Amount :  16500
        --------------------
        customer id :  115
        Bill Amount :  5700
        --------------------
        customer id :  116
        Bill Amount :  3300
        --------------------
        ***************************************
        Total amount    :         25500
        ***************************************


DROP PROCEDURE total_purchase_amt_sp;
DROP TABLE     trans_jan_to_dec_12;
DROP TABLE     trans_jan_to_dec_13;