1) What are the different types of manufacturing processes? And what are the primary differences between them? OR Explain Process Manufacturing v/s Discrete manufacturing?
Discrete Manufacturing is the production of distinct items that use bills of material and routings to determine costs and lead times. Eg: Car manufacturing, Computer Manufacturing etc you get all the parts and assemble them.
OPM on the other hand caters to capabilities to allow for multiple units of measure because the flexibility in batch production is required in process industries. e.g Chemical Industries, food processing etc. This involves complex internal processes and need a high level of control.
Eg: You mix x kgs of sugar and n litres of milk, you get a sweet as the output. This can be in kilos, litres or smaller units. If you do not allow the water to evaporate the output will be a liquid, if you heat longer the output will be a burfi.
What is the difference between the two?
1)ODM uses BOM (Bill of Materials), OPM uses formulas and Recipes
2)UOM will vary depending on the material used in OPM but in ODM UOM will be each or unit
3)ODM assembles or builds things, OPM mixes , blends and transforms or converts.
4)OPM is driven by item attributes and ODM is driven by part number
Oracle Process Manufacturing –
It the entire product lifecycle for recipe-based manufacturing, from new product development, recipe
management and production, to cost, quality, and regulatory management. It enables you to formulate products to individual customer specifications, manage variability, optimize capacity, and drive continuous process improvement
2) What are the modules that come under oracle’s OPM solution?
Answer: OPM includes OPM Process Planning, Product Development(which includes Formula,Recipe,Quality), Production, Financials(Costing,MAC), Logistics, Regulatory Management etc. These are products which come under this umbrella..
· OPM Cost Management
· OPM Formula Management
· OPM Intelligence
· OPM Inventory Management
· OPM Laboratory Management
· OPM Master Production Scheduling
· OPM Material Requirements Planning
· OPM Production Management
· OPM Purchasing Management
· OPM Quality Management
· OPM Capacity
· OPM Sales Management
· Oracle Financial
3) Name and explain frequently used terms in Oracle Process Manufacturing?
Answer:
4) Name some important tables used in OPM? ORWhich tables stores the formula information?
Answer:
1) select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,decode(b.line_type,-1,’Ingredient’,’Product’) Type
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
2) Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,’Ingredient’,’Product’) Type,
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
5) Explain what do you mean by Formula and Recipe?
Formula is Ingredients and their proportions
Receipe is Formula + Routing.
6) What are different kinds of losses?
Fixed loss and Variable loss.
Q. Once an item is assigned to an organization, is it possible to remove this association at a later time?
A. If you have assigned an item to an organization, it can only be deleted from that organization if the item has NOT been transacted. If it has been transacted against you cannot delete the item from the organization. You can, however, make the status of the item “inactive” on the main region of the Item Define form INVIDITM in ‘Item Status’ field. This will prevent users from transacting against the item although the item will continue to be included in LOV’s.
Q. How do you setup Unit of Measure for an item?
A. When transacting an item, it will always default to the defined Primary Unit of Measure, so if you need to use another UOM, you must select it from the List of Values when you transact it. This implies that you want the Primary Unit of Measure to be that which is used most often and which is likely to be the smallest unit of that type of item. Once you have set the primary UOM for an item, it CANNOT be changed.
Q. How do I change an item’s locator control method once I have transacted against it, for example from No Control to Prespecified?
A. You cannot change this attribute if there is onhand quantity for the item. You must first perform a miscellaneous issue of all quantity. Then the attribute can be changed and once the locator(s) are set up, you can perform a miscellaneous receipt to receive the quantity back into a specified subinventory/locator.
Q. How do I change an item’s Unit of Measure?
A. You CANNOT change the UOM for an item once the UOM is assigned to the item. The suggested solution is as follows: If the item has not been transacted you can delete the item and then re-add it with the correct UOM. If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.
1. Rename the existing item ’123′ to ’123-Bad UOM’ and save the item. Any prior history for item ’123′ will now be associated with item ’123-Bad UOM’.
2. Add a new item ’123′ with the correct UOM.
Q. How do I delete an Item Template I no longer use?
A. Suggested steps to follow to delete unwanted Item Templates:
1. Navigate Setup: Items: Templates – Find Item Templates form appears.
2. Click on the LOV, and select the Item Template that is to be removed.
3. Click the [Find] button on the Find Item Templates form. The Item Templates Summary form appears, with the Item Template selected.
4. Click the [Open] button on the Item Templates Summary form, and the Item Templates form appears.
5. From the menu, select Edit | Delete Record, then select Action | Save and Proceed. 6. Close the Item Templates Summary form. Be careful not to delete Item Templates you may need again as they are not retrievable once deleted.
Q. Is there a way to create custom item attributes?
A. No but you can create and define the descriptive flexfield for this form. This will allows you to associate additional data with an item such as an attribute. See the Oracle System Administrators
Users Guide and the Oracle Flexfields Manual for information on creating Descriptive Flexfields.
Q. Why does the application prompt you for Organization when you enter the Master Item form for the very first time even though you have only one Master Organization.
A. Once you setup a Multi-Org environment and you enter the form for the first time, there is the possibility there can be more than one Master Organization. The application
therefore prompts you for an organization which is then used to determine the Master Organization associated with it. In other words, you are choosing the Master Organization by choosing one of the Orgs that belongs to it.
Q. How do you change the control level for item attributes from master level to organization level for example. When I try to change this in the Attribute Control Form, I get the message: frm-40200: field protected against update.
A. First change value of the field called “status” to “not used”. You should then be able to change the control level of the attribute from master level to org level. Make sure and change to status back to what it was before the change and save the record
Q. How do I inactivate an item?
A. On the Master Item Form (Inventory->Items->Master Items), change the item status field to “Inactive” or any another defined status which disables all attributes that make an item usable (stockable, shippable, orderable, etc.). The “Inactivate” status comes pre-seeded with the Application and is for this purpose.
Q. What characters can I use in an item name (including special characters)?
A. The only hard fast rule is not to use the character designated in the segment delimiter for the System Item Flexfield. Also, you will not be able to enter any characters not allowed by the valueset associate with the flexfield. It is recommended that you avoid the use of any char that can be used as a delimiter to avoid future problems. It is best to not use special characters like #@%& if at all possible as these may have a negative impact down the road. Some characters, like percent (%), underscore (_), and pound(#), have special meaning when performing queries by form as do the query operator characters (=!><).
Q. Can you change the costing enabled and inventory asset flags on an item that has already been transacted against even if there is no on-hand quantities for the item?
A. Yes, you can change the flags. If you are changing the flags from checked to unchecked you will receive a warning that the associated cost records will be deleted alerting you that the Cost of Goods will not be calculated any longer for transactions of this item. In effect what you are doing is changing the item from an asset item to an expense item. If you are changing the flags from being unchecked to checked then a database
trigger will fire and cause cost records to be added for this item. There is no warning that is given.
Q. When I create a new item, and choose to assign to All orgs, the application allows me to even though some of those organizations are restricted to my responsibility. Is this the way it is supposed to work? If I assign in an Organization by Organization fashion, I am not allowed to assign to the restricted organizations.
A. The situation described is standard functionality. Even if a responsibility has limited access to a certain number of organizations, the “Assign to All Organizations” check box will assign the item to all orgs, not just the ones limited to the user’s responsibility.
Q. How do you tell that an item is an expense item or asset item?
A. Navigate to the Organization Items form: Inventory->Items->Organization Items. Query up the item in question. Change to the “Costing” alternative region. The value of the “Inventory Asset Value” flag determines this. If checked, the item is an asset item.
Q. Is there a way to delete or disable an item revision once entered?
A. There is no way to delete a revision nor is there a way to disable it. It must remain there for historical data. The suggested workaround is to create a new item, transfer any Quantity on-hand from the old item to new item and delete the old item. The only down side is you will lose some transaction history
Query to find Pegging Details in ASCP
MSI1.ITEM_NAME||’ (‘||MSI1.INVENTORY_ITEM_ID||’)’ “COMPONENT (ID)”
, substr( md1.order_number,1,22) order_num
, MS1.NEW_ORDER_QUANTITY COMP_QTY — this is the pegged or previously pegged column
, decode (ms1.order_type,’1′,’Purchase order’
,’11’,’Intransit shipment’
,’12’,’Intransit receipt’
,’13’,’Suggested repetitive schedule’
,’14’,’Discrete job co-product/by-product’
,’15’,’Nonstandard job by-product’
,’16’,’Repetitive schedule by-product’
,’17’,’Planned order co-product/by-product’
,’18’,’On Hand’
,’2′,’Purchase requisition’
,’27’,’Flow schedule’
,’28’,’Flow schedule by-product’
,’29’,’Payback Supply’
,’3′,’Discrete job’
,’30’,’Current repetitive schedule’
,’32’,’Returns’
,’4′,’Suggested aggregate repetitive schedule’
,’41’,’User Supply’
,’45’,’Demand Class Consumption’
,’46’,’Supply Due To Stealing’
,’47’,’Demand Due To Stealing’
,’48’,’Supply Adjustment’
,’49’,’PO Acknowledgment’
,’5′,’Planned order’
,’50’,’ATP Aggregate Supply’
,’51’,’Planned inbound shipment’
,’52’,’Requested inbound shipment’
,’53’,’Internal requisition’
,’60’,’Order Rescheduling Adjustment’
,’7′,’Non-standard job’
,’70’,’Maintenance Work Order’
,’8′,’PO in receiving’) Order_type
— , decode (MFP1.supply_type, 1, ‘Purchase Order’, 2, ‘Purchase Requisition’, 3, ‘Work Order’, 11, ‘Intransit Shipment’, 12, ‘Intransit receipt’) Supply_type
— ,decode (MFP2.supply_type, 1, ‘Purchase Order’, 2, ‘Purchase Requisition’, 3, ‘Work Order’, 11, ‘Intransit Shipment’, 12, ‘Intransit receipt’) Supply_type2
, MFP1.demand_quantity
–, MFP1.supply_quantity
, MFP1.demand_date
, MFP1.supply_date
, ROUND(MFP1.ALLOCATED_QUANTITY,1) COMP_ALLOC
, decode(MD1.origination_type, ’29’,’Peg_Fcast’,’30’,’Peg_Sales’) Pegged_To
, decode (MFP1.DEMAND_ID, -1,’peg_Excess’,-2,’Peg_SS’) Pegged_To_1
, MFP2.ORGANIZATION_ID
,MFP1.ORGANIZATION_ID
, decode(MD2.origination_type, ’29’,’Peg_Fcast’,’30’,’Peg_Sales’) Pegged_To_2
, TRUNC(MS1.NEW_SCHEDULE_DATE) COMP_SUP_DUE
, ‘X’
, DECODE(MD2.ORDER_NUMBER,NULL
,TO_CHAR(MD2.DEMAND_ID)
,(SUBSTR(MD2.ORDER_NUMBER,1,14)||SUBSTR(MD2.ORDER_NUMBER,42,5))) SO_NUM
, MSI2.ITEM_NAME||’ (‘||MSI2.INVENTORY_ITEM_ID||’)’ “ASSEMBLY (ID)”
— , MSI2.DESCRIPTION
, TO_CHAR(MD2.REQUEST_SHIP_DATE,’DD/MM/YYYY’) RDATE
, TRUNC(MD2.USING_ASSEMBLY_DEMAND_DATE) SSDATE
, MD1.Creation_date
, ROUND((MD2.DMD_SATISFIED_DATE-MD2.USING_ASSEMBLY_DEMAND_DATE),1) LATE
, MD2.DEMAND_PRIORITY PRIORITY
, ROUND(MFP2.DEMAND_QUANTITY,1) so_QTY
, ROUND(MFP2.ALLOCATED_QUANTITY,1) WIP_QTY
, TRUNC(MD1.OLD_DEMAND_DATE) ULSD
, TRUNC(MD1.USING_ASSEMBLY_DEMAND_DATE) COMP_DEM_DUE
FROM APPS.MSC_SUPPLIES MS1 — COMP
, APPS.MSC_SYSTEM_ITEMS MSI1 — COMP
, APPS.MSC_FULL_PEGGING MFP1 — COMP
, apps.msc_sales_orders mso
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD1 — COMP
, (SELECT *
FROM APPS.MSC_FULL_PEGGING
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MFP2 — FG
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD2 — FG
, (SELECT *
FROM APPS.MSC_SYSTEM_ITEMS
WHERE SR_INSTANCE_ID = &1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MSI2 — FG LEVEL
WHERE 1=1
AND MS1.SR_INSTANCE_ID =&1
AND MS1.ORGANIZATION_ID =&2
AND MS1.PLAN_ID =&3
and mso.demand_id=md1.demand_id
AND MSI1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MSI1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MSI1.PLAN_ID = MS1.PLAN_ID
AND MSI1.INVENTORY_ITEM_ID = MS1.INVENTORY_ITEM_ID
AND MFP1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MFP1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MFP1.PLAN_ID = MS1.PLAN_ID
AND MFP1.TRANSACTION_ID = MS1.TRANSACTION_ID
AND MD1.DEMAND_ID (+) = MFP1.DEMAND_ID
AND MFP2.PEGGING_ID (+) = MFP1.PREV_PEGGING_ID
AND MD2.DEMAND_ID (+) = MFP2.DEMAND_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MFP2.INVENTORY_ITEM_ID
AND MSI1.ITEM_NAME = ‘item’
and md1.order_number=’ord’
ORDER BY MS1.OLD_SCHEDULE_DATE, MD2.REQUEST_SHIP_DATE
Share this:
Oracle Application OPM Interview Questions R12
OPM on the other hand caters to capabilities to allow for multiple units of measure because the flexibility in batch production is required in process industries. e.g Chemical Industries, food processing etc. This involves complex internal processes and need a high level of control.
Eg: You mix x kgs of sugar and n litres of milk, you get a sweet as the output. This can be in kilos, litres or smaller units. If you do not allow the water to evaporate the output will be a liquid, if you heat longer the output will be a burfi.
What is the difference between the two?
1)ODM uses BOM (Bill of Materials), OPM uses formulas and Recipes
2)UOM will vary depending on the material used in OPM but in ODM UOM will be each or unit
3)ODM assembles or builds things, OPM mixes , blends and transforms or converts.
4)OPM is driven by item attributes and ODM is driven by part number
Share this:
R12 – How to use Business Events in Oracle Workflow
Step 4) Create a event as follows
Share this:
Methods to Calculate Actual Cost in Oracle Apps
1. Period Weighted Average Cost (PWAC)
This is the strict average cost of the raw material during the period, based on the total estimated receipt (or invoiced) price for the entire inventory quantity. The period weighted average cost is a strict average cost for the period based on Period Total Quantity and Estimated or Final Prices.
PWAC is calculated by dividing — the sum of the transaction quantity multiplied by price — by the sum of transaction quantity, as shown in the following illustration:
the picture is described in the document text
Where:
Trans Qty – Receipt Quantities or AP interfaced quantities within the costing period
Price – Receipt estimated prices or AP invoice final prices within the costing period
2. Period Moving Average Cost (PMAC)
OPM calculates the average cost for the period while moving previous period’s cost with last period’s inventory balance and cost:
PMAC is calculated by dividing the result of — the quantity of the prior period inventory balance multiplied by the prior period cost, plus the sum of the transaction quantity multiplied by price — by the prior period inventory balance plus the sum of transaction quantity, as shown in the following illustration.
Where:
Prior Period Inv Balance – This is the prior period inventory balance captured from the inventory period ending balances.
Prior Period Cost – The prior period actual cost component from the cost component details table.
Trans Qty – Receipt Transaction Quantities or AP Interfaced Quantities within the costing period.
Price – Receipt estimated prices or AP invoice final prices within the costing period.
the picture is described in the document text
3. Perpetual Weighted Average Cost (PPAC)
The perpetual weighted average cost type computes the average cost for the entered receipts and quantities within the defined boundaries of the cost calendar. The calendar definition may in turn be identical to a fiscal year, or may span multiple fiscal years providing the flexibility of a variety of Perpetual Weighted Average cost methods.
PPAC is calculated by dividing — the sum of the transaction quantity multiplied by price — by the sum of transaction quantity, as shown in the following illustration:
the picture is described in the document text
Where:
Trans Qty – Receipt Quantities or AP interfaced quantities from the start of the costing calendar to the end of the current period.
Price – Receipt estimated prices or AP invoice final prices within the costing calendar.
Last Transaction Cost
There are two methods for determining last actual cost of a raw material:
LSTT – This method uses the last transaction within the costing period, regardless of whether the transaction is a receipt or an Accounts Payable invoice.
LSTI – This method uses the last Accounts Payable Invoice transaction within the costing period, even if there are latest receipts with estimated prices. In the absence of AP invoice transactions the latest receipt will be considered for the actual cost.
Last transaction cost adjustments will superseded any other transaction for the actual cost. For both methods, the adjustment unit cost is the actual cost.
Last Transaction (LSST) – OPM uses the last transaction in the costing period as the basis for the raw material cost (if there is no Accounts Payable invoiced cost for the period, the last receipt price is used to cost the raw material).
Last Invoice Transaction (LSTI) – OPM uses the last Accounts Payable invoice transaction in the costing period as the basis for the raw material cost, even if there are raw material receipt transactions that occur later in the period. If there are no Accounts Payable invoiced costs for the period, the last receipt price is then used to cost the raw material. Actual cost adjustments supersede any of the methods used to calculate actual cost – an adjusted cost is the actual cost.
Share this:
Re-Compile Invalid Schema Objects in Oracle Database
Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query.
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations.
EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘MY_SCHEMA’, ‘MY_PROCEDURE’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);
This method is limited to PL/SQL objects, so it is not applicable for views.
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, ‘PACKAGE’, 1,
‘PACKAGE BODY’, 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
AND status != ‘VALID’
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = ‘PACKAGE’ THEN
EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
ElSE
EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
‘ : ‘ || cur_rec.object_name);
END;
END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.
EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);
UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.
PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below.
schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags – Used for internal diagnostics and testing only.
The following examples show how these procedures are used.
— Schema level.
EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);
EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);
— Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
— Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);
There are a number of restrictions associated with the use of this package including:
Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Running DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.
0 – The level of parallelism is derived based on the CPU_COUNT parameter.
1 – The recompilation is run serially, one object at a time.
N – The recompilation is run in parallel with “N” number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
For more information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
Share this: