How to connect to a remote database using sqlplus

Often we might need to connect to remote database servers using sqlplus.

In such cases we can use the following connection string format

Open command prompt and execute the following line after giving proper values for

1)remotehostname
2)portnumber
3)sid
4)username
5)password


sqlplus username/password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= remotehostname)(PORT=portnumber)))(CONNECT_DATA=(SID=sid)))'

OSB : Dynamically Change the end point for Business Service

There could be a requirement to change the endpoints dynamically while invoking external service from  OSB.

This can be achieved by injecting the endpoint just before the service invocation.

Configure the endpoints in an .xq file as shown

Assign the content of xq file to a variable

In the message flow add an xpath expression and assign the target endpoint value to a variable

In the service-callout/publish/routing RequestAction block , add a Routing Option and update the URI using the above created variable

Table is mutating, trigger/function may not see it

If you write a trigger to act on the same table, we could end up with the following error.

table TRANSACTION is mutating, trigger/function may not see it

This is due to the trigger is trying to act on the record which is getting inserted.

So if use an AFTER INSERT trigger, it will always fail.

Instead we can use BEFORE INSERT construct as shown below.

Here the source application trying to insert a record with status value either PROCESSED or INPROCESS

But if you have a requirement to get the status as 'NOT PROCESSED' for your application to process that record, we can update the source record before it commits to DB

================ Trigger Start=============================

create or replace
TRIGGER "ORAUSER"."TRANSACTION_INSERT"
BEFORE INSERT
ON ORAUSER.TRANSACTION
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_event_seq NUMBER;
s_error VARCHAR2(256);

BEGIN

   IF :new.STATUS ='PROCESSED' OR :new.STATUS ='INPROCESS' THEN
           :new.STATUS :='NOT PROCESSED';
END IF;
 
   EXCEPTION
     WHEN OTHERS THEN
           BEGIN
   
s_error := SUBSTR(TO_CHAR(SQLCODE)||' '||SQLERRM,1,256);  
SELECT event_seq.NEXTVAL
INTO v_event_seq
FROM dual;  
INSERT INTO ERROR_TABLE
        VALUES (v_event_seq,s_error,:new.TRANSACTION_ID,'ERROR','TRANSACTION_INSERT',1,SYSDATE,0,NULL);
             
      END;
END TRANSACTION_INSERT;



=================Trigger End==============================

DB Polling Issue in Cluster

DB Polling is always a challenging requirement. Often we encounter various issues with polling, be it not picking up the record or not updating the status. Some times we can see there are duplicate BPEL instances created for the same record.

The duplicate instances mainly happens in clustered environment where as each node will try to poll the same record.

This can be avoided in two ways

One by choosing the Distributed Polling option while configuring the DB Adapter. Once this is selected it means the record is locked by the reading instance. No other instance can read the record. In that way duplicate instances can be avoided

The second approach is to set a singleton parameter for the JCA in the composite.xml


    <binding.jca config="triggerTransaction_db.jca">
             <property name="singleton">true</property>
    </binding.jca>

In this case too only one node will poll the record and duplicates will be avoided