Oracle PL SQL Interview Questions

Get Technical Answers for Oracle PL SQL Interview Questions by Oracle PL SQL Developers, Advanced Technical Questions for Freshers and Experience Persons. Get Free Practical Tests.

1. What are the differences between PL/SQL and SQL?

Ans:  

SQL:

  • It is a natural language useful for interactive processing.
  • SQL doesn’t offer any procedural capabilities like condition testing, looping etc.
  • It is a time-consuming process where all SQL queries are executed by the database one at a time.
  • There are no error handling procedures in SQL.

PL/SQL:

  • It is a procedural language which is an extension for SQL.
  • PL/SQL offers procedural capabilities as well as high language features such as condition, looping statements etc.
  • It saves time and increases efficiency where the entire block of statements is sent to the database server at once to be executed.
  • The customized error handling procedures are implemented in PL/SQL.

2. What are the benefits of PL/SQL Packages?

Ans:  The benefits of PL/SQL Packages are.

  • A package is used for storing the functions and procedures in a single unit.
  • Packages provide security to grant privileges.
  • A package consisting of the functions and procedures shares a common variable among them.  
  • Packages support even if the functions are overloaded.
  • Packages enhance the performance even when the multiple objects are loaded into memory.

3. What are the characteristics of PL/SQL?

Ans:  

  • Multiple applications in PL/SQL allow accessibility and sharing the same subprograms.
  • It is portable as the code can be executed on any operating system provided that Oracle is loaded on it.
  • PL/SQL allows the users to write their own customized error handling routines.
  • Improved transaction performance with integration to Oracle data dictionary.

4. What are different methods to trace the PL/SQL code?

Ans: Tracing the code is a technique that measures performance during the runtime. The different methods for tracing the code includes the following:

  • DBMS_APPLICATION_INFO
  • DBMS_TRACE
  • DBMS_SESSION and DBMS_MONITOR
  • trcsess and tkprof utilities

5. Can you use IF statement inside a SELECT statement? How?

Ans: Yes. The IF statement is used as a DECODE in versions 9 and above.

Example:

SELECT day_of_week,

DECODE (number, 0, 'Sunday',

1, 'Monday',

2, 'Tuesday',

3, 'Wednesday',

4, 'Thursday',

5, 'Friday',

6, 'Saturday',

'No match') result FROM weekdays;

6. What is the difference between %TYPE and %ROWTYPE? Give an example

Ans:  

%TYPE:

It is the attribute that declares a variable of the same data type as of a table column.

Example:

DECLARE

studentId

students.student_id%TYPE;

%ROWTYPE:

It is the attribute that declares a variable of type RECORD which has the same structure as that of a table row. The row is a RECORD that contains fields having the same data types and names as that of columns of a table or view.

Example:

DECLARE

Stud_rec

students.%ROWTYPE;

7. Why do we use database triggers? Give the syntax of a trigger.

Ans: The trigger is a stored procedure which invokes automatically when an event occurs. The event could be any of these DML commands like insert, update, delete, etc.

Syntax:

create trigger [trigger_name]

[before | after]

on [table_name]

[for each row]

[trigger_body]

8. What are the different types of cursors in PL/SQL?

Ans: There are two different types of cursors available in PL/SQL.

Implicit cursor: PL/SQL applies these implicit cursors for the DML commands such as INSERT, UPDATE, DELETE and SELECT statements which return a single row.
Explicit cursor: It is created by the programmer for queries that return more than a single row. 
Syntax:

CURSOR is

SELECT statement;

OPEN ;

FETCH INTO ;

CLOSE ;

9. List some cursor attributes in PL/SQL.

Ans: 

  • %ISOPEN: This attribute checks if the cursor is open.
  • %ROWCOUNT: This attribute returns the number of rows that are updated, deleted or fetched.
  • %FOUND: This attribute checks if the cursor has fetched any row and returns boolean if it finds the record.
  • %NOT FOUND: This attribute checks if the cursor has fetched any row and returns boolean if it doesn’t find any record.

Oracle PL SQL Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

 

10. When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?

Ans:  The outcome of the statement is saved in 4 cursor attributes. They are.

  1. SQL%FOUND
  2. SQL%NOTFOUND
  3. SQL%ROWCOUNT
  4. SQL%ISOPEN

11. Explain the difference between commit and savepoint.

Ans: 

  • COMMIT: It is applied for making the database changes permanent. All the save points are erased and the transaction ends. Once it is committed, a transaction cannot be rolled back.
  • SAVEPOINT: It is applied to set points during a transaction such that a programmer can roll-back it later. It is helpful during a series of transactions that can be divided into groups having a savepoint.

12. What are the ways of commenting in a PL/SQL code?

Ans: Comments are the text which is used while implementing the code to enhance the readability such that a reader is able to understand the code. These comment codes are not executed. There are two types of comments in PL/SQL.

Single line comment: This comment starts with double “–”.
Example:

DECLARE

num NUMBER(2);    — it is a local variable.

BEGIN

Multi-line comment: This comment starts with “/*” and ends with “*/”.

Example:

BEGIN

num := &p_num;     /* This is a host variable used in program body */

……….

END

13. What are the three modes of parameter?

Ans: The three modes of parameters are.

  1. IN parameters: These parameters allow you to pass values to the procedure being called and can be initialized to default values. It acts like a constant and cannot be assigned any value.
  2. OUT parameters: These parameters return value to the caller and they must be specified. It acts like an uninitialized variable and cannot be used in an expression.
  3. IN OUT parameters: These parameters pass initial values to a procedure and return updated values to the caller. It acts like an initialized variable and should be assigned a value.

14. What are the actual parameters and formal parameters?

Ans:

Actual parameters: 

The actual parameters are the variables or an expression which is referred to as parameters that appear in the procedure call statement.

Example:

raise_sal(emp_num, merit+amount);


In this above example, “emp_num” and “amount” are the two actual parameters.

Formal parameters: 

The formal parameters are the variables which are declared in the procedure header and are referenced in the procedure body.

Example:

PROCEDURE raise_sal( emp_id INTEGER) IS

curr_sal REAL:

………..

BEGIN

SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;

…….

END raise_sal;

In the above example, “emp_id” acts as a formal parameter.

15. What do you understand by Exception handling in PL/SQL?

Ans: In PL/SQL, the exception is raised when an error occurs. In other terms, to handle unpredicted events such as when the PL/SQL scripts are terminated unexpectedly than an error-handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section. There are three types of Exception Handling in PL/SQL.

  1. Predefined Exceptions: These are the exceptions which have common errors with predefined names.
  2. Undefined Exceptions: These are the exceptions which have less common errors with no predefined names.
  3. User-defined Exceptions: These exceptions don’t cause runtime error but violate the business rules.

16. What are Views and why are they used?

Ans: A View is a logical representation of data subsets from one or more tables. It is a logical table that is based on a SQL table or another view. A view doesn’t contain the data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.

The views are used for the following purposes.

  • For restricting the data access.
  • For making the complex queries easier.
  • For providing the data Independence.
  • Views provide groups of the user for accessing the data according to their requirement.

17. Write a simple procedure to select some records from the database using some parameters.

Ans: 

Example:

CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)


AS

BEGIN

SELECT * FROM customers WHERE age = @age AND city = @city;

END;

18. Write a program that shows the usage of the WHILE loop to calculate the average of user entered numbers and entry of more numbers are stopped by entering number 0?

Ans: 

DECLARE

n NUMBER;

avg NUMBER :=0 ;

sum NUMBER :=0 ;

count NUMBER :=0 ;

BEGIN

n := &enter_a_number;

WHILE(n<>0)

LOOP

count := count+1;

sum := sum+n;

n := &enter_a_number;

END LOOP;

avg := sum/count;

DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);

END;

Subscribe to our youtube channel to get new updates..!

 

19. How to display records having the maximum salary from an employee table?

Ans: 

Select * from emp where sal= (select max(sal) from emp)

20. How to display the second highest salary from an employee table?

Ans:

Select max(sal) from emp where sal not in (select max(sal) from emp)

21. What is the difference between functions, procedures, and packages in PL/SQL?

Ans:

Function: A function is specified with a return type and must return a value specified in that type. The main purpose of a PL/SQL function is to compute and return a single value. 

Procedure: A procedure doesn’t have a return type and it doesn’t return any value, but it can have a return statement which stops its execution and returns to the caller. A procedure returns multiple values, unlike a general function.

Package: A package is a group of functions, procedures, variables, and record TYPE statements. It is a schema object which groups logically related PL/SQL types, items, and subprograms. It provides modularity for application development. It is used for hiding the information from unauthorized users.

22. Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

Ans:

SET SERVER OUTPUT ON

DECLARE

BEGIN

FOR i IN REVERSE 1..99

LOOP

IF Mod(i,3) = 0 THEN

DBMS_OUTPUT.PUT_LINE(i);

END IF;

END LOOP;

END;

/

23. Explain the difference between Triggers and Constraints?

Ans: 

Triggers:

  • Triggers are stored as separate objects.
  • It is fired when an event occurs and so the triggers are fired after constraints.
  • Triggers perform faster in comparing table to table.
  • It is for the entire table.
  • Triggers are the stored procedures that are automatically executed and so it won’t check for data integrity

Constraints:

  • A constraint on a table is stored along with the table definition.
  • Constraints are fired soon when the tables are used.
  • Constraints performance is slow while comparing the memory location to the table.
  • The constraint is applied only for the column of a table.
  • Constraints prevent duplicate and invalid data entries.

24. Write a PL/SQL cursor program to display all employee names and their salary from the emp table by using % not found attributes?

Ans: 

Declare

Cursor c1 is select ename, sal from emp;

v_ename varchar2(10);

v_sal number(10);

begin

open c1;

loop

fetch c1 into v_ename, v_sal;

exist when c1 % notfound;

dbms_output.put_line(v_name ||’ ‘||v_sal);

end loop;

close c1;

end;

/

25. What does PLV msg allow you to do?

Ans: The PLV msg allows for.

  • Assigning the individual text message to a specific row in the PL/SQL table.
  • PLV msg retrieves the message text by number.
  • It substitutes your own messages automatically for standard Oracle error messages with the restrict toggle.
  • Batch load message numbers and text from a database table are directly into the PLV msg PL/SQL table.

26. What PLVcmt and PLVrb do in PL/SQL?

Ans: PL/Vision offers two packages for managing the transaction processing in PL/SQL. It is PLVcmt and PLVrb.

PLVcmt: This package wraps the logic and complexity to deal with commit processing.

PLVrb: This package provides a programmatic interface to roll-back activity in PL/SQL.

27. How would you convert the date into Julian date format?

Ans: Use the J format string.

SQL > select to_char(to_date('29-Mar-2013','dd-mon-yyyy'),'J') as julian from dual;

JULIAN

Oracle PL SQL Training

Weekday / Weekend Batches

 

28. What is the use of MERGE?

Ans: MERGE is used to combine multiple DML statements into one.

Syntax : 

merge into tablename

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

29. Explain SPOOL.

Ans: The spool command is used for printing the output of SQL statements in a file.

spool/tmp/sql_outtxt

select emp_name, emp_id from emp where dept='accounts';

spool off;

30. What is the use of PLVprs and PLVprsps?

Ans: 

PLVprs: It is an extension for string parsing for PL/SQL, and the functionality of string parsing works at the lowest level.

PLVprsps: It is the highest level package used in parsing the PL/SQL source code into separate atomics. It relies on other parsing packages to get the work done.

Submit an interview question

Categories

Request for more information

Sachin
Sachin
Networking, OS, Salesforce & Sap
Am sachin I have worked with hkr trainings for more than 4 years .and our methodologies had been a practical suit for student culture and professionals. And I have trained more than 30 batches over the last 8 months. And I have great experience working with hkr trainings. Contact me with my LinkedIn and Twitter.

WhatsApp
To Top