51 ORACLE SQL PL/SQL INTERVIEW QUESTIONS

Hi Guys, I have come up with my new post to put Oracle interview questions which I came across during my interview with various companies. I believe that this post will be helpful to freshers as well as experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. Some questions are having answer and some are not in below list of questions .You can post queries and doubts related with questions below, so I can answer them. Please let me know if you like the post, so you can see more post on Oracle!

Keep Sharing,Keep Learning guys :)

51 Oracle Interview Questions and Answers -

1. Reference Cursor ?

A reference cursor is a pointer to a memory location that can be passed 
between different PL/SQL clients, thus allowing query result sets to be 
passed back and forth between clients.

A reference cursor is a variable type defined using the PL/SQL TYPE 
statement within an Oracle package, much like a PL/SQL table:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

Here, ref_type_name is the name given to the type and return_type represents 
a record in the database. You do not have to specify the return type as this 
could be used as a general catch-all reference cursor. Such non-restrictive 
types are known as weak, whereas specifying the return type is restrictive, 
or strong. The following example uses %ROWTYPE to define a strong return 
type that represents the record structure of the emp table:

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;



================================================================================
2. Anonymous Block ?
An unnamed PL/SQL block is called an anonymous block.

A PL/SQL block contains three parts or sections. They are:
• The optional Declaration section.
• The mandatory Execution section.
• The optional Exception (or Error) Handling
section.

================================================================================
3. Explain how cursor is implemented in PL/SQL



A cursor is a variable that runs through the rows of some table or answer to 
some query.

CURSOR employee_cur IS SELECT * FROM employee;

Once I have declared the cursor, I can open it:
OPEN employee_cur;

And then I can fetch rows from it:
FETCH employee_cur INTO employee_rec;

and, finally, I can close the cursor:
CLOSE employee_cur;

***********
DECLARE
   CURSOR joke_feedback_cur
   IS
      SELECT J.name, R.laugh_volume, C.name
        FROM joke J, response R, comedian C
       WHERE J.joke_id = R.joke_id
         AND J.joker_id = C.joker_id;
BEGIN
   ...
END;

================================================================================
4. Constraints ?


Integrity constraints are used to enforce Business logic.
Enforcing rules with integrity constraints is less costly than enforcing the 
equivalent rules by issuing SQL statements in the application.

Only define NOT NULL constraints for columns of a table that absolutely 
require values at all times.

Use the combination of NOT NULL and UNIQUE key integrity constraints to 
force the input of values in the UNIQUE key; this combination of data 
integrity rules eliminates the possibility that any new row's data will ever 
attempt to conflict with an existing row's data.

Choosing a Table's Primary Key :
Each table can have one primary key. A primary key allows each row in a 
table to be uniquely identified and ensures that no duplicate rows exist. 
Use the following guidelines when selecting a primary key:

Choose a column whose data values are unique.
The purpose of a table's primary key is to uniquely identify each row of the 
table. Therefore, the column or set of columns in the primary key must 
contain unique values for each row.

Choose a column whose data values are never changed.
A primary key value is only used to identify a row in the table; primary key 
values should never contain any data that is used for any other purpose. 
Therefore, primary key values should rarely need to be changed.

Choose a column that does not contain any nulls.
A PRIMARY KEY constraint, by definition, does not allow the input of any row 
with a null in any column that is part of the primary key.

Choose a column that is short and numeric.
Short primary keys are easy to type. You can use sequence numbers to easily 
generate numeric primary keys.

Avoid choosing composite primary keys.
Although composite primary keys are allowed, they do not satisfy the 
previous recommendations. For example, composite primary key values are long 
and cannot be assigned by sequence numbers.

Foreign Key(Referential Integrity): Whenever two tables are related by a 
common column (or set of columns), define a PRIMARY or UNIQUE key constraint 
on the column in the parent table, and define a FOREIGN KEY constraint on 
the column in the child table, to maintain the relationship between the two 
tables.

Using CHECK Integrity Constraints
Use CHECK constraints when you need to enforce integrity rules that can be 
evaluated based on logical expressions. Never use CHECK constraints when any 
of the other types of integrity constraints can provide the necessary 
checking.

*The condition must be a Boolean expression that can be evaluated using the 
values in the row being inserted or updated.
*The condition cannot contain subqueries or sequences.
*The condition cannot include the SYSDATE, UID, USER, or USERENV SQL 
functions.
*The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM;
*The condition cannot contain a user-defined SQL function.

Eg.
CREATE TABLE dept (

deptno  NUMBER(3) PRIMARY KEY,
dname   VARCHAR2(15),
loc     VARCHAR2(15),
        CONSTRAINT dname_ukey UNIQUE (dname, loc),
        CONSTRAINT loc_check1
                CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));

================================================================================
5. Difference between Primary Key and Unique key ?

*  Primary keys are used to identify each row of the table uniquely. Unique 
keys should not have the purpose of identifying rows in the table.
* A primary key field cannot be Null, whereas a Unique column can have a 
Null value.
* There could be only 1 Primary Key per table, but there could be any number 
of Unique Key columns.
* A primary key should be a Unique column, but all Unique Key column need 
not be a PRimary Key
================================================================================
6. what are diffenent Joins explain . outer ? how would you write outer 
join(=+)
   if specify + to the right of equal to sign, which table data will be in 
full.

A join is a query that combines rows from two or more tables.
An equijoin is a join with a join condition containing an equality operator. 
( where A.b = C.d)
A self join is a join of a table to itself.
Cartesian Products
If two tables in a join query have no join condition, then Oracle returns 
their Cartesian product. Oracle combines each row of one table with each row 
of the other. A Cartesian product always generates many rows and is rarely 
useful. For example, the Cartesian product of two tables, each with 100 
rows, has 10,000 rows. Always include a join condition unless you 
specifically need a Cartesian product. If a query joins three or more tables 
and you do not specify a join condition for a specific pair, then the 
optimizer may choose a join order that avoids producing an intermediate 
Cartesian product.

An inner join (sometimes called a "simple join") is a join of two or more 
tables that returns only those rows that satisfy the join condition.

An outer join returns all rows that satisfy the join condition and also 
returns some or all of those rows from one table for which no rows from the 
other satisfy the join condition.

To write a query that performs an outer join of tables A and B and returns 
all rows from A( left outer join), apply the outer join operator (+) to all 
columns of B in the join condition.
For example,
SELECT ename, job, dept.deptno, dname
    FROM emp, dept
    WHERE emp.deptno (+) = dept.deptno;

will select all departments even if there is no employee for a particular 
dept.

Similarly to get all rows from B form a join of A & B , apply the outer join 
operator(+) to A.
Example,
    SELECT ename, job, dept.deptno, dname
    FROM emp, dept
    WHERE emp.deptno = dept.deptno (+)

================================================================================
7. Is a function in SELECT possible ?
Yes. FOr example, Select ltrim(price) from Item.
User defined functions work only from Oracle 8i onwards.
================================================================================
8. EXCEPTION handling in PL/SQL

An exception is a runtime error or warning condition, which can be 
predefined or user-defined. Predefined exceptions are raised implicitly 
(automatically) by the runtime system. User-defined exceptions must be 
raised explicitly by RAISE statements. To handle raised exceptions, you 
write separate routines called exception handlers.

Example of an exception handler:
DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   ...
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;  -- exception handlers and block end here


Unlike predefined exceptions, user-defined exceptions must be declared and 
must be raised explicitly by RAISE statements.
Example:
DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
     ...
     ...
     IF ... THEN
         RAISE past_due;  -- this is not handled
      END IF;

EXCEPTION
   WHEN past_due THEN  -- does not handle RAISEd exception
   WHEN OTHERS THEN    -- handle all other errors

END;


The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error 
messages from stored subprograms. That way, you can report errors to your 
application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax

raise_application_error(error_number, message[, {TRUE | FALSE}]);


where error_number is a negative integer in the range -20000 .. -20999 and 
message is a character string up to 2048 bytes long. If the optional third 
parameter is TRUE, the error is placed on the stack of previous errors. If 
the parameter is FALSE (the default), the error replaces all previous 
errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as 
with package STANDARD, you do not need to qualify references to it.

An application can call raise_application_error only from an executing 
stored subprogram (or method). When called, raise_application_error ends the 
subprogram and returns a user-defined error number and message to the 
application. The error number and message can be trapped like any Oracle 
error.

Another way to handle exception is to use DECODE statements. For example:
INSERT INTO stats (symbol, ratio)
   SELECT symbol, DECODE(earnings, 0, NULL, price / earnings)
   FROM stocks WHERE symbol = 'XYZ';

Here the DECODE function checks whether the earnings is 0 and if it is zero, 
then returns earnings, else price/earnings.
================================================================================
9. When an exception is triggered in a loop, how you
continue to next itereration ?
By having an exception andling block within the loop.

================================================================================
10. Decode ? Using Decode map this logic,

If A>B Display 'A is Big', If A=B Display 'A equals B'
Else 'B is Big'

DECODE(Greatest(A,B), A, 'A is big',
                      B, 'B is Big',
                         'A equals B')

================================================================================
11. What is an index? what are diff type of indices? what is Clustered and 
Non Clustered Indeces ?

Indexing is typically listing of keywords alongwith its location and is done 
to speed up the Data base.

Create index WORKERSKILL_NAME_SKILL on WORKERSKILL(Name,Skill);

A clustered index is a special type of index that reorders the way records 
in the table are physically stored. Therefore table can have only one 
clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order 
of the index does not match the physical stored order of the rows on disk. 
The leaf node of a nonclustered index does not consist of the data pages. 
Instead, the leaf nodes contain index rows.
================================================================================
12. Mutating Error ?
When we use a row level triger on a table, and at the same time if we 
query/insert/delete/update on the same table, it will give the mutating 
error.

================================================================================
13. How to avoid mutating error?
Two possible solutions.
1. Change the design of the table so as to avoid querying or any update on 
the table on which the row level trigger is holding on.
2. Create a package which holds a PL/SQL table with the RowID of the rows 
need to insert/update and a counter of the rows
a. In BEFORE STATEMENT trigger set the counter to zero.
b. In every ROW trigger call a procedure from the package which will 
register the RowId and modify the counter
c. In AFTER STATEMENT trigger call a procedure which will make the needed 
checks and update the particular rows of the table.
If you are not familiar with PL/SQL tables alternatively you can use 
temporary Oracle tables. It is also a good idea.

================================================================================
14. Difference between Delete and Truncate.
Truncate will permenantly delete the record and no rollback capability will 
exist.
The delete command remove records from a table. Delete moves records to the 
rollback segment enabling rollback capability.
Excessive use of triggers can result in complex interdependencies, which can 
be difficult to maintain in a large application.


================================================================================
15. How many types of triggers are there and what are ? (Before Insert, 
after Insert, update,
delete etc..)

Triggers are similar to stored procedures. A trigger stored in the database 
can include SQL and PL/SQL or Java statements to run as a unit and can 
invoke stored procedures. However, procedures and triggers differ in the way 
that they are invoked. A procedure is explicitly run by a user, application, 
or trigger. Triggers are implicitly fired by Oracle when a triggering event 
occurs, no matter which user is connected or which application is being 
used.
Different Types
---------------
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers ( ON views,for INSERT etc, instaed of Inserting, it will 
carry out the trigger statements)
Triggers on System Events(shut down) and User Events ( log on)
================================================================================
16. What all composite datatypes in oracle
Composite Data types:

Table

1. Is similar but not the same as a database table
2. Must contain only one column of any scalar datatype
3. Is like a one-dimensional array of any size
4. Has its elements indexed with a binary integer column called the primary 
key of the table

Record

1. Contains uniquely defined columns of different data types,
2. Enables us to treat dissimilar columns that are logically related as a 
single unit.

================================================================================
17. what is PL/SQL tables?


================================================================================
18. What are the default packages provided by oracle
The ones with "DBMS_" prefix. Eg. DBMS_Output, DBMS_ALERT

================================================================================
19. What is the diff between Where and Having

Having clause is used where group by clasue is used to restrict the groups 
of returned rows to those groups for which the specified condition is TRUE.
The HAVING condition cannot contain a scalar subquery expression
eg:
SELECT department_id, MIN(salary), MAX (salary)
   FROM employees
   GROUP BY department_id
   HAVING MIN(salary) < 5000;

================================================================================
20. How is error Handling done?


================================================================================
21. I have table that contains state codes,
I might have more than 1 rows with the same state code, how can I find out?
select count(*), state_code from table. IF this is > 1 , then we can assure 
that there are duplicates.


================================================================================
22. How do you copy rows from Schema a , table p to
table p in schema b ?
To copy between tables on a remote database, include the same username, 
password, and service name in the FROM and TO clauses:

COPY FROM HR/your_password@SchemaA -
TO HR/your_password@SchemaB -
INSERT tableP-
USING SELECT * FROM tableP


================================================================================
23. What is %Rowtype and %Type

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)
..Means the INput Parameter Dept_num should have the same Data Type as 
Emp_tab.DeptNo.

Use the %ROWTYPE attribute to create a record that contains all the columns 
of the specified table. The following example defines the Get_emp_rec 
procedure, which returns all the columns of the Emp_tab table in a PL/SQL 
record for the given empno:

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

You could call this procedure from a PL/SQL block as follows:

DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || 
Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;



================================================================================
24. How to use the cursor with using open, fetch and close.?

================================================================================
25. using select Statement how you will retrieve the user who is logged in?

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
FROM DUAL;

================================================================================
26. When Exception occurs, i want to see the error generated by Oracle. How 
to see it?
SQLERRM
================================================================================
27. how to generate the out of the select statement in a file?
SPOOL

================================================================================
28. how to find the duplicate records?

select count(*), job from emp group by job having count(*) > 1

then there are duplicates.
================================================================================
29. What is Procedure, function, package and anonymous block?

Procedures are named groups of SQl statements.
Functions are like Procedures, but can return a value.
Packages are groups of procedures, functions, variables and SQL statements.
An unnamed PL/SQL block is called an anonymous block.
================================================================================
30. What is group by funcion and where it is used.?
Specify the GROUP BY clause if you want Oracle to group the selected rows 
based on the value of expr(s) for each row and return a single row of 
summary information for each group.
Expressions in the GROUP BY clause can contain any columns of the tables, 
views, or materialized views in the FROM clause, regardless of whether the 
columns appear in the select list.


It is used for aggraegate functions.

================================================================================
31. What is the difference between RDBMS and DBMS? Sol: Chords 12 rules.



================================================================================
32. What is normalised and denormalised data?
process of removing redundancy in data by separating the data into multiple 
tables.


================================================================================
33. What is a VIEW?
A view is a custom-tailored presentation of the data in one or more tables. 
A view can also be thought of as a "stored query." Views do not actually 
contain or store data; they derive their data from the tables on which they 
are based.

================================================================================
34. Can a view update a table?

Can, but with restrictions.
Like tables, views can be queried, updated, inserted into, and deleted from, 
with some restrictions.
All operations performed on a view affect the base tables of the view.
================================================================================
35. What happens if there is an exception in the cursor?
How do we ensure that the execution for other records in the cursor does not 
stop.



================================================================================
36. What are cursors ? After retrieving the records into the cursor can we 
update
the record in the table for the retrieved record. What effect will it
have on the cursor?

Cursors
Oracle uses work areas to execute SQL statements and store processing 
information. A PL/SQL construct called a cursor lets you name a work area 
and access its stored information. There are two kinds of cursors: implicit 
and explicit. PL/SQL implicitly declares a cursor for all SQL data 
manipulation statements, including queries that return only one row. For 
queries that return more than one row, you can explicitly declare a cursor 
to process the rows individually. An example follows:

DECLARE
   CURSOR c1 IS
      SELECT empno, ename, job FROM emp WHERE deptno = 20;


The set of rows returned by a multi-row query is called the result set. Its 
size is the number of rows that meet your search criteria
Multi-row query processing is somewhat like file processing. For example, a 
COBOL program opens a file, processes records, then closes the file. 
Likewise, a PL/SQL program opens a cursor, processes rows returned by a 
query, then closes the cursor. Just as a file pointer marks the current 
position in an open file, a cursor marks the current position in a result 
set.

You use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN 
statement executes the query associated with the cursor, identifies the 
result set, and positions the cursor before the first row. The FETCH 
statement retrieves the current row and advances the cursor to the next row. 
When the last row has been processed, the CLOSE statement disables the 
cursor.


================================================================================
37. What are user defined data types ?

SUBTYPE subtype_name IS base_type;
Eg. SUBTYPE EmpDate IS DATE;           -- based on DATE type
SUBTYPE ID_Num IS emp.empno%TYPE;  -- based on column type

   CURSOR c1 IS SELECT * FROM dept;
   SUBTYPE DeptFile IS c1%ROWTYPE;    -- based on cursor rowtype


To specify base_type, you can use %TYPE, which provides the datatype of a 
variable or database column. Also, you can use %ROWTYPE, which provides the 
rowtype of a cursor, cursor variable, or database table.

A subtype does not introduce a new type; it merely places an optional 
constraint on its base type.

================================================================================
38. How to copy the Structure and data from one table to another in one SQL 
stmt?
COPY {FROM database | TO database | FROM database TO database} 
{APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, 
...)]
USING query

where database has the following syntax:

username[/password]@connect_identifier
================================================================================

39. Describe UNION and UNION ALL.  UNION returns distinct rows selected by 
both queries while UNION ALL returns all the rows. Therefore, if the table 
has duplicates, UNION will remove them. If the table has no duplicates, 
UNION will force a sort and cause performance degradation as compared to 
UNION ALL.
================================================================================
40. What is 1st normal form?  Each cell must be one and only one value, and 
that value must be atomic: there can be no repeating groups in a table that 
satisfies first normal form.
================================================================================
41. What is 2nd normal form?  Every nonkey column must depend on the entire 
primary key.
================================================================================
42. What is 3rd normal form? (another explanation than #1)  No nonkey column 
depends on another nonkey column.
================================================================================

43. What is 4th normal form?  Fourth normal form forbids independent 
one-to-many relationships between primary key columns and nonkey columns.
================================================================================
44. What is 5th normal form?  Fifth normal form breaks tables into the 
smallest possible pieces in order to eliminate all redundancy within a 
table. Tables normalized to this extent consist of little more than the 
primary key.

45. How to find out top n salary from a employee table?
================================================================================

46.If we will modify a function , which is defined in package even though stored package will be valid ?

================================================================================

47.How do you define public and private procedure in oracle?

================================================================================

48.If same kind of logic we put in function as well as procedure then which one will be faster?

================================================================================

49.If we deleted any rows in table and commited then is it possible to retrieve the deleted data?

================================================================================

50. While handiling excecption if we have written “When others then” first before other exception handler then what will happen?

================================================================================
51. What is sql injection attack???

<form method="post" action="http://testasp.vulnweb.com/login.asp">
<input name="tfUName" type="text" id="tfUName">
<input name="tfUPass" type="password" id="tfUPass">
</form>
The easiest way for the login.asp to work is by building a database query that looks like this:
SELECT id
FROM logins
WHERE username = '$username'
AND password = '$password’
If the variables $username and $password are requested directly from the user's input, this can easily be compromised. Suppose that we gave "Joe" as a username and that the following string was provided as a password: anything' OR 'x'='x
SELECT id
FROM logins
WHERE username = 'Joe'
AND password = 'anything' OR 'x'='x'
As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
The 'x'='x' part guarantees to be true regardless of what the first part contains.
This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!


                                                            COPYRIGHT © 2013 TECH BRAINS