Monday, May 21, 2012

Multiple IN parameters in DB Adapter Dynamic Query


I explained how to pass the multiple parameters to IN clause in Pure SQL here. However, the solution needed to assign the same input string to bind parameter #val 9 times. Later on while browsing Oracle Forums, I came across a more-refined query that would address this concern.

To select employees with f_name as 'NEERAJ' or 'JACOB' or 'ROBERT', write your Pure SQL in DB Adapter like below. This would create DB schema automatically further to which you might need to specify the type of the bind parameters as xs:string in this schema.

Now, invoke the DB Adapter and using the Assign activity , assign the bind parameter values in the above query as under:

#InputString - Your Input String with delimiters (but no spaces) e.g. 'NEERAJ,JACOB,ROBERT'
#Delimiter1 - Assign the delimiter e.g. ','. This is to suffix above string with same delimiter.
#Delimiter2 - Assign the same delimiter e.g. ','

And that's it. Invoke your process and test it. Happy Learning....


Tuesday, May 1, 2012

Changing the Archive File Name in Inbound File/FTP Adapter


Requirement:

To customize the archive file name, remove the junk characters from the archived file name. The default format is as under.

SOA 10g - filename_yyyymmdd_hh24mmss
SOA 11g - filename_encryptedToken_yyyymmdd_hh24mmss (new format introduced with 11g)

The possible customization is given in the solution below.

Solution:

SOA 10g

You have a JCA property for FIle/FTP adapter, UseLongArchiveFileName, which can be used to prefix the process name to the archive filename to make the format filename_processname_timestamp.

SOA 11g (11.1.1.3/+) 

UseLongArchiveFileName is not available in 11g. Apply Patch 10155914 and it will become available.    

SOA 11g (11.1.1.5/+) 

Apply Patch 13249896.
A new property  UseDigest can be used now in addition to  UseLongArchiveFileName (11g format as filename_processname_digest_timestamp) with File/FTP adapter.
By default UseDigest is set to true which leads to the digest (encrypted token) being sandwiched in the filename and timestamp as filename_digest_timestamp.
Setting this property to false will change the format to filename_timetamp. To use this property edit the .jca file to include this property as given below.

SOA 11g (11.1.1.7/+)

UseDigest property will be available by default without the need to apply the above patch.

There is, however, no further customization possible (presently) in terms of adding instance id to the file name or specifying custom file name. Hope the same gets available in the 11.1.1.7(+) or 12C version.