Package Compilation takes long time ...

Oracle will lock the package exclusively when the user attempt to compile the package. Incase if the package is used by other process, the compilation hangs..

Resolution:

1. Identify the package which is locked

SELECT * FROM DBA_DDL_LOCKS WHERE name = 'package name';

2. Get the sid

select * from v$access where object='package name';

3. Get the serial#

select sid,serial# from v$session where sid=;

4. Kill the session
alter system kill session 'sid,serial#';

Script to exclude the control M(^M) from unix files

Script to exclude the control M(^M) from unix files
The simple way to replace the control M character in UNIX format is as follows.

1. dos2unix filename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g

How to get Sales Order information - Total- freights charges

SELECT oola.line_number "LINE NUMBER", oola.ordered_item "ORDERED ITEM",
oola.ordered_quantity "QTY", oola.order_quantity_uom "UOM",
oola.unit_selling_price "UNIT SELLING PRICE",
oola.cancelled_quantity "QTY CANCELLED",
oola.shipped_quantity "QTY SHIPPED", oola.tax_code "TAX CODE",
ott.NAME "LINE TYPE",
DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
) "LINE_CHARGES",
ol.meaning "CALCULATE PRICE FLAG", oola.pricing_quantity,
oola.unit_selling_price, oola.unit_list_price, oola.tax_value,(oola.shipped_quantity) * (oola.unit_selling_price) "LINE TOTAL",
((oola.shipped_quantity) * (oola.unit_selling_price)
)
+ (DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
)
) "ORDER TOTAL"
FROM apps.oe_order_lines_all oola,
apps.oe_transaction_types_tl ott,
apps.oe_price_adjustments opa,
apps.oe_order_headers_all ooha,
apps.oe_lookups ol
WHERE oola.line_type_id = ott.transaction_type_id
AND opa.header_id = ooha.header_id
AND opa.line_id = oola.line_id(+)
AND opa.list_line_type_code = 'FREIGHT_CHARGE'
AND opa.applied_flag = 'Y'
AND ott.LANGUAGE = USERENV ('LANG')
AND ooha.order_number = 1004781
AND ol.lookup_type = 'CALCULATE_PRICE_FLAG'
AND oola.calculate_price_flag = ol.lookup_code

JBO-27122: SQL error during statement preparation

Today Morning, when i tried to run one of my OA page in R12, get into this strange error message : JBO-27122: SQL error during statement preparation.

The sql statement was perfect, just copied the sql statement into the sql developer it works great. Based on my further research found that its bug in the oracle JDeveloper 10.1.3.3.0.

Problem : JBO-27122: SQL error during statement preparation
2
Resolution: Please refer the metalink note: Note:413797.1 for this fix.

Oracle SQL Developer problem - enter / backspace / delete button not working

I extensevily use Oracle SQL Developer, one fine morning the enter key, backspace and delete button inside the SQL query editor stopped working all of a sudden.

I tried restarting the Oracle SQL Developer, even restarting the computer, but the problem persisted.

so I just went and searched in oracle forums and found the simple solution

To resolve the problem you have to go to:
Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK