FAQ_PL/SQL

1.   What is Exception? Types of Exceptions? Exception Propagations?
·         Exception is a warning or error condition in oracle
o   User defined
o   Pre-defined
o   Non pre-defined

2.   What is RAISE_APPLICATION_ERROR? And what is PRAGMA EXCEPTION_ INIT?
·         It is user defined exception,
·         it is used to write the our own error message with oracle error number

3.   What are the Predefined exceptions we have?
Predefined exception is nothing but already defined by Oracle,
               
4.   When we SELECT statement in the Execution block what are the Predefined exceptions we have to use in the Exception Block?
NO_DATA_FOUND


5.   What is CURSOR? What are the Cursor types? What are cursor declaration steps?

·         Cursor is nothing but a private SQL work area which is used to store process information.

·         Types – Implicit – Explicit

6.   What is the diff between Implicit and Explicit and Ref Cursor?
·         Implicit – It is defined by the Oracle Server for queries that return only one row.
·         Explicit – Which is defined by the Users, for queries that return more than one row
·         Ref – With this we can change the select statement dynamically.
Without closing we can again open the cursor.

7.   In which program u has used the Cursor? One example for implicit cursor?

8.   What are the Cursor attributes we have?
AttributeExplanation
%ISOPEN- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns NULL if cursor is open, but fetch has not been executed.
- Returns TRUE if a successful fetch has been executed.
- Returns FALSE if no row was returned.
%NOTFOUND- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Return NULL if cursor is open, but fetch has not been executed.
- Returns FALSE if a successful fetch has been executed.
- Returns TRUE if no row was returned.
%ROWCOUNT- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns the number of rows fetched.
- The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.
9.   Can we issue the commit inside of the Cursor For Loop?
·         Yes

10.What is For Update OF Clause in the Cursors?
·         Is used in cursor to lock the rows first before u performs DML operations.

11.What is Procedure and what is Function?
·         Procedure: Is used perform an action
·         Function: Is used to compute a value

12.What is the RND and WND?
RND – Read No Database
WND – Write No Database

13.What is the diff between Procedure and Function?
·         Function must return at least one value, where procedure may or may not return value.

14.If we drop the table which we have used in the procedure do we need to recompile the procedure then how?
Yes, we need to recompile the procedure by using below command
 - ALTER PROCEDURE COMPILE

15.How to get the Procedure Source code from database?

      SELECT * FROM USER_SOURCE   -  Text is the column name

16.What is the Advantage of Package?
·         We can declare the global variables
·         Other users can’t see the package body or logic what it contains.

17.What are the other objects we can group inside of Package?
                  Procedures            
                  Functions
               
18.When we will go for creation of Package? Can we create Package body without creation of Package Specification?
·         NO
19.Can we declare Procedure directly in the package body without declaring in the package specification?
·         Yes
20.What is Trigger?
·         Trigger is a PL/SQL block when ever an event occurs it will fire.
21.Can we commit inside of trigger? How to delete the Trigger? How many triggers we can use maximum?
·         No  - You can’t Commit Inside a Trigger
·         Drop Trigger
·         12

22.What are The DML Triggers? What is diff between Row and Statement level trigger?
·         Insert, Update, Delete – DML Trigger
·         Row level  - For every row, when row doesn’t exists it will not fire
·         Statement level – only once

23.How to Debug PL/SQL Code?
·         DBMS_OUTPUT Package

24.What are the Trigger Predicates we have?
·         :OLD  :NEW

25.What is diff between Trigger and Procedure?
·         It is action based
·         When ever we need we can call

26.What is Package Variable what is advantage?
·         The variable which are declared in the Package Specifications
o   We can use these variables any where

27.What is PL/SQL Table what is the advantage of that when we will use PL/SQL Table?
·         PL/SQL Table is nothing but a INDEX by table

28.What is Global temporary table what diff between PL/SQL table and Temporary table?
·        

29.What is %ROWTYPE?
·         It is a record type to hold the different type of data types of the particular table.

30.What is mutating trigger? How can you handle this?
·         If Procedure contains any DML operations on the table again we will using the same table for same operations, it will lock the table
·         By using Pragma Autonomous Transaction

31.What are PL/SQL table attributes?
·         %ROWTYPE
·         %TYPE


No comments:

Post a Comment