PL SQL Interview Questions and answers pdf free download for freshers

            1. Basic Concepts of PL SQL

1.      What is the basic structure of a Procedural Language/Structured Query Language (PL/SQL)  block?

2.     What are the different datatypes that can be defined in a PL/SQL block?

3.     What is the difference between SQL and PL/SQL?

4.     What are the advantages of PL/SQL?

5.    What are the mandatory keywords in a PL/SQL program block?

6.    Can arrays be used within a PL/SQL program block?

7.    How does a PL/SQL block integrate with data dictionary?

8.    Can a variable be assigned a value from database? If yes, then how?

9.    Does a NOT NULL constraint of a database column apply to %TYPE variables?

10.  Can SQL group functions be used within the procedural statements?

11.  Explain the DECODE function? Can it be used in the procedural statements?

12.  What is the maximum length of an identifier that can be used?

13.  What is the equivalent of DECODE function in the procedural statements?

14.  Can DDL commands be used within a procedure? If so, then how?

15.  What is an anonymous block and how is it executed?

16.  What is a bind variable and how is it used?

17.  How can you display data from a PL/SQL block?

18.  What is an identifier?

19.  In the following code snippet, can the v_ex variable reference the value of the v ey variable?

20.  Explain why the following code generates a compilation error.

21.  How is global variable declared within a nested block?

22.  What is the use of NVL function?

23.  What are the different ways to determine the status of a cursor?

24.  What is the difference between implicit cursor and explicit cursor?

25.  Why does SQL%ISOPEN always returns the FALSE value?

26.  What are OPEN, FETCH, and CLOSE cursor commands?

27.  Suppose, emp_details is an explicit cursor and v_emp_dtls is a %ROWTYPE variable. Choose  the correct FETCH statement from the following options.

a. FETCH emp_details INTO v_emp_dtls.emp_code

b. FETCH emp_details INTO v_emp_dtls%ROWTYPE

c. FETCH v_emp_details INTO emp.dtls

d. FETCH emp_details INTO v_emp_dtls.

28. What are Transaction Control statements? Can they be used within the PL/SQL block?

29. What is a SAVEPOINT command?

30. What are pseudo-columns? How can you use pseudo-columns in procedural statements?

31. What are cursor variables and how are they declared?

32. How can you check if an UPDATE statement in PL/SQL is successfully executed?

33. What is a RECORD datatype?

34.  What is the function of OPEN cursor command?

35.  What is the advantage of using the %ROWTYPE datatype?

36. Explain how the %NOTFOUND attribute can be used to exit from a loop.

37.   How can explicit cursors be used to fetch multiple rows?

38.  Can you define a record type based on an explicit cursor?

39.  What is a cursor FOR loop and how does it differ from an explicit cursor?

40.   Write a program to pass parameters to a cursor and print the result.

41.   What is SELECT FOR UPDATE and how can it be used in a cursor?

42.   How is it possible to fetch a current row from a cursor without using ROWID?

43.   What is the error in the following code?

44.   Which data types can be used with a RECORD Type?

45.   What are the different forms of conditional IF construct?

46.   What is the difference between EXIT and EXIT-WHEN statements?

47.   Is it possible to label a loop? If yes, then what is its benefit?

48.   Distinguish between WHILE-LOOP and FOR-LOOP.

49.   In a FOR-LOOP, can the counter value be assigned a value within the loop?

50. Is it possible to assign a dynamic value to a loop counter?

51. What will be the consequence if the upper bound of the FOR-LOOP counter gets a smaller  value than the lower bound?

52.  How can you force FOR-LOOP to end unconditionally?

53.  What is the error in the following code?

54.  Give an example of the CASE construct.

55.  What will be output of the following code?

56.  Suppose there are two Boolean variables, v_samplel and v_sample2. What will be the value of the v_sample variable in the following conditions?

57.  What is the error in the following code?

58.  What is the error in the following code(Below)?

59.   Choose the correct statement from the following options.

60.   Can Index-By table be stored in a database? If not, then which type of collection can be used to store in a database?

61.  What is the difference between nested tables and VARRAYs?

62. What is the difference in the usage of SELECT INTO and FETCH?

63.  How can two PL/SQL records be compared?

64.  Which datatype is not allowed in the definition of PL/SQL record?

65.  What is the value of SQL%FOUND attribute after and before an INSERT statement is executed?

66.  How are elements added to an Index-By table?

67.  Can an IF-THEN-ELSIF block have infinite number of ELSE clauses?

68.  What will be the value of %N0TF0UND attribute after a cursor is opened but before a FETCH statement is executed?

69.  What is the advantage of defining implicit records?

70.  Is it possible to define a NOT NULL field in a record?

71.  Is it possible to define a CONSTANT value in a record?

72.  Is it possible to copy contents of one record to another?

73. Do records have methods?

74. Can a VARRAY be used in a record definition?

75. Can an uninitialized nested table be referenced?

76. What is the difference between initialization of Index-By table and nested table?

77. Can collections be compared with Boolean operators?

78. Can the EXISTS method be used for Index-By table?

79. What will the result of the NEXT(<x>) method, if x is the last element of the collection?

80. What is the difference in accessing objects of LOB and LONG datatypes?

81. What is the difference between LOB objects stored in-line and out-of-line?

82. Which package is used to manipulate LOB objects?

83. What are LOB locators?

  2.  Triggers

1.  What are the system privileges that are required by a schema owner (user) to create a trigger on a table?

2. What are the different types of triggers?

3. How can triggers be used for the table auditing?

4.  What are INSTEAD OF triggers?

5. What is the difference between database trigger and stored procedure?

6.  How can the performance of a trigger be improved?

7.  What are the events on which a database trigger can be based?

8. What is a CALL statement? Explain with an example.

9.   What is a mutating table?

10.   Which data dictionary views have the information on the triggers that are available in the database?

11.   What are schema-level triggers?

12.   What is a database event trigger?

13.   In what condition is it good to disable a trigger?

14.   Which column of the USERJTRIGGERS data dictionary view displays the database event that will fire the trigger?

15. What is the meaning of disabling a trigger?

16.  Can triggers stop a DML statement from executing on a table?

17. Can a view be mutating? If yes, then how?

18.  Can a COMMIT statement be executed as part of a trigger?

19.  What is the difference between ALTER TRIGGER and DROP TRIGGER statements?

20.  Do triggers have restrictions on the usage of large datatypes, such as LONG and LONG RAW?

21.  Are DDL triggers fired for DDL statements within a PL/SQL code executed using the DBMS.SQL package?

22. Does a USER_OBJECTS view have an entry for a trigger?

23.  How can you view the errors encountered in a trigger?

24. Does USERJTRIGGERS have entry for triggers with compilation errors?

25.  Which of the following events cannot be customized with triggers?

26.  Is it possible to pass parameters to triggers?

27.  Can a SELECT statement fire a trigger?

    3. Procedures, Functions and Packages

1.      What is a subprogram? What are its advantages?

2.      Give the basic block structure of a subprogram.

3. What is a procedure? Can it be stored in the database?

4.    What does the REPLACE option indicates in the procedure definition?

5.    Can the size of the parameter passed to the procedure be restricted in the procedure definition?

6.  What is the difference between formal and actual parameters in a subprogram specification?

7.  Which datatypes can be used for parameters within a procedure?

8.  What are the different parameter modes, which can be used in the procedure specification?

9. How can a parameter be initialized in a procedure?

10. Can default values be assigned to IN OUT parameters in a procedure?

11.  What are the different methods for passing parameters to a procedure?

12.  Can a procedure be declared and defined within a PL/SQL block?

13.  What is the scope of the procedure defined within a PL/SQL block?

14.  What happens if an exception is unhandled within a procedure?

15.  What is the basic specification for a function?

16.  Can there be multiple RETURN statements within a function?

17.  Is it possible to define a RETURN statement in the exception part of a function?

18.  What are the different parameter modes, which can be used in the function specification?

19.  Can a function be defined without a RETURN statement?

20.  Can functions be stored within a database as database objects?

21.  Which data types can be used within functions that can be invoked in SQL statements?

22.  What restrictions can be applied on stored functions, which are called from SQL statements?

23.  What is the difference between CREATE OR REPLACE FUNCTION and DROP FUNCTION commands?

24.  Can a role be used to define privileges to access database objects of another schema in procedures and functions?

25.  How can a compiled object code be viewed?

26.  What is the use of the USERJERRORS data dictionary view?

27.  Write the SQL query to view all the procedures within a schema.

28.  How can the text of a procedure or function specification be viewed?

29.  Write the syntax for the SHOW.ERROR command.

30.  How can the parameter list for a function or procedure be displayed?

31.  What are the advantages of using stored procedures and functions?

32.  What is a package and how does it differ from procedure and function?

33.   What is the difference between private package construct and public package construct?

34.   Can a complete package be called?

35.   What is the difference between local and global variables within a package?

36.   Is it possible to write a package specification without a package body?

37.   Suppose a procedure, a_proc, is defined with a number parameter as input within a package called a_package in the a_user schema. Write the statement to invoke the a_proc procedure on the SQL prompt from the user, b_user.

38. When does a package need to be recompiled?

39.   What are the advantages of using packages?

40.   Is DROP PACKAGE command used to drop the package specification or the package body?

41.   Is it possible to drop the package body without dropping the package specification?

42.   How the concept of overloading is implemented within a package?

43.   What is forward declaration and how can it be achieved within a package?

44.   What is a One-Time-Only procedure and how is it declared within a package?

45.   Which privileges are required to execute a subprogram owned by another user?

46. What is the significance of AUTHID property while creating a procedure?

47.   How can you bug a procedure or function for errors?

48.   When does a subprogram need recompilation?

49.   When should a procedure and a function be used?

50.   Can stand-alone programs be overloaded?

51.   What are function purity levels?

52.   What are the restrictions on functions that are called within SQL statements?

53.   Which of the following is a correct option to input a parameter to a procedure from a calling block?

54.   Suppose a procedure proc__calc_student_marks inserts the values into the student_total_marks table. If the table is modified by adding another column, what needs to be done to make the procedure valid?

55.   Suppose a procedure is created using the CREATE PROCEDURE command; however, it gives a syntax error.

        Which data dictionary views will have the source code of the procedure?

56.  Can a parameter use a sequence generator for the assignment of default value to a parameter?

57.  Can an OUT parameter, v_total_marks, be referenced within the procedural code without being assigned a value?

58.  Can an IN parameter be assigned a value within a procedure?

59.  Suppose a procedure proc_get_lighest_rank is stored in the user schema, user_a. How can you access the procedure within this schema?

60.  Can BOOLEAN datatype be used in functions that are called from SQL statements?

61.  Is it possible to make procedure or function part of the IF THEN ELSE block?

62.  Can you invoke a stored function or procedure from the Oracle Forms in the database?

63.  What is the meaning of the following error statement: ORA-06575: Package or function func_test is in an invalid tate?

     4. Error Handling

1.  What is an exception?

2.  How is an exception raised and handled?

3.  When is an exception propagated to the outside environment?

4. Give the block structure of an exception section within the PL/SQL code.

5.  What are the different types of exceptions?

6.  Describe the OTHERS exception handler.

7.  Can the PL/SQL block process more than one exception at a time?

8.  Is it possible to have more than one OTHERS clause?

9.  Give a few pre-defined Oracle errors.

10. What is the advantage of OTHERS exception handler?

11.  Describe the SQLCODE and SQLERRM functions.

12. What is RAISE.APPLICATIONJERROR?

13. What happens to the program control when the RAISE_APPLICATION_ERROR procedure is encountered?

14. What happens after a sub-block handles an exception?

15.  Can RAISE.APPLICATION.ERROR be  called in the executable section of the program unit?

16.  What happens if an exception is not handled in a sub-block?

17. Can processing be resumed from the point exception was raised after the exception is handled?

18.  How can a transaction be retried after an exception?

19. What causes the INVALID_CURSOR exception?

20.  Where will the control be transferred if an exception is raised within an exception section of the block?

21.  When is the CURSOR_ALREADY_OPEN exception raised?

22.  When is the ROWJTYPE.MISMATCH exception raised?

23.  What is the advantage of having a separate exception section within a PL/SQL code?

24.  What happen when there is no handler for a raised exception?

25.  What is the difference between user-defined exceptions and Oracle-defined exceptions?

26.  What is the difference between RAISE and RAISE.APPLICATION ERROR?

27.  What is the meaning of PRAGMA keyword?