Oracle PL/SQL interview questions and answers

  The following PL/SQL Interview Question and Answers created based on my person experience, I hope it will use for you while you are facing PL/SQL interview, These are very frequently asking question and answers (FAQS) in PL/SQL interview.

1. What is the difference between %TYPE and %ROWTYPE?

      %TYPE : It provides the datatype of a variable or database column

       %ROWTYPE : It provides the record type that represents a row in a table.

2. What are the main Benefits using %TYPE and %ROWTYPE?

·        You need not know the exact datatype of the field in the table.

·        If you change the database definition (field size increased), datatype declared using %TYPE or %ROWTYPE at the time of runtime it will be take changed database field.

                   

3. What is Collection? What are the collection types using in PL/SQL?

          A Collection is an ordered group of elements , all of the same type.

         PL/SQL offers these Collections:

a.  Nested Tables

b.  VArrays

c.  Index-By-tables (Associate arrays)

4. Give the Brief description on Nested Tables?

     PL/SQL Nested tables like one dimensional array. Nested tables size unbounded ,So the size of the Nested table can increase dynamically.

     We can delete elements from Nested table using DELETE ( it might leave gaps) and NEXT for iterate over the scripts.

Syntax:

     TYPE  type_name AS TABLE OF element_type

Ex:

CREATE TYPE  Stulist AS TABLE OF VARCHAr2(10)

/

CREATE TYPE student AS OBJECT (

id NUMBER,

name VARCHAR2(10),

stuinfo stulist)

5. What is VARRY?

              Varrays  allow you to associate a single identifier with an entire collection.

              Varray has the maximum size which you must specify in its type definition.

Syntax:

         TYPE type_name AS VARRAY(size limit) OF  element_type

                       Ex:

CREATE TYPE  Stulist AS VARRAY(50) OF VARCHAr2(10)

/

CREATE TYPE student AS OBJECT (

id NUMBER,

name VARCHAR2(10),

stuinfo stulist)

6. What is the Index-By-Tables (Associated Arrays) ?

                  Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

   

Syntax :

             TYPE type_name IS TABLE OF element type

               INDEX  BY  BINARY_INTEGER

Ex:          TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE

         INDEX BY BINARY_INTEGER;

         emp_tab EmpTabTyp;

      

7.What is the difference between Nested tables and Varrays?

             Varrays is good choice  when the number of elements known in advance and all the elements are usually accessed in sequence.

              Nested tables are dynamic. You can delete arbitrary elements rather than just removing an item from end.

8. What is the difference between Nested tables and Index-By-tables(Associated Arrays)?

               Nested tables can be stored in database column, but associated arrays can not. Nested tables are appropriate for important data relationships that must be stored persistently.

              

                Associated Arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or Package is initialized. These are good for collecting information whose value is unknown before hand, because there is no fixed limit on their size.

9. Can we Delete Individual Element from VARRAYS?

             VARRAYS are dense. We can’t delete elements from VARRAYS.

10. What is bulk binding?

          A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding.

           For example   If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation.

·        This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines         

11. Where can you use the FORALL statement?

            To do bulk binds with INSERT,UPDATE and DELETE statements you enclose the SQL statement  with in a PL/SQL  using FORALL

Syntax:

      FORALL index IN lower_bound..upper_bound

   sql_statement;

12. Where you use the BULK COLLECT?

            To do bulk binds  with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

13. What is a cursor? Why Use a cursor?

              When a query executed in oracle, the result set is produced and stored in the memory .Oracle allows accessing this result set in the memory through Cursor.

              Need of the cursor is Many times, when a query returns more than one row. We might  want to go through each row and process the data in different way for them.

               

14. What are the CURSOR types?

          PL/SQL uses 2 types of Cursors

                Implicit Cursor:

                         PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row.

                Explicit Cursor:

                         Queries that return more than one row, you must declare an explicit cursor

15. How many ways CURSOR can open?

           CURSOR can open in two ways

                                               1) OPEN ---FETCH –CLOSE

                                                Ex:

                                                       CURSOR c1 IS

                                                          SELECT ename, empno from EMP;

                                                      OPEN C1;

                                                         LOOP

                                                              FETCH ename, empno INTO var1, var2;

                                                              EXIT WHEN C1%NOTFOUND;

                                                          END LOOP;

                                                        CLOSE C1;

                                               2)  FOR LOOP

                                                  Ex:

                                                           CURSOR c1 IS

                                                          SELECT ename,empno from EMP;

                                                       

                                                     FOR c1 in C2 LOOP

                                                              Var1 =c2.ename;

                                                          Var2 = c2.empno;

                                                    END LOOP;

               

                

16. What is the difference between OPEN-FETCH-CLOSE and FOR LOOP in CURSOR?

          FOR LOOP in CURSOR:

                   A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

        OPEN-FETCH-CLOSE:

                                       Explicitly we have to open the query and closing the query.

17. Can we pass the parameters in CURSOR?

       Yes, we can

18. What are the explicit cursors attributes?

       %FOUND,%ISOPEN,%NOTFOUND,%ROWCOUNT

19. What are the implicit cursors attributes?

        %FOUND,%NOTFOUND and %ROWCOUNT

 20. What is the purpose of %ROWCOUNT?

             How many rows effected we can know from the %ROWCOUNT. %ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, DELETE or SQL statement.

              If INSERT,UPDATE,DELETE statements effected no rows or SELECT statement returns no rows then the %ROWCOUNT value is ZERO.

             If SELECT statement returns more than one  row and PL/SQL raises the TOO_MANY_ROWS then %ROWCOUNT VALUE is 1. it doesn’t retrieve the actual value.

21. What is the REF CURSOR?

           REF CURSOR is a cursor variable.  Use of the cursor variables to pass the query result sets between PL/SQL stored subprograms, packages to client.

          Syntax:

                  TYPE  ref_cursor_name IS REF CURSOR [RETURN return_type]

           Ex:

                   DECLARE

                         TYPE  DeptCur IS REF CURSOR RETURN  dept%ROWTYPE;

                           Deptcut deptmain;

22. What are the PL/SQL Exceptions?

         Pre defined Exceptions:

                                  These Exceptions are raised implicitly by the run-time system.

                        Ex: NO_DATA_FOUND, ZERO_DIVIDE

         User defined Exceptions:

                       User-defined exceptions must be raised explicitly by RAISE statements.

                         DECLARE

                              SAL_check   EXCEPTION;

                        BEGIN

                                        IF SAL <100  THEN

                                               RAISE SAL_CHECK

                               END IF;

                        EXCEPTION

                               WHEN SAL_CHECK THEN

                                             -----

                      END;

23. How to define our Own Error Messages?

          We can define using RAISE_APPLICATION_ERROR.        

       Syntax:

                 RAISE_APPLICATION_ERROR (Error number, error message);

·        When this called, it ends the subprogram and returns the user defined error message to application.

·        Error Number range  is -20000 to-20999

          

24. What are SQLCODE and SQLERROR?

          SQLCODE: returns the number of the Oracle Error.

          SQLERRM: Associated error message for the SQLCODE          

25. What are subprograms?

             Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called Procedures and functions.

26. Where can we use procedure and Function?

            Procedure used for to perform an action. Function to compute Value.

27. Advantages of subprograms?

            Extensibility, modularity, reusability and maintainability.

28. Give the procedure Syntax?

          [CREATE [OR REPLACE]]

          PROCEDURE procedure_name[(parameter[, parameter]...)]

          [AUTHID {DEFINER | CURRENT_USER}] {IS | AS}

          [PRAGMA AUTONOMOUS_TRANSACTION;]

          [local declarations]

          BEGIN

          executable statements

          [EXCEPTION

          exception handlers]

          END [name];

29. What is AUTHID? What is the Purpose of AUTHID in procedure?

            The AUTHID clause determines whether a stored procedure executes with the Privileges of its owner (the default) or current user.

30. What is AUTONOMOUS_TRANSACTION?

·        The pragma AUTONOMOUS_TRANSACTION instructs the PL/SQL compiler to mark a

 procedure as autonomous (independent).

·        Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.

31. Can we call a function from SELECT Statement?

        

          YES

              

32. What is forward declaration?

         Forward Declaration advises PL/SQL that the body of procedure can be found later in the block, but the specification should be declared first.

33. What are actual and Formal Parameters?

            Variables or expressions referenced in the parameter list of a subprogram call are actual parameters.

      Ex: update_sal(empnum,sal);

             Variables declared in subprogram specification and referenced in the subprogram body are  formal parameters.

     Ex: PRCODURE update_sal(empnum number,sal number)

34. What are the types of notations?

            Positional, Named and Mixed notations

Ex:    PROCEDURE  acc_update (acct_no NUMBER, amount NUMBER);

Positional Notation :

                               Acc_update(acct,amt);

Named Notation : acc_update(amount =>amt, acct_no => acct);

                                  Or

                           Acc_update(acct_no=>acct,amount=>amt);

Mixed Notation:

               Positional Notation must precede named notation. And the reverse notation is not allowed.

                    Acc_update(acct,amount => amt);

35. What are the Parameter Modes and what is the default parameter mode?

               Parameter Modes are IN,OUT,INOUT

         IN parameter is the default parameter mode.

36. In Parameter modes which are pass by Reference and Pass by Value?

     Pass By Reference :  IN

                                   Pointer to the actual parameter is passed to the corresponding formal parameters. Both Parameters use the  same memory location.

                                 

     Pass By Value       :OUT,IN OUT                

                                 The Values of OUT Actual parameters copied into the corresponding formal parameters.

37. What is NOCOPY? When we use it?

         

                 NOCOPY is Compiler Hint. When the Parameters hold large data structures such as collections and records , all this time copying slows down the execution. To prevent this we ca specify NOCPY. This allows the PL/SQL Compiler to pass OUT and INOUT parameters by reference.

                

38. What is Table Functions?

                Table functions are functions that produce a collection of rows (either a nested table or a Varray) that can be queried like a physical database table or assigned to PL/SQL collection variable. We can use the table function like the name of the database table.

39. What is PL/SQL Package? And what are the parts of the package?

               Package groups logically related PL/SQL types, procedures, functions.

            Package having the two parts: Package specification and Package Body.

        

·        Package Body is optional in some cases

40. What are the advantages of the Packages ?

        Modularity, Easier application design, Information hiding, better performance.

         

·        When you call the Packaged Procedure for the first time ,the whole package is loaded in to memory. So later calls to related subprograms in the package require no disk I/O.

·        Packages stop cascading dependencies and thereby avoid unnecessary recompiling.

41. What are Private and Public variables in Package?

·        Variables declared in Package Body and restricted to use with in the package those variables are called Private Variables.

·        Variables declared in Package specification and this variable is Visible outside the package ,those variables are called public variables.

42. Which Package can we use to display the output from the PL/SQL blocks or subprogram?

             DBMS_OUTPUT

43. Which statement we have to set to display output on SQL*PLUS?

           SET SERVEROUTPUT ON

44. How to read and write the text files?

           Using UTL_FILE

45. What is Dynamic SQL?

            Some Statements can, probably will change from execution to execution means change at runtime, and they are called dynamic SQL statements.

46. What is the need for Dynamic SQL?

·        You want to execute the DDL statements from the PL/SQL block

·        You  want to Execute the Control statements from the PL/SQL block

47. How can we  execute DDL statements from PL/SQL Block?

              Using EXECUTE_IMMEDIATE statement

               Ex: EXECUTE_IMMEDIATE(‘TRUNCATE TABLE T1’);

48. What is Trigger? And Define the Parts of the trigger?

          Trigger is stored procedure, that run implicitly when an INSERT, UPDATE Or DELETE statement issued against the table or against the view or database system action Occurs.

  Parts of the Trigger :

·        Triggering Event or statement

·        Trigger restriction

·        Triggering action

49. What are the types of triggers?

·        ROW Level Triggers

·        Statement Level Triggers

·        BEFORE and AFTER Triggers

·        INSTEAD of Triggers

·        System Event and User event Triggers

50. What is the difference Between Row Level Trigger and Statement Level Trigger?

         

·        Row level trigger executes once for each row after (or before) the event.  This is defined By using FOR EACH ROW

Statement Level trigger executes once after (or before) the event, independent how many rows are affected by the event.    

51. How can we access the attribute values in triggers?

                     Using :OLD and :NEW  only with Row level trigger

52. Where can we use instead of triggers?

                INSTEAD-OF triggers Provide a transparent way of modifying views, that can’t be modified directly through SQL DML statements.

53. What are the System Event Triggers?

          System events that can fire triggers are related to instances startup and shutdown and error messages

  STARTUP

  SHUTDOWN

  SERVERERROR

 54. What are the User event Triggers?

             User events that can fire triggers are related to user logon and logoff, DDL statements

                       LOG ON

                   LOG OFF

                       BEFORE CREATE and AFTER CREATE

                       BEFORE ALTER and AFTER ALTER

                       BEFORE DROP and AFTER DROP

55. Can we give the Commit and Roll back in side the Trigger?

                 NO

56. What is mutating table Error?

          A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.

57. What is Optimization? What are the types of optimization?

                  Optimization is the process of choosing the most efficient way to execute SQL statement.

Optimization is in two approaches:

                       RULE Based

                       COST Based

58. What is Execution Plan?

               Combination of steps Oracle Uses to execute a statement is called an execution plan. Execution plan includes an access method for each table that the statement accesses and ordering of the tables

              We can check the execution plan by EXPLAIN PLAN Command.

 59. What are the LOCKS providing by ORACLE?

           Oracle provides two different levels of locking

  ROW LEVEL

            Each row in the table locked individually

  TABLE LEVEL

      

                   Entire Table Locked

60. What are the Modes of the LOCKING?

o   Exclusive LOCK Mode

o   Share Lock Mode

61. What is exclusive Lock mode?

                This Lock prevents the associated resource from being shared. This Lock Mode obtained to modify data.

          Ex :  LOCK TABLE table_name IN EXCLUSIVE  MODE

62. What is Share Lock Mode?

               This Lock allows the associated resource to be shared, depending on the operations involved.

          Ex:  LOCK TABLE table_name  IN SHARE MODE

                     

You May Like:

SQL interview questions and answers

Oracle Interview Questions and Answers - FAQs   

Oracle Reports Interview Questions and Answers

Oracle Forms Interview Questions and Answers  

SQL Practice Queries