SQL Questions

1)What are the key differences between a corelated query and sub query?

In the Corelated query for every instance of outer query,inner query is executed and based on the result of the inner query outer query displays the records. In the sub query depending upon the inner query the outer query will be executed.

2)What is a dead lock?

If two or more users are accessing the same data source with different operations then all the user resources are in dead lock.

3)What are indexes ? Name the various types of Indexes available along with the usage (i.e where and which type of index has to be used?)

Index provides direct access to the data.
The Various types of indexes are Btree Index,Function-Based Index,Bitmap Index,Domain Index

4)What are Autonomous transaction ?

Autonomous is an independent transaction which has to be commited or rollbacked before passing the control to the parent transaction

5)What is mutation error and how to overcome this?

The mutation error is occurred when the row-level trigger access the same table while executing

6)What is a REF cursor?

REF cursor is used to return Record set to be returned from stored procedures,functions and packages.
ref cursor is a simple cursor but it is a datatype that allow developers to declare cursor variable

ref cursor has 2 types:-

strong ref cursor where we mention the return type with rowtype.
weak cursor where we don't mention return type.the advantage of this is that we can use weak cursor with any query.it is not rowtype bounded.
for ex:-strong ref cursor
type curvar_type is ref cursor return emp%rowtype;
weak cursor:-
type curvar_type is ref cursor is


7)What is a materialized view? Where should this be used and what are the advantages of using this?

Materialized views can hold data.They are used to update the remote data base.

8)What is a bulk collect?

Bulk collect facilitates high-speed retrieval of data.Bulk collect is used to fetch multiple rows into one or more collections,rather than individual variables or records.

9)What is dynamic SQL? How does a dynamic sql get fired?

Dynamic SQL is one which is executed at run time.

10)What is rowchaining?

Row Chaining is the storage of data in a chain of blocks.This primarily occurs in lob,clob,blob or big varchar2 data types.

11)what a table is dropped, what are the associated objects that get invalidated / dropped?

Objects which get invalid are Views,Synonyms. Objects which get droped are triggers,indexes,constaints

12) Difference between POST-TEXT-ITEM and WHEN-VALIDATE-ITEM?

Post-text-item will fire every time your cursor moves out of the field. It doesn't matter whether a value is newly entered or is being queried from database. This will fire in both cases.

When-validate-item will fire only when you have entered new value or you change an exixting value of an item. This means that you this will fire only when the status is CHANGED or NEW.


13) This Question is for diff b/w POST-TEXT-ITEM and WHEN-VALIDATE-ITEM?
Suppose you have two fields 'A' and 'B'
A will Have initial value --> 100
Now u want go to change the Value of A to ---> 200 (WVI will be fire)It will not give any error
And in When Validate Item u give a condition ---> If a<500
And u r assigning the value to 'A' in POST-TEXT-ITEM --> now the POST-TEXT-ITEm will fire and the value of 'A' should be change to 700.

Now the Question is WHEN-VALIDATE-ITEM will raise error or not?
Yes, It will fire and it will give an error message. for when validate item there is no need to change the value if it is change as internally then also WHEN-VALIDATE-ITEM will fire.

Question: Why are PL/SQL ref cursors important to PL/SQL performance?

Answer: The ref cursor is a "pointer" data types that allows you to quickly reference any cursor result (usually an internal PL/SQL table array) with data values kept in super-fast RAM.

Definition of a ref cursor
The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor. In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer. Here is a simple example of using a ref cursor.

Once you load a PL/SQL table (an in-RAM array), a ref cursor allows you to reference the array within your PL/SQL code without having to pass the array to a new procedure or function. Instead of passing the "baggage" of the actual RAM array, you simple pass the ref cursor, which points to the array values.