Oracle PL/SQL Interview Questions

Hi All,

I would like to share some Oracle PL/SQL interview questions here which I faced in all my interviews. Some of them are simple, some are tricky.Hope they help you.

Oracle PL/SQL Questions:

1. What is the difference between a view and a materialized view ?

Ans:

i)A View is stored in database as SQL statement. It is created as and when needed.

Materialized view is created as a table and stored physically in the database.

ii) Views created from multiple tables cannot be modified Materialized view can be modified

iii) Views are used to hide the complexity of SQL statement. For Eg: if you have a SQL statement with 3 tables joined by UNION with different WHERE clauses, it will be faster to have a view and just select from the view instead of complex SQL stmt.

Materialized views are the tables with pre-calculated joins and aggregated data from multiple tables. They improve performance as the it precalcutes the joins and aggregations and stores the results into the database even before the execution. When the query is executed, the query optimizer rewrites the query to use the materialized view instead of the underlying detail tables.

2. What are outer joins ? Name its types ?

Ans : Outer joins are used to join 2 tables so that the unmatched rows from one table are also retrived along with the matching rows. There are 3 types of outer joins: Left outer join, right outer join and Full outer join.

Left Outer Join :unmatched rows from left table, (+) should to attached to the right table.

Right outer join :unmatched rows from right table, (+) should to attached to the left table.

To avoid the confusion with (+) symbol, from oracle 9i, we use the word ' left outer join' Eg: select....from emp e LEFT OUTER JOIN dept d where emp.deptno = d.deptno;

Full Outer join is a combination of left and right outer join. For eg: you want to see a list of all projects and the tasks associated with all projects.

Eg: select t.task_name, p.project_name

from all_tasks FULL OUTER JOIN all_projects

where t.project_id = p.project_id;

3. How does the use of a package improves the performance ?

Ans: i) When you call a packaged function or subrpogram, the whole package is called and stored into the the memory. So the next time when we call any packaged function/subprogram, there is no need of disk I/O

ii) Packages stop cascading dependencies and unnessasary recompiling. When a packaged function is compiled, Oracle does not recompile the calling subprogram. That program will be recompiled only if we made any changes in the specs of the calling program.

4.Can we have a procedure declared in package body but not declared in package specifications?

Ans: Yes.But that procedure will be local and can not be called by other procedures in the package.When we declare a proc in package spec, it becomes global for the package.

5. Can we have a package spec without a body?

Ans:Yes

6. Can we have a procedure spec without body ?

Ans: No. It should atleast have NULL; inside begin and end.

7. Can we have a commit in trigger?

Ans: Yes. You can commit inside the trigger but you have to declare it as AUTONOMOUS_TRANSACTION.

8. How do u use variable in code of a trigger ?

Ans: by using':old' and ':new' clauses. Eg :old.salary, :new.salary

9. You wrote a PL/SQL stored procedure. How do u make it hidden so that NOBODY,not even the DBA or the owner can read the procedure ?

Ans: Use Wrap feature of Oracle 9i. Once wrapped, NO one, can read the code. Eg : Wrap iname=c:\programs\my_file.sql. OR wrap iname=c:\programs\my_file.plb. .plb extension is PL/SQL Binary file, output file of the wrap function.

10. What do you know about STANDARD package in Oracle ?

Ans: A package named STANDARD defines the PL/SQL environment. The package spec includes All the types, functions, procedures, etc which are automaticaly available to PL/SQLprograms. For eg: ABS(),SUM(),COUNT()

11. Name some Oracle's built-in packages?

Ans:

DBMS_OUTPUT - used to display a messgae, especially while debugging

DBMS_PIPE - This allows 2 different sessions of the database to communicate over the named pipe.

DBMS_DDL - Contains procedures to wrap single PL/SQL units like package specs, package body, function, procedure, type specs, type body, etc. Eg : DBMS_DDL.CREATE_WRAPPED();

DBMS_ALERT - lets you use database triggers to alert an application when some database values change

UTL_FILE - it allows the PL/SQL program to read from and write into text files.

UTL_SMTP - allows the PL/SQL program to send emails over Simple Mail Transfer Protocol

UTL_HTP, HTF - allows PL/SQL to generate HTML tags.

UTL_HTTP - allows PL/SQL programs to make HTTP calls. It gets the data from Internet. The inputs ar the URL and the contacts of the website. The return data is usually in HTML format.

DBMS_SQL - Helps you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. For ex: it allows us to create a procedure based on a table, whose name is given at run-time.

12. What are database trigger? Types of database trigger?

Ans: These are the stored subprograms associated with tables, views , schemas or database

Table level triggers - 3 ways to catagorize these triggers : Before/After , Insert/Update/Delete, and Row-level (for each row) vs statement-level

View level - We cannot use the Before/After triggers on views, so we use " Instead of " triggers. Eg: Create or replace trigger trg_cal_sal INSTEAD OF INSERT on v_emp....

System triggers on database or Schema level - event triggers : Eg: LOGON, LOGOFF

13. What are implicit and explicit Cursors? Which one is recommended ?

Ans: A cursor is a handle or a pointer for the memory associated with a specific SQL statement. A cursor is basically an area allocated by Oracle for executing the Sql Statement. An implicit cursor statement are managed by Oracle itself and generally used for Single row query. When u need precise control on the cursor and its processing, use Explcit cursors used for multi row query.

Implicit cursor is always faster than explicit cursor.Bcoz, expilit cursor will have more no of lines, and the rule is "Less the no of lines less is the processing time." Also, implicit cursor does exception handling for NO_DATA_FOUND and TOO_MANY_ROWS by itself . We need not to write these exceptions. In explicit cursors, we have to write these exceptions.

14. What is mutating error? How do u resolve it?

Ans : When you perform a DML query (like Insert, Update, Delete) on a table,and another query is accessing the same table at the same time,none of the queries get completed and the table is called as mutuating table.It will then throw a mutating error. You can perform only 'Select' on such a table. You can resolve this error by using a PL/SQL table.

15.What is the difference between cursor & ref cursor?

Ans: Cursor is a handle or pointer to the memory location associated with a SQL statement. With a REF cursor, you can define a cursor variable, which will point to that memory space. With Ref cursor, we can dynamically retrive data in form of recordset from a PL/SQL stored procedure.

It works very similar to explicit cursor except that we define the query for this cursor at run-time.

The syntax of explicit cursor :

Cursor emp_cur IS

SELECT * from emp;

.......

OPEN emp_cur;

Syntax of REF cursor :

TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --cursor type

emp_curvar emp_curtype; --cursor variable

.........

v_state := 'NY'

OPEN emp_curvar FOR select * from emp where state = v_state;

..........

v_state := 'NJ'

OPEN emp_curvar FOR select * from emp where state = v_state;

As you can see, REF cursors can be reused for different SQL queries at runtime.

There are 2 types of REF cursors : Strong and weak.

TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --Strong REF cursor

TYPE emp_curtype IS REF CURSOR; --weak REF cursor

Which one is better ? depends on your requirement. In strong cursor, we attach the return ROWTYPE at the declaration time itself. So, the compiler knows what the cursor variable should return.So, its less error prone. Weak cursors , on the other hand , are more flexible because they can be used with any query, any return ROWTYPE , even different ROWTYPES at different calls to it.

16. What are different options in function declaration ?

Ans:

AUTHID : Specifies how to execute the function. Ex: DEFINER/CURRENT USER

PARALLEL_ENABLE: Specifies if we can run the function parallelly

DETERMINISTIC:

AUTONOMOUS_TRANSACTION: Specifies if this function is an Autonous Transaction
17. What is PRAGMA ? What are the different types of PRAGMA ?

Ans: Pragmas are specially formatted comments. Pragma tells Oracle to use specific rules other than the default rules for a particular object. They are processed at compile time, not at run time.

1. RESTRICTED_REFERENCES : This pragma checks if the function meets the rules.

create package cal_loans as

function credit_ok return boolean;

PRAGMA RESTRICTED_REFERENCES(credit_ok, RNPS,WNDS );WNDS - Write No Database State

RNPS - Read No Package State
2. EXCEPTION_INIT : This pragma is used to bind a user-defined exception to a particular number.

Declare MyExcep exception;

PRAGMA EXCEPTION_INIT(MyExcep,10001);

begin

..…

exception

when I then

dbms_output.put_line( 'Duplicate value');

end;

3. AUTONOMOUS_TRANSACTION: Its a kind of PRAGMA which changes the way a subprogram works within a transaction. A child program marked with this pragma can be committed and rolled back without committing or rolling back the parent transaction.

Eg : When you declare a trigger as AUTONOMOUS_TRANSACTION, then you can commit inside the trigger, irrespective of the calling procedure.

CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;

4. SERIALLY_REUSABLE: This pragma tells the PL/SQL engine to reuse the package data. By default, the package level data is available throughout the session, by specifing this pragma, the data will not persist after the package is executed. You should specify the PRAGMA in both, package specification and body.

18.How to avoid errors with Autonomous transactions?

Ans:

1. If the autonomous transactions uses the resources used by main transac, a deadlock will occur.

2. The auto transac should be commited or rolled back beforeexiting, or oracle will give error.

3. The maximum no of concurrent transac is set by Oracle. That number might be exceeded as autonomous transac and main transac run concurrently.

19.What is the difference between Autonomous Transaction and Nested Transaction ?

Ans: As in case of Nested Transaction, Autonomous Transaction :

1. Do not share trasaction resources like variables with main transaction.

2.Have to be commited or rolled back separately.

3. It does not depend on main transaction. When main transaction rolls back, nested transaction also rolls back but autonomous transaction does not.

4.Exceptions raised in autonomous transaction are transaction-level and not statement-level.

20.What is the difference between ANY and ALL conditions?

Ans:ANY will check for the rows in the right side matching the condition and returns the rows for which the condition is true. It will not wait and search for all rows.

ALL will check if the condition is true for ALL rows and then only it will return the value.

21.What is the difference between UNION and UNION ALL?

Ans: Both do the same job but UNION ALL will give all rows,whereas UNION will skip the duplicate rows and give only distinct rows. Hence, UNION is faster than UNION ALL.

22.What is the difference between CHAR and VARCHAR2?

Ans:Char is used for fixed length strings, varchar2 is used for variable length string. Char is faster than varchar2,so if the length is fixed and not very long, it is recommanded to use Char.

23.What is the difference between EXISTS and IN operator?

Ans:IN works same as OR operator. The IN statement is internally converted into no of OR statements.It returns all the values for which the condition matches. EXISTS first analyzes the subquery to check if it returns any rows. If yes, then it returns True, else it returns False.

Exists - always has a subquery, IN- may have a subquery of list of values.

Example:

SELECT e.* from employees e

where exists(select deptno from dept d where e.deptno = d.deptno);

24.What are Global Temporary tables?

Ans: The temporary tables are the database tables or PL/SQL tables. The maintainance and management of these tables is handled by Global Temporary Tables. They are temporary because the data is available only to the current session. They are called as Global because the table definition is available to all sessions.

Example: Create global temporary table my_g_temp_tab(

name varchar2(20), city varchar2(10) )

on commit delete rows /*OR on commit reserve rows*/;

25.