add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, April 1

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.

No comments: