Oracle 10g : Top 10 Oracle PL/SQL Performance Tips

1. Avoid NOT EQUAL Operators ‘<>’ and ‘!=”

When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.

2. Avoid ‘IS NULL’ and ‘IS NOT NULL’

The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)

3. Avoid FUNCTIONS in SQL Where Clause

The optimizer will not use an index when a function is used on an indexed column.

4. Comparing datatypes that are not similar

The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).

5. Use the EXISTS function when possible

Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.

6. Use BIND variables

With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.

7. Where are you referencing form fields?

Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.

8. Utilize the FORALL statement rather than a FOR LOOP.

When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.

9. Utilize the BULK COLLECT statement

BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.

10. Using NOCOPY

The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.


Richard J. Niemiec, Oracle Performance Tuning, Osborne/McGraw-Hill Book Co., 1999

Scott Urman, Oracle8i Advanced PL/SQL Programming, Osborne/McGraw-Hill Book Co., 2000

How to Compile Invalid Objects on APPS Schema For Packages & API

How to Compile Invalid Objects on APPS Schema For Packages and APIs-


This is an example for OZF – Trade Management Schema

cursor invalid_objects is
select object_name, object_type
from user_objects
where object_type in ('PACKAGE', 'PACKAGE BODY')
and status = 'INVALID'
and object_name like 'OZF%';
for rec in invalid_objects
DBMS_DDL.ALTER_COMPILE (rec.object_type ,'OZF' ,rec.object_name);
end loop;

Oracle Order to Cash Queries

Query to Join OM and requisition Interface table for Back 2 back Order

select l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,



oe_order_lines_all l,

po_requisitions_interface_all pr

where l.line_id = pr.interface_source_line_id

and pr.interface_source_code=&apos;CTO&apos;

Query to Join OM and Purchase Order tables for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id


mtl_reservations a,

oe_order_lines_all l,

po_headers_all ph

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = ph.po_header_id

Query to Join OM and PO Requisition table for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id


mtl_reservations a,

oe_order_lines_all l,

po_requisition_headers_all pqh

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = pqh.requisition_header_id

Query to Join OM , WSH and AR table

SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,


a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,



ra_customer_trx_all a,

ra_customer_trx_lines_all b,

oe_order_headers_all h,

oe_order_lines_all l,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

where a.customer_trx_id = b.customer_trx_id

and a.interface_header_context = &apos;ORDER ENTRY&apos;

and b.interface_line_attribute1 = to_char(h.order_number)

and h.header_id = l.header_id

and to_char(l.line_id) = b.interface_line_attribute6

and l.line_id = wdd.source_line_id

and wdd.delivery_detail_id = wda.delivery_detail_id

and wda.delivery_id = wnd.delivery_id

and to_char(wnd.delivery_id) = b.interface_line_attribute3

Mapping Between AR and OM (Transaction Flex field)









via Oracle Technologies.: Oracle Order to Cash Queries.