Pragma EXECPTION_INIT and its usage ?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

 

     e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

 

Pragma exception_init Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program and handle it accoding to your own way.
It should be declare at the DECLARE section.
example
declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING 100);
begin
select sal in to salaryfrom emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;

difference between explicit cursor and implicit cursor in oracle

When a single insert, delete or update statement is executed within PL/SQL program then oracle creates an implicit cursor for the same, executes the statement, and closes the cursor. You can check the result of execution using SQL%ROWCOUNT function.

Explicit cursors are created programmatically. The cursor type variable is declared and associated with SQL query. The program then opens a cursor, fetches column information into variables or record type variable, and closes cursor after all records are fetched. To check whether cursor is open or not use function SQL%ISOPEN and to check whether there are any records to be fetched from the cursor use function SQL%FOUND.

Difference between a PROCEDURE & FUNCTION in oracle

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.

Oracle Data Dictionary Objects for Retriving data for a given schema

1) tables
2) views
3) triggers
4) procedures
5) constraints
6) all of the above mentioned objects

The objects used are:

•  user_tables or tabs
•  user_views
•  user_triggers
•  user_procedures
•  user_constraints
• user_objects

Different SQL queries in the same PL/SQL program vs. design time declared explicit cursors with an association to only one query.

What is Oracle's data dictionary used for

Data dictionary in Oracle contains information about all database objects such as tables, triggers, stored procedures, functions, indexes, constraints, views, users, roles, monitoring inform

How to Rectify Oracle Deadlock | Oracle Deadlock Rectification with a Quick solution

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

Causes to Deadlock

LocksWorker threadsMemoryParallel query execution-related resourcesMultiple Active Result Sets (MARS) resources
All the above listed may cause to Deadlock.The default interval is 5 seconds.
If the lock monitor thread finds deadlocks the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
If the lock monitor thread stops finding deadlocks the Database Engine increases the intervals between searches to 5 seconds.
You can find some more information regarding Deadlock
http://msdn.microsoft.com/en-us/library/ms178104.aspx

Oralce Dynamic SQL Debugging |Oracle PL/SQL Interview Questions for TCS,Wipro,Infosys,IBM,and Accenture

You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it so that you can refer the same file after executing the code this here serves you as a log file for debugging.

For Ex:

UTL_FILE.PUT_LINE(v_filehandle p_string_in);

where p_string_in is dynamic SQL for each line.

Suppose if my dynamic SQL consists of following 3 lines.

------

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''

END;

------

then you will call UTL_FILE.PUT_LINE function 3 times

as follows:

UTL_FILE.PUT_LINE(v_filehandle 'BEGIN');

UTL_FILE.PUT_LINE(v_filehandle 'EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''');

UTL_FILE.PUT_LINE(v_filehandle 'END');

What is the best technique of debugging large and fragmented dynamic SQL?

The biggest challenge in debugging dynamic sql is to re-construct the actual query that had been sent to the sql parser at run time. The complexity increase as the size and number of fragmentation of the query increases.

As per my experience to debug dynamic sql the best method is to store the sql string in a table before execution. If the dynamic sql size is small then a simple varchar2 data type of the column storing the sql string is fine. But if the query size is quite big i.e. number of characters exceeding 2000 then the best method is by storing it in a column of debug table having CLOB data type of the field storing the sql string.

Personally I feel the CLOB method is a better choice since it would work under all circumstances.

Oracle PL/SQL Interview Questions | Oracle Database Links

What is the purpose of database links in Oracle?

Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information . They are also useful when you want to copy production data into test environment for testing.

Oralce PL/SQL Interview Questions

PL-SQL Interview Questions with Answers

1. Describe the difference between a procedure, function and anonymous pl/sql block.
Level: Low
Expected answer : Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.

2. What is a mutating table error and how can you get around it?
Level: Intermediate
Expected answer: This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Level: Low
Expected answer: %ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.

4. What packages (if any) has Oracle provided for use by developers?
Expected answer: Oracle provides the DBMS_ series of packages. There are many
which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION,
DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.

5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a
binary integer. They can be used to hold values for use in later queries
or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.

6. When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.

7. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the NOTFOUND cursor variable in the exit when statement? Why?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.

8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.

10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can
also be used.

11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of
combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and
ALL key words:
BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.