1) What are the difference between Package and Procedure ?
PL/SQL Packages are used to group all logically related components.
These components can be any or all of the below type:
PL/SQL Types , Variables , Exceptions , Subprograms ( Procedure & Functions).
Example:
Suppose we have 10 Procedure and 5 Functions which belong to HR module
for hiring process,exit process, salary and payout process.
we can group all these 10 Procedures and 5 Functions into 1 single Package called
HR_PKG.
Benefit of doing this is that whenever one single procedure or function is called from this
Package the entire Package gets loaded into Oracle Memory. Hence, there is a performance improvement when we call any other procedure or function from this package.
2) What are the differences between Procedure and Function ?
Procedure and function have identical structure both can be stored in DB hence called as subprograms.
But it is mandatory for Function to return a value whereas the same is not true for a procedure. A procedure may or may not return a value.
Also a function can be used in SQL Statement.
Eg:- SELECT tax(salary) FROM EMPLOYEES;
Where tax is a function which will calculate the tax for the salary given as input parameter.
3) What are Indexes and its types.
Indexes are used to speed up query performance for data retrieval.
They are special look up tables which the database engine uses for faster retrieval.
1) BTREE or Binary Tree.
2) BITMAP
3) Reverse BTREE
4) Function based Index
4) What are Materialized views?
Is a view but unlike normal views its result set is stored in DB like tables.
It is useful specially if we want to replicate data from a remote database.
Materialized views are refreshed either on commit in one of the base tables or on demand.
5) Have you worked on sql tunning? What is explain plan?
When we execute SQL query the oracle optimizer generates a execution plan using db statistics.
Explain will help you to analyse the query by showing you the execution plan of your query.
This will often show whether the query is using the relevant indexes or indexes to support the query are missing.
5) Query to delete duplicate records in a table?
DELETE FROM table_name A WHERE rowid < (SELECT MAX(rowid) FROM table_name B WHERE A.col1.name=B.col1.name AND A.col2.name =B.col2.name );
6) What are cursors and types of cursors?
Cursor is a private SQL area. This private area is created for each sql statement that is fired in database (Implicit Cursor). And we can access this area for information fetching. They are required to process each row individually for queries returning multiple rows.
Two types are Implicit and Explicit cursors.
Implicit cursor created by Oracle for each SQL statement.
Explicit cursor created by Programmer to process multiple rows one at a time.
7) Pinning of package and procedures in Oracle.
Database objects like stored procedures and packages which are frequently used can be stored in shared pool memory. This will give a performance benefit as these objects will not be parsed again and again.This is called as pinning of objects in Oracle.
Do not pin rarely used objects for obvious reasons.
Eg: To pin a package or procedure and then unpin it from memory.
EXECUTE dbms_shared_pool.keep ('MYPACK', 'P'); Pinning
EXECUTE dbms_shared_pool.unkeep ('MYPACK', 'P'); Unpinning
8) Explain in short the working of Indexes and types of Indexes.
Types : B Tree Index ( Default)
BITMAP Index
Reverse B Tree Index
Function based Index
9) Mutating Trigger Error.
This error occurs when a trigger is tries to UPDATE or SELECT from the same table which has caused the trigger to fire.
Eg: suppose we have trigger TRG_ABC which is fired on UPDATE of any column in table ABC
and also we have put a SELECT or UPDATE statement on table ABC inside the trigger body.
We can over come the above situation by make the trigger as AUTONOMOUS i.e all transaction inside the trigger will be independent of the parent transaction which lead to firing of the trigger.
