dbms_utility.format_error_stack in Oracle 10g

In Oracle database 10g, Oracle added format_error_backtrace which can and should be called from exception handler. It displays the call stack at the point where exception was raised.
 Let's see what happen when exception handled using dbms_utility.format_error_stack procedure P3.

scott@10gR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

scott@10gR2> create or replace procedure p3 as
  2  begin
  3     p2;
  4  exception
  5             when others then
  6             dbms_output.put_line (' calling format error stack from P3 ');
  7             dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
  8  end;
  9  /

Procedure created.


And now when I run the procedure P3, I will see the following output.


scott@10gR2> exec p3;
raising error at p1
 calling format error stack from P3
ORA-06512: at "scott.P1", line 4
ORA-06512: at "scott.P2", line 3
ORA-06512: at "scott.P3", line 3

PL/SQL procedure successfully completed.


The information that had previously been available only through an unhandled exception is now retrievable from within the PL/SQL code.

Payment term setup in Order Management

Set up a payment term in for Payment Due with Order. Payment terms that have one or more installments with Days Due set to zero will be used to identify the Payment Due with Order order lines.

Steps

1. As Order Management Super User, navigate to Setup, Orders, Payment Terms.
     Below is the setup screen.
   
   
   
   
   
   
   
   
2. Enter a name for the payment term (e.g., Pay Now).
3. In order for the payment term to be a "pay now" payment term, ensure that no installment is allowed. You specify this by entering 0 (zero) in the Days Due field in the Payment Schedule region).

Forced Replacement of Types in 11g release 2

If you have used types, you must have realized how powerful they can be. You can define your own data type that can be a composite of various other data types, or they can be records to group related pieces of data together, even to match a complete table row. Here is an example of a type called TY_TRANS that defines the elements of a transaction:

create or replace type ty_trans
as object
(
    trans_id    number(2),
    trans_amt   number(10)
)
/

Next, you can a type to hold sales information. Since every sale will have a transaction, you can define a column of type ty_trans, shown below:

create or replace type ty_sales
as object
(
    sales_id    number(2),
    trans_rec   ty_trans
)
/

Once you define the structures this way, TY_SALES becomes a dependent of TY_TRANS. You can confirm that by querying USER_DEPENDENCIES view:

SQL> select referenced_name, dependency_type
  2  from user_dependencies
  3  where name = 'TY_SALES'
  4  /

REFERENCED_NAME       DEPE
---------------------           ----
STANDARD              HARD
TY_TRANS              HARD

This shows that TY_SALES has a “hard” dependency on the type TY_TRANS.

Now, let’s look at a real world possibility. What if you made a mistake in defining the types and defined an attribute with a wrong precision or just want to change the precision keeping with the business needs? Well, not a problem – you simply use the CREATE OR REPLACE statement to recreate that type:

create or replace type ty_sales
as object
(
    sales_id    number(3),
    trans_rec   ty_trans
)
/

Here you recreated the type with the one of the attributes as number(3) instead of number(2), as it was previously. While this operation was successful for this type, what if you had to do the same for ty_trans?

SQL> create or replace type ty_trans
  2  as object
  3  (
  4      trans_id    number (4),
  5      trans_amt   number
  6  )
  7  /
create or replace type ty_trans
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

The error message says it all – you can’t alter this type since it has a dependent, as we saw earlier from the user_dependencies view. It’s sort of a parent-child relationship between the types. If there is at least one child, you can’t drop the parent. So, what are your options for changing the “parent” type definition?

Until Oracle Database 11g Release 2, the only option for modifying that type is the MODIFY ATTRIBUTE clause of ALTER TYPE statement, which is an expensive and potentially error prone proposition. In Release 2, there is a very convenient FORCE clause to replace the type forcibly. Using this, we can alter the type TY_TRANS as:

create or replace type ty_trans
force
as object
(
    trans_id    number (4),
    trans_amt   number
)
/

This will execute successfully and the type will be created, due to the FORCE clause shown in bold above. This makes it very convenient when you deploy applications – you don’t have to worry about which specific attributes have changed; rather, a full replace type takes care of the type definition, changed or not.

Difference between Drop Shipments and Back to back order

Drop Shipments is similar to this back-to-back process in that your sales order line creates a requisition line that becomes a PO sent to your supplier. In a drop shipment; however, you instruct your supplier to send the item or configured item directly to your customer. The items never physically pass through your warehouse, and therefore you do not pick, pack or ship them yourselves. In the back-to-back scenario, you instruct your supplier to send you the goods, and then you ship them on to your customer.

How the Order Management linked with pricing and sales credits

Relationship between orders, lines, order types, line types, and workflow processes

Restriction in FORALL Statements Removed in Oracle 11g

The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct. To see this in action, create and populates a test table using the following code.

CREATE TABLE forall_test (
  id          NUMBER,
  description VARCHAR2(50)
);

INSERT INTO forall_test VALUES (1, 'ONE');
INSERT INTO forall_test VALUES (2, 'TWO');
INSERT INTO forall_test VALUES (3, 'THREE');
INSERT INTO forall_test VALUES (4, 'FOUR');
INSERT INTO forall_test VALUES (5, 'FIVE');
COMMIT;

The PL/SQL block below populates a collection with the existing data, amends the data in the collection, then updates the table with the amended data. The final query displays the changed data in the table.

DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  l_tab t_forall_test_tab;
BEGIN
  -- Retrieve the existing data into a collection.
  SELECT *
  BULK COLLECT INTO l_tab
  FROM   forall_test;

  -- Alter the data in the collection.
  FOR i IN l_tab.first .. l_tab.last LOOP
    l_tab(i).description := 'Description for ' || i;
  END LOOP;

  -- Update the table using the collection.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test
    SET    description = l_tab(i).description
    WHERE  id          = l_tab(i).id;

  COMMIT;
END;
/

SELECT * FROM forall_test;

        ID DESCRIPTION
---------- ---------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5

5 rows selected.
 Notice both the SET and WHERE clauses contain references to individual columns in the collection. This makes using bulk-binds for DML even easier as we no longer need to maintain multiple collections if we need to reference columns in the WHERE clause. It can also improve performance of updates, as previous versions required updates of the whole row using the ROW keyword, which included potentially unnecessary updates of primary key and foreign key columns. 

SKIP LOCKED clause in Oracle 11g

Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked in any other active session of the database. This looks quite similar to exclusive mode of locking. The SQL statement in Example code (15) queries the unlocked records from EMP table

Example :

SELECT * FROM EMP FOR UPDATE SKIP LOCKED

Read Only Tables In Oracle 11g

 In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

Example 1:

SQL> ALTER TABLE EMP READ ONLY;

Example 2: 

SQL> ALTER TABLE EMP READ WRITE;

Multitable Inserts feature

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format.

-- Unconditional insert into ALL tables

INSERT ALL

  INTO sal_history VALUES(empid,hiredate,sal)

  INTO mgr_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Conditionally insert into ALL tables

INSERT ALL

  WHEN sal > 10000 THEN

    INTO sal_history VALUES(empid,hiredate,sal)

  WHEN mgr > 200 THEN

    INTO mgr_history VALUES(empid,mgr,sysdate)

  WHEN 1=1 THEN

    INTO full_history VALUES(empid,mgr,sysdate)

SELECT employee_id AS empid,

       hire_date AS hiredate,

       salary AS sal,

       manager_id AS mgr

FROM   employees

WHERE  employee_id > 200;

-- Insert into the FIRST table with a matching condition

INSERT FIRST

  WHEN sal > 25000  THEN

    INTO special_sal VALUES(deptid,sal)

  WHEN hiredate LIKE ('%') THEN

    INTO hiredate_history_00 VALUES(deptid,hiredate)

  WHEN hiredate LIKE ('%99%') THEN

    INTO hiredate_history_99 VALUES(deptid,hiredate)

  ELSE

    INTO hiredate_history_not_99 VALUES(deptid, hiredate)

SELECT department_id AS deptid,

       SUM(salary) AS sal,

       MAX(hire_date) AS hiredate

FROM   employees GROUP BY department_id;

The restrictions on multitable inserts are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

Back end table information for Processing Constraintsin Order Management

OE_PC_ASSIGNMENTS This table stores the responsibilities that are constrained by the constraint identified by constraint_id.

OE_PC_CONDITIONS This table stores the conditions associated with the constraints identified by constraint_id.

OE_PC_CONDITIONS_TL This table stores the translated conditions associated with the constraints identified by constraint_id.

OE_PC_CONSTRAINTS - This table stores the constraints that prevent create/update/delete of order entities.

OE_PC_EXCLUSIONS - This table stores the responsibilities that are not constrained by a particular constraint.

OE_PC_RSETS - This table store the constraint record sets and the constraint conditions can be built to validate against any or all the records in this record set.

OE_PC_RSETS_TL - OE_PC_RSETS_TL stores the translated columns for record sets for constraints.

OE_PC_RSET_SEL_COLS - This table stores columns to be matched for a constraint record set.

OE_PC_VALIDATION_PKGS - This table stores the names of the dynamically generated constraints validation packages.

OE_PC_VTMPLTS - This table stores the constraint validation templates that are used in constraint conditions.

OE_PC_VTMPLTS_TL - OE_PC_VTMPLTS_TL stores the translated columns for validation templates for constraints.

OE_PC_VTMPLT_COLS - This table stores the attribute validation pairs for validation templates that are based on column values.

Differences between 11i and R12

Workflow tables and its usage

WF_ITEM_TYPES:

The wf_item_types table contains one record for each item_type created. The eight character name of the item_type represents the “Internal Name” of the item. It also functions as the primary key for this table. Some key columns are:

§  NAME: It is a mandatory field. It represents the internal name of the item type.

§  PROTECT_LEVEL: Level at which the data is protected. A mandatory field.

§  CUSTOM_LEVEL: Level of user who last updated the row. Again a mandatory field.

§  WF_SELECTOR: It stores the name of the PL/SQL procedure which implements selector function. This is an optional field.

§  PERSISTENCE_TYPE: Indicates whether item type is temporary or permanent.

§  PERSISTENCE_DAYS: Number of days until purge if persistence is temporary.

Workflow Item Type Display Name and description can be found in WF_ITEM_TYPES _TL table. 

WF_ITEM_ATTRIBUTES:

This table stores definitions of attributes associated with a process. The entries in this table correspond to the “Attributes” subheading in the Workflow Builder. An item attribute works like a variable which can hold values that are specific to the process instance or which may change at run time. Some key columns are:

§  ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.

§  NAME: Internal name of the attribute. A mandatory field.

§  SEQUENCE: Order of the attribute within the message

§  TYPE: Each item attribute is assigned a datatype, such as “Character”, “Number”, or “Date”.

There are three fields to hold a default value, but only one of them will be populated for any item attribute, depending upon the datatype. For example, if you create an item attribute with a datatype of “Number”, and then supply a default value, that value would be stored in the “number_default” field.

The “format” field stores information about a format mask that should be applied to number or date values, and the “subtype” field contains “SEND” or “RECEIVE”. The Translation table is WF_ITEM_ATTRIBUTES_TL and the related view is WF_ITEM_ATTRIBUTES_VL.

WF_ACTIVITIES:

This table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub-process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, but it provides a means of grouping activities. Some key columns are:

§  ITEM_TYPE: Internal name for the Item Type that owns the message.

§  NAME: Internal name for the activity.

§  VERSION: It is used to support multiple versions of the same process running at the same time. The version number works in concert with the “begin_date” and “end_date” fields, to ensure that only one version of any activity is active at any given time. By versioning, the previously launched processes retain the process definition that was in force at the time they were launched.

§  TYPE: The “type” field is the way that the individual types of activities can be distinguished. There are five valid values found in the “type” field: “FUNCTION”, “NOTICE”, “EVENT”, “PROCESS”, and “FOLDER”.

§  RERUN: Determines if activity is rerun during looping.

§  EXPAND_ROLE: Determines how many roles are required to respond to a notification activity.

§  FUNCTION: For function activities only, the field is used to store the name of the PLSQL procedure that the Workflow Engine should call to implement the function.

§  RESULT_TYPE: If you intend to model transitions in a process based upon values returned by an activity node, then the expected results must be predefined by supplying a lookup type, which is stored in this field.

§  ICON_NAME: Name of activity icon used in process window.

§  MESSAGE: For notification activities only, the field called “message” will be populated. In these cases, it will contain the internal name of the message that the notification will deliver.

§  ERROR_PROCESS: Workflow process to run in case of an error.

§  ERROR_ITEM_TYPE: Name of item type to execute in case of error.

§  RUNNABLE_FLAG: Flag (Y or N) to indicate if activity is runnable.

§  FUNCTION_TYPE: Indicates whether function type is pl/sql or internal.

WF_ACTIVITY_ATTRIBUTES:

This table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities. Each row includes the associated activity, type of attribute, and the format used by the activity. Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2. Notice that the table requires three fields just to identify to which activity the attribute is attached: the item_type, name, and version of the activity. To join this table to the wf_activities tables you must join all three of these fields to their corresponding fields in that table. Some key columns are:

§  ACTIVITY_ITEM_TYPE: Item type the activity is associated with

§  ACTIVITY_NAME: Internal name of the activity

§  ACTIVITY_VERSION: Version of the activity

§  NAME: Internal name of the attribute

§  SEQUENCE: Order of the attribute within the message

§  TYPE: This field refers to the datatype of the values that the attribute will contain.

§  VALUE_TYPE: Defines if the default is a constant or a reference to an item attribute.

WF_ACTIVITY_ATTR_VALUES:

This table used to track values contained in activity attributes. This table is identical in purpose to wf_item_attribute_values except it holds values for activity attributes instead of item attributes. Each row includes the process activity id and the associated value for the attribute. The interesting thing about this table is that it uses the process_activity_id to identify the activity to which the attribute is attached. The same activity can be inserted into a process more than one time, so the only way to uniquely identify the node to which this attribute is attached is to use the process_activity_id.

WF_MESSAGES:

The messages that are associated with notifications are stored in this table. Each message, which is uniquely identified by the combination of item_type and message_name (stored in the fields “type” and “name”) receives a single record in the wf_messages table. The actual text of the message is stored only in its localization table (wf_messages_tl). They can found in the “body” and “html_body” fields.

WF_MESSAGE_ATTRIBUTES:

This table contains message attribute definitions. Each message may have zero or more message attributes. Message attributes define additional information that is to be sent to, or received from the user. These attributes can be used as tokens in the subject or body of a message template to place variables values into the message at runtime.

WF_PROCESS_ACTIVITIES:

A process is a sequence of activities performed in a pre-determined order. When you create a process definition in the Workflow Builder by dragging various notifications and functions into the process window, the records created by the Builder are stored into this table.

WF_ACTIVITY_TRANSITIONS:

The flow of a process from node to node as indicated by the transition arrows is not saved in the wf_process_activities table. Instead this information is stored in this table.

A transition is defined by three discrete pieces of information: the node where the arrow begins, the node toward which the arrow points, and the result which, when returned by the beginning node, causes the transition to be followed. Not surprisingly, it is those three fields which are the most important fields in this table: “from_process_activity”, “to_process_activity”, and “result_code”. The values stored in “from_process_activity” and “to_process_activity” are numbers which represent the instance_id of the records from wf_process_activities from which and to which the transition is moving.

WF_LOOKUP_TYPES_TL & WF_LOOKUPS_TL:

Wf_lookup_types_tl is the table used to set up the types of results expected from Workflow activities like functions and notifications. This table does not contain the actual result values, it holds the groupings of the result_codes – the names you see in the Workflow Builder as the names of the Lookups. Wf_lookups_tl is the table that stores the component values that comprise a lookup_type.

Overview of workflow background engine and it is statuses

The Workflow Engine manages all automated aspects of a workflow process for each item. The engine is implemented in server-side PL/SQL and is activated whenever a call to a workflow procedure or function is made. Since the engine is embedded inside the Oracle Database, if the Workflow server goes down for any reason, the Oracle Database is able to manage the recovery and transactional integrity of any workflow transactions that were running at the time of the failure.

Additionally, Workflow engines can be set up as background tasks to perform activities that are too costly to execute in real time.

The Workflow Engine performs the following services for a client application:

• It manages the state of all activities for an item, and in particular, determines which new activity to transition to whenever a prerequisite activity completes.
• It automatically executes function activities (execution is either immediate or deferred to a background engine) and sends notifications.
• It maintains a history of an activity's status.
• It detects error conditions and executes error processes.

Based on the result of a previous activity, the engine attempts to execute the next activity directly. An activity may have the following status:

• Active - activity is running.
• Complete - activity completed normally.
• Waiting - activity is waiting to run.
• Notified - notification activity is delivered and open.
• Deferred - activity is deferred.
• Error - activity completed with error.
• Suspended - activity is suspended.

Important: The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. For this reason, you should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.
For environments such as database triggers or distributed transactions that do not allow savepoints , the Workflow Engine automatically traps "Savepoint not allowed" errors and defers the execution of the activity to the background engine.
Oracle Workflow components that continue workflow processing asynchronously, such as background engines and the Notification System, do issue commits when appropriate on behalf of the calling application.

how to check the workflow status from back end in oracle applications

SELECT wat1.display_name "process name", wat.display_name "activity name",

       wa.FUNCTION "function",wa.*

  FROM wf_process_activities wpa,

       wf_activities_tl wat,

       wf_activities_tl wat1,

       wf_activities wa,

       wf_item_activity_statuses wias_h

WHERE wpa.activity_item_type = wa.item_type

   AND wpa.instance_label = wa.NAME

   AND wat.item_type = wa.item_type

   AND wat.version = wa.version

   AND wat.LANGUAGE = 'US'

   AND wat.NAME = wa.NAME

   AND wat1.NAME = wpa.process_name

   AND wat1.item_type = wat.item_type

   AND wat1.version = wat.version

   AND wat1.LANGUAGE = wat.LANGUAGE

   AND wat1.version = wpa.process_version

   AND wias_h.item_type = 'OEOL'

   AND wias_h.process_activity = wpa.instance_id

   AND wias_h.item_key = :item_key