oracle interview questions and answers

Oracle Interview Questions and Answers

by GangBoard Admin, December 8, 2018

Although a student becomes adept at handling any type of question after successfully completing the Oracle tutorial and certification from the institutes of GangBoard, this list of Oracle interview questions and answers will come in handy for a quick revision and when you are a bundle of nerves before the interview. However, this Oracle interview question and answer is for not only learned Oracle professionals but also beginners and those with no knowledge can use it to understand the terminologies and the fundamentals of Oracle. The Q & A includes topics from beginner, intermediate, to the most advanced level explained in Layman’s language.

Best Oracle Interview Questions and Answers

This field is growing at an astounding pace due to the high demand for certified professionals. Companies prefer individuals who are certified in the technology. We have observed that students who are adept in the use of Oracle and even score high marks in the certification fail to justify the same during the interview. This is one of the reasons why we have compiled the elaborate list of questions on Oracle and the answers are provided by the expert tutors who themselves are Oracle professionals and several are recruiters themselves for leading multinationals hiring Oracle certified professionals.

These Oracle interview questions and answers are based on mega trending topics. If you master most of these topics, there will be no Oracle interview question that you cannot answer and secure your dream job.

Q1) What is the use of Oracle ASM?


  • ASM is a file system for oracle, and controlled by oracle volume manager.
  • Oracle asm used for standalone databases as well as Cluster database (RAC)

Q2) Difference between normal file system and ASM?

Answer: Load balancing is high in ASM file system compare to the traditional file system. IN oracle asm we call it as ASM striping.

  • To balance the loads across all the disks in a disk group
  • The I/O latency is very low
  • And also we can specify the redundancy level Normal or High like RAID.

Q3) What is ORACLE AFD?

Answer: Oralce asm Filter driver(ASMFD). It is a kernel module resides in the I/O paths of the oracle ASM disks. It is optional one. After completed Grid installation we need to configure the ASMFD to the system. In case if we used oracle asmlib we need to migrate ASMLIB to ASMFD. Then we need to cleanup the oracle asmlib. Usually when the server restarted the need rebind the disk devices with oracleasm, to simplifies this the Oracle ASMFD we need to configure. It will allow only oracle I/O requested and will rejects the invalid or non Oracle I/O requests.

Q4) How to create Disk Group?

Answer: The disk group will have one or more number of disks, And is the fundamental object for ASM manager.

To create the disk we need to use the command

oracleasm createdisk ORADATA /dev/sdb1

Oracle database files are allocated from disk groups. A disk groups might contain several databases. And single database can use multiple disk groups. What are all files we are storing inside the disk groups we call it as ASM files.

Q5) How to list the Disk Groups?


  • To list the disk groups we are having more views below are the some example
  • V$ASM_DISKGROUP – Group name, size related information, state
  • V$ASM_FILE – list the ASM files and the groups
  • V$ASM_ALIAS – Disk group mounted by oracle ASM

Q6) How to drop Disk groups?


  • We need to mount the disk group which we are going to drop.
  • Need to specify INCLUDING CONTENTS for dropping all the files in this disk group.
  • Need to perform the above command in ASM instance not normal database instance.

Q7) Difference between the ORACLE asmlib and ORACLE afd?


  • Oracle asm usually rebind the asm files once the server restarted
  • Oracle asm might allow non oracle I/O to Oracle files
  • Oracl ASMFD is a kernel module and it will avoid the non oracle I/O request.
  • ORACLE ASMFD is available from the release 12.2 linux verion

Q8) How to start ASM instance?

Answer: ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has minimal amount of SGA compare to normal instance.

Q9) What is the use of ASM instance?

Answer: ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has minimal amount of SGA compare to normal instance. In cluster database we will have separate ASM instance for each nodes.

Q10) What is ACFS file system?


  • Oralce automatic storage management cluster file system(ACFS).
  • The ACFS files can be accessed from outside of the databases.
  • It is a scalable and multi-platform file system
  • ACFS does not support Grid infrastructure files
  • ACFS does not support OCR and voting disk related files

We can use the ACFS file system for backup related information like RMAN, Data pump DUMPSET and archived log files

 Q11) What is the use of OCR file?


OCR is a file that contain the information about the Oracle database configuration information to manages cluster.  It will create and placed on shared storage while installation oracle cluster ware.

Q12) What is the use of Voting Disk?


The Voting disk is a file that contains information about node membership. It will create and placed on shared storage while installation oracle cluster ware. It will reassign the cluster ownership between the nodes in case of failure.

Q13) What is the use of CRS in oracle RAC?


CRS- cluster ready service provides the cluster software which is installed in each hosts via OUI cluster installation.  Oracle clusterwate manages as called as CRS resources. Like database , instances VIP address , listener and services

Q14) What are all the cluster ware storage?


  • Raw devices
  • OCFS file system
  • ASM file system

Q15) What is cache fusion in oracle RAC?

Answer: For instance recovery in RAC database it will first look into the cache, if it is not available it will look in to the remote cache instead of looking into the disk. This is call it as cache fusion. We are using cache coherency mechanism for cache fusion. IT depends on three services.

  • Global Resource Directory (GRD)
  • Global Cache Services (GCS)
  • Global En-queue Services (GES)
  • Where we need to use CVU?
  • Cluster verification utility (CVU) to verify that all the nodes will meets all the criteria for oracle clusterware installation.

Q16) How to start database in Oracle Rac database?

Answer: We can use SRVCTL command to start the database in oracle RAC environment.

  • To start all the instance
  • Srvctl start instance –db db_name
  • To start a particular instance using by using instance name
  • srvctl start instance –db db_name –instance “instance_name1,instance_name2”
  • To start the database using node name
  • Srvctl start instance –db db_name –node node_name

Q17) How the Function and Procedure are differ to each other ?


  • Functions are generally used for computation purpose, whereas procedures are used to implement the business logic.
  • Functions can be called through SQL query, but procedure can’t.
  • Function must returns a value, whereas procedure may or may not.

Q18) Can we create package body, without creating a package specification ?

Answer: We must create the package specification first. Specification can be created without body, but vice versa cannot be possible.

Q19) Explain the mutation of the trigger

Answer: It occurs when a Trigger tries to update a row that is currently in execution stage. So it can be solved by using temporary tables and views.

Q20) How to generate primary key on a table without using sequence ?

Answer: We can refer the following example for that :


a number(2) := 10;


— while loop execution


dbms_output.put_line (‘value of a: ‘ || a);

a := a + 1;

IF a > 15 THEN

— terminate the loop using the exit statement





Q21) How to handle the exception for duplicate record entry?

Answer:  We can use dup_val_index as an exception handler.

Q22) What will happen, if we will write the exception section like below :


WHEN others THEN


WHEN no_data_found THEN

dbms_output.put_line(‘No such customer!’);

no_data_found exception will never be occurred as it will always get the ‘others’ exception handler at first place.

Q23) What are the PL/SQL cursors?

Answer: Oracle uses workspaces to execute the SQL commands. That means, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access of it’s information.

Q24) What is returned by the cursor attribute SQL%ROWCOUNT?

Answer: It returns the number of rows that are processed by a SQL statement.

Q25) We have the following datasets from team_a and event_a table:

sql> SELECT * FROM team_a;


| no | fname        |


| A1 | A. Das       |

| A2 | B. Moothoot  |

| A3 | C. Paul      |


sql> SELECT * FROM event_a;


| no | action         | a_id      |


| B1 | 10 meter dash  | A2        |

| B3 | cross-country  | A5        |

| B4 | triathalon     | NULL      |

Now, what should be the answer of the following query :

SELECT * FROM team_a WHERE no NOT IN (SELECT a_id FROM event_a)

Answer:  The answer should be null as if there is any null exists in a dataset, then the output of the whold set is becoming the null only.

Q26) Check the below scenario :

We need to convert from meter to inches. Instead of doing manually, need to create an object which should execute each time as a converter.

What is the suitable way to implement this ?

Answer:  Best way to write a function, which should have meter to inches conversion logic and should be execute from the SQL statements.

Q27) If we have a function, declared in a package body, can be called from the outside of the package ?

Answer: No, because that kind of function will be treated as private one, not the public or global one.

Q28) Can we use one select query in another select query ?

Answer: Yes. Using subquery.

Q29) We have given table emp


Then what should be the output of the following query :

Select a.*,nvl2(aph_no,’null’,’not null’) null_col from emp;


Output should be like this :

2XYZnot null

Q30) you need to compare two values,   if both are equal, then the result will be null  and if not equal then the first value will be returned.

Which function should you use?


B – NVL2



Answer: C – NULLIF

Q31) Which of the following is  true about the COUNT function?

A – COUNT(expression) returns the number of rows with non-null values for the expression.

B – COUNT(DISTINCT expression) returns the number of unique, non-null values in the column.

C – COUNT(*) returns the number of rows in the table.

D – All are true.

Answer: D – All are true.

Q32) Check the follwing example :

insert into emp values (1,’abc’);

insert into emp values (2,’bcd’);

insert into emp values (3,’cde’);

savepoint sp1;

insert into emp values (4,’def’);

insert into emp values (5,’efg’);

insert into emp values (6,’fgh’);

savepoint sp2;

insert into emp values (7,’ghi’);

insert into emp values (8,’hij’);

savepoint sp3;

Now if you want to keet id 7 and 8, but not 4,5,6. So you have executed the below command :

rollback to sp1;

What will happen ?

Answer: After savepoint sp1, all the inserted records will be deleted.

Q33) Is a NULL value is equal zero or a blank space? If not then what is the difference?

Answer: A NULL value is not equal as zero or a blank space. But still it’s a value which can occupy space. But blank space and 0 are the character and number respectively.

Q34) For a materialized view, is that possible to auto refresh the structure of the base table ? So that whenever that is changed, corresponding mat view is geeting also changed.

Answer: No, it’s not possible. Mat view can be refreshed only for data, not for the table structure. So, if the structure is changed, we need to drop and re-create the mat view.

Q35) Can any view have any DML operation ?

Answer: yes. Using of instead of triggers, a view can manipulate the data.

Q36) Can sorting is possible using a column alias?

Answer: Yes. A column alias instead of the actual column name can be used in the ORDER BY clause.

Q37) What is the requirement of MERGE statement ?

Answer:  The MERGE statement is doing update or insertion of data conditionally into a database table.  It performs an UPDATE if the row/s is/are already exists, or an INSERT if the row/s does not exist.

Q38) Which DDL statement is used to add, modify or drop columns in a database table?

Answer: The ALTER TABLE statement.

Q39)  Please select the correct answer from the below statements :

i) Union returns all the dataset including the duplicate one

ii) Union All returns all the dataset including the duplicate one

Ans: ii) Union All returns all the dataset including the duplicate one

Q40) How do you find a number as integer or floating ?

Answer:  select case when 111 – floor(111) < 1 and 111- floor(111) > 0 then ‘Decimal’ else ‘Integer’ end from dual;

Note : Taking example of an integer value : 111

Q41) What are the NVL and the NVL2 functions in SQL? How do they differ?

Answer:  NVL is required only to replace null value of a particular column, whereas NVL2 is required to replace the null as well as the not value of a particular column.

So, NVL has required 2 parameter and NVL2 3 parameters.

Q42) How to find the 4th highest salary from the Emp table. Write the query.

Answer: SELECT Salary, salary_rank FROM


SELECT DISTINCT Salary,RANK() OVER (ORDER BY salary DESC) AS salary_rank  FROM hr.Employees

) where salary_rank <= 4;

Q43) How do you find a duplicate record?

Answer: select attributeid,count(1) from attr_adi group by attributeid having count(1) > 1;

Q44) How to Delete a duplicate record?

Answer: delete from attr_adi where rowid not in (select min(rowid) from attr_adi group by attributeid);

Q45) How to a create alike table structure, but without records of an existing table ?

Answer: create table emp_1 as select * from emp where 1=2 ;

Q46) How to replace all the numeric characters with ‘NUMBERS’ and Alphabets with ‘ALPHABETS’


[‘a’, ‘z’, ‘E’, 3, ‘f’, 6, 2, 0, ‘Q’]

should output:


Ans : SELECT col1, case when upper(col1) = lower(col1) then ‘NUMBERS’ else ‘ALPHABETS’ end as alphanumeric from table;

Q47)  What is Query to find Nth highest salary for employee using With Clause










Note : N means any numbers can be put

Q48) How to find a table name with its owner ?

Answer: Select owner,table_name from all_tables where table_name = <table> ;

Q30. How to add the email validation using only one query?


SELECT email from emp

where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

Q49) How to put a condition with case incensitivity

Answer: select * from emp where lower(ename) = lower(‘John Smith’);

Q50) How can you fetch first 5 characters of the string?

Answer: select substr(name,1,5) from table;

Q51) What should be the SQL for the following output (rounded to next 10):


Answer: select cell (cell(101.000001)/10)*10 from dual;

Q52) Whether Foreign key contains null values ?

Answer: No. Although logically it can’t be as any primary key can not contains any null values. But Oracle gives this facility for the data conveniency.

Q53) How do you declare a user-defined exception?

Answer: User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax − <exception_name> EXCEPTION;

SQL Interview Questions and Answers

Q1) What  is the difference between TRUNCATE and DELETE commands?


It is a DDL statementIt is DML statement
Truncate command will remove all the rows from a table, leaving the table empty.Using Delete command we can remove specific rows from a table using where clause.
Once table is truncated the data is lost and data cannot be rollback.Data can be rollback before issuing COMMIT
It is an auto commit statement.It is not an auto commit statement.
In case of TRUNCATE Trigger doesn’t get firedBut in DML commands like DELETE. Trigger get fired
It free up the memory space
Truncate is faster than delete

Q2) Which system table contains information on constraints on all the tables created?


USER_CONSTRAINTS table contains the information on constraints created on all the tables.







Q3) Difference between decode and case?



  • It specify the single column
  • Can compare only discrete values
  • Cannot process null


  • It specify the multiple columns
  • Can handle range values (between, <, >)
  • Processing time is faster when compared to Decode
  • Can process null
  • All the expressions (columns) must be of the same date types.

Q4) Types of functions


Types of functions

  • Single row functions
  • Multiple row functions

Single row functions 

  • Case manipulation functions
  • character manipulation functions
  • Date functions
  • Data type conversion
  • Number Functions
  • General Functions

Multiple row functions

Group Functions

Single row functions


Lower   – select lower(last_name) from employees;    O/P  –  king

Upper   – select upper(last_name) from employees;   O/P – KING

Inticap  –  select inticatp(last_name) from employees;   O/P – King


SUBSTR    – select substr(last_name,1,3) from employees;

INSTR        – select instr(last_name,’n’) from employees;

LENGTH    – select length(last_name) from employees;

CONCAT      –  select concat(first_name,last_name) from employees; (only 2 arguement)

REPLACE   –   select replace (last_name,’a’,123) from employees;   (a replace with 123)

REVERSE     –     select reverse(last_name) from employees;

LPAD / RPAD   –   select lpad(last_name,20,’#’) from employees;

LTRIM / RTRIM   –  select ltrim(last_name,’k’) from employees;


MONTHS_BETWEEN   –  select months_between(sysdate,’01-jan-10’) from dual;

(only months_between date functions returns a number)

ADD_MONTHS   –  select add_months(sysdate,3) from dual;

NEXT_DAY    –   select next_day(sysdate,’Friday’) from dual;

LAST_DAY   –  select last_day (sysdate) from dual;









General functions

NVL   – it accepts 2 arguments. If the 1st argument is null it return 2nd argument’s value. Else it returns the 1st argument’s value.

NVL2  – it accepts 3 arguments. 1st argument is null it return 3rd argument’s value. Else it returns the 2nd argument’s value.

NULLIF   –   it accepts 2 arguments. If the both arguments are equal it returns null. Else it returns the 1st argument’s value.

COALESCE    – it accepts N number of arguments. It returns the 1st not null value.

Multiple row functions

Group functions

  • MAX
  • MIN
  • SUM
  • AVG

Q5) What Operator Performs Pattern Matching?


We have to use the LIKE operators in the where clause to perform the pattern matching.


1.For finding first character pattern matching :-

Select *

From employees

Where last_name like ‘a%’;

2.For finding last character pattern matching :-

Select *

From employees

Where last_name like ‘%a’;

3.For finding the letter in any part of the last_name:-

Select *

From employees

Where last_name like ‘%a%’

Q6) What is database?


A database is the collection of information where we can  store the date, manipulate the date and retrieve the stored data.

Database is designed so that we can store and manage the enterprise information

Q7) How can I hide a particular table name of our schema?


Using SYNONYMNS we can hide a particular table name of our schema.



After creating the above synonymn we can access the data of EMPLOYEES table using EMP as table name as below

SELECT * from EMP;

We can also give alternate name to the objects as well.

Q8) How do I eliminate the duplicate rows ?

Using DISTINCT keyword we can eliminate duplicate records.




Q9) How do I display row number with records?


Using ROWNUM keyword we can display row number with records.

  • Rownum is a pseudocolumn.
  • After issuing a select statement, oracle does is to assign an increasing (starting with 1, increased by 1)
  • number to each row returned.




Q10) Find out nth highest salary from emp table?



select salary from

(select salary,rownum RK from

(select salary from employees

order by salary desc))

where RK=n;

Q11) Display Odd/ Even number of records?


We can use the MOD functions is number functions



select * from employees

where (rowid,1) in

(select  rowid, mod(rownum,2) from employees)

Alternative query

select * from employees

where mod(employee_id,2)=0;


select * from employees

where (rowid,0) in

(select  rowid, mod(rownum,2) from employees)

Alternative query

select * from employees

where mod(employee_id,2)=1;

Q12) What is a inline view?


  • An inline view is a SELECT statement in the FROM-clause of another SELECT statement.
  • In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query.






Q13) Which date function returns number value?


  • MONTHS_BETWEEN date function returns number value.
  • In that date function, we can able to find the months between the defined date by the user.


select months_between (sysdate,’01-jan-10′) from dual;

date1  and  date2

The date1 is later than date2 – Result will be in Positive.

The date1 is earlier than date2 – Result will be in Negative.

The defined date format should be in “DD-MM-YY”. If we insert the other date format, the oracle server will through the error.

Q14) What are the more common pseudo-columns?



Rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria  in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.


A rowid is a pseudo column (like versions_xid), that uniquely identifies a row within a table, but not within a  database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid.


Sysdate is a pseudo column, It’ll show the system date.


Systimestamp is a pseudo column, It’ll show the system date with time


USER is a pseudo column. It returns the name of the user currently connected


UID is a pseudo column,it’ll display the user ID. For every user the oracle server has produces the unique ID for every user.

Q15) What is a relational database management system?



  • A database management software system that organizes data into a series of records that are stored in linked tables. This provides the ability to relate different records, fields and tables, and aids data access and data transformation.
  • A relational Databse is collections of relation (or) two dimensional tables. The organization wants to store the information about all the employees in your company. In the relational databases the informational can store in the different tables. All the table have relations

Q16) State the difference between a primary key and foreign key?


Uniquely identifies each row of the tableIt is referencial identifier
It’ll not allow the null valuesIt’ll allows the null values
It’ll not allow the duplicate valuesIt’ll allows the duplicate values
Only one primary key allow in a tableEach table can have more than one foreign key
Primary key is unique


foreign key reference as Primary key in another table
Oracle server enforce the uniqueness by creating a unique index on the primary key columnOracle server doesn’t enforce such unique index for the foreign key column.

Q17) What is a synonym?


SYNONYM is an alternative name for an object. The advantages are

  1. creating easy reference to a table.
  2.  shorter length object name.


To create synonym


To drop synonym

Drop synonym SEMP;

To create the public synonym

Create public synonym dept for deparments;

The public synonym is created so that the all users can access the synonyms. The database administrator can create the public synonym.  And the removal of public synonym can be done only by the database administrator.

Drop public synonym dept;

To display all the synonyms created by the user

Select * from user_synonyms;

Q18) What is a view?


A VIEW is a virtual table.

  • View logicaly a subset of data from two or more table.
  • Views contains no data of its own
  • Its a window through which the data from table can view.

Types of Views        


ONENumber of tablesOne or more
NOContains FunctionsYES
NOContains groups of dataYES
YESDML operations through viewNot always


To create the view

Create or replace view [View_name]


Select statement

To drop the view

Drop view [view_name]


Create or replace view [View_name]


Select statement

With read only

By adding “with read only” keyword, the DML operators are restricted in the particular view.


  • It is used to restrict data access
  • It makes complex query easy
  • Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.

Q19) What is a schema?


  • A SCHEMA is a collection of database objects.
  • A schema is owned by a database user and has the same name as that user.
  • Schema objects are logical structures created by users to contain, or reference, their data.
  • Schema objects include structures like tables, views, and indexes.

Q20) What is a join, explain the types of joins?


  • Selecting data from 2 or more tables is called as joins.
  • To link the two table, first we have to find out the column which is exist in the both the table

Types of joins

  • Natural joins
  • Cross joins
  • Outer joins
  • Equi joins
  • Nonequijoins
  • Self joins


  • Natural joins clause is based on all columns in the two tables that have the same name.
  • The selected rows frim the two tables that have equal values in all matched column.
  • The natural joins automatically select the column which is common in the both tables. But the column name in the both table should be the same and data types as well


Select column_name1,column_name2

From table1



The cross join clause produces the cross product of two tables.


Select column_name1,column_name2

From table1



Selecting matched, Un-matched records is called as outer joins.

Types of outer joins

  • left outer joins
  • Right outer joins
  • Full outer joins

Left Outer Joins

A joins between two tables that returns the matched rows as well as the unmatched rows from the left table is called as left outer join


select e.department_id,


from departments d left outer join employees e

on (e.department_id = d.department_id);

Right Outer Joins

A joins between two tables that returns the matched rows as well as the unmatched rows from the Right table is called as Right outer join


select e.department_id,


from departments d Right outer join employees e

on (e.department_id = d.department_id);

Full Outer joins

A join between that returns the matched rows and the unmatched rows from the both left and right tables is called as full outer joins.


select e.department_id,


from departments d full outer join employees e

on (e.department_id = d.department_id);

Q21) What command is used to get back the privileges offered by the GRANT command?


  • REVOKE command is used to get back the privileges offered by the GRANT command.
  • The SQL command revoke allows to take away System privileges and object privileges from users and roles

Q22) What command is used to create a table by copying the structure of another table?





Invalid Condition

If we doesn’t give the invalid condition in the where clause the whole data will copy to the new table (EMP table). For copying the structure alone we have to give one invalid condtion in the where clause.

Q23) How can variables be passed to a SQL routine?


  • using the & symbol a variables can be passed to a SQL routine.
  • It is a SQLPLUS command.

Entering the data in a row


Insert into t1 values(&a,&b);

We can assign the both the values every time.

Insert into t1 values (&&a,&b);

We can fix the a value as constant and b value will change

Q24) What is the use of the DROP option in the ALTER TABLE command?


The use of the DROP option in the ALTER TABLE command is to drop a specific COLUMN.




Q25) What is a Cartesian product?


  • A Cartesian product is a result set which contains all the possible combinations of each row in each table included in a query.
  • A Cartesian product is almost always an incorrect result.
  • When a join condition is invalid or omitted completely the result is known as Cartesian product.
  • By using the cross join , the Cartesian product will produce.
  • To avoid the Cartesian product, insert the valid join condition.

Q26) What is the usage of SAVEPOINTS, COMMIT, ROLLBACK?


COMMIT is used to confirm the changes done whenever a DML(INSERT/UPDATE/DELETE) statement is issued on a table.



ROLLBACK is used to undo the changes done issued by a DML(INSERT/UPDATE/DELETE) statement command.



Note:- we can undo the changes unless a COMMIT is not issued.


  • We specify the savepoint after the current transaction with specified name.
  • We can rollback to that particular point by “rollback to savepoint_name”




Savepoint A      —-First savepoint

Insert 2


Savepoint B     —–Second savepoint

If we gave the rollback command as


The rollback operators execute upto the savepoint A. The update2 and insert2 will not be committed until we gave “commit” command.

Q27) What is difference between CHAR and VARCHAR2?  What is the maximum SIZE allowed for each type?


The char is a fixed-length character data type.The varchar is a variable-length character data type.
The storage size of the char value is equal to the maximum size for this column.The storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.
A char can be used when the data entries in a column are expected to be the same size.A varchar can be used when the data entries in a column are expected to vary considerably in size.
Maximum size is 2000 and (minimum/default) is 1Maximum size is 4000 and (minimum/default) is 1

Q28). How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?


  • Only One LONG column is allowed in each table.
  • Long column cannot be included in group by clause (or) order by clause.
  • Long column is not copied when a table is created using sub-query.
  • No constraints can be defined on a long column.

Q29) Can a view be updated/inserted/deleted? If Yes – under what conditions?


  • You cannot add the data through a view, if the view contains the following.
  • Group Functions
  • Group by Clause
  • DISTINCT Keyword
  • Pseudo column ROWNUM keyword
  • Columns defined by expressions
  • NOT NULL column in the base table that are not selected by view.

 Q30) What are the advantages of VIEW?


  • To restrict data access
  • To make complex queries easy
  • To provide data independence
  • To present different views of same data

Q31) How can I find the total number of records in a table?


To find the number of records in a table we can use the COUNT(*) function.



Q32) How can you compare a part of the name rather than the entire name?


By using LIKE operator in the where clause we can compare the part of the name.


Select employee_id,last_name from employees

Where last_name like ‘%A’;

Q33) What is GROUP BY?


  • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
  • The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
  • If the arguement / Expression is added in the select statement with the group function. That expression will include with the group by clause in the statement.
  • If you fail to include the expression in the GROUP BY clause, the ORACLE server through the error.
  • The GROUP BY column doesn’t have to be in the select list.





Q34) How can I change my Oracle password?


ALTER user hr identified by admin;

PL/SQL Interview Questions and Answers

Q1) What is the starting oracle error number ?

Answer: ORA-00000 is the staring oracle error number.

Q2) What is meant by forward declaration in functions?


  1. If you are defining a package body having two procedures, if you want to use second procedure in the definitions of first procedure.
  2. You have to declare the second package with its arguments (if have) before using in the definition of first procedure it is labeled as forward declaration.

Q3) Can commit, rollback, savepoint be used in database triggers? If yes then how? If no why? With reasons


  1. Triggers should not contain transaction control statements like commit, rollback, savepoint or set transaction.
  2. Because it is fired as part of the execution of the triggering statement.
  3. When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

Q4) Can we declare a column having number data type and its scale is larger than precision ex: column name NUMBER (10,100), column name NUMBER (10,-84)

Answer: Yes such declaration is possible .Explanation with example.

  1. Number (9, 11) means there are 11 digits after decimal .However as the max precision is 9 so the rest are zero padded .Like 0.00999999999
  2. Number (9, -11) means that there are 9 digits whereas the rest is zero padded towards left of the decimal point Like 99999999900000000000.0

Q5) Explain how procedure and functions are called in a pl/sql block ?

Answer: Procedure can be called in the following ways:

  1. CALL <procedure name> direct
  2. EXCECUTE <procedure name> from calling environment
  3. <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways:

  1. EXCECUTE <Function name> from calling environment. Always use a variable to get the return value.
  2. As part of an SQL/PL SQL Expression.

Q6) Explain the types of cursor?

Answer: Cursor is a sql private work area. It opens an area of memory where the query is parsed and executed.


  1. Implicit: Implicit cursor are declared by pl/sql implicitly for all dml and pl/sql statements, including queries that return only one row.
  2. Explicit: For queries that return more than one row, explicit cursors are declared and named by the programmer and manipulated through specific statements in the block’s executable actions.

Q7) Explain the usage of WHERE CURRENT OF clause in cursors?

Answer: When referencing the current row from an explicit cursor,use the where current of clause.This allows you to apply updates and delete to the row currently being addressed,without the need to explicitly reference the rowid.You must include the for update clause in the cursor query on open.

Syntax: Where current of cursor;

Q8) Give the structure of the function?

Answer: CREATE OR REPLACE FUNCTION function_name(formal parameters list with only IN mode)

RETURN datatype


local variable declarations


executable statments;

RETURN value;

END function name;

Q9) Give the Structure of the procedure?

Answer: CREATE OR REPLACE PROCEDURE procedure_name(Optional Parameters)




Executable statements [EXCEPTION exception handlers]


Q10) Explain rowid,rownum ?What are the pseduocolumns we have?


  1. Row id: Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
  2. Rownum: For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

The lists of pseudocolumns are:

  2. LEVEL
  3. ROWID
  7. USER
  8. UID

Q11) How packaged procedures are called from the stored procedure or anonymous block?


Variable: = PACKAGE NAME.FUNCTION NAME (arguments);


Q12) How to disable multiple triggers if a table at a time?

Answer: ALTER table table_name DISABLE ALL TRIGGERS

Q13) How we can create a table through procedure?

Answer: You can create table from procedure using Execute immediate command.

CREATE procedure p1 is




Q14) In pl/sql what is the use of out parameter even though we have return statement.

Answer: Without parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions.

Q15) What are integrity constraints?

Answer: Data integrity allows defining certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, Oracle will not allow so.

Constraint types:

There are five integrity constraints in Oracle.

Not Null:

A column in a table can be specified not null. It’s impossible to insert a null in such a column. The default is null.

Unique Key:

The unique constraint doesn’t allow duplicate values in a column. If the unique constraint encompasses two or more columns, no two equal combinations are allowed.

Primary Key:

On a technical level, a primary key combines a unique and a not null constraint. Additionally, a table can have at most one primary key. After creating a primary key, it can be referenced by a foreign key.

Foreign key:

A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table. If a table has a foreign key that references a table, that referenced table can be dropped with a drop table. Cascade constraints. It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Check constraints:

A check constraint allows stating a minimum requirement for the value in a column. If more complicated requirements are desired, an insert trigger must be used.

Q16) Name the table where characteristic of package,procedure and functions are stored?

Answer: user_objects



Q17) State the advantage and disadvantage of cursor?

Answer: Cursor is nothing but it’s a memory area of executing sql or oracle statements.


  1. We can retrieve more than one record by one query.
  2. Its use our RAM so retrieval is fast.
  3. By using Collection we can retrieve the bundle of data in cursor.
  4. A cursor is an oracle variable.
  5. It is similar to a 2D Array.
  6. It is a forward only and read only variable type.


No such of disadvantage of cursor. When we use cursor one thing is necessary. When I have select only one record then we should use implicit cursor and when more record then we should use explicit cursor.

Q18) State the difference between implicit and explicit cursor?


Implicit Cursor:

  1. When a query return s Single Row Value then Implicit Cursor is used. It’s return Only One Row. Cursor.
  2. Name is assigned implicitly.
  3. Implicit Cursor used for all SQL Statements, that, DECLARE, OPEN, FETCH, CLOSE.
  4. It’s defined by the Oracle Server where never the Operations Single Row.
  5. Implicit Cursors Automatically provides by Oracle which performs DML Statements. Qureies return only one row.
  6. We are able to handle NO_DATA_FOUND Exception in implicit Cursor.

 Explicit Cursor:

  1. A subquery returns more than one row Explicit Cursor is Created.
  2. The rows returned by the query are called Active Set. Its return multiple rows.
  3. Curosr is assigned explicitly. It’s used to process Multirow SELECT Statements.
  4. Retrieving multiple rows the Programmer declares cursors explicitly.
  5. Explicit Cursors defined by the User. Queries return more than rows.
  6. We are not able to handle NO_DATA_FOUND Exception.

Q19) What are %type and %row type? What are the advantages of using these over data types.

Answer: %TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.

ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table.

In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then individually refer these variables inside the ROWTYPE variables.

These two provides data independence and allows you to adopt database changes due to new business requirements. You need not know datatype and size in advance.

Q20) What are the advantages of stored procedure?

Answer: The procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;

  1. The code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
  2. Each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
  3. As the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients.

This results in much less network traffic and again improves scalability;

  1. When using PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster stored procedures/functions can be compiled into “native” machine code making them even faster.

Q21) Difference between %type and %row type.

Answer: %TYPE is used to declare a variable with the same type as that of a database table column. TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime.

ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table. In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then individually refer these variables inside the ROWTYPE variables.

Q22) What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?


  1. A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
  2. A cursor declared in a procedure is local to the procedure that cannot be accessed by other procedures.

Q23) What is the difference between procedure and functions?



  1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
  2. Function can be called from SQL statements where as procedure can not be called from the sql statements
  3. Functions are normally used for computations where as procedures are normally used for executing business logic.
  4. You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
  5. Function returns 1 value only. Procedure can return multiple values (max 1024).


  1. Stored Procedure supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..But actually not exists in database is allowed only in during creation but runtime throws error Function won’t support deferred name resolution.
  2. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
  3. Stored procedure is pre-compiled execution plan where as functions are not.
  4. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

Q24) What is pl/sql. Advantages of pl/sql.


  1. Pl/sql is procedural language. It is an extension of sql with design feature of programming languages.
  2. Pl/sql offers modern software engineering features such as data encapsulation, exception handling information hiding, and object orientation.
  3. pl/sql reduces the network traffic, it provides you with ability to control the flow of constructs.
  4. Pl/sql application can run on any platform on which oracle runs.
  5. Pl/sql is not an oracle product in its own write.


  1. You can program with procedural language with control structures.
  2. Pl/sql can handle errors.
  3. Easy maintenance.
  4. Improved data security and integrity.
  5. Improved performance.
  6. Improved code clarity.
  7. Pl/sql is portable.
  8. You can declare variables.
  9. It reduces network traffic.

Q25) What is ref cursor?

Answer: Ref Cursors also known as Dynamic cursors can be associated with as many SELECT statements you want at different times.

You can associate with different SELECT statements at dynamic time.

Q26) What is the basic structure of pl/sql.

Answer: Declare——Optional

Variable declaration

Cursor declaration

User defined exceptions


Sql statements

Application or business logic

Exception ——-Optional

Handling Errors

End; ———-Mandatory

Q27) What will happen after commit statement?

Answer: Commit in this context will not do anything except the committing the changes into database, done using DML statements.

However, if the cursor is created with FOR UPDATE clause, it will raise runtime exception as commit, in that case, would also release all locks and thus close the cursor implicitly and user will get ORA-1002 “fetch out of sequence ” error as after the loop is executed one time, with the commit statement, the cursor will be closed and fetch into statement will fail.

Q28) What is trigger,cursor,functions with example.

Answer: Trigger: Trigger is a pl/sql block structure which is fired when DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

Syntax of Triggers

The Example for creating a trigger is:





IF :NEW.deptno = 30 THEN

:NEW.comm := :NEW.sal * .4;



Functions:  Function can accept a parameter and must return a value.Function can be called as an part of an expression. Function is a named pl/sql block that is stored in the database for repeated execution. Function must have at least one return statement.


Create or replace function nthsal(i  number)

Return number

c number;


select salary into c from (select salary,rownum r from (select salary from employees group by salary))where r=i;

return c;



Cursor: Cursor is a sql private work area.it opens an area of memory where the query is parsed

is parsed and executed.


v_employeeID    employee.id%TYPE;

v_FirstName     employee.first_name%TYPE;

v_LastName      employee.last_name%TYPE;

CURSOR  c_employee  IS

SELECT id, first_name, last_name FROM employee WHERE department_id=80;


OPEN c_employee;


FETCH c_employee INTO v_employeeID, v_FirstName, v_LastName;






CLOSE c_employee;



Q29) What is the cursor attributes used in pl/sql.

Answer: As with explicit cursor there are four attributes for obtaining status information about a cursor. When appended to the cursor variable name these attribute return useful information about the execution of a data manipulation statement.

Attribute Type Description
%ISOPENBooleanEvaluates to true if the cursor is open
%NOTFOUNDBooleanEvaluates to true if the most recent fetch does not return a row
%FOUNDBooleanEvaluates to true if the most recent fetch returns a row; complement of % not found
%ROWCOUNTBooleanEvaluates to the total number of rows returned so far

Q30) What are the modes of parameters that can be passed to the procedure.

Answer: There are three modes of parameters:

In (default): Passes a constant value from the calling environment to the procedure. Formal parameter acts as a constant. Actual parameter can be literal, expression, constant or initialized variable. Can be assigned a default value.

Out: Passes a value from the procedure to the calling environment. Initialized variable, must be a variable, cannot be assigned a default value.

In Out: Passes a value from the calling environment in to the procedure and a possibly different value from the procedure back to the calling environment using the parameter. Initialized variable must be a variable cannot be assigned a default value.

Q31) What are two parts of package?

Answer: Package body:

A package body usually has a specification and a body stored separately in the data base.

The specification is the interface to your applications. It declares the types, variables, constants and subprograms available for use.

Package specification:

The package specification may also include PRAGMAs, which are directives to the compiler.

The body fully defines cursors and subprograms and to implement the specification.

Q32) What is raise_application_error?

Answer: You can use this procedure to issue user_defined error messages from stored subprograms. You can report errors to your application and avoid returning unhandled exceptions. It is used in two different places executable section and exceptional section.

Syntax:  raise_application_error(error_number,message[,{true|false}];

In th syntax:

Error_number :  is a user specified number for the exception between -20000 and -20999.

Message:  is the user specified message for the exception.It is a character string up to 2048 bytes.

True|False :  is an optional Boolean parameter(if true the error is placed on the stack of previous error .if false ,the default ,the error replaces all previous errors.

Q33) What is cursor .why it is recquired ?

Answer: A cursor is a private sql work area where the statements are executed and the processing information is stored.

Q34) What is cursor for loop?

Answer: A cursor for loops processes rows in an explicit cursor. It is a shortcut because the cursor is opened, rows are fetched once for each iteration in the loop, the loop exists when the last row is processed, and the cursor is closed automatically. The loop itself is terminated at the end of the iteration where the last row is fetched.

Syntax: for record_name in cursor_name  loop

Statement 1;

Statement 2;


End loop;

Q35) What is a stored procedure?


  1. A procedure is a named pl/sql block that can accept parameters and be invoked. You can use it to perform an action.
  2. A procedure has a header, a declaration section, an executable section, and an optional exception handling section.
  3. A procedure can be compiled and stored in the data base as a schema object.
  4. Procedures promote reusability and maintainability. When validated they can be used an any number of applications.
  5. If the requirements change, only the procedure needs to update.

Q36) What is an exception? What are types of exception?

Answer: An exception is an identifier in pl/sql block that is raised during the execution of a block that terminates its main body of actions. A block always terminates when pl/sql raises an exception,but can you perform an exception handler to perform final actions.

There are two types of exceptions:

Implicitly raised

  1. Predefined oracle server: One of approximately 20 errors that occur most often in pl/sql code.
  2. Non-predefined oracle server: Any other standard orale server error.

Explicity raised

  1. User defined: A condition that the developer determines is abnormal.

Q37) Where are predefined exception are stored?

Answer: Predefined exceptions were stored in the oracle server.

Q38) What is pragma_exception_init explain the usage?

Answer: In pl/sql,the pragma_exception_init tells the compiler to assiociate an exception name with an oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

Q39) What are the return values of function sqlcode and sqlerm ?

Answer: Sqlcode returns the numeric value for the error code or error message by using two functions.Based on the value of the code or message,you can decide which subsequent action to take based on the error.

Sqlerrm returns character data containing the message associated with the error number.

Q40) Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why?

Answer: It is not possible.  As triggers are defined for each table, if you use COMMIT   of    ROLLBACK in a trigger, it affects logical transaction processing.

Q41) What are the pl/sql statements used in cursor?

Answer: Declaring a CURSOR

Open the CURSOR

FETCH the rows from the CURSOR


Q42) What are the datatype used in pl/sql?

Answer: Scalar:

Single values with no internal components.


Data items that have internal components that can be accessed individually.


Pointers to other data items.

Large Object (LOB):

Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

Q43) What is database triggers and name some usage?

Answer: A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens.

Data auditing, implementing complex business rules, security are main uses of database triggers.

Q44) What happens if a procedure that updates a column of table X is called in a database trigger of the same table?

Answer: Mutation of table occurs.

To avoid the mutation table error, the procedure should be declared as an AUTONOMOUS TRANSACTION. By this the procedure will be treated as a separate identity.

Q45) What is overloading of procedure?

Answer: The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.


No Comments

    Leave a Reply

    Your email address will not be published Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


    Online Training Quick Enquiry

    Get Free Online training

    Looking for Online Training