Handle Exceptions in Oracle Workflow

While working with Oracle workflow, one frequent requirement is to handle errors in Functions that are used by workflow Activity. There are two ways to handle this requirement.

Approach 1 – Do Nothing

Yes, you read it correctly, in the function just write your code without doing anything special to capture the exception i.e. do not put any exception block. Workflow will automatically capture the exception and display appropriate error code and error message against the activity where the error was encountered in Activity History.

Approach 2 – Use WF_CORE API’s

Add an exception block in your code and call WF_CORE.CONTEXT API, the syntax for which is as follows. The first two parameters for this functions are the package name and the procedure name, from third parameter onwards you can pass any argument. This can be used to set any values which are specific to the procedure and which can help in debugging.

procedure CONTEXT (pkg_name  IN VARCHAR2,

                   proc_name IN VARCHAR2,

                   arg1      IN VARCHAR2 DEFAULT '*none*',

                   arg2      IN VARCHAR2 DEFAULT '*none*',

                   arg3      IN VARCHAR2 DEFAULT '*none*',

                   arg4      IN VARCHAR2 DEFAULT '*none*',

                   arg5      IN VARCHAR2 DEFAULT '*none*');

For example the call can be as follows.

BEGIN

 ...

EXCEPTION

  WHEN OTHERS THEN

             wf_core.CONTEXT (pkg_name    => 'XXARM_ADDRESS',

                              proc_name   => 'ADDRESS_AR_OUTGOING_PROCESS',

                              arg1        => pv_itemtype,

                              arg2        => pv_itemkey,

                              arg3        => pn_actid,

                              arg4        => pv_funcmode,

                              arg5        => ‘ERROR :'|| SQLCODE || ‘ ‘ || SUBSTR(SQLERRM,1,300));

    RAISE;

END;

In the above call an interesting point to note is the arguments passed in fields from arg1 to arg4. If you note these are the values which the workflow passes to the function, so now you have the same values and it is very easy to debug the function. In arg5 we can capture the SQL error that was encountered.

If the control goes in the exception block as defined, an entry is added to the error stack to provide context information that helps locate the source of an error. In the workflow monitor you can view the details of the above error  by navigating to ‘Activity History’ and clicking on the ‘Error’ link in the Status column on the activity which has got error.

Continuous Integration - For Oracle Apps?

What is Continuous Integration? 

Continuous Integration is a software development practice where members of a team integrate their work frequently, usually each person integrates at least daily - leading to multiple integrations per day. Each integration is verified by an automated build (including test) to detect integration errors as quickly as possible.

Why should you use Continuous Integration in your project?

When embarking on a change, a developer takes a copy of the current code base on which to work. As other developers submit changed code to the source code repository, this copy gradually ceases to reflect the repository code. The longer a branch of code remains checked out, the greater the risk of multiple integration conflicts and failures becomes when it is reintegrated into the main line. When developers submit code to the repository they must first update their code to reflect the changes in the repository since they took their copy. The more changes the repository contains, the more work developers must do before submitting their own changes.

Eventually, the repository may become so different from the developers' baselines that they enter what is sometimes called "integration hell", where the time it takes to integrate exceeds the time it took to make their original changes. In a worst-case scenario, developers may have to discard their changes and completely redo the work.

Continuous integration involves integrating early and often, so as to avoid the pitfalls of "integration hell". The practice aims to reduce rework and thus reduce cost and time.

Principles of Continuous Integration or Activities that make effective Continuous Integration

Maintain a code repository (i.e. a version control system) – This is the most basic requirement without this it would be impossible to manage multiple versions of code.
Automate the build – Getting code sources into running systems can be a tedious and error prone process without automation. Tools such as ‘make’, ‘ant’ help you with this.
Make your build self testing – This is the most important step to evaluate whether your build was successful. Traditionally a build means compiling, linking, and all the additional stuff required to get a program to execute. A program may run, but that doesn't mean it does the right thing. A good way to catch bugs more quickly and efficiently is to include automated tests in the build process. Tools such as ‘XUnit’ can be used for this activity. For end-to-end kind of testing tools such as ‘Fitnesse’ can be used.
Everyone commits to the baseline every day and Every commit (to baseline) should be built - By committing regularly, every committer can reduce the number of conflicting changes. Checking in a week's worth of work runs the risk of conflicting with other features and can be very difficult to resolve. Early, small conflicts in an area of the system cause team members to communicate about the change they are making. More importantly this makes it easier to identify the area in the code which might have caused the bug to be introduced.
Keep the build fast - The build needs to complete rapidly, so that if there is a problem with integration, it is quickly identified.
Test in a clone of the Production Machine – This is so that you identify bugs in the kind of environment in which the code is finally supposed to run.

Where can I find more information?

Continuous Integration concept was proposed by Martin Fowler and his website has an excellent essay on this topic. http://www.martinfowler.com/articles/continuousIntegration.html

Can this be used in Oracle Apps projects?

As described above, the need for Continuous Integration arises when multiple developers work have to work on the same code. In case of Oracle Apps, most of the source files are such that at a time only one person can work on it for e.g. Forms (FMB), Reports (RPT), Workflow (WFT) etc.  (These are binary source files and it is not easy for the someone to identify the changes between two different versions, hence I say that at a time only one person can work on it. In contrast source files written in text format, for e.g. Java program lend themselves easily to be worked on by multiple people at the same time). Although there are files such as PL/SQL packages which are written in text format, but they are rarely required to be worked on by multiple people and there aren’t frequent updates required to them.

Also looking at the big picture, any development work that happens in Oracle Apps does not involve developing something from scratch, where many people are working to build the same system, it is always a bolt on to the basic technology framework of Oracle Apps. This reduces the issues faced while integrating new developments, which are normally encountered in projects using technologies such J2EE, .NET etc.

I therefore believe, Continuous Integration would be more appropriate to use in a product development project using technologies such as J2EE or .NET. Complex product development projects normally require frequent updates to functionality, leading to new releases, where the using Continuous Integration would really help.

Learn SOA using Q&A

Q)    What is SOA?

R)    As Per Gartner, "Service Oriented Architecture (SOA) is a client-server software design approach in which an application consists of software services and software service consumers (also known as clients or service requesters). SOA differs from the more general client/server model in its definitive emphasis in loose coupling between software components, and in its use of separately standing interfaces."

Q) When should you use SOA?

R) SOA should be the proposed when

1) There are multiple applications which are running in an enterprise and if there is a data dependency in the applications (i.e. data output from one application is data input for another application).

2) And This interfacing is not automated (i.e. it is being done manually or some other tedious method which leads to data issues)

3) And The enterprise is huge and can afford costs for implementing SOA. (SOA software and corresponding hardware would be costly, which makes it suited for big companies)

4) And Speed of implementing the integration is important. (Instead of SOA, custom interfaces can be written to integrate all the applications of the process, but that would be a time consuming process, which will create custom interfaces, requiring lot of testing, maintenance and support. By the time you build it the need for integration might not be there.) SOA will not only help you to build your applications faster. The maintenance and support costs would be minimal as you would be using standard adapters/operations provided by your SOA software.  

Q) When should you propose Oracle SOA?

A) It would be a easy sell for Oracle’s SOA stack when customer is using lot of other Oracle products. If they are not using many Oracle products, then you might have to think about alternative SOA products from IBM, Tibco etc.

Q) When does the SOA process start or get triggered?

A) It would start when the first application in the new business process (which needs to be implemented using SOA) gets the data required for the process to start. It might then provide the data to SOA process in variety of different ways such as SOAP post or place the data in a file at particular location which the SOA process is polling etc. SOA process as such would never have it own UI in the process.

Fresher interview questions for Oracle Database, SQL, Forms & Reports

Interviewing a fresher for working on Oracle projects. Following points can be handy in checking their knowledge in different areas of RDBMS concepts, SQL, PL/SQL, Forms and Reports.

DBMS	Introduction to DBMS
	Views
	Database concepts
	Normalization
	E-R Diagrams
	Types of Databases
	Database Model
Oracle SQL	Basics of Oracle SQL
	Character, Number and Miscellaneous Functions
	Date and Conversion Functions
	Aggregate Functions, GROUP BY and HAVING clauses
	Equijoins, Outer Joins
	Subqueries
	SET Operators
	Complex Joins
	INSERT, UPDATE and DELETE
	CREATE, ALTER and DROP tables
	Views, Indexes and Sequences
	Data Dictionary and Security
	Regular expressions and hierarchical queries
Oracle PL/SQL	PL/SQL Programming Fundamentals
	SQL in PL/SQL
	Conditional Statements: IF & CASE
	Iterative Loops
	Error handling and exceptions in PL/SQL
	Basics of PL/SQL Cursors
	Advanced Cursors
	Triggers
	Compound Triggers
	Collections
	Records
	Native Dynamic SQL
	Bulk SQL
	Procedures
	Functions
	Packages
	UTL_File
	Introduction to SQL * Loader
Oracle Forms	Introduction to Forms GUI
	Forms Layout editor and property palette
	Manipulating properties and property classes
	Executing PL/SQL block(s) and Items in Forms
	Defining Master-Detail relationships in Forms
	Using Triggers in Forms
	Alerts and Editors
	LOV and Record groups in Forms
	Passing parameters to a form and canvas views
	Editing/Creating Form Menus
	Error Handling in Forms
Oracle Reports	Data Modeling in Reports
	Introduction to Reports GUI
	Reports Layout editor
	Implementing formulas and summaries in report
	Embedded SQL in Reports
	Embedded PL/SQL blocks in Reports

Oracle iProcurement : Realms

In Oracle iProcurement, Realms can be used to control access to punchout catalogs(/stores) to different responsibilities or users. This helps in limiting the access to catalogs for users from different departments. So for e.g. if your company policy limits the purchasing of computer hardware to the Information Technology (IT) department. To adhere to this policy and restrict certain users from ordering these types of items the Realms feature can be used.

To use this the following steps need to be followed.

    1. Create the realm. (Log in to Oracle Purchasing and use the following navigation path to open the Realms window: Setup > E-Catalog Admin > Realms)
    2. Assign the realm to a responsibility. (Login to System Administrator responsibility. Open the Responsibilities window using the following navigation path:Security > Responsibility > Define. Open the         Securing Attributes tab and do the necessary setup).

For detailed steps refer Chapter number 2 - Buyer Setup in the Oracle iProcurement user guide (http://download.oracle.com/docs/cd/B12190_11/current/acrobat/icx115punchout.pdf) provided by Oracle.

Oracle Database: Utility Queries

--*****************************************************************************************************
--***************************** To find value of parameters set in init.ora****************************
--*****************************************************************************************************
select name,value from v$parameter where name = 'utl_file_dir'
--*****************************************************************************************************
--***************************** To find object information ****************************************
--*****************************************************************************************************
-- for all objects user dba_<xxxx> views
select * from dba_objects -- gives details of all types of objects
select * from dba_procedures
select * from dba_tables
select * from dba_indexes
-- for objects owned by user use user_<xxxx> views
select * from user_objects -- gives details of all types of objects
select * from user_procedures
select * from user_tables
select * from user_indexes
--*****************************************************************************************************
--***************************** To find current sessions running on a database*************************
--*****************************************************************************************************
select distinct
sid
,s.serial#
,s.module
,lockwait
,s.schemaname
,s.username
,osuser
,machine
,s.terminal
,s.program
,status
,q.sql_text
from v$session s
,v$process p
,v$sqlarea q
where p.addr(+)=s.paddr
and q.hash_value(+)=s.sql_hash_value
and q.address(+)=s.sql_address
and status = 'ACTIVE'
order by sid desc
--*****************************************************************************************************
--***************************** To kill a session on a database *************************************
--*****************************************************************************************************

--ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390'; -- SID and SERIAL# received from above query.

--*****************************************************************************************************
--***************************** To get the current execution of cursor *******************************
--*****************************************************************************************************
SELECT Executions,
loads,
first_load_time ,
elapsed_time
FROM V$sqlarea
WHERE hash_value in (SELECT sql_hash_value
FROM V$session
WHERE sid = 48 );
--*****************************************************************************************************
--**************** To check which processes have locked a resource (generally a Table)*****************
--*****************************************************************************************************

SELECT c.sid,
c.serial#,
c.username,
a.object_id,
b.object_name,
c.program,
c.status,
d.name,
c.osuser
FROM v$Locked_object a,
all_objects b,
v$session c,
audit_actions d
WHERE a.object_id=b.object_id
AND a.session_id =c.sid(+)
AND c.command=d.action;
--*****************************************************************************************************
--***************************** To check ACTIVE session in any instnace *******************************
--*****************************************************************************************************
SELECT s.osuser, s.sid, s.sql_hash_value, s.module, w.event
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
AND event not like 'rdbms ipc message'
AND event not like 'pipe get'
AND event not like 'queue messages'
ORDER BY s.module

--*****************************************************************************************************
--***************************** To check current activity done by the REQUEST_ID **********************
--*****************************************************************************************************
SELECT fcr.request_id
,st.sql_text
,fcr.status_code
,s.sid
,s.serial#
,s.*
FROM apps.fnd_concurrent_requests fcr
,v$session s
,v$sqltext st
WHERE fcr.os_process_id = s.process (+)
AND s.sql_hasH_VALUE = st.hash_value (+)
AND fcr.request_id = 18740879
ORDER BY fcr.request_id, st.piece
--*****************************************************************************************************
--****************************************** To check deadlock ****************************************
--*****************************************************************************************************
select /*+ ordered */ w1.sid waiting_session
,h1.sid holding_session
,w.kgllktype lock_or_pin
,w.kgllkhdl address
,decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held
,decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w
, dba_kgllock h
, v$session w1
, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr ;

Oracle Apps : Set Organization (Operating Unit) while working in a database session

-- ####################################
-- Notes
-- ####################################
-- All the queries below set the Organisation (read: Operating Unit) information for the session
--
-- Query 01 -- does the same by setting the user and responsibility for the session.
-- The calling code then derives the Organisation (Operating Unit, value in profile "MO: Operating Unit"
-- for that user/responsibility.

-- Query 02 -- does the same by directly setting the client info to the required 'Operating Unit'

-- Query 03 -- also does the same by directly setting the client info to the required 'Operating Unit'

-- Fyi - Operating units are defined in the HR_ALL_ORGANIZATION_UNITS table.

-- ####################################
-- Query 01
-- ####################################
-- The following call to apps_intialize function does not pass
-- the parameter "security_group_id", hence it is defaulted to
-- to zero (that is the setup Business Group, Business Group is the topmost level in the
-- Organization Definition Hierarchy. So generally the ORG_ID for the same is zero)

declare
    l_user_id      number;
    l_resp_id      number;
    l_resp_appl_id number;
begin
    -- get conversion user
    select fu.user_id
    into   l_user_id
    from   fnd_user fu
    where  fu.user_name = :p_user_name;
    -- get conversion responsibility
    select fr.responsibility_id,
           fr.application_id
    into   l_resp_id,
           l_resp_appl_id
    from   fnd_responsibility fr
    where  fr.responsibility_key = :p_responsibility_key;
    fnd_global.apps_initialize(
      user_id       => l_user_id,
      resp_id       => l_resp_id,
      resp_appl_id  => l_resp_appl_id);
end;
/

-- ####################################
-- Query 02
-- ####################################
-- To set the organisation for the session the following code can be used.

begin
  fnd_client_info.set_org_context('255');
end;
/


-- ####################################
-- Query 03
-- ####################################
-- To set the organisation for the session the following code can also be used.

exec dbms_application_info.set_client_info ('255');