PL-SQL interview question

PL-SQL-Interview-Question-Answer

Q.1-What is the basic structure of PL/SQL?

Ans:- Below is the basic structure of PLSQL

Declare

 Declaration Section;  --Optional

begin

 Executable Section;  --Mandatory

Exception

 Exception Section;    --Optional

end;

/

Q.2-What are uses of database Triggers ?

Ans:-We can use Triggers for following purpose

        1)  Auditing of DML operation on table

          2) Applying business rules on table 

Q.3-What are benefits of PLSQL package?

Ans:-Below are benefits of PLSQL packages

1) Encapsulation/hiding of business logic

2) Security

3) Performance improvement

4) Re-usability

5) Modular approach

Q.4:-What are the differences between Function and Procedure ?

Ans:- a) Functions are used for calculations while as Procedures are used for implement business logic

         b) Function always return a value that is why returning value we can assign  to variable but Procedure never return a value.

         c) We can call function in select statement while as we can't call procedure in select statement.

         d) Call method of procedure and function are different. 

Q.5- What is cursor and why it is required ?

Ans:-In PLSQL program we can not use query that return multi-row.

To handle multi-row query in PLSQL program , we use cursor . Cursor is a pointer to memory area that hold the data of query which is attached with cursor.

Q.6- What are cursor attributes ?

Ans:-Below are the cursor attributes

          1) %ISOPEN

          2) %FOUND

          3) %NOTFOUND

          4) %ROWCOUNT

Note:-Explicit Cursor attributes are used with cursor name while Implicit cursor attributes are used with "SQL" keyword.

Example:- If c1 is the name of cursor then c1%isopen for Explicit and for Implicit sql%isopen.

Q.7- What is Ref Cursor  ?

Ans:-Ref Cursor is a type that can hold a query. We can use variable of this type for assigning multiple queries.

Declaration of REF CURSOR:-

Strong REF CURSOR:- type is ref cursor return ;

Weak REF CURSOR:- type is ref cursor ;

Example :- type rc is ref cursor return number;

                   type rc1 is ref cursor ;

Assigning REF CURSOR type to variable:-

                ;

Example :- As rc & rc1 are the ref_cursor_type so these type can be assign to variable

                       rc_rec rc;                  

                       rc1_rec rc1 ;

Q.8- What are Collection Type in PLSQL ?

Ans:- Below Collection types are available in PLSQL

         1) PLSQL Table or Associative Array

         2) Nested Table

         3) Varray or variable array

 Q.9- What  is PLSQL Table ?

Ans:-PLSQL Table is a type of Collection and is also known as Associative Array.

Q.10- What are Collection methods ?

Ans:- Below are the collection method

           COUNT

           DELETE

           EXISTS

           EXISTS(n)

           EXTEND

           FIRST

           LAST

           LIMIT

           NEXT

          PRIOR

          PRIOR(n)

          TRIM

Q.11- What is mutating table error ?

Ans:-When a trigger is going to perform DML operation on table where another DML is going on then trigger will not be able to perform action on that table and return with mutating table error.

Q.11- How can we resolve mutating table error ?

Ans:-There are different ways to handle mutating table error, some are below

         1) Change row level trigger to statement level trigger

         2) Make trigger to Autonomous Transaction.

         3) Change before trigger to after trigger .

Q.12- What is Autonomous Transaction ?

Ans:- Autonomous Transaction is a independent transaction from calling environment means what you perform outside from autonomous transaction that does not affect to autonomous transaction.

Note :- We can make procedure and trigger to autonomous transaction by declaring "pragma autonomous_transaction" in declare section.

 

Q.13-What are different methods to trace the PL/SQL code ?

Ans:-Below are the different methods for tracing of PLSQL code

          a) DBMS_APPLICATION_INFO

          b) DBMS_TRACE

          c) DBMS_SESSION

          d) DBMS_MONITOR

          e) trace and tkprof utilities

Q.14- What is Exception and what are types of Exception in PLSQL ?

Ans:- Exception is an error condition during a program execution. PL/SQL supports programmers to handle such conditions using EXCEPTION block in the program and take an appropriate action  against the error condition.

Types of exception are

1) Pre defined Exception

2) User defined Exception

3) Un-named Exception

Q.15- What is the difference between "raise" and "raise_application_error"?

Ans:-raise:- raise statement use to raise a user defined exception, declared in declaration section of the pl/sql block,

Example:-

raise exception_name;

raise_application_error:-raise_application_error is use to display user defined error code and message for any error condition.

Example:-

RAISE_APPLICATION_ERROR(-20999, 'Eror_message');

Q.16- What are predefined  Exceptions ?

Ans:-Below are the predefined Exceptions

1)  ACCESS_INTO_NULL                 ORA-06530

2)  CASE_NOT_FOUND                    ORA-06592

3)  COLLECTION_IS_NULL             ORA-06531

4)  CURSOR_ALREADY_OPEN       ORA-06511

5)  DUP_VAL_ON_INDEX                 ORA-00001

6)  INVALID_CURSOR                       ORA-01001

7)  INVALID_NUMBER                      ORA-01722

8)  LOGIN_DENIED                            ORA-01017

9)  NO_DATA_FOUND                       ORA-01403

10) NOT_LOGGED_ON                      ORA-01012

11) PROGRAM_ERROR                      ORA-06501

12) ROWTYPE_MISMATCH              ORA-06504

13) SELF_IS_NULL                             ORA-30625

14) STORAGE_ERROR                       ORA-06500

15) SUBSCRIPT_BEYOND_COUNT  ORA-06533

16) SUBSCRIPT_OUTSIDE_LIMIT   ORA-06532

17) SYS_INVALID_ROWID                ORA-01410

18) TIMEOUT_ON_RESOURCE        ORA-00051

19) TOO_MANY_ROWS                     ORA-01422

20) VALUE_ERROR                            ORA-06502

21) ZERO_DIVIDE                              ORA-01476

Q.17- What is the difference between %type and %rowtype ?

Ans:- %type take a reference for datatype from one column while %rowtype take a reference for datatype of record.

Example:- vsal                 emp.sal%type;

                   emp_rec         emp%rowtype;

Q.18- What are Cursor Exceptions ?

Ans:- Below are the Cursor Exceptions

           a) cursor_already_open

           b) invalid_cursor

Q.19-Tell me some predefined packages 

Ans:- Below are the pre defined Oracle supplied packages

           DBMS_OUTPUT, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT,  

           DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

 Q.20- What is the difference between formal parameter and actual parameter ?

Ans:- Formal parameter:-Formal parameters are the parameters which are used on the time of definition/Creation of the procedure/function.

Actual parameter:-Actual parameters are the parameters which are used on the time of calling of the procedure/function.

Q.21-What is the maximum number of triggers can apply on a single table?

Ans:- 12