30 PL SQL Interview Questions and Answers

Meenakshi Agarwal
By
Meenakshi Agarwal
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large...
14 Min Read
PL SQL Interview Questions and Answers for Freshers.

No matter, whether you are a fresher QA engineer or a beginner in the DBA field. PL/SQL is a skill that is essential for engineers of both these profiles. Hence, we curated this post with thirty PL SQL interview questions that confront most candidates in DBA/QA interviews.

Before you drive in to read the questions, it’s customary to learn a few basic facts about PL/SQL.

1. PL/SQL is a modular programming extension of SQL cast by Oracle in the late 1980’s.
2. It offers an interpreted development environment for creating platform-independent scripts.
3. It supports executing from external programming tools to operate on databases.
4. PL/SQL provides a number of data types, programming constructs, functions, and procedures.
5. It inculcates object-oriented features and supports web applications and server-side scripting.

Looking at the gist of PL/SQL features, you can imagine how powerful this language is. So don’t miss reading out even a single PL SQL interview question from the below list. We’ve designed it in a quiz format so that it’ll keep your interest until the end.

Also, if you sincerely want to be successful in a DBA or a QA interview, then do follow the posts listed below. These would guide you further on the theoretical questions asked during the job interviews.

PL SQL Interview Questions and Answers for Freshers.

PL SQL Interview Questions and Answers for Freshers
PL SQL Interview Questions.

Q-1. What will be the value of a variable declared as type NUMBER which is not initialized but getting used in the executable section of the PL/SQL block?

A. NULL
B. 0
C. Results in a compilation error
D. Raise an exception

Show Answer

Option – A

Q-2. Which of the following literals are not available in PL/SQL?

A. String
B. Date and Time
C. Boolean
D. Integer

Show Answer

Option – D

Q-3. Which of the following sections is mandatory for a PL/SQL block?

A. Exception-handling Section
B. Executable Section
C. Declaration Section
D. All Sections are mandatory

Show Answer

Option – B

Q-4. Which of the following statements hold true for the declaration section of a PL/SQL block?

A. It starts with the DECLARE keyword.
B. It is a mandatory section.
C. It defines all variables, cursors, subprograms, and other elements to be used in the program.
D. All of the above.

Show Answer

Option – A and C

Q-5. What is the command to get the server output and display it on the screen?

A. set serveroutput on
B. set server output on
C. set dbmsoutput on
D. set dbms output on

Show Answer

Option – A

Q-6. Which of the following sections of a function declaration typically contains a return keyword?

A. Header Only
B. Declarative
C. Executable and Header
D. Executable and exception handling

Show Answer

Option – C

Q-7. What will be the output of the following code?

DECLARE
   fruit char(1) := 'P';
BEGIN
   case 
      when fruit = 'A' then dbms_output.put_line('Apple');
      when fruit = 'B' then dbms_output.put_line('Banana');
      when fruit = 'C' then dbms_output.put_line('Chiku');
      when fruit = 'P' then dbms_output.put_line('Pineapple');
      when fruit = 'F' then dbms_output.put_line('Hard Luck');
      else dbms_output.put_line('No such fruit');
   end case;
END;

A. Apple
B. No such fruit
C. Pineapple
D. Hard Luck

Show Answer

Option – C

Q-8. What will be the output of the following code?

DECLARE
   num1 number;
   num2 number;
   num3 number;
FUNCTION fx(x IN number, y IN number) 
RETURN number
IS
    z number;
BEGIN
   IF x > 2*y THEN
      z:= x;
   ELSE
      z:= 2*y;
   END IF;

   RETURN z;
END; 
BEGIN
   num1:= 20;
   num2:= 40;

   num3 := fx(num1, num2);
   dbms_output.put_line(num3);
END;

A. 20
B. 60
C. 80
D. 40

Show Answer

Option – C

Q-9. Which of the following control structures provides an unconditional approach to a particular part of a complex PL/SQL block?

A.  If-Then-Else
B.  While loop
C.  GoTo
D.  Decode

Show Answer

Option – C

Q-10. How will you define a variable using referential datatypes, if the variable name is EMPLOYEE_LASTNAME and the corresponding table and column name is EMPLOYEE and LNAME respectively?

A. Use employee.lname%type
B. Use employee.lname%rowtype
C. Search the data type of the LNAME column in the EMPLOYEE table to use it.
D. Declare it to be of type LONG.

Show Answer

Option – A

Q-11. Which of the following are the implicit cursor attributes?

A. %found
B. %too_many_rows
C. %notfound
D. %rowcount
E. %rowtype

Show Answer

Option – A, C, and, D

Q-12. Which of the following may cause an infinite loop to occur in a simple loop?

A. LOOP
B. END LOOP
C. IF-THEN
D. EXIT

Show Answer

Option – B

Q-13. Which of the following holds true when the FETCH statement is used to retrieve rows from the cursor?

A. It causes the cursor to close.
B. It causes the cursor to open.
C. It loads the current row values of the cursor into variables.
D. None of the Above

Show Answer

Option – C

Q-14. Which of the following statements are correct for PL/SQL cursors?

A. Explicit cursors are automatically created by Oracle.
B. Implicit cursors are programmer-defined cursors.
C. The latest implicit cursor is called the SQL cursor and has attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
D. All of the above.

Show Answer

Option – C

Q-15. What is the maximum number of handlers that get processed before the PL/SQL block is exited, in case an exception occurs?

A. One
B. All that apply
C. All referenced
D. None

Show Answer

Option – A

Q-16. What is the value of emp_id within the nested block in the sample code?

/* Start main block */
DECLARE
emp_id NUMBER(9) := 12939;
emp_age NUMBER(2) := 30;
BEGIN
/* Start nested block */
DECLARE
emp_id VARCHAR2(9) := 'GN56';
current_balance NUMBER(10,2) := 155.65;
BEGIN
-- what is the value of emp_id at this point?   NULL; 
END;
END;

A. 12939
B.  30
C.  GN56
D.  155.65

Show Answer

Option – C

Q-17. Which of the following statements holds true about the PL/SQL data structure VARRAY?

A. It has a maximum size that cannot be changed.
B. A VARRAY type is created with the CREATE VARRAY statement, at the schema level.
C. The maximum size of a VARRAY can be changed using the ALTER TYPE statement.
D. ALTER VARRAY statement can be used to modify the size of a VARRAY.

Show Answer

Option – C

Q-18. Which of the following parameters do cursors support?

A. IN
B. OUT
C. %ROWTYPE
D. IN OUT

Show Answer

Option – A, B, and, D

Q-19. What is the correct sequence of commands to process a set of records using explicit cursors?

A. INITIALIZE, GET, CLOSE
B. CURSOR, GET, FETCH, CLOSE
C. OPEN, FETCH, CLOSE
D. CURSOR, FETCH, CLOSE
E. GET, SEEK, HIDE

Show Answer

Option – C

Q-20. Which of the following statements holds true when you add the keyword “FOR UPDATE” at the end of a cursor?

A. Alert the DBA about the update on the table.
B. Create a bind variable.
C. Lock the rows before doing any updates.
D. Free up rollback segments before any update.

Show Answer

Option – C

Q-21. What are the three-parameter modes for procedures?

A. IN, OUT, IN OUT
B. R(ead), W(rite), A(ppend)
C. CONSTANT, VARIABLE, DEFAULT
D. COPY, NOCOPY, REF

Show Answer

Option – A

Q-22. Which of the following aggregate functions ignore NULL?

A. Distinct
B. Count(*)
C. Average()
D. None of the above.

Show Answer

Option – B

Q-23. Which of the following statements is not correct for large object data types in PL/SQL?

A. BFILE is used to store large binary objects in operating system files outside the database.
B. BLOB is used to store character data in the database.
C. CLOB is used to store large blocks of character data in the database.
D. NCLOB is used to store large blocks of NCHAR data in the database.

Show Answer

Option – B

Q-24. What will be the output of the following code snippet?

message varchar2(11) := 'Tech Beamers';
dbms_output.put_line ( SUBSTR (message, 6, 7));

A. Tech
B.  Beamers
C.  Beam
D. None of the above.

Show Answer

Option – B

Q-25. Which of the following lines represents the correct syntax to open a cursor named cursor_sql?

A. OPEN cursor_sql;
B. OPEN CURSOR cursor_sql;
C. FETCH cursor_sql;
D. FETCH CURSOR cursor_sql;

Show Answer

Option – A

Q-26. What will be the output of the following PL/SQL block?

DECLARE
   a number;
   b number;
   c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;

BEGIN
   a:= 10;
   b:= 12;
   findMin(a, b, c);
   dbms_output.put_line(c);
END;

A. 10
B.  12
C.  2
D. Syntax Error

Show Answer

Option – B

Q-27. Which of the following events require execution of trigger as its response?

A. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
B. A database definition (DDL) statement (CREATE, ALTER, or DROP).
C. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
D. All of the above.

Show Answer

Option – D

Q-28. Which of the following statements holds true for the inheritance of PL/SQL Objects?

A. PL/SQL allows the creation of an object from existing base objects.
B. Base objects should be declared as NOT FINAL to implement inheritance.
C. The NOT INSTANTIABLE clause allows you to declare an abstract object.
D. All of the above.

Show Answer

Option – D

Q-29. What operation does the {INSERT [OR] | UPDATE [OR] | DELETE} clause indicate in the syntax below?

CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

A. DDL operation.
B. DML operation.
C. DCL operation.
D. All of the above

Show Answer

Option – B

Q-30. Which of the following statements holds true for PL/SQL package specifications?

A. The specification is the interface to the package.
B. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.
C. It contains all information about the content of the package and the code for the subprograms.
D. All of the above.

Show Answer

Option – C

Must Read: 15 PHP Interview Questions and Answers for Experienced

Summary – PL SQL Interview Questions for Freshers.

It was one more post where we took up a key topic for QA Engineers. Also, PL/SQL skills are becoming more and more important for end-to-end validation. That’s why we came up with this blog post on the top thirty PL SQL interview questions for fresher DBA/QA engineers.

It would be great if you let us know your feedback on this post. Also, you can ask us to write on a topic of your choice. We’ll add it to our writing roadmap.

Top SQL Queries Asked in Interviews

Check out the 50 most-asked SQL query interview questions.

We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.

Keep Learning,
TechBeamers.

Share This Article
Subscribe
Notify of
guest

0 Comments
Newest
Oldest
Inline Feedbacks
View all comments