Oracle Advance PL/SQL Questions and Answers

Difference between 11i and R12

R12	11i
4C’s (Currency, calendar, char of accounts & conversions accounts)	3C (Currency, calendar, char of accounts)
SLA sub ledger accounting	NO
Multi Org access controls	NO
ledgers and ledger sets	set of books
Suppliers moved to TCA	Supplier defined in AP/PO
Supplier Sites as TCA party site for each distinct address	NO
Date base version is 10g Release 2	9i release 2
Application tier Build on fusion middleware (10g web server and 10g forms and reports)	Application tier consists of developer 6i (forms and reports)

TCA?
Trading Community Architecture.  It allows us to manage complex information about customers. This is a party customer relationship and stored in HZ tables.

1. What is the difference between REF Cursor & Normal Cursor in oracle?

REF cursor is typically used to return record set or a cursor from stored procedure. REF Cursor is basically a data type. It is normally declared as type r_cursor is REF CURSOR; REF cursor supports dynamic change of query.

Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time.

2. Pragma Autonomous Transaction

The child code runs independently of its parent.

The child code can commit or rollback & parent resume.

The Parent Code can continue without affecting chid work.

3. What is the difference between %ROWTYPE and %TYPE and what is the main advantage to using these?

%ROWTYPE works on a full object whereas %TYPE works on a single column. 

4. What is an overloaded procedure?

It allows to reuse the same procedure name  for different subprogram inside a plsql block by varying the parameter datatype. 

example:

DECLARE

PROCEDURE callit (anumber INTEGER) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Invoked number callit');

END callit;

 PROCEDURE callit (acharacter VARCHAR2) IS

 BEGIN

 DBMS_OUTPUT.PUT_LINE('Invoked character callit');

 END callit;

BEGIN

callit(1);

callit('1');

END;

Invoked number callit

Invoked character callit

PL/SQL procedure successfully completed.

5. What is difference between TRUNCATE & DELETE ?

Truncate is DDL statement and can not be rollback 

delete is DML stmt and can be rollback if required.

6. What is difference between SUBSTR and INSTR

    

      SUBSTR returns a specified portion of a string

      eg  SUBSTR('BCDEF',4)       output  BCDE

          INSTR  provides character position in which a pattern

      is found in a string.

      eg  INSTR('ABC-DC-F','-',2)    output   7 (2nd occurence of ‘-')

7.What is difference between DECODE and TRANSLATE ?

Translate: translate function replaces a sequence of characters in a string with another set of characters.

For example:

Translate('1tech23', '123', '456); would return '4tech56'

Translate('222tech, '2ec', '3it'); would return '333tith'

Decode: The DECODE function compares one expression to one or more other expressions and, when the base expression is equal to a search expression, it returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified

8.What is use of a cursor variable? How it is defined.

A cursor variable is associated with different statements at run time, which can hold

different values at run time. Static cursors can only be associated with  one run time

query. A cursor variable is reference type(like a pointer in C).

Declaring a cursor variable:

TYPE  type_name  IS REF CURSOR RETURN  return_type  type_name is the name of

the reference type,return_type is a record type  indicating the types of the select list

that will eventually be returned by the cursor variable.

9. B Tree and Bit map:

Bitmap indexes are widely used in data warehousing environments.

it is used for less no.of data entries for a given column in a table. example gender, marital status 

B tree:

its the default index when we use indexes in oracle. 

A type of index that uses a balanced tree structure for efficient record  retrieval. B-tree indexes store key data in ascending or descending order.

10. View and Materialized view?

View - getting the query info from multiple tables.

Materialized view- A materialized view is a database object that contains the results of a query.

11. for all and for 

FOR is an actual loop which will go through records one by one and do some processing.

FORALL is NOT an actual loop, it's just a notation for a bulk DML operation. It will NOT go through rows one by one. For example, you can do some row processing in a FOR loop, but you won't be able to do it in FORALL.

12. Bulk Collect and Bulk Binding

Bulk binding is a generic term for any PL/SQL . it make use of collection in order to improve performance.

Bulk collect  is one particular type of bulk binding that is used to quickly populate the SELECT statement using bulk collect.

13. case and decode 

Decode can be used only in SQL queries 

Case can be use in PL/SQL and SQL.

14. in and exit 

exit will come out and return the value

in wil keep on iterating till it fetch completely 

15.Key Join Conditions for P2P?

PO req and PO

PO_REQ_DISTRIBUTIONS_ALL (req_sistribution_id) = PO_DISTRIBUTIONS_ALL (distribution_id)

PO AND SHIPMENTS

PO_HEADERS_ALL(po_header_id) = RCV_TRANSACTIONS(po_header_id)

SHIPMENTS AND INVOICE

RCV_TRANSACTIONS(rcv_transaction_id)=  AP_INVOICE_DISTRIBUTIONS_ALL(rcv_transaction_id

SHIPMENT AND INVENTORY

RCV_TRANSACTIONS(organization_id)= MTL_SYSTEM_ITEMS_B (organization_id)

PO AND AP

PO_DISTRIBUTIONS_ALL(po_distribution_id)= AP_INVOICE_DISTRIBUTIONS_ALL(po_distribution_id)

PO AND INV

PO_REQUISITION_LINES_ALL(item_id) = MTL_SYSTEM_ITEM_B(inventory_item_id)

org_id = Organization_id