Oracle DML Error Logging

Oracle DML Error Logging

Key Points of DML Error Logging
• DML SQL Query fails even if 1 row does not meet criteria or violates a constraint
• Instead of the 100,000 row update/insert/whatever failing because a single row doesn’t quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table
• It is intuitive and errors are logged into error log table
• Feature available with Oracle database 10g release 2

-When you need to load millions of rows of data into a table, we use INSERT , UPDATE, MERGE .Similarly , delete for deleting bulk amount of rows.
-So when loading millions of rows into a table, a single integrity or constraint violation leads to failure of the statement and rollback . 
-No matter you loaded only one or 90000 out of 100000 rows , statement fails and rollback will be performed. In these situations, you need to have data either error free or efficient way to handle errors.
-You can take advantage using SQL* Loader if your data is in a file.
-However, if you have data in table or other objects ,you can use an anonymous block to handle errors , and you can use bulk collect , forall features for performance.
-A direct path INSERT DML is much faster than the above approach.

You need to create  error logging table for this DML error logging.

Syntax for creating error logging table.

DBMS_ERRLOG.CREATE_ERROR_LOG (
        dml_table_name                  IN VARCHAR2,
        err_log_table_name              IN VARCHAR2 := NULL,
        err_log_table_owner             IN VARCHAR2 := NULL,
        err_log_table_space             IN VARCHAR2 := NULL,
        skip_unsupported                IN BOOLEAN  := FALSE);

-All the parameters except DML_TABLE_NAME are optional.
-If the optional details are omitted, the name of the error logging table will be ERR$_ together with the first 25 characters of the DML_TABLE_NAME.
-The SKIP_UNSUPPORTED parameter, if set to TRUE, instructs the error logging clause to skip over LONG, LOB, and object type columns that are not supported and omit them from the error logging table.

LOG ERRORS SYNTAX:
With the error logging table created, you can add the error logging clause to most DML statements,  

LOG ERRORS [INTO [schema.]table] 
[ (simple_expression) ] 
[ REJECT LIMIT  {integer|UNLIMITED} ]


-The INTO clause is optional;
-If you omit it, the error logging clause will put errors into a table with the same name format used by the CREATE_ERROR_LOG procedure.
-SIMPLE_EXPRESSION is any expression that would evaluate to a character string and is used for tagging rows in the error table to indicate the process that caused the error, the time of the data load, and so on.
-REJECT LIMIT can be set to any integer or UNLIMITED and specifies the number of errors that can occur before the statement fails.
-This value is optional, but if it is omitted, the default value is 0, which effectively disables the error logging feature.


The following types of errors are handled by the error logging clause: 
Column values that are too large
Constraint violations (NOT NULL, unique, referential, and check constraints), except in certain circumstances detailed below
Errors raised during trigger execution
Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
Partition mapping errors


The following conditions cause the statement to fail and roll back without invoking the error logging capability: 
Violated deferred constraints
Out-of-space errors
Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation



we are using scott schema to demonstrate 

Create a regular table
SQL> create table emp_test as select * from scott.emp where 1=0;

Add Primary Key
SQL> alter table emp_test add constraint emp_pk primary key(empno);

Add Constraint
SQL> alter table emp_test add constraint emp_check_sal check(sal > 900);

Create error log table
SQL> exec dbms_errlog.create_error_log( ‘emp_test' );


SQL> desc err$_emp_test
Name Null? Type
----------------------- -------- ----------------
ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)
EMPNO VARCHAR2(4000)
ENAME VARCHAR2(4000)
JOB     VARCHAR2(4000)
MGR VARCHAR2(4000)
HIREDATE VARCHAR2(4000)
SAL        VARCHAR2(4000)
COMM VARCHAR2(4000)
DEPTNO VARCHAR2(4000)

SQL> insert into emp_test select * from emp; --Note DML fails because we have added check constraint
SQL> select * from emp_test; --Note no rows displayed
SQL> insert into emp_test select * from emp
LOG ERRORS REJECT LIMIT UNLIMITED ; --Note DML succeeds
SQL> select * from emp_test; --Note rows displayed
SQL> select ora_err_number$, ora_err_tag$,
ora_err_optyp$,empno,ora_err_mesg$ from err$_emp_test;
SQL> update emp_test set sal = sal - 2000 where sal between 2000 and 3000
LOG ERRORS ('My Update') REJECT LIMIT UNLIMITED;

Oracle Temporary Tables

About Temporary Tables :

Temporary tables are database tables that hold session specific data
Each session can only see and modify its own data
Types :– Transaction Specific – Session Specific

Transaction Specific
– Data exists for the duration of the Transaction
– Declared by syntax “On Commit Delete Rows”
Session Specific
– Data exists for the duration of the Session
– Declared by syntax “On Commit Preserve Rows”

Key Points :

• TRUNCATE statement issued against a temporary table, will truncate only the
session specific data. There is no effect on the data of other sessions.
• Data in temporary tables is automatically deleted at the end of the database
session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the
scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and
permanent tables.
• Tempoarary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data
rows are processed
• Foreign keys cannot be specified
• Cannot contain columns of nested table or varray
• Cannot be partitioned, index- organized or clustered

Example :

create global temporary table emp_temp
(empno number primary key,
deptno number,
ename varchar2(10))
on commit delete rows;

insert into emp_temp
select empno,deptno,ename from emp;
Commit;


Notice all the rows are lost after commit.

Oracle SQL WITH Query

WITH Query:

In complex queries that process the same subquery multiple times, the WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within
the original complex query.

This query joins two tables and computes the aggregate SUM(SAL) more than once.
The text in blue represents the parts of the query that are repeated. 

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING SUM(sal) >( SELECT SUM(sal) * 1/3 FROM emp, dept
WHERE emp.deptno = dept.deptno ) ORDER BY SUM(sal) DESC;

Query optimized with WITH syntax. 
The aggregation and fetch from the database tables is done only once

WITH summary AS
( SELECT dname, SUM(sal) AS dept_total FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname )
SELECT dname, dept_total
FROM summary
WHERE dept_total > ( SELECT SUM(dept_total) * 1/3 FROM summary )
ORDER BY dept_total DESC;

Dell Oracle SQL PL/SQL Interview Questions

Select * from a where hiredate+60 < sysdate; (here hiredate is non-unique index, so query scanning index range or full table scanning.
Have you worked on explain plan, how to tune sql query
Can you write pivot query?                                                                                 
Which exception rise in bulk collect and forall processing
What are analytical functions?             
What is dbms_profiler do?
Difference between sql *loader and external table, explain requirements for that
Have you ever worked on partitions, explain Virtual column based partitioning
what is nvl and nvl2 function and examples
Explain about TK-proof , syntax of TK-proof 
Merge statement syntax
Partition Exchange(One table have 5 partitions like P1...P5, I want to move P5 partition insert into another Table)? 
Difference between 10g and 11g
COLN
---------
A
A
A
B
B
B
B

Write  a query  output like as
coln
------
3
4
coln
-----
A
B
C
D
E
I want output like this
TEXT
--------
A,B,C,D,E
EMPNO     ENAME  
-----------    -----------
12345         REDDY
I want to count of empno and ename
coln
------
-1
-2
-3
-10
1
8
7
I want to count of positive and negative
select sum(decode(sign(value),1,value)) pos, sum(decode(sign(value),-1,value)) neg from pn
select ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from dual
select unique ( select sum(value) from pn where 0<value) as positive, (select sum(value) from pn where 0>value) as negative from pn

CTS Oracle SQL PL/SQL Interview Questions

Tell me something yourself?
What are the activities are you doing daily in your   project?
Tell me ratios of sql , pl/sql&unix?
Difference between primary key & unique key?
Diff b/w primary key & unique key + not null?
What is the use of views?
If table dropped, then what happen view?
What is index? types of indexes?
Which functions are not supported to index?
What is save point?
What is ora-1555?
What are the data types available in pl/sql, not in sql?
What is the main use of %type,%rowtype?
What is cursor? Types of cursors?
Diff b/w explict cursor & for loop cursor?
What is exception? Types of exceptions?
What is raise_application_error?
What is pragma_exception_init?
Diff b/w triggers&procedures?
Can you call procedure in select statement?
Restrictions in functions?
What is the use of packages?
How to drop package body only?
Can use pragma_autonamous_tranctions in packages?
What is bulk collect?
What is nullif()?
What is mutating error?
What are the module's , you are working
Which will fire default first statement level or row level   trigger?
What is bulk collect? And any restrictions in bulk collect?
What is the use of limit clause in bulk collect?
How to debug your code?
How to trace error handling?
How to find which line error was raised?
What are the methods there in save exceptions?
What is functional based index? Write syntax?
How to update complex view?
Can you alter procedure with in package?
Is it possible to open cursor which is in package in    another procedure?
What is substr()&instr()?
Difference between case and decode?
Can you use sysdate in check constraints? If no, why?
Difference between column level constraints & table level    constraints?
What is optimizer?
What is nested_loop join?
What is pivot operater?
Tell me some oracle 10g features?
What is m_view?
What is master & detail tables?
What is sqlcode and sqlerrm?
Diff b/w procedure & packages?
What are the dis_adv of packages?

CSS Oracle SQL PL/SQL Interview Questions

Tell me about yourself
What is index?
How does index form internally?
What are pseudo columns?
What are exceptions? How to handle exceptions in PL/SQL?
Explain about exception propagation?
What is autonomous transaction?
What is trigger? Have you ever used it?
Define view
Can we update a view?(what are the conditions)?
if I have created view from two tables by taking all the columns in the table now is it possible to update that view?( ans : NO)
then I have to compulsory update that view how to do that(ans: by using instead of triggers).
What is instead of triggers?
what is syntax of case?
Difference between procedure and functions?
What is a package and what are its advantages?


Then asked on project for 5 to 10 min.
asked two queries
select duplicate records from a emp table?
Select Nth maximum salary from emp table?
SELECT * FROM EMP  WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM EMP  GROUP BY EMPNO);
SELECT A.* FROM EMP A WHERE &N=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);                                                               
Why PL/SQL
Why package?
Why Triggers while you are having Constraints
Types of Triggers and Mutating Triggers
Examples for statement level and row level triggers
Bulk-fetch and bulk binding
What are the options that you use while pre-compiling a Pro*C program and why?
All about cursors
What is the need of Error Handling in PL/SQL and how will you do it
Explain Method 4 in paper
Write queries for deletion of duplicate records and nth max salary
Search and Replace in UNIX
Find syntax
Grep syntax
Copy all the fields from the result of ls –l command into different host variables and insert them into a database object
Describe materialized view
I/O redirection
Why Locks and different types of locks
Autonomous transaction
Referential Constraint

CSC Oracle SQL PL/SQL Interview Questions

Tell me about your self
What is index?
How does index form internally?
What are pseudo columns?
What are exceptions? How to handle expections in PL/SQL?
Explain about exception propagation?
What is autonomous transaction?
What is trigger? Have you ever used it?
Define view
Can we update a view?(what are the conditions)?
If I have created view from two tables by taking all the columns in the table now is it possible to update that view?( ans : NO)
Then I have to compulsory update that view how to do that(ans: by using instead of triggers).
What is instead of triggers?
What is syntax of case?
Difference between procedure and functions?
What is a package and what are its advantages?
How to call procedure in Pro*C
Have you ever used dynamic Sql statements in your project (methods in Pro*C)?
How to handle exceptions in Pro*C
What are the Sqlca structure elements?
How do you handle/trap errors in Pro*C?
Then asked on project for 5 to 10 min.
Asked two queries
Select duplicate records from a emp table?
Select Nth maximum salary from emp table?

Clover Infotech Oracle SQL PL/SQL Interview Questions

Tell me about yourself and what are the responsibilities in your project.
What is the difference between procedure and function
Can we return 2 values through function,i want return empno and sal how to design a function.
I have a package with 2 procedures and declared one procedure in specification only second procedure not declared in specification, package body compiled or not?
What is the use of forward declaration and if you can use in package can we call procedure outside package.
Difference between primary and unique and i am retrieving unique column, that time unique column firing or not?
Difference between nvl and nvl2
How many types of exceptions
Explain pragma types, pragma exception_init and pragma serially_reusable
Difference between bulk collect and forall, suppose i am inserting 10000 records due to some issue few records are not inserted, how to find which records are not inserted, which method you follow to find.
How many types of collection and methods
What are the exceptions raised in collections.
Have you worked on analytical functions?
Difference between rank and dense rank
I have a package with number of logics inside package but package is slow down due to some logic, how to find which query taking more time after finding the query how to tune a query.
Difference between truncate and delete, why truncate faster rather than delete.
Difference between view and materialized view
What is correlated subquery?
What is complex query and have you ever work on that.
Difference between btree and bitmap index
Explain composite data type, tell me the syntax.
Explain briefly about sql loader.

CGI Oracle SQL PL/SQL Interview Questions

Explain briefly about your project
How many type of environments r there in your company?
Exception handling
How we will send error msg with no to ui environment.
Ronum,rowid
Use of hint.
In which format u will get requirements?
Select stmt structure..
Can we develop exceptions in cursor environment?
After you work is completed how you produce the documentation for that?
What is the documentation file called?
Delete,truncate structure..
Query for top n records..
Explain plan mechanism..
Structure for the procedure in exception mechanism.

Cegedim Oracle SQL PL/SQL Interview Questions

Write a query to find 3rd highest salary
Write a query to find 10 highest salary
Can you write syntax of case statement?
Can we create a table on exiting table?
How to create a synonym, write syntax of synonym
What are partitions, when we go for partitions?
What is cursor?
How to rebuild index, syntax of rebuild index
What is the syntax of the procedure?
How to create a BLOB, syntax of blob
What is UTL_FILE
What is control file, syntax of control file?
What truncate and delete?
How to check the table or view exiting or not

Capgemini Oracle SQL PL/SQL Project Round Interview Questions

Explain your profile(introduction, project explanation,responsibilities, client, regular activities)
What is sql tuning, how to increase the performance of query can u explain.
What is bulk collect and forall, what is the use of bulk collect and forall.
What is data migration, key points to remember migrate the data.
What is data modeling and how to create relation between A and B tables.
What kind of challenges you faced in your project and explain.

Capgemini Oracle SQL PL/SQL Interview Questions

What is difference between Char and Varchar2.
What is Trunc function where we can use it?
What is Round function and Sign function.
What INSTRING AND SUB STRING.
Difference between Translate  and Decode.
Difference between Delete and Truncate.
Which is faster Case or Decode.
What are pseudo columns in oracle
What is Implicit and Explicit cursor.
Difference between Primary key and Unique.
Difference between procedure and function.
Can we use Out parameter in Function.
Can we use return statement in procedure.
If we use out parameter in function and also use return statement , function return 2 values or one value.
What is advantages of Packages.
I have a package with 2 procedures and same name with different signature it is possible.
How many types of trigger, instead of trigger.
What is mutating table, how to resolve the error.
What are pragma exception types.
Tell me how to delete duplicate rows on a table.
I have a table and create a row level trigger on a table, i deleted 10 records,how many times records are deleted(raised another question now rows deleted what happens)
Capgemini PL/SQL Interview Questions asked in Chennai
What is your roles and responsibility?
How to communicate to your pm?
Select count(1) from dual;,what is output?
How to delete duplicate records from table without distinct clause
How to retrieve second height salary from table?
Difference between primary key & unique key?
Difference between procedure & package?
Which is better procedure or package?
What is cursor?Types of cursor? And Difference between cursors?
What is exception? Types of exceptions?
Tell me some user_defined exceptions with error codes?
What is sqlcode and sqlerr?
What is ref_cursor? Type of ref_cursors and Difference between?
How give privilege a one procedure in package?
What is %type & %rowtype? What is main use?
How to improvee performence?
What is bulk collect? What is use of limit clause in bulk    collect?
What is forall?
What is merge statement? Give me syntax?
What is view? Use of view's?
What is nocopy clause?Where it is used?
What are the environments are there in your company?
How to connect your database?
What are the shartcut of compile & execution of    programes in sql developer tool?
Are faced any trouble in your project?
What are the module's , you are working?
Which will fire default first statement level or row level   trigger?
What is bulkcollect? And any restrictions in bulkcollect?
What is the use of limit clause in bulkcollect?
How to debugg your code?
How to trace error handling?
How to find which line error was raised?
What are the methods there in save exceptions?
What is functional based index? Write syntax?
How to update complex view?
Can you alter procedure with in package?
Is it possible to open cursor which is in package in    another procrdure?
What is substr()&instr()?
Difference between case and decode?
Can you use sysdate in check constraints? If no, why?
Difference between column level constraints & table level    constraints?
What is optimizer?
What is nested_loop join?
What is pivot operater?
Tell me some oracle 10g features?
What is m_view?
What is master & detail tables?
What is sqlcode and sqlerrm?
Difference between procedure & packages?
What are the dis_adv of packages?

Birlasoft Oracle SQL PL/SQL Interview Questions

What is the use of package
What is module overloading?
What are composite data types.
What is the use of record. Why we use record.
What is the difference b/w pl/sql record and pl/sql table.
What is collection?
Difference b/w nested tables and varrays.
What is isolation table?
What are dead locks.
What are shared locks and implicit locks.
How many types of cursors we use.
What is the ref cursor and how to send unlimited data to front end.
Syntax of sql *loader.
How to get the data from database to unix environment
How to control module over loading.

Attra Infotech Oracle SQL PL/SQL Interview Questions

What is driving table?

As a developer how to tune sql query, explain the steps.

Can we create a primary key constraint on 2 columns and foreign key on other column is it possible?

Create sequence sequence_name; , this sequence generate a sequence value or not.

Write a query to find 4th highest salary (without analytical function).

10 records in excel file, i wan to load 8 records only through sql * loader, is it possible.

Explain records and collections.

What is difference between procedure and function except procedure may or not return value and function must return value.

I have table A with 1 lack records write a program using any collection , forall insert data into B table and use limit 10000.

I have created a simple view but there is no base table, what happens.

Accenture Oracle SQL PL/SQL Interview Questions

What are different types of queries
Write a correlated sub query and explain how it works
What are different type of statements
What TCL do
What is difference between stored procedure and function
How you handle errors
What is %type and %row type
What are different types of cursors
How implicit cursor works
What are different type of triggers
Write a pl/sql block for trigger at the time of insertion for inserting old and new values auditing table.
What are different types of joins
What self join do
Write an example and explain about self join
Write an example for left outer join and how output comes
What is difference between in and = operator
What is package
Tell some advantages of packages
What are collections in pl/sql
What are pointers in c
How you connect to data base
some more interview questions asked in scope
Difference between cursor for loop and for loop, which is faster?
In proc and PL/SQL, which is faster?
Which is best method to kill the process (unix or oracle)?
Difference between c shell, bourn shell, k shell?
What is inode?
How to search two patterns using grep pattern?
Difference between ls and find?
Difference between view and snapshot?
Difference between function and procedure?
Why we cannot use DDL commands in procedure?
Difference between global variable and static global variable?
How to access variable of nested procedure?
If we have both user exception and system exception, which will be handled first?
Difference between anonymous block and normal block?
Can we create snapshot for another schema?
How to access table from another database?

3i-infotech Oracle SQL PL/SQL Interview Questions

Explain about yourself and it experience?
What are the environments are you worked?
You have any experience in production?
You have any experience in unix?
What is dual? It is database object?
How many column's and row's contains dual table?
Select '1'+1 from dual; what is output?
Select * from emp where comm in (null);
Comm=null; what is output?
How to count no of records in table without count?
What is height sal whthout using max()?
Select only those employee information who are earning same salary?
How to find last inserted records from table?
Select 'vik'||null||'ram' from dual? What is output?
I have table, in column data is 'su@re#sh$', now i want to my output is 'suresh'? How to retrive?
How to retrive top 3 salaries from each departments?
Can you update complex view? If no, why?
How to know oracle use my index or not?
When should rebuild one an index?
Tell me some errors?
Tell me some restrictions on cursor variables?
What is context swithcing?
What is bulk collect?
What is nullif()?
What is mutating error?
What are the module's , you are working?
Which will fire default first statement level or row level trigger?
What is bulkcollect? And any restrictions in bulkcollect?
What is the use of limit clause in bulkcollect?
How to debugg your code?
How to trace error handling?
How to find which line error was raised?
What are the methods there in save exceptions?
What is functional based index? Write syntax?
How to update complex view?
Can you alter procedure with in package?
Is it possible to open cursor which is in package in another procrdure?
What is substr()&instr()?
Difference between case and decode?
Can you use sysdate in check constraints? If no, why?
Difference between column level constraints&table level constraints?
What is optimizer?
What is nested_loop join?
What is pivot operater?
Tell me some oracle 10g features?
What is m_view?
What is master & detail tables?
What is sqlcode and sqlerrm?
Diff b/w procedure & packages?
What are the dis_adv of packages?

3i-infotech Oracle SQL PL/SQL Interview Questions

3i-infotech Oracle SQL PL/SQL Interview Questions

Explain your project

difference between case and decode

difference between procedure and package

select * from emp what are the steps to follow sql engine to display the output

what is parsing and types of parsing

what is materialized view

what is the use of forward declaration

can we use commit in trigger, without pragma can we commit trigger it is compile or not.

we are using :old,:new , before trigger processing  what is the value of :new.

create table a;

delete from b(10 records);

roll back;

select count(*) from b; what is the output

Oracle PLSQL - Advantages of Using Packages

Advantages of Using Packages

Packages provide an alternative to creating procedures and functions as stand-alone
schema objects, and they offer several benefits.

Modularity and ease of maintenance: You encapsulate logically related programming
structures in a named module. Each package is easy to understand, and the interface
between packages is simple, clear, and well defined.

Easier application design: All you need initially is the interface information in the
package specification. You can code and compile a specification without its body. Then
stored subprograms that reference the package can compile as well. You need not define
the package body fully until you are ready to complete the application.

Hiding information: You decide which constructs are public (visible and accessible) and
which are private (hidden and inaccessible). Declarations in the package specification are
visible and accessible to applications. The package body hides the definition of the private
constructs, so that only the package is affected (not your application or any calling
programs) if the definition changes. This enables you to change the implementation
without having to recompile the calling programs. Also, by hiding implementation details
from users, you protect the integrity of the package.

Added functionality: Packaged public variables and cursors persist for the duration of a
session. Thus, they can be shared by all subprograms that execute in the environment.
They also enable you to maintain data across transactions without having to store it in the
database. Private constructs also persist for the duration of the session but can be accessed
only within the package.

Better performance: When you call a packaged subprogram the first time, the entire
package is loaded into memory. Later calls to related subprograms in the package
therefore require no further disk I/O. Packaged subprograms also stop cascading
dependencies and thus avoid unnecessary compilation.

Overloading: With packages, you can overload procedures and functions, which means
you can create multiple subprograms with the same name in the same package, each taking
parameters of different number or data type.

Restrictions on Calling Functions from SQL Expressions

Restrictions on Calling Functions from SQL Expressions:

The user-defined PL/SQL functions that are callable from SQL expressions must meet the following
requirements:

• The function must be stored in the database.
• The function parameters must be input parameters and should be valid SQL data types.
• The functions must return data types that are valid SQL data types. They cannot be PL/SQLspecific
data types such as BOOLEAN, RECORD, or TABLE. The same restriction applies to the
parameters of the function.



The following restrictions apply when calling a function in a SQL statement:

• Parameters must use positional notation. Named notation is not supported.
• You must own or have the EXECUTE privilege on the function.
Other restrictions on a user-defined function include the following:
• It cannot be called from the CHECK constraint clause of a CREATE TABLE or ALTER TABLE
statement.
• It cannot be used to specify a default value for a column.
Note: Only stored functions are callable from SQL statements. Stored procedures cannot be called
unless invoked from a function that meets the preceding requirements.

To execute a SQL statement that calls a stored function, the Oracle server must know whether the
function is free of specific side effects. Side effects are unacceptable changes to database tables.
Additional restrictions also apply when a function is called in expressions of SQL statements. In
particular, when a function is called from:
• A SELECT statement or a parallel UPDATE or DELETE statement, the function cannot modify a
database table, unless the modification occurs in an autonomous transaction
• An INSERT... SELECT (but not an INSERT... VALUES), an UPDATE, or a DELETE statement,
the function cannot query or modify a database table that was modified by that statement
• A SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute directly or
indirectly through another subprogram or through a SQL transaction control statement such as:
- A COMMIT or ROLLBACK statement
- A session control statement (such as SET ROLE)
- A system control statement (such as ALTER SYSTEM)
- Any data definition language (DDL) statements (such as CREATE), because they are
followed by an automatic commit
Note: The function can execute a transaction control statement if the transaction being controlled is
autonomous.

Oracle SQL Queries based on scenarios

Scenario 1:

The table Sales has the columns cust_id, prod_id, date_sold and qty_sold and the primary key is (cust_id, prod_id, date_sold). Date_sold has datatype DATE. Qty_sold has datatype INTEGER and is a not null column. Cust_id represents a customer and prod_id represents a product sold to the customer. Date_sold is the date on which the sale occurred. This table is populated for each customer, product and calendar date (in column date_sold) combination for all calendar dates in years 2014 and 2015. If there were no sales for a customer and product on a day, then the qty_sold is populated with 0.

Using an analytical function, write a select statement on table Sales to list all rows with date_sold in year 2015 displaying columns cust_id, prod_id, date_sold, qty_sold and the prev_qty_sold. The prev_qty_sold is computed as the quantity sold immediately prior to the date_sold of the current row for a customer and product combination. 

See example below for one customer and one product

Date_Sold Qty_Sold Prev_Qty_Sold
01/01/2015 50 
02/01/2015 40 50
03/02/2015 30 40


Solution:

select cust_id,
prod_id,
date_sold,
qty_sold,
lag(qty_sold, 1, 0) over(partition by cust_id, prod_id, date_sold order by cust_id, prod_id, date_sold) prv_qty_sold
from sales
where to_char(date_sold, 'yyyy') = '2015'
order by cust_id, prod_id, date_sold;



Scenario 2:

Table Sales has the columns cust_id, prod_id, date_sold and qty_sold and the primary key is (cust_id, prod_id, date_sold). Date_sold is of DATE datatype. Using an analytical function, write a query to list the rows with the latest date_sold for each combination of cust_id and prod_id.

Solution :

SELECT CUST_ID,
PROD_ID,
MAX(DATE_SOLD) OVER(PARTITION BY CUST_ID, PROD_ID ORDER BY CUST_ID, PROD_ID, DATE_SOLD DESC) MAX_DATE_SOLD
FROM SALES;

Oracle SQL - LEAD and LAG - Analytical Functions

Introduction

Both LAG and LEAD functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression :- Can be a column or a built-in function, except for other analytic functions.
offset :- The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default :- The value returned if the offset is outside the scope of the window. The default value is NULL.


Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,
       ename,
       job,
       sal
FROM   emp
ORDER BY sal;

SQL>

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

SQL>

LEAD

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

SELECT empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;

SQL>

Oracle SQL - Nth highest salary

Below are the queries for finding nth highest salary of employees:

Using correlated subquery:

SELECT e1.salary,e1.*

  FROM employees e1

 WHERE (n-1) = (SELECT COUNT(DISTINCT(e2.Salary))

                FROM employees e2

               WHERE e2.Salary > e1.salary);

Note : where n is the number of the highest salaried employee

  
Using dense_rank() analytics function;   

 

SELECT *

  FROM (SELECT salary, dense_rank() OVER(ORDER BY salary DESC) AS ranking

          FROM employees) emp

 WHERE emp.ranking = n;


 Note : where n is the number of the highest salaried employee

Examples:

Below query shows the employees salaries in descending order.

Below query shows  the 2nd highest salaried employee using correlatd subquery.

Below query shows the 1st highest paid salary