Oracle PL SQL Interview Questions

PL stands for Procedural Language which is an extension to SQL where the developer writes the complex database interaction by using the procedures, control structures like branching and iteration, modules and functions. it supports both dynamic and static SQL.

In this article, you can go through the set of Oracle PL/SQL interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR training.

Let us have a quick review of the Oracle PL/SQL interview questions.

Most Frequently Asked Oracle PL/SQL Interview Questions

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.

  Become an Oracle PL SQL Certified professional by learning this HKR Oracle PL SQL Training!

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;

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)

Want to know more about Oracle PL/SQL, visit here Oracle PL/SQL Tutorial!

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

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.

31. What is meant by Literal in PL/SQL?

Ans: Literals in PL/SQL are the specific string, character, numerical, or Boolean values not constituted by an identifier. Below are the different types of literals that PL/SQL supports:-

  • Numeric Literals
  • String Literals
  • Date and Time Literals
  • Boolean Literals
  • Character Literals

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

32. Define the PL/SQL functions- Date, Character, and Numbers.

AnsThe date function runs on the values of the date data types, and they return the value of the data type "DATE". A character function accepts one or more character values as input and returns both characters and numbers as values. The character function has different types, such as UPPER, LOWER, CONCAT, INSTR, LENGTH, TRIM, INTICAP, etc. Number functions in PL/SQL take the Numeric as input, and further, it gives back the numeric values. These functions include ROUND, MOD, POWER, CEIL, TRUNC, etc.

33. What is meant by JOIN?

Ans: In PL/SQL, JOIN is a query that combines rows from two or more tables, views, or materialized views. Oracle Database executes a Join query when different types of tables occur within the FROM clause of the query. 

34. Define VERIFY command in PL/SQL.

Ans: A VERIFY command is helpful to verify or validate the changes in the SQL statement, and we can define it by SET VERIFY On or OFF.

35. What is the FEEDBACK command in PL/SQL?

Ans: The FEEDBACK command in PL/SQL shows the total number of records returned by a query.

36. Define Mutating Table and Constraining Table in PL/SQL.

Ans: A mutating table in PL/SQL is a table which can be modified or it is changing. We can modify it using the DML, update it, or insert a statement. A constraining table is a type of table helpful to read for referential integrity constraints.

37. What is the difference between an Error and an Exception?

Ans: An error is a kind of bug where an exception is an error condition which is user-defined or internally defined. Exception raises where an error occurs.

38. What is meant by Mutating Table Error in PL/SQL?

Ans: A mutating table error takes place when a trigger tries to modify or update a row which is currently in use. Also, we can fix this error using temporary tables or views.

39. Define Rollback Statement.

Ans: A rollback statement is just the opposite of a commit supplied when the transaction comes to an end. It undoes all the database changes made during the existing transaction process.

40. List out the different Data Types available in PL/SQL.

Ans: The following are the various PL/SQL Data Types:-

  • Scalar
  • Reference
  • Large Object
  • Composite

41. Define a Dual Table.

AnsA Dual Table in PL/SQL is automatically built by the Oracle DB while creating a data dictionary. The user SYS owns this table, and all the users can access this table. Moreover, we can use this table when we wish to return a value only once.

42. What is meant by Sub Query?

Ans: A Subquery is a query inside a query or a SELECT statement nested within another statement. We can place a subquery within a WHERE HAVING and the FROM clause. There are two types of Subqueries: Single row subquery and Multiple row subquery. Also, there is another type of subquery, such as correlated subquery.

43. What are the commands TITLE and BTITLE denote?

AnsThe command TITLE denotes the top title, and the BTITLE represents the bottom title in PL/SQL. Using these commands, we can set the title for the top of the page and the bottom of the page.

44. Explain the basic PL/SQL structure.

AnsIn general, PL/SQL uses the block structure where a PL/SQL program includes SQL and PL/SQL statements. The statements develop a PL/SQL block. Further, the PL/SQL block comprises three sections - declaration, execution, and exception handling. 

45. What is the command used to remove a Trigger?

Ans: To delete or remove a trigger, we use the command - DROP TRIGGER.

46. Why is the

Ans: The "WHEN" clause in trigger states a condition on which a trigger has to be triggered.

47. Define the various components of a Trigger.

AnsA trigger in PL/SQL is similar to the stored procedure that automatically invokes when we perform DML operations against a view or table. There are two different triggers that PL/SQL supports- Row Level Trigger and Statement Level Trigger.

The row level trigger performs once for each row within the transaction. A Statement level trigger performs only once for a single transaction. 

48. Explain what the Stored Procedure is.

AnsA Stored Procedure is a series of SQL statements executing specific functions. It is similar to other programming languages' procedures which are saved within a database and can perform frequently. Further, we can invoke them using triggers or other methods as well.

49. Define SYSDATE and USER keywords in PL/SQL.

Ans: In PL/SQL, SYSDATE is a keyword that means the current server system date. It returns the current date and time set for the OS where the database server exists. The USER keyword is a pseudo column that returns an existing user's login ID for the current session.

50. Name the basic components of a Trigger.

Ans: The following are the basic components of a Trigger:-

  • A Triggering Statement
  • Trigger Action
  • Trigger Restriction

51. Define Syntax error and Runtime error in PL/SQL.

Ans: A Syntax error is an error that a compiler or an interpreter quickly detects.

52. How can you delete a Package in PL/SQL?

Ans: We can use the command DROP PACKAGE to remove the package in PL/SQL.

53. Define the compilation process for PL/SQL.

Ans: The compilation process in PL/SQL includes syntax checking, binding, and P-code generation. While compiling, the syntax checking verifies the errors within PL/SQL code. After fixing all the errors/mistakes, we allot a storage address to the variables holding data which is a Binding process. P-code consists of a set of instructions from the PL/SQL engine. This code is stored within the database and executed the next time it comes to use.

Oracle PL SQL Training

Weekday / Weekend Batches

54. What is meant by Global Variables?

Ans. In PL/SQL, global variables are stated within the farthest block or a package. These variables can store a maximum length of 255 characters of the character string. Moreover, we cannot formally declare global variables similar to the local ones.

55. What is a Ref Cursor and its different types in PL/SQL?

Ans: In PL/SQL, Ref Cursor is a data type whose value is the memory location of a database query workspace. Two types of Ref Cursors are available- Strong and Weak Ref Cursors.

56. Mention the different types of PL/SQL packages.

Ans: The PL/SQL package includes the following:-

  • Functions
  • Procedures
  • Cursors
  • Variables
  • Table and Statements of Record type 
  • Names of Exceptions
  • Pragmas for connecting an error number with an anomaly or exception

57. What is meant by Raise_application_error?

Ans: This package procedure enables us to send a user-defined error message from a stored program/block. It raises an exception based on the error provided by the user. We can report an error to the caller using this process rather than returning unhandled exceptions.

58. Define an Overloading Procedure.

Ans: An overloading procedure repeats the same name but with different parameter types. In Oracle, we can also apply an overloading procedure using a package.

59. Why do we use Database links?

Ans: Database links are helpful to build communication across different databases or environments like test, production, development, etc. We can also access any other information through database links as read-only. In other words, it is like a schema object within a database that allows users to access another database's objects.

60. When can we use the DECLARE block in PL/SQL?

Ans: It is a statement that unknown blocks use under PL/SQL as a stand-alone procedure. While using them, the statement within the stand-alone file will come first. Moreover, there is a need for a declaration section in case any variables are used within the PL/SQL block. 

61. What is a Discard File in PL/SQL?

Ans: A discard file is a file that includes records not placed into any table within a database. The extension for a discard file is .dsc, and we need to state the discard file through the discard file clause.

Find our upcoming Oracle PL SQL Training Online Classes

  • Batch starts on 9th Feb 2023, Weekday batch

  • Batch starts on 13th Feb 2023, Weekday batch

  • Batch starts on 17th Feb 2023, Fast Track batch

 
Global Promotional Image
 

Categories

Request for more information

Webinar

Live webinar: Transform your career and business with Digital Marketing

28th February | 07:00 pm

2 Registered

Gayathri
Gayathri
Research Analyst
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.