Oracle PL/SQL Faqs

1.                  What are the features of OOPS used in PL/SQL ?

Ans:           Inheritance – Reusability

                  Abstract Datatype

                  Method Overloading

2.                  What are the built in packages available in PL/SQL ?

Ans:     DBMS_OUTPUT, DBMS_SQL, DBMS_STANDARDS, DBMS_UTL, DBMS_LOCK, DBMS_LOB, DBMS_JAVA, UTL_FILE,

           

3.                  Diff. In Function and Procedures?

      Ans:           1.         Function must return a value where as procedure does not.

2.         Function can be used in SQL statement where as Procedure does not.

4.                  What is the structure of the PL/SQL block?

      Ans:           <<Declare>>  (Optional)

                                    Variable & Cursor Declaration

                        <<Begin>>

                        Executable Statements

            <<Exception>> (Optional)

                        Exception handling

            End;

                       

5.                  Can we use label for anonymous PL/SQL block?

      Ans:           Yes, use it in “<<label name>>” structure

   

6.                  What are the exceptions in PL/SQL Block?

Ans:          CURSOR_ALREADY_OPEN, DUP_VAL_ON_INDEX, INVALID_CURSOR, INVALID_NUMBER, NO_DATA_FOUND,

                  PROGRAM_ERROR, VALUE_ERROR, ZERO_DIVIDE

7.                  What are the types of Variable binding?

      Ans:           Two types of binding

1.                  Early Binding (at compile time)

2.                  Late Binding (Runtime)

8.                  What are the PL/SQL table and nested table ?

     Ans:           

9.                  What are the Scalar type of variables?

Ans:     Number, Char, Varchar2, rowed, urowid, raw, long, longraw, blob, clob, bfile, Nchar, nvarchar2, date 

10.              What are the user defined  data types ?

     Ans:            1.         Structured Types

·         Object Types

2.      Collection Types

·         Varray

·         Nested Tables

3.      REFS (To object Types)

11.              How can we use label in PL/SQL block?

Ans:           We can declare a label in “<<label_name>>” format & can pass control to it using “GOTO” statement.

12.              How can we know that proc has passed a value ?

     Ans:            We can check it using “IN” or “OUT” or “INOUT” parameter.

13.              What is the difference between REF Cursor & PL/SQL Table.

Ans:           REF Cursor is like Pointer whereas PL/SQL Table is like ARRAY.

REF Cursor can pass to a procedure/function as a parameter directly whereas in PL/SQL table one record has to be passed each time.