Sunday, July 19, 2015

DB: ORA-02064: distributed operation not supported

Recently I faced an error when I tried to execute a remote procedure having transaction control statements(Commit/Rollback) over DB link. 

Error: ORA-02064: distributed operation not supported

Reference note for this error code:
1.One of the following unsupported operations was attempted:1. array execute of a remote update with a subquery that references a dblink, or
2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call


In my case, the reason was the remote procedure was performing insert and commit.

Solution:
Modified the remote procedure to create Autonomous Transaction using PRAGMA AUTONOMOUS_TRANSACTION syntax as shown below.

CREATE OR REPLACE PACKAGE test_pkg AS
    PROCEDURE test_proc (p_param OUT VARCHAR2 );
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS
    PROCEDURE test_proc (p_param OUT VARCHAR2) IS
    l_var NUMBER;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    ...
    --BUSINESS LOGIC
    ...
    EXCEPTION
       WHEN OTHERS THEN
      ..
   END test_proc;
END test_pkg;

http://docs.oracle.com/database/121/LNPLS/autotransaction_pragma.htm

The term "automous transaction" refers to the ability of PL/SQL temporarily suspend the current transaction and begin another, fully independent transaction (which will not be rolled-back if the outer code aborts).  The second transaction is known as an autonomous transaction. The autonomous transaction functions independently from the parent code.

An autonomous transaction has the following characteristics:

> The child code runs independently of its parent
> The child code can commit or rollback & parent resumes
> The parent code can continue without affecting child work

Friday, July 10, 2015

WLS: non-XA JDBC Datasource

Recently I faced below error, when tried to test SOA composite, due to incorrect configuration of the data source.

Error:
Unable to roll back JCA LocalTransaction due to:
Internal Exception: java.sql.SQLException: Cannot call Connection.rollback in distributed transaction.  Transaction Manager will commit the resource manager when the distributed transaction is committed.

Fix: 
Unchecked 'Supports Global Transactions' option in the data source configuration. This is applicable in case  Non-XA driver used to configure data source.


ADF: Date & Time

Provided different examples to get date & time in ADF.

Groovy:
Current Database Date: DBTransaction.currentDbTime
Current Date: adf.currentDate
Current DateTime: adf.currentDateTime
Add Dates to Current Date: adf.currentDateTime.plus(10)


Java:
Find AM or PM: 
Calendar.getInstance().get(Calendar.AM_PM)
returns 0 for AM & 1 for PM

Get Today's date:
Date d = new Date(Date.getCurrentDate())

Get todays Timestamp:    
Timestamp myTimestamp = new Timestamp((new java.util.Date()).getTime());

Get the database time and set VO attirbute:
vo.setAttribute("OrderDate", ((DBTransactionImpl)getDBTransaction()).getCurrentDbTime());

Get current database time in the middle tier:
oracle.jbo.server.DBTransactionImpl.getCurrentDbTime()

Get current time as java.sql.Date:
new java.sql.Date(((DBTransactionImpl)(this.getDBTransaction())).getCurrentDbTime().getTime());

Get Date value in Page/Fragments:
Add following values to adfc-config.xml or taskflow.xml:

<managed-bean>    
<managed-bean-name>currentDate</managed-bean-name>    
<managed-bean-class>java.util.Date</managed-bean-class>    
<managed-bean-scope>request</managed-bean-scope>
</managed-bean>  

And refer as below
<af:outputText value="#{currentDate}">    
<f:convertDateTime pattern="MM/dd/yyyy" type="date"/>
</af:outputText>