tag:blogger.com,1999:blog-50836742659127304172024-03-13T09:31:39.026-07:00Neeraj Sehgal - Experimenting with SOASolutions and Thoughts on Oracle Fusion Middleware and its components : Oracle AIA, ADF, BPEL, Rules, OWSM, WebCenter Suite etc.Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.comBlogger37125tag:blogger.com,1999:blog-5083674265912730417.post-17033434048596202512015-10-05T05:45:00.000-07:002015-10-05T05:45:31.534-07:00Forward (Email) the received Attachment via UMS Adapter<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"></meta>
<title>ForwardReceivedAttachment</title>
</head>
<body>
<p>
<em>
<font color="#0000ff">
Note
</font>
: This post assumes that you already have some working knowledge of
BPEL and XSLT functions and you're able to retrieve the content of Email
attachment as described in my previous post -</em>
<a href="http://neeraj-soa-tips.blogspot.com/2015/09/ums-adapter-all-about-email-attachments.html">UMS
Adapter
-
All
about
email
attachments</a>.
</p>
<p>
There may be cases when you have received an email attachment via UMS
Adapter and you want to forward the same email as-is to another recipient
e.g. if received attachment is invalid then forward to same to support
team for further diagnosis. Following instructions show how you can
achieve the same.
</p>
<ul>
<li>
Drag and Drop another
UMS Adapter
(because the one you may have already was used to originally receive
the Email) onto the right swimlane and select
<font color="#0000ff" face="Courier New">
Outbound Send Notification
</font>
in the
<font face="Courier New">
Operation Type
</font>
. Also
<font color="#0000ff">
check
</font>
<font face="Courier New">
Receive Message Id as Reply
</font>
. Click
<font face="Courier New" color="#0000ff">
Next.
</font>
</li><li>
On the next screen choose
<font face="Courier New">
Type of notification
</font>
=
<font face="Courier New" color="#0000ff">
Email
</font>
and assign appropriate
<font face="Courier New">
Subject
</font>
and
<font face="Courier New">
To
</font>
Email ID. You can leave
<font face="Courier New">
From
</font>
as blank. Click
<font face="Courier New" color="#0000ff">
Next.
</font>
</li><li>
Choose
<font face="Courier New">
Message is of String type
</font>
. Click
<font face="Courier New" color="#0000ff">
Next
</font>
and
<font face="Courier New" color="#0000ff">
Finish.
</font>
</li><li>
Now drag and Drop an
<font face="Courier New" color="#0000ff">
Invoke
</font>
activity and connect to the UMS Adapter which will allow to create
Input/Output variables e.g.
<font face="Courier New" color="#0000ff">
InvokeSendErrorNotification_InputVariable
</font>
and
<font face="Courier New" color="#0000ff">
InvokeSendErrorNotification_OutputVariable
</font>
</li><li>
Now drag another
<font face="Courier New" color="#0000ff">
Assign
</font>
activity and place it just before the
<font face="Courier New" color="#0000ff">
Invoke
</font>
just created.
</li><li>
Assign relevant text for payload string to
<font face="Courier New">
InvokeSendErrorNotification_InputVariable > body > message >
payload
</font>
. You can also use html formatted text if you wish in the payload.
</li><li>
Not navigate to
<font face="Courier New">
InvokeSendErrorNotification_InputVariable > body > message >
attachment > href
</font>
and assign the incoming attachment href variable
<font face="Courier New" color="#0000ff">
(ReceiveEmail_InputVariable).
</font>
This variable was created as part of
<font face="Courier New" color="#0000ff">
Receive
</font>
activity when we configured previous UMS Adapter to receive the
incoming email and attachment. Click OK and that's it.
</li>
</ul>
<p>
The code for Assign activity is shown below for guidance.
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<assign name="AssignEmailBody" xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable">
<copy>
<from>concat("<html><head><style>body{font-family: Arial; font-size: 10pt;} table{font-family: Arial; font-size: 10pt; table-layout: auto;}</style></head><body><p>Dear User,<br/><br/>SOA has encountered an issue while processing Email Attachment. Please find below the details of the errors and take the corrective action -</p><table><tr><td><B>SOA Composite :</B></td><td>",ora:getCompositeName(),"</td></tr><tr><td><B>SOA Instance Id :</B></td><td>",ora:getCompositeInstanceId(),"</td></tr></table><p>Please refer the FMW console for more details.<br/><br/><B><I>SOA Automailer</B></I></p></body></html>")</from>
<to>$InvokeSendErrorNotification_InputVariable.body/ns7:payload</to>
</copy>
<copy>
<from>$ReceiveEmail_InputVariable.body/ns2:attachment[1]/@href</from>
<to>$InvokeSendErrorNotification_InputVariable.body/ns7:attachment/@href</to>
</copy>
</assign>
]]>
</script>
</p>
<ul>
<li>
First
<font face="Courier New" color="#0000ff">
from
</font>
expression - the payload that you want to appear as the email message.
This is html formatted just to show how you can use html formatted tags.
</li><li>
<font face="Courier New" color="#0000ff">
InvokeSendErrorNotification_InputVariable.body/ns7:payload
</font>
- Input variable created as new UMS Adapter. We're assigning into
payload element of the same.
</li><li>
<font face="Courier New" color="#0000ff">
$ReceiveEmail_InputVariable.body/ns2:attachment[1]/@href
</font>
- variable created from previous UMS Adapter to receve the attachment.
<strong>
<font color="#0000ff">
[1]
</font></strong>
is used to refer to the first attachment in the email in case received
email has multiple attachments.
</li><li>
<font face="Courier New" color="#0000ff">
$InvokeSendErrorNotification_InputVariable.body/ns7:attachment/@href
</font>
- the variable created as part of new UMS Adapter. We're referring to
the @href here which contains the reference to the attachment.
</li>
</ul>
<p>
Happy Learning and let me know if any doubts!
</p>
</body>
</html>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com0tag:blogger.com,1999:blog-5083674265912730417.post-59963029386675541802015-10-05T05:44:00.001-07:002015-10-05T05:56:31.300-07:00Parse and Read the contents of Email Attachment in the BPEL process<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"></meta>
<title>ParseAndReadAttachmentContent</title>
</head>
<body>
<p>
This post assumes that you already have some working knowledge of
<font color="#0000ff" face="Courier">
BPEL
</font>
and
<font color="#0000ff" face="Courier">
XSLT
</font>
functions and you're able to retrieve the content of Email attachment as
described in my previous post -
<a href="http://neeraj-soa-tips.blogspot.com/2015/09/ums-adapter-all-about-email-attachments.html">UMS Adapter - All about email attachments</a>.
</p>
<p>
Many a times the requirement is to parse through the content of the
attachment to extract desired information out of it. As attachment read is
in the
<font color="#0000ff" face="Courier">
base64
</font>
binary data by default, first and foremost thing is to convert it into
String and then parse it into the required XSD schema. Oracle provides a
function Out-of-the-box (OOTB )to do both the bits in one call. Follow
below steps to parse the incoming attachment.
</p>
<p>
The simple case I have taken here is that I receive a CSV file as
attachment in email and I parse it into XML for further transformations on
the content.
</p>
<ul>
<li>
Create a schema of the email attachment that you want to parse using
<font color="#0000ff">
Native Schema builder.
</font>
Basically that means dragging and dropping a File Adapter and create a
schema with that, later cancel or delete the adapter as we only need the
schema so crated. I receive a comma delimited file as attachment, hence
used a
<font color="#0000ff">
delimited
</font>
option to create my schema but you can also parse XML, Fixed Length any
other type of file.
<em>In case of XML files, please pay attention to the XML file namespace
and the target Schema namespace</em>
.
</li><li>
Create a variable
<font color="#0000ff" face="Courier">
targetMessage
</font>
of the schema created above (shown below in Examples)
</li><li>
Use the OOTB function
<font color="#0000ff" face="Courier">
ora:doTranslateFromNative(Input Message in binary format, Relative
path to the schema from composite directory, Schema Root Element Name,
'DOM' )
</font>
as shown below
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
BPEL 2.0
<copy>
<from>ora:getAttachmentContent('Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]')</from>
<to>$CustomAttachmentVariable/ns2:attachment</to>
</copy>
<copy>
<from>ora:doTranslateFromNative($CustomAttachmentVariable,'xsd/AttachmentFile.xsd', 'TargetRequest', 'DOM')</from>
<to>$targetMessage</to>
</copy>
]]>
</script>
</li><li>
The first assign is to fetch the Attachment Content into another Custom
variable of the same type
</li><li>
<font color="#0000ff" face="Courier">
$targetMessage
</font>- is the
<font color="#0000ff" face="Courier">
Element
</font> Type variable of the target schema (Root element
<font color="#0000ff" face="Courier">
TargetRequest)
</font>
which will hold the translated message
</li><li>
Once the
<font color="#0000ff" face="Courier">
targetMessage
</font>
is formed, you can traverse through all elements of this message and
carry out all desired transformation operations on this like any other
XML variable.
</li>
</ul>
<p>
Below shown is the structure of file and the associated code, although not
full but should give an idea of the main components involved.
</p>
<p>
<strong>Example Email Attachment format :</strong>
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
1234;20070203163000;Separated;Better Role
3456;20150203163000;Employee;
5678;20120203163000;Separated;Unknown
]]>
</script>
</p>
<p>
<strong>Example Schema :</strong>
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<?xml version="1.0" encoding="UTF-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd"
xmlns:tns="http://xmlns.oracle.com/TargetFile"
targetNamespace="http://xmlns.oracle.com/TargetFile"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
nxsd:version="NXSD"
nxsd:stream="chars"
nxsd:encoding="UTF-8"
>
<xsd:element name="TargetRequest">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="record" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="emp-no" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=";" nxsd:quotedBy=""" />
<xsd:element name="joining-date" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=";" nxsd:quotedBy=""" />
<xsd:element name="current-status" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy=";" nxsd:quotedBy=""" />
<xsd:element name="reason-for-leaving" type="xsd:string" minOccurs="0" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy=""" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
]]>
</script>
</p>
<p>
<strong>Variable Created in BPEL:</strong>
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<variable name="targetMessage" element="ns4:TargetRequest"/>
]]>
</script>
</p>
<p>
Please let me know if any issues and I'll try to help! Happy Emails!
</p>
</body>
</html>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com3tag:blogger.com,1999:blog-5083674265912730417.post-32710619636273144432015-09-02T09:49:00.001-07:002015-09-02T09:49:33.569-07:00How to retrieve name of the Email Attachment<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"></meta>
<title>GetAttachmentName</title>
</head>
<body>
<p>
Once you have made Attachments to work (<a href="http://neeraj-soa-tips.blogspot.com/2015/09/ums-adapter-all-about-email-attachments.html">UMS
Adapter
-
All
about
email
attachments</a>)
in
your
BPEL
process
and
able
to
parse
them,
it
may
be
necessary
to
retrieve
the
actual
name
of
the
email
attachment
read.
The
best
way
to
do
that
is
using
out-of-the-box
function
as
given
below
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
ora:getAttachmentProperty('Content-Disposition', 'Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]')
]]>
</script>
</p>
<p>
The above should return
<font color="#0000ff" face="Courier">
attachment; filename="test.txt";
</font>however, nothing is as simple in this world and to make it true,
it is a known
<a href="https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=1941333.1&id=19492062">BUG:19492062.</a>
Luckily, we have a patch
<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=301565529960731&parent=WIDGET_RECENTLY_VIEWED&sourceId=&id=1941333.1&_afrWindowMode=0&_adf.ctrl-state=jjvuav1wr_353">1941333.1</a>
available to apply and get going but it may be time consuming to request
a patch, do the impact assessment and get it applied across all
environments. Whilst that may be the ideal solution in the long term but
to quickly test and retrieve the attachment name you can use property
<font color="#0000ff">
Content-Type
</font>
as given under.
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<assign name="AssignEmailAttachmentName">
<copy>
<from>substring-before (substring-after(concat(ora:getAttachmentProperty('Content-Type', 'Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]'), ';'), 'name=' ), ';')</from>
<to>emailAttachmentName</to>
</copy>
</assign>
]]>
</script>
</p>
<ul>
<li>
<font color="#0000ff" face="Courier">
ora:getAttachmentProperty()
</font>
function on Content-Type returns
<font color="#0000ff">
text/plain; charset=UTF-8; name=test.txt; any additional text;
</font>
</li><li>
<font face="Courier" color="#0000ff">
Substring-before() and substring-after()
</font>
have been done to intelligently extract string after
<font color="#0000ff">
name=
</font>
</li><li>
<font face="Courier" color="#0000ff">
emailAttachmentName</font>- is a
<font color="#0000ff">
string
</font>
type custom variable to store the attachment name
</li>
</ul>
<p>
Quite simple till the patch is applied, or do we indeed need the patch now!
</p>
</body>
</html>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com4tag:blogger.com,1999:blog-5083674265912730417.post-43431687740718739362015-09-02T09:43:00.000-07:002015-10-05T05:50:43.254-07:00UMS Adapter - All about email attachments<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"></meta>
<title>untitled1</title>
</head>
<body>
<font face="Trebuchet MS">
<p>
SOA Suite - 11.1.7.6
</p>
<p>
A lot has already been written at
<a href="https://technology.amis.nl/2012/05/18/proces-email-with-the-new-ums-adapter-of-soa-suite-ps5/">this</a>
blog about how to configure UMS Adapter and read attachments from it.
Hence this post is just to describe additional points to read/process
the attachments. The post assumes that you're able to receive the incoming
email using UMS Adapter successfully and able to get the BPEL process instantiated.
</p>
<h3>
<font color="#008400">
How to Read/Fetch the Attachment?
</font>
</h3>
<p>
<strong>BPEL 1.1</strong>
- It's fairly simple in BPEL 1.1 using the following syntax in the
<font face="Courier New">
Assign
</font>
Activity.
</p>
<p>
<script class="brush: xml; highlight: 2" type="syntaxhighlighter">
<![CDATA[
<copy>
<from>ora:getAttachmentContent('Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]')</from>
<to>CustomAttachmentVariable/ns2:attachment</to>
</copy>
]]>
</script>
</p>
<ul>
<li>
<font color="#0000ff" face="Courier New">
<font face="Courier">
Receive1_ReceiveNotification_InputVariable
</font>
</font>
- is the variable created out of Receive Activity connected to
Inbound UMS Adapter
</li><li>
<font color="#0000ff" face="Courier">
Body, ns2:message/ns2:attachment
</font>
- are the variable part and element inside the variable respectively
created by default.
</li><li>
<font color="#0000ff" face="Courier">
ns2:/attachment [1]
</font>
is used to read the
<strong> first</strong>
attachment always. You may want to loop through all the attachments
and choose the one you desire based on
<font color="#0000ff" face="Courier">
Content-Type
</font>
or
<font color="#0000ff" face="Courier">
Attachment Name
</font>
or etc. Read my another post on how to retrieve the attachment names
from the Email attachments.
</li><li>
<font color="#0000ff" face="Courier">
CustomAttachmentVariable
</font>
- is the custom
<font face="Courier New">
Element
</font>
type variable of Attachment Message with the namespace
<font color="#0000ff" face="Courier">
{http://platform.integration.oracle/blocks/adapter/fw/metadata/ReadEmailAttachment}message.
</font>
I created this variable simply to complete the
<font face="Courier New">
Assign.
</font>
You may further access this variable for extracting the contents of
the attachment or writing to file etc.
</li>
</ul>
<p>
<strong>BPEL 2.0</strong>
- It's not a rocket science here as well, just one thing to note is
that default BPEL 2.0 variable format
<font color="#0000ff" face="Courier">
($Receive1_ReceiveNotification_InputVariable.body/ns2:attachment)
</font>
(note the
<font color="#0000ff">
.
</font>
between InputVariable.body ) is not supported by the
<font color="#0000ff" face="Courier">
ora:getAttachmentContent()
</font>
function. So you have to explicitly write the variable in the BPEL 1.1
format. You can assign intially from the Design View and then go into BPEL source and modify the default Assign code generated to below:
<font color="#0000ff" face="Courier">
(ora:getAttachmentContent('Receive1_ReceiveNotification_InputVariable'
<strong>,</strong>
'body','/ns2:message/ns2:attachment[1]'))
</font> - note the body part.
</p>
<p>
<font face="Trebuchet MS">
</font>
</p>
<h3>
<font color="#008400">
What to do with the Attachment read?
</font>
</h3>
<p>
There are basically 3 things that you may want to do with the attachment
just read. To keep this post concise, they have been put into separate posts.
</p>
</font><ul>
<li>
<a href="http://neeraj-soa-tips.blogspot.com/2015/08/write-email-attachment-to-file.html">Write
the
incoming
attachment
to
another
file</a>
</li><li>
<a href="http://neeraj-soa-tips.blogspot.com/2015/10/forward-email-received-attachment-via.html">Forward
the
incoming
attachment</a>
</li><li>
<a href="http://neeraj-soa-tips.blogspot.com/2015/10/parse-and-read-contents-of-email.html">Parse
the
attachment
content
into
XML
for
further
use
within
the
process</a>
</li>
</ul>
<p>
Please feel free to navigate through and let me know if any doubts! Happy
Emailing!
</p>
</body>
</html>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com5tag:blogger.com,1999:blog-5083674265912730417.post-87606064523956850892015-08-07T07:00:00.000-07:002015-10-05T05:54:33.199-07:00Write Email Attachment to a File<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"></meta>
<title>WriteIncomingAttachmentToFile</title>
</head>
<body>
<p>
To be able to do this, you must be able to first retrieve the attachment
from the
<font color="#0000ff" face="Courier">
Receive
</font>
variable as described in the
<a href="http://neeraj-soa-tips.blogspot.com/2015/09/ums-adapter-all-about-email-attachments.html">previous
post</a>.
Also
this
post
assumes
that
you
have
some
basic
knowledge
of
BPEL,
Adapters
and
XSLT
functions.
Just
for
understanding,
by
default,
Attachment
is
stored
in
the
<font color="#0000ff" face="Courier">
ATTACHMENT
</font>
table in
<font color="#0000ff" face="Courier">
SOA_INFRA
</font>
user schema and the table's row reference (called
<font color="#0000ff">
href)
</font>
is received in the Receive variable. When
<font color="#0000ff" face="Courier">
ora:getAttachmentContent()
</font>
function is called, it fetches the attachment content from the table in
<font color="#0000ff" face="Courier">
base64
</font>
encoded format and assigns to the target custom variable.
</p>
<p>
There are 2 ways you can write incoming attachment to an external file.
</p>
<p>
<strong>
<font size="4">
ora:writeBinaryToFile()
</font></strong>
</p>
<p>
There's inbuilt function
<font color="#0000ff" face="Courier">
ora:writeBinaryToFile()
</font>
that automatically extracts the attachment from the Attachment href (in
the binary format) and writes to the specified target file (hence you
don’t need to use
<font color="#0000ff" face="Courier">
ora:getAttachmentContent()
</font>
in front of it). Follow the below steps to write it to file using
<font color="#0000ff" face="Courier">
ora:writeBinaryToFile()
</font>.
</p>
<ul>
<li>
If you're starting from the beginning, then create a new Simple BPEL
project with
<font color="#0000ff">
Define service later
</font>
option
</li><li>
Drag and Drop the UMS Adapter and configure the adapter. It will
automatically create the schema for UMS Adapter and WSDL file.
</li><li>
Go to BPEL process and drag and drop
<font color="#0000ff">
Receive
</font>
Activity on the swim lane. Connect the activity to UMS Adapter and
create a Receive variable (
<font color="#0000ff" face="Courier">
Receive1_ReceiveNotification_InputVariable
</font>
)
</li><li>
Create another variable of the type
<font color="#0000ff" face="Courier">
attachment
</font>
and name
<font color="#0000ff" face="Courier">
$CustomAttachmentVariable
</font>
</li><li>
Next drag and drop the
<font color="#0000ff" face="Courier">
Assign
</font>
Activity and drop
<font color="#0000ff" face="Courier">
Expression
</font>
on the
<font color="#0000ff" face="Courier">
$CustomAttachmentVariable
</font>
on the Target side
</li><li>
Assign the following in the Source side. In BPEL source , comple Assign
statement would look like as shown below.
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<copy>
<from>ora:writeBinaryToFile('Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]', '/u01/app/oracle/userdir/NewAttachment.xml')</from>
<to>$CustomAttachmentVariable/ns2:attachment</to>
</copy>
]]>
</script>
-
</li><li>
<font color="#0000ff" face="Courier">
Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]
</font>
- is the BPEL 1.1. notation of the variable as mentioned earlier. Even
in BPEL 2.0 you must use the same format.
</li><li>
<font color="#0000ff" face="Courier">
/u01/app/oracle/userdir/NewAttachment.xml
</font>
- specifies the full path of the file where attachment content needs to
be written
</li><li>
<font color="#0000ff" face="Courier">
$CustomAttachmentVariable/ns2:attachment
</font>
- is just meant to complete the
<font color="#0000ff" face="Courier">
Assign
</font>
Statement. It doesn’t have any significance in writing the file.
Run the project, it should work.
</li>
</ul>
<p>
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<copy>
<from>ora:getAttachmentContent('Receive1_ReceiveNotification_InputVariable','body','/ns2:message/ns2:attachment[1]')</from>
<to>$Invoke1_Write_InputVariable.opaque</to>
</copy>
]]>
</script>
</p>
<p>
<strong>
<font size="4">
Using the File/FTP Adapter
</font></strong>
</p>
<p>
The other way is to use a File Adapter through a opaque schema variable.
Follow the below steps to write a file with the content retrieved from
attachment. Note - You don’t have any control over the content of
the file, so whatever comes in the attachment will be written directly. If
you want to parse the content of the file before write read my another
post -
<a href="http://neeraj-soa-tips.blogspot.com/2015/10/parse-and-read-contents-of-email.html">Parse
and
Read
contents
of
Email
Attachment
in
the
BPEL
process</a>.
</p>
<ul>
<li>
Drag and Drop a File adapter to the above project (created with UMS
Adapter) and choose option
<font color="#0000ff" face="Courier">
Native Format Translation is not Required
</font>
(Schema is Opaque).
</li><li>
Go to BPEL, drag and drop an Invoke activity, connect to File Adapter
just created and create an Invoke
<font face="Courier">
Variable (Invoke1_Write_InputVariable.opaque
</font>
)
</li><li>
Assign the Attachment content to the variable just created as shown
below. Note - we are using
<font color="#0000ff" face="Courier">
ora:getAttachmentContent()
</font>
to first fetch the content in opaque
<font color="#0000ff" face="Courier">
base64
</font>
format which is then assigned to the opaque variable of the File
Adapter.
</li><li>
Run the project, it will write the attachment content in a file with the
name specified in the File Adapter. You can also retrieve the name of
the attachment and write with the same name if required. Read post -
<a href="http://neeraj-soa-tips.blogspot.com/2015/09/how-to-retrieve-name-of-email-attachment.html">How
to
retrieve
name
of
the
Email
Attachment</a>
.
</li>
</ul>
<p>
Let me know if any issues and I'll try to help!
</p>
</body>
</html>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com4tag:blogger.com,1999:blog-5083674265912730417.post-74811489299811523812013-05-23T07:28:00.002-07:002013-05-23T07:28:45.233-07:00Export a SOA project from the FMW Console<div dir="ltr" style="text-align: left;" trbidi="on">
Once projects deployed, they surely show up on the FMW Console but is the reverse possible? I mean is it possible to get the code of the project back from the deployed project that you see on the FMW console? Fortunately yes, it is possible. Follow the below steps to achieve the same.<br />
<br />
- Navigate to your composite in FMW console and in the <span style="color: blue;">SOA Composite</span> menu on top middle click <span style="color: blue;">Export</span>.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-E-gCRV2KGzU/UZ4ljSbXCDI/AAAAAAAAAXs/1mraHjdQy90/s1600/Export.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="206" src="http://4.bp.blogspot.com/-E-gCRV2KGzU/UZ4ljSbXCDI/AAAAAAAAAXs/1mraHjdQy90/s320/Export.jpg" width="320" /></a></div>
<br />
- Choose the default option <span style="color: blue;">Option 1</span> and click again <span style="color: blue;">Export</span>.You can try with other options too though.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-9HytRWTyP7c/UZ4lqJam-0I/AAAAAAAAAX0/DvI2v-8OhK8/s1600/SaveFile.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="136" src="http://1.bp.blogspot.com/-9HytRWTyP7c/UZ4lqJam-0I/AAAAAAAAAX0/DvI2v-8OhK8/s320/SaveFile.jpg" width="320" /></a></div>
<br />
- It will extract your project in .jar file and offer you to save it. <span style="color: blue;">Save </span>the file to your local machine.<br />
- Extract this jar file using Winzip into some directory and it'll create the files of your project. <b>Note :</b> There is no project (.jpr) or application (.jws) file still after extraction.<br />
- Hence, you need to create a dummy application or you can use any existing application to create a New project in Jdeveloper with the same name as of your composite. In my case, <span style="color: blue;">HelloWorld</span>.<br />
- Once the project is created, copy the contents of the extracted directory above into the new project directory from the physical location.<br />
- Refresh your project from Jdeveloper after copying and It's ready.<br />
<br />
Very small but useful learning originated from someone's <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2540095&tstart=0" target="_blank">question</a>.</div>
Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com2tag:blogger.com,1999:blog-5083674265912730417.post-29780506958394538992013-01-01T00:12:00.000-08:002013-01-01T00:12:10.332-08:00String replace() function in BPEL XSLT<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: center;">
<b><span style="color: #bf9000;">First of all a very Happy New Year to all the readers and the readers to come. May this year brings more prosperity and unending happiness for all across the globe.</span></b></div>
<div style="text-align: center;">
<br /></div>
This post originates from my recent cerebral exercise of pondering over the best way to replace the occurrence of a string inside another string in XSLT. Especially in BPEL which doesn't have this inbuilt string-replace() function and hence we're bound to have something of our own, this was worth thinking. However, this resulted in additional findings too but with due respect to this post I'd like to keep them with me for the time being. So following lists the ways to have something of our own that can do string replacement. You can choose the best way based on your requirements.<br />
<br />
<ul style="text-align: left;">
<li>Write an <a href="http://geekswithblogs.net/Erik/archive/2008/04/01/120915.aspx" target="_blank">XSLT template</a> for string replace function and call this template whenever string replacement is required.</li>
<li>Create your own <a href="https://blogs.oracle.com/bwb/entry/creating_custom_xpath_functions_using" target="_blank">Custom XPath function</a> for String replacement that can be imported in JDeveloper and used across the whole developer team</li>
<li>Harness the capabilities of using Java classes in XSLT. This is the simplest method and I'll explain this in just 2 steps, attributed to its simplicity.</li>
<ul>
<li>Create a namespace in XSLT for the Java String class to be used (let's prefix this as <span style="color: blue; font-family: Courier New, Courier, monospace;">:strClass</span>).</li>
<li>Use <span style="color: blue; font-family: Courier New, Courier, monospace;">replaceAll()</span> function of the String class referenced above as highlighted under. And the job is done.
</li>
<script class="brush: xml; highlight: [3, 5]" type="syntaxhighlighter">
<![CDATA[
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:strClass="http://www.oracle.com/XSL/Transform/java/java.lang.String">
<xsl:template match="/">
<xsl:value-of select="strClass:replaceAll($input, 'hello', 'HELLO')"/>
</xsl:template>
</xsl:stylesheet>
]]>
</script>
</ul>
</ul>
Please note that any function of the String class can be used above and also any of the Java classes can be referenced in the similar way e.g. Math, DOMParser, Integer etc.<br />
<div>
<br /></div>
<div>
Once again Happy New Year and Happy Learning....</div>
</div>
Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com5tag:blogger.com,1999:blog-5083674265912730417.post-91042870406655858842012-06-10T09:36:00.001-07:002012-06-10T09:36:08.936-07:00MQ Adapter and SSL - Part 1<div dir="ltr" style="text-align: left;" trbidi="on">
Oracle MQ Series Adapter enables applications to connect to MQ Series queue managers and enqueue/dequeue messages to/from the Queues. MQ Series supports secure communication through the use of SSL. <b>SSL </b>stands for <b>Secure Socket Layer</b>. Oracle MQ Series Adapter can be easily configured to support SSL to secure the data-communication between Adapter and Server.<br />
<br />
The article further explains some general concepts of SSL on MQ Adapter and points how you can enable SSL on Oracle MQ Adapter. <br />
<br />
There are 2 types of SSL that can be configured on MQ Adapter:<br />
<br />
<div style="text-align: left;">
<b><u><span style="color: blue;">One-Way SSL:</span></u></b> </div>
In this SSL pattern, <b>only </b>the server gets authenticated. This ensures that the server to which MQ adapter is connecting is valid and correct. When MQ Adapter connects to MQ server, it requests the MQ server-certificate. On providing the certificate by MQ server, MQ Adapter verifies and matches it against the list of certificates available with it in its store. If the certificate match is found, the certificate is considered valid and the SSL connection is established.<br />
<br />
<b><u><span style="color: blue;">Two-Way SSL:</span></u></b><br />
In two-way SSL, <b>both </b>Client and Server are authenticated. The connection process is very similar to One-way SSL except that client is also authenticated in two-way SSL. When MQ Adapter connects to MQ server, it requests the server-certificate. MQ server provides the appropriate certificate. This certificate is verified by the MQ Adapter against the list of certificates available with it. After this certficate is validated by MQ Adapter (client), MQ server (server) then requests the client certificate to ensure that the requesting client is also valid. MQ Adapter, then, presents its certificate to MQ server which is verified by the server against the list of certificates available with it. If both the client and server certificates are found valid, SSL connection is established.<br />
<br />
<u>Two-way SSL is always enforced by the server</u><span style="color: #f1c232;">.</span> Hence, to enable two-way SSL on MQ Series, MQ Server needs to enforce Two-way SSL on incoming channel. On the server side, set the property <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Authentication of Parties initiating connection</span> to <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Required </span>as shown in the screenshot. This will enforce the clients connecting to it to present their certificates for validation before an SSL connection can be established.<br />
<br />
Also ensure that this channel must be of <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Server-connection </span>type. Contact your MQ Server Administrator to confirm this.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-pcJBHkrajr4/T7zx49bqVAI/AAAAAAAAAW4/Qz4Bo7xb2iY/s1600/Two-way+SSL+on+MQ+Server.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="242" src="http://1.bp.blogspot.com/-pcJBHkrajr4/T7zx49bqVAI/AAAAAAAAAW4/Qz4Bo7xb2iY/s400/Two-way+SSL+on+MQ+Server.JPG" width="400" /></a></div>
<br />
Further, to configure One-way or Two-way SSL on MQ Adapter and simultaneously keeping this post short and sweet, follow another post <a href="http://neeraj-soa-tips.blogspot.com/2012/06/enabling-ssl-on-mq-series-adapter-part.html" target="_blank">Enabling SSL on MQ Series Adapter - Part 2</a>.</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com1tag:blogger.com,1999:blog-5083674265912730417.post-18233972770687923932012-06-10T09:33:00.001-07:002012-06-10T09:38:44.199-07:00Enabling SSL on MQ Series Adapter - Part 2<div dir="ltr" style="text-align: left;" trbidi="on">
I've already explained the basic concepts in <a href="http://neeraj-soa-tips.blogspot.com/2012/06/mq-adapter-and-ssl-part-1.html" target="_blank">MQ Adapter and SSL - Part 1</a>. This post explains how you can configure your MQ Adapter to use One-way or Two-way SSL. To enable One-way or Two-way SSL on MQ Adapter, following steps need to be followed. I'm outlining the simplest possible steps. Once understood, they can be modified based on your security needs.<br />
<ul style="text-align: left;">
<li>Get the Public certificate <b>(.DER, .CER)</b> of the MQ Server Queue Manager. Ask your MQ server Admin to give this to you.</li>
<li>Import this certificate into default Weblogic Trust store or you custom keystore if any (all Public certs are stored here because Weblogic trusts the certs imported here) at <span style="color: blue; font-family: 'Courier New', Courier, monospace;"><WL_HOME>/server/lib/DemoTrust.jks</span><span style="color: purple; font-family: 'Courier New', Courier, monospace;"> </span>using the following command. You need the keytool utility to execute following commands.</li>
</ul>
<blockquote class="tr_bq">
<span style="color: purple; font-family: 'Courier New', Courier, monospace;">keytool -import -alias <choose_name_for_this_entry> -file <public_cert_received> -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase</span></blockquote>
<ul style="text-align: left;">
<li>Verify the imported certificate by running following command. Your entry should be present here. </li>
</ul>
<blockquote class="tr_bq">
<span style="color: purple; font-family: 'Courier New', Courier, monospace;">keytool -list -v -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase</span></blockquote>
<span style="text-align: left;"><i><span style="color: red;">Note: Below 3 steps are required only for configuring Two-way SSL on MQ Adapter. For One-way SSL, you must skip these steps.</span></i></span><br />
<div>
<span style="text-align: left;"><br /></span></div>
<div>
<span style="text-align: left;">For Two-way SSL, you need to provide Weblogic Public certificate to MQ server Admin team so that they can import it in MQ Server's Trust store. Follow the additional 2 steps for Two-way SSL.</span><span style="text-align: left;"><br /></span></div>
<div style="text-align: left;">
<blockquote class="tr_bq">
<span style="text-align: left;">1. Extract the PUBLIC certificate of your Weblogic from its identity and give this certficate to MQ Server team.</span></blockquote>
<blockquote class="tr_bq">
<span style="text-align: left;"><span style="color: purple; font-family: 'Courier New', Courier, monospace;">keytool -export -alias demoidentity -file WLS_PUBLIC_CERT.der -keystore <WL_HOME>\server\lib\DemoIdentity.jks -storepass DemoIdentityKeyStorePassPhrase</span></span></blockquote>
<blockquote class="tr_bq">
<span style="text-align: left;">2. Ask MQ server Admin to import the given certificate into the Server Trust store being used by the Queue Manager that you connect to. </span></blockquote>
<blockquote class="tr_bq">
3. E<span style="text-align: left;">nsure that the Private key and Identity Keystore passwords of your Keystore are same. Hence, change the keystore password to the Private key password by executing the following command.</span></blockquote>
<blockquote class="tr_bq">
<span style="text-align: left;"><span style="color: purple; font-family: 'Courier New', Courier, monospace;">keytool -storepasswd -new DemoIdentityPassPhrase -keystore <WL_HOME>\server\lib\DemoIdentity.jks</span></span></blockquote>
<ul style="text-align: left;">
<li>Next step is to create a simple BPEL process with MQ Adapter that connects to MQ using a JNDI configured on Weblogic Server. The key to configure SSL on MQ Adapter lies in this JNDI only. To create a JNDI for MQ Adapter,
Follow <a href="http://neeraj-soa-tips.blogspot.com/2012/06/how-to-create-jndi-for-mq-adapter-on.html" target="_blank">How to create MQ Adapter JNDI</a> . </li>
<li>Once the JNDI is configured successfully and tested with a dummy BPEL process, edit the following JNDI properties. Remember to hit <b>Enter</b> after updating each property.</li>
</ul>
<ul style="text-align: left;">
</ul>
<table border="1" cellpadding="0" cellspacing="0" rules="all" style="font-size: x-small; text-align: left;">
<tbody>
<tr style="border-width: thin;">
<th height="35" style="border-width: thin;" width="63"><div align="center">
<span style="color: #008484;">
S. No
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="184"><div align="center">
<span style="color: #008484;">
Property Name
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="203"><div align="center">
<span style="color: #008484;">
Property Value
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="796"><div style="text-align: left;">
<span style="color: #008484;">
Description
</span>
</div>
</th>
</tr>
<tr style="border-width: thin;">
<th style="border-width: thin;" width="63"><div align="center">
1
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
channelName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Channel Name>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
The name of the channel which is of server-conection type. Ask
your MQ Server Admin to provide with this detail. Your process
will connect to this channel.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
2
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
CipherSuite
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
SSL_RSA_EXPORT_WITH_ RC4_40_MD5
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Cipher suite that will be used for Message Encryption. Ensure that
this is same as CipherSpec set on the above channel. e.g. for
<a href="http://publib.boulder.ibm.com/infocenter/wmqv7/v7r1/index.jsp?topic=%2Fcom.ibm.mq.msc.doc%2Fxms_csecure_mapping_wpm.html">CipherSpec
to
be
set </a>on
Server
Connection
Channel
for
the
mentioned
CipherSuite
is
RC4_MD5_EXPORT
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
3
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
hostName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Host Name>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
The host name of the MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
4
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
KeyStoreLocation
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Keystore Location>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
For One-way SSL, specify this to the location of the keystore in
which you imported the Public certificate of the MQ Server (in our example DemoTrust.jks). This property will be same as TrustStoreLocation in One-way SSL.<br />
<br />
However, for
Two-way SSL, specify the location of Identity keystore (in our example DemoIdentity,jks)</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
5
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
KeyStorePassword
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Keystore Password>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Specify the password to access the above keystore
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
6
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
KeyStoreType
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
jks
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
By default this is Java Keystore (jks). Ensure you create the
Keystore of the .jks type.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
7
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
password
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Password to access MQ>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Specify the password to access the MQ Server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
8
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
portNumber
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<MQ Server Port>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Network port to connect to MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
9
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
Protocol
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
TLS
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
The algorigthm used to manage the Keys. Default Value is TLS. Keep
it as it is.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
10
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
queueManagerName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Queue Manager name on MQ Server>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Queue Manager provides access to the queues and also transfers
messages to other queue managers through message channels.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
11
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
SSLEnable
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
true
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Set this value to true to tell MQ Adapter to use SSL</div>
</td>
</tr>
<tr>
<th height="27" style="border-width: thin;" width="63"><div align="center">
12
</div>
</th>
<th height="27" style="border-width: thin;" width="184"><div align="center">
TrustStoreLocation
</div>
</th>
<td height="27" style="border-width: thin;" width="203"><div align="center">
<Keystore Location>
</div>
</td>
<td height="27" style="border-width: thin;" width="796"><div style="text-align: left;">
Provide the Trust keystore location (e.g. /dir/DemoTrust.jks) in which you imported the PUBLIC cert of the MQ Queue Manager.</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
13
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
TrustStorePassword
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Keystore Password>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Provide the password for Trust Store</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
14
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
userID
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<username to connect MQ>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
User Id to access MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
15
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
XATransaction
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
false
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
By default this is False
</div>
</td>
</tr>
</tbody></table>
<ul style="text-align: left;">
<li>Save the properties and Update the Adapter as given in create JNDI post.</li>
</ul>
And it's done. This will enable One-way SSL (or Two-way SSL) for your MQ Adapter communication.<br />
<b><u><br /></u></b></div>
<div style="text-align: left;">
<b><u>Troubleshooting:</u></b><br />
<ul style="text-align: left;">
<li>In case you encounter any errors, set the following <span style="color: blue; font-family: 'Courier New', Courier, monospace;">EXTRA_JAVA_PROPERTIES</span> in <span style="color: blue; font-family: 'Courier New', Courier, monospace;">setDomainEnv.sh</span> (or <span style="color: blue; font-family: 'Courier New', Courier, monospace;">setDomainEnv.cmd</span>) file and restart the servers.</li>
</ul>
<blockquote class="tr_bq">
<span style="color: purple; font-family: 'Courier New', Courier, monospace;">set EXTRA_JAVA_PROPERTIES=%EXTRA_JAVA_PROPERTIES% -Dssl.debug=true -Dweblogic.StdoutDebugEnabled=true -Dweblogic.security.SSL.verbose=true</span></blockquote>
<ul style="text-align: left;">
<li>This will generate verbose logs for SSL to help you diagnose the errors. See the below logs for diagnosis.</li>
</ul>
<blockquote class="tr_bq">
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"><DOMAIN_HOME>/servers/soa_server1/logs/soa_server1.log</span></blockquote>
<ul style="text-align: left;">
</ul>
Still if you encounter any problems, I'm happy to help!</div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com3tag:blogger.com,1999:blog-5083674265912730417.post-90777211067902488662012-06-10T09:32:00.000-07:002012-06-10T09:35:19.761-07:00How to create a JNDI for MQ Adapter on Weblogic<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
This post explains how to create a simple JNDI for MQ Adapter in Weblogic 10.3.x. This JNDI properties can be further changed to configure One-way or Two-way SSL on MQ Adapter as explained in post <a href="http://neeraj-soa-tips.blogspot.com/2012/06/enabling-ssl-on-mq-series-adapter-part.html" target="_blank">Enabling SSL on MQ Series Adapter - Part 2</a>.<br />
<ul style="text-align: left;">
<li>Login to Weblogic Server Administration Console and navigate to <b>Deployments > MQSeriesAdapter > Configuration Tab > Outbound Connection Pool</b>.</li>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-V2MBp7iytZ4/T7zWD2D3zJI/AAAAAAAAAWg/11hrsibJBQE/s1600/MQAdapterConfiguration.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="165" src="http://1.bp.blogspot.com/-V2MBp7iytZ4/T7zWD2D3zJI/AAAAAAAAAWg/11hrsibJBQE/s400/MQAdapterConfiguration.JPG" width="400" /></a></div>
<br />
<li>Click on <b>New</b> button and select <span style="color: blue; font-family: 'Courier New', Courier, monospace;">javax.resource.cci.ConnectionFactory</span> and click <b>Next</b>.</li>
<li>Give an appropriate JNDI name and press <b>Finish</b>. This JNDI name will be used by your MQ Adapter inside the BPEL process. If it asks to Create/Save a Deployment Plan, Create/Save a deployment plan for this JNDI reference.</li>
<li>Go back to the <b>MQSeriesAdapter > Configuration Tab > Outbound Connection Pool</b> and expand the connection factory
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">javax.resource.cci.ConnectionFactory</span>. Click on the JNDI created by you above.</li>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-VgrOyCoEz18/T7zaj1JqS4I/AAAAAAAAAWs/KtFw4iG9_no/s1600/AccesingJNDIProps.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="185" src="http://1.bp.blogspot.com/-VgrOyCoEz18/T7zaj1JqS4I/AAAAAAAAAWs/KtFw4iG9_no/s400/AccesingJNDIProps.JPG" width="400" /></a></div>
<br />
<li>Set the following properties for the above created JNDI. Leave the other properties to their default. Remember to hit <b>Enter </b>after updating each property. </li>
</ul>
<ul style="text-align: left;"> <br />
<table border="1" cellpadding="0" cellspacing="0" rules="all" style="font-size: small; text-align: left;">
<tbody>
<tr style="border-width: thin;">
<th height="35" style="border-width: thin;" width="63"><div align="center">
<span style="color: #008484;">
S. No
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="184"><div align="center">
<span style="color: #008484;">
Property Name
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="203"><div align="center">
<span style="color: #008484;">
Property Value
</span>
</div>
</th>
<th height="35" style="border-width: thin;" width="796"><div style="text-align: left;">
<span style="color: #008484;">
Description
</span>
</div>
</th>
</tr>
<tr style="border-width: thin;">
<th style="border-width: thin;" width="63"><div align="center">
1
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
channelName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Channel Name>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
The name of the channel which is of server-conection type. Ask
your MQ Server Admin to provide this detail. Your process
will connect to this channel.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
2
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
hostName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Host Name>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
The host name of the MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
3
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
password
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Password to access MQ>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Specify the password to access the MQ Server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
4
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
portNumber
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<MQ Server Port>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Network port to connect to MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
5
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
queueManagerName
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<Queue Manager name on MQ Server>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
Queue Manager provides access to the queues and also transfers
messages to other queue managers through message channels.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
6
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
SSLEnable
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
false
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
By default this value is false. As we're not using any SSL in this post,
leave this value as false.
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
7
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
userID
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
<username to connect MQ>
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
User Id to access MQ server
</div>
</td>
</tr>
<tr>
<th style="border-width: thin;" width="63"><div align="center">
8
</div>
</th>
<th style="border-width: thin;" width="184"><div align="center">
XATransaction
</div>
</th>
<td style="border-width: thin;" width="203"><div align="center">
false
</div>
</td>
<td style="border-width: thin;" width="796"><div style="text-align: left;">
By default this is False. Keep it false unless you want to
enable global transactions on this adapter.</div>
</td></tr>
</tbody></table>
<br />
</ul>
<ul style="text-align: left;">
<li>Save the properties by hitting the <b>Save </b>button at the bottom of the properties page.</li>
<li>Navigate back to <b>Deployments </b>and check <b>MQSeriesAdapter</b>. Now press <b>Update </b>button on the top.</li>
<li>On the next page, choose to <b>Redeploy this application using the following deployment files</b> and hit <b>Finish</b>.</li>
</ul>
After the JNDI is created successfully and properties are set as above, use this JNDI in a simple BPEL process to enqueue a message in MQ. If this is successful, JNDI configuration is correct.</div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com1tag:blogger.com,1999:blog-5083674265912730417.post-27896783674644410682012-05-21T09:31:00.000-07:002013-05-24T06:59:32.574-07:00Multiple IN parameters in DB Adapter Dynamic Query<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
I explained how to pass the multiple parameters to IN clause in Pure SQL <a href="http://neeraj-soa-tips.blogspot.com/2011/07/pass-multiple-parameters-dynamic-query.html" target="_blank">here</a>. However, the solution needed to assign the same input string to bind parameter <span style="color: blue; font-family: 'Courier New', Courier, monospace;">#val</span> 9 times. Later on while browsing Oracle Forums, I came across a more-refined query that would address this concern.<br />
<br />
To select employees with <span style="font-family: 'Courier New', Courier, monospace;">f_name</span> as <span style="font-family: 'Courier New', Courier, monospace;">'NEERAJ' or 'JACOB' or 'ROBERT'</span>, write your Pure SQL in DB Adapter like below.
<script class="brush: sql; " type="syntaxhighlighter">
<![CDATA[
SELECT *
FROM employees
WHERE f_name IN
(SELECT SUBSTR (DELIMITED_INPUT_STRING , DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) , INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL) - DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) )
FROM
(SELECT #InputString || #Delimiter1 AS DELIMITED_INPUT_STRING ,
#Delimiter2 AS DELIMITER
FROM DUAL
)
CONNECT BY INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL)>0
)
order by f_name
]]>
</script>
This would create DB schema automatically further to which you might need to specify the type of the bind parameters as <span style="color: blue; font-family: 'Courier New', Courier, monospace;">xs:string </span>in this schema.<br />
<br />
Now, invoke the DB Adapter and using the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Assign</span> activity , assign the bind parameter values in the above query as under:<br />
<br />
<span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;">#InputString</span> - Your Input String with delimiters (but no spaces) e.g. <b>'NEERAJ,JACOB,ROBERT'</b><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">#Delimiter1</span> - Assign the delimiter e.g. <b>','</b>. This is to suffix above string with same delimiter.<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">#Delimiter2</span> - Assign the same delimiter e.g. <b>','</b><br />
<br />
And that's it. Invoke your process and test it. Happy Learning....<br />
<br />
<div>
<br /></div>
</div>
Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com11tag:blogger.com,1999:blog-5083674265912730417.post-52743650447917553502012-05-01T06:24:00.000-07:002012-05-10T05:41:49.020-07:00Changing the Archive File Name in Inbound File/FTP Adapter<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h2 style="text-align: center;">
<b><u>Requirement</u>:</b></h2>
To customize the archive file name, remove the junk characters from the archived file name. The default format is as under.
<br />
<br />
SOA 10g - <span style="color: #76a5af; font-family: 'Courier New', Courier, monospace;">filename_yyyymmdd_hh24mmss</span><br />
SOA 11g - <span style="color: #76a5af; font-family: 'Courier New', Courier, monospace;">filename_encryptedToken_yyyymmdd_hh24mmss </span>(new format introduced with 11g)<br />
<br />
The possible customization is given in the solution below.<br />
<br />
<h2 style="text-align: center;">
<b><u>Solution:</u></b></h2>
<h4 style="text-align: left;">
<u>SOA 10g</u>: </h4>
<div style="text-align: left;">
You have a JCA property for FIle/FTP adapter, <span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseLongArchiveFileName</span>, which can be used to prefix the process name to the archive filename to make the format <span style="color: #76a5af; font-family: 'Courier New', Courier, monospace;">filename_processname_timestamp</span>.</div>
<br />
<h4 style="text-align: left;">
<u>SOA 11g (11.1.1.3/+)</u> </h4>
<div style="text-align: left;">
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseLongArchiveFileName</span> is not available in 11g. Apply Patch <b>10155914 </b>and it will become available. </div>
<br />
<h4 style="text-align: left;">
<u>SOA 11g (11.1.1.5/+)</u> </h4>
<div style="text-align: left;">
Apply Patch <b>13249896</b>.<br />
A new property
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseDigest </span>can be used now in addition to
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseLongArchiveFileName</span> (11g format as <span style="color: #76a5af; font-family: 'Courier New', Courier, monospace;">filename_processname_digest_timestamp</span>) with File/FTP adapter.<br />
By default <span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseDigest </span>is set to <span style="color: blue; font-family: 'Courier New', Courier, monospace;">true </span>which leads to the digest (encrypted token) being sandwiched in the filename and timestamp as <span style="color: #76a5af; font-family: 'Courier New', Courier, monospace;">filename_<b>digest</b>_timestamp</span>.<br />
Setting this property to <span style="color: blue; font-family: 'Courier New', Courier, monospace;">false </span>will change the format to filename_timetamp. To use this property edit the .jca file to include this property as given below.</div>
<script class="brush: xml" title="File_Adapter.jca" type="syntaxhighlighter">
<![CDATA[
...
<property name="UseDigest" value="false"/>
...]]>
</script>
<br />
<h4 style="text-align: left;">
<u>SOA 11g (11.1.1.7/+)</u></h4>
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">UseDigest </span>property will be available by default without the need to apply the above patch.<br />
<br />
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.</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com12tag:blogger.com,1999:blog-5083674265912730417.post-69058433862143301042012-04-10T08:27:00.000-07:002012-04-10T08:30:26.261-07:00Polling files on-demand : File Based Triggers<div dir="ltr" style="text-align: left;" trbidi="on">
Many a times there is a need to trigger the polling of File/FTP Adapter on the basis of some event e.g. after process A has finished writing the file F1, process B should poll for the file F1 OR just process the files only between 10.00 AM to 11.00 AM. While the above could be accomplished to some extent using <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Sync Read</span> operation, but it requires the knowledge of the File Name in advance (wild cards can't be used).<br />
<br />
Well, the solution is File-Based Triggers. File-based triggers can be used to control the File/FTP adapter activation. File Triggers are nothing but the files with <span style="color: blue; font-family: 'Courier New', Courier, monospace;">.trg</span> extension. The content of trigger file doesn't matter. When File-based triggers are used, File/FTP adapter checks for the existence of a Trigger file (e.g. <span style="color: blue; font-family: 'Courier New', Courier, monospace;">ReadFile.trg</span>) before reading the actual file. If the trigger file is found, it reads and processes the actual file else it does nothing. You can configure how frequently the adapter checks for the trigger file and hence can control the processing of the actual files.<br />
To use a File-Based trigger, follow the below steps.<br />
1.) Define a File/FTP adapter with <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Read </span>Operation.<br />
2.) On the File Polling page, Check the option <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Use trigger file</span> and provide the Trigger File details as shown below.
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-B4_YfJmxj_I/T4RE9cbdWZI/AAAAAAAAAWI/U_s_74YX70I/s1600/UseTriggerFile.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="250" src="http://1.bp.blogspot.com/-B4_YfJmxj_I/T4RE9cbdWZI/AAAAAAAAAWI/U_s_74YX70I/s320/UseTriggerFile.JPG" width="320" /></a></div>
<br />
3.) Complete the adapter wizard.<br />
4.) Follow the normal steps to create a bpel process to poll the file. Deploy this process.<br />
<br />
As soon as the process is deployed, File/FTP adapter looks for the trigger file in the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">C:/TriggerFileDir</span> location. If it finds the triger file, it will poll
and process the file. How frequently it looks for the trigger file depends on the property <span style="color: blue; font-family: 'Courier New', Courier, monospace;">TriggerFileStrategy </span>(we didn't specify this property anywhere
because the property is set already by default to value <span style="color: blue; font-family: 'Courier New', Courier, monospace;">EndpointActivation</span>).<br />
<br />
The various possible values for <span style="color: blue; font-family: 'Courier New', Courier, monospace;">TriggerFileStrategy </span>are:<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"><u>OnceOnly</u></span>: The adapter will looks for the trigger file only once in its lifetime. Once it finds the trigger file, it remembers that across restarts and
redeployments. This could be very risky as the adapter would never look for a trigger file again in future.<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"></span><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"><u>EveryTime</u></span>: The adapter looks for the trigger file everytime it goes for polling the file. At every polling interval, it checks if the trigger file is there
or not. If the trigger file is present, it picks the actual file else it will do nothing and will check for the existence of trigger file at next polling
cycle.<br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"><u></u></span><br />
<span style="color: blue; font-family: 'Courier New', Courier, monospace;"><u>EndpointActivation </u></span>(Default): The adapter looks for the trigger file every time the composite is activated that means everytime you Restart/Activate/Redeploy
your composite.<br />
<br />
Add the property in <span style="color: blue; font-family: 'Courier New', Courier, monospace;">.jca</span> file of your File Adapter as shown below and it's done.<br />
<br />
<script class="brush: xml; highlight: 8" title="PollFile_file.jca" type="syntaxhighlighter">
<![CDATA[
<activation-spec className="oracle.tip.adapter.file.inbound.FileActivationSpec">
<property name="DeleteFile" value="true"/>
<property name="MinimumAge" value="0"/>
<property name="TriggerFileLogicalDirectory" value="FileTriggerDir"/>
<property name="PollingFrequency" value="10"/>
<property name="LogicalDirectory" value="InputDirectory"/>
<property name="TriggerFile" value="ReadFile.trg"/>
<property name="TriggerFileStrategy" value="EveryTime"/>
<property name="IncludeFiles" value=".*\.txt"/>
<property name="UseHeaders" value="false"/>
</activation-spec>
]]>
</script><i><b><u>
Note</u></b>: You may want to delete the Trigger file after your file has been successfully processed. For this, there is no Delete operation. But you can use <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Sync
Read </span>operation to read the trigger file and check <span style="color: blue; font-family: 'Courier New', Courier, monospace;">Delete Files after successful retrieval</span> option.</i><br />
<br />
Happy to hear back if any suggestions or comments!
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com8tag:blogger.com,1999:blog-5083674265912730417.post-65813850589617631502012-01-22T13:36:00.000-08:002012-01-22T13:36:57.936-08:00Sending attachment using Email Activity in BPEL 11g<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
Sending an attachment using Email Activity (in SOA 11.1.1.5) is an easy task. All you have to do is drag and drop an email activity and double click on it. Assign the following parameters and the attachment will be sent along with the email.</div>
<div>
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">Name</span>- Name of the attachment as it should appear</div>
<div>
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">MimeType</span>- This MimeType is for attachment. Choose appropriate MimeType from <a href="http://kb.mediatemple.net/questions/151/%28gs%29+MIME+Types#/list-of-mime-types" target="_blank">here</a>.</div>
<div>
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">Value</span> - Write what you would like to see in the attachment. Simply hard coding.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-6ZUH13eTgUE/Txx5HKq9-YI/AAAAAAAAAVs/EowLwCbm28g/s1600/EmailActivity.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="100" src="http://2.bp.blogspot.com/-6ZUH13eTgUE/Txx5HKq9-YI/AAAAAAAAAVs/EowLwCbm28g/s200/EmailActivity.JPG" width="200" /></a></div>
<div>
<br /></div>
<div>
Well, sometimes the requirement may be to send some custom data, may be you fetched some data at runtime from the DB and would lke to send it as attachment. This isn't complex either, all you have to do is modify the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">ContentBody</span> parameter as follows. <i>You can do this successfully from the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">EmailParamsAssign</span> Activity but don't forget to verify the same in the bpel source too.</i></div>
<script class="brush: xml; highlight: 2" type="syntaxhighlighter">
<![CDATA[
<copy>
<from expression="string(bpws:getVariableData('inputVariable','payload','/client:process/client:input'))"/>
<to variable="varNotificationReq" part="EmailPayload"
query="/EmailPayload/ns1:Content/ns1:ContentBody/ns1:MultiPart/ns1:BodyPart[2]/ns1:ContentBody"/>
</copy>
]]>
</script><br />
<div>
<br />
Extending this a bit further, if you'd like to send some files (<span style="color: blue; font-family: 'Courier New', Courier, monospace;">image,pdf,doc,xls,csv</span>) that are already stored at some server location or you may have written them from your process, follow these steps.</div>
<div>
1.) Set the appropriate <span style="color: blue; font-family: 'Courier New', Courier, monospace;">MimeType </span>for the file.</div>
<div>
2.) Add the following line in the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">.bpel</span> source under appropriate <span style="color: blue; font-family: 'Courier New', Courier, monospace;">BodyPart</span>.</div>
<script class="brush: xml; highlight: 14" type="syntaxhighlighter">
<![CDATA[
<content xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">
<mimetype xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">multipart/mixed</MimeType>
<contentbody xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">
<multipart xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">
<bodypart xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">
<mimetype xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
<contentbody xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
<bodypartname xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
</BodyPart>
<bodypart xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService">
<mimetype xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
<contentbody xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
<bodypartname xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
<contentencoding xmlns="http://xmlns.oracle.com/ias/pcbpel/NotificationService"/>
</BodyPart>
</MultiPart>
</ContentBody>
</Content>
]]>
</script>
<br />
<div>
3.) Add another <span style="color: blue; font-family: 'Courier New', Courier, monospace;">copy </span>operation in the bpel source to assign <span style="color: blue; font-family: 'Courier New', Courier, monospace;">string('base64')</span> to <span style="color: blue; font-family: 'Courier New', Courier, monospace;">ContentEncoding</span> in BodyPart[2].</div>
<script class="brush: xml" type="syntaxhighlighter">
<![CDATA[
<copy>
<from expression="string('base64')"/>
<to variable="varNotificationReq" part="EmailPayload"
query="/EmailPayload/ns1:Content/ns1:ContentBody/ns1:MultiPart/ns1:BodyPart[2]/ns1:ContentEncoding"/>
</copy>
]]>
</script>
<br />
<div>
4.) Modify the
<span style="color: blue; font-family: 'Courier New', Courier, monospace;">ContentBody</span> parameter to read the file at runtime using <span style="color: blue; font-family: 'Courier New', Courier, monospace;">ora:readFile()</span> function as follows.</div>
<script class="brush: xml; highlight: 2" type="syntaxhighlighter">
<![CDATA[
<copy>
<from expression="ora:readFile('file:///<ABSOLUTE_FILE_PATH>/FILE_NAME.EXTN')"/>
<to variable="varNotificationReq" part="EmailPayload"
query="/EmailPayload/ns1:Content/ns1:ContentBody/ns1:MultiPart/ns1:BodyPart[2]/ns1:ContentBody"/>
</copy>
]]>
</script>
<br />
<div>
<br /></div>
<div>
<b><u>Key Points to Note:</u></b><br />
<br />
<ul style="text-align: left;">
<li>The file location and name in <span style="color: blue; font-family: 'Courier New', Courier, monospace;">ora:readFile()</span> above are <b>case-sensitive</b>.</li>
<li>Specifying the correct <span style="color: blue; font-family: 'Courier New', Courier, monospace;">MimeType</span> is very important. If the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">MimeType </span>is incorrect, either the mail won't go or you may get encrypted characters in the attached file.</li>
<li><span style="color: blue; font-family: 'Courier New', Courier, monospace;">file:///</span> (three slashes mean absolute path and two slashes mean relative path)</li>
</ul>
<br />
The above solution works with all the files. Should anyone need the working project, I can mail the same. </div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com53tag:blogger.com,1999:blog-5083674265912730417.post-37419854858755673072012-01-22T12:45:00.000-08:002012-02-23T04:32:24.820-08:00Inserting a New Line character in message using BPEL XSLT<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
In a requirement, we were trying to format the data fetched from DB (in XML format in BPEL) into a <span style="color: blue; font-family: 'Courier New', Courier, monospace;">String</span> variable with a new line inserted after each record. So that the data below
<script class="brush: xml" title="Source XML" type="syntaxhighlighter">
<![CDATA[
<selectfromlookupoutputcollection>
<selectfromlookupoutput>
<property_name>Colour</property_name>
<property_value>RED</property_value>
</SelectFromLookupOutput>
<selectfromlookupoutput>
<property_name>Email</property_name>
<property_value>ABC.XYZ@PQR.COM</property_value>
</SelectFromLookupOutput>
<selectfromlookupoutput>
<property_name>Technology</property_name>
<property_value>ORACLE</property_value>
</SelectFromLookupOutput>
</SelectFromLookupOutputCollection>
]]>
</script>
would appear in the <span style="color: blue; font-family: 'Courier New', Courier, monospace;">String </span>variable like
<script class="brush: xml" title="Target String" type="syntaxhighlighter">
<![CDATA[
Colour,RED
Email,ABC.XYZ@PQR.COM
Technology,ORACLE
]]>
</script>
Following is the simple transformation to achieve the same.<br/><br/><b>
<xsl:for-each select="/ns0:SelectFromLookupOutputCollection/ns0:SelectFromLookupOutput"><br/>
<xsl:value-of select='concat(ns0:property_name, ",", ns0:property_value,"&#10;")'/><br/>
</xsl:for-each><br/><br/></b>
<b><u>
Key Points to Note:</u></b><br />
<br />
<ul style="text-align: left;">
<li>The magic lies in the word <span style="color: blue; font-family: 'Courier New', Courier, monospace;">&#10;</span> which serves as a new line character in XSLT (11g). In above case, it gets appended at the end of each record resulting in new line at the end of each record.</li>
<li>The function somehow doesn't work in SOA Suite 11.1.1.3 (BUG-13602524, thanks to my colleague Nivea for identifying this) but works in 11.1.1.4 and older versions.</li>
</ul>
<br />
<div>
<br /></div>
</div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com2tag:blogger.com,1999:blog-5083674265912730417.post-13495008504370402452012-01-17T02:19:00.000-08:002012-01-17T02:23:48.696-08:00How to pass JNDI name dynamically to an Adapter<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: 'Trebuchet MS', sans-serif;">Recently, I've been trying configure an MQ adapter that could enqueue data of any structure into <a href="http://neeraj-soa-tips.blogspot.com/2011/10/how-to-pass-queue-name-dynamically-to.html" target="_blank">specified queue</a> based on the specified JNDI, all passed dynamically at runtime. This post decsribes how you can pass the JNDI Name dynamically to an Adapter.</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br />All you have to do is define a </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">String </span><span style="font-family: 'Trebuchet MS', sans-serif;">variable (say </span><span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;">myJndiName</span><span style="font-family: 'Trebuchet MS', sans-serif;">) that stores your JNDI name (</span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">eis/MQ/someThing</span><span style="font-family: 'Trebuchet MS', sans-serif;">...already configured on Weblogic) and pass this variable into </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">Invoke </span><span style="font-family: 'Trebuchet MS', sans-serif;">Activity properties directly in the </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">.bpel </span><span style="font-family: 'Trebuchet MS', sans-serif;">source.</span><span style="font-family: 'Trebuchet MS', sans-serif;"> </span><span style="background-color: white; color: blue; font-family: 'Courier New', Courier, monospace;"> </span><span style="font-family: 'Trebuchet MS', sans-serif;"> </span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /><i>Note that you'll <b>not</b> be able to find this property in the </i></span><i><span style="color: blue; font-family: 'Courier New', Courier, monospace;">Invoke Activity > Properties</span><span style="font-family: 'Trebuchet MS', sans-serif;"> tab.</span></i><br />
<script class="brush: xml" title="For BPEL 1.1" type="syntaxhighlighter">
<![CDATA[
<invoke name="Invoke_1"
inputVariable="Invoke_1_Enqueue_InputVariable"
partnerLink="PutInMQ" portType="ns1:Enqueue_ptt"
operation="Enqueue">
<bpelx:inputProperty name="jca.jndi" variable="myJndiName"/>
</invoke>
]]>
</script>
<br />
<script class="brush: xml" title="For BPEL 2.0" type="syntaxhighlighter">
<![CDATA[
<invoke name="Invoke1" partnerLink="PutInMQ"
portType="ns1:Enqueue_ptt"
operation="Enqueue"
inputVariable="Invoke1_Enqueue_InputVariable"
bpelx:invokeAsDetail="no">
<bpelx:toProperties>
<bpelx:toProperty name="jca.jndi" variable="myJndiName"/>
</bpelx:toProperties>
</invoke>
]]>
</script>
<br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><span style="font-family: 'Trebuchet MS', sans-serif;">And that's it. You can assign different values on the fly to the variable </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">myJndiName </span><span style="font-family: 'Trebuchet MS', sans-serif;">and same adapter can enqueue in different JNDI locations. It also works for FTP and JMS adapters too. </span></span></div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com7tag:blogger.com,1999:blog-5083674265912730417.post-51068234396053026212011-10-07T08:38:00.000-07:002012-01-17T02:21:19.959-08:00How to pass the queue name dynamically to MQ Adapter<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Requirement:</u></b> To pass the queue name dynamically to MQ Adapter from a variable</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u><br /></u></b></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Use-case:</u></b> I have a process that is required to enqueue the data in one of the two MQs at runtime based on certain condition using single MQ Adapter. So if the condition is true the data is to be enqueued in <span class="Apple-style-span" style="color: blue;">queue A</span> else in <span class="Apple-style-span" style="color: blue;">queue B</span>. However, the payload schema remains the same in both the queues. <i>(using SOA Suite 11.1.1.3)</i></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u><br /></u></b></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Solution:</u></b> Use the </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">jca.mq.ISpec.EnqueueMsgToQ</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> property appearing in the </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Properties </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">tab of </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Invoke </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Activity. Assign the value of the property to the variable name which contains the MQ name.</span><br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://4.bp.blogspot.com/-65HxqrjiWb4/To8bcLS6Q5I/AAAAAAAAAVM/0-s43ncPaOs/s1600/QueueNameDynamically.JPG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="304" src="http://4.bp.blogspot.com/-65HxqrjiWb4/To8bcLS6Q5I/AAAAAAAAAVM/0-s43ncPaOs/s320/QueueNameDynamically.JPG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Passing Queue Name from a variable</span></td></tr>
</tbody></table>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Setting the value to this variable will allow you to set the queue name dynamically in the process or from outside the process.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u><br /></u></b></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Advantage:</u></b> </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">MQ Adapter can be loosely coupled and kept in a separate composite which can be called by any caller composite. Also a s</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">ingle MQ Adapter can be used for en-queuing to multiple queues. </span></div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com4tag:blogger.com,1999:blog-5083674265912730417.post-15450867316447122002011-07-13T08:33:00.000-07:002013-05-24T06:58:55.955-07:00Pass multiple parameters, dynamic query in Pure SQL in Database Adapter<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">I came across a case in which I was required to pass the bind parameters values on the fly. The values of parameters were not fixed and hence I couldn't define all of them as the bind parameters. Hence in the search of the solution I wasted considerable time until I was directed to see this great solution. The solution was fantastic and I customized it further to make it work with SOA Suite11g. This can be further extended with Joins to do wonders as required. </span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><br />
</b></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>Use Case :</b> I want the salaries of all the employees whose first names I'd pass at run time. Hence it can be <b>single </b>name or <b>n </b>names coming as parameters. The very simple query, as it comes to our mind, would be</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT SALARY FROM EMPLOYEES WHERE F_NAME IN (?)</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Where </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">?</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> is the argument that is passed dynamically. </span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">However, the same query doesn't work in DB adapter for any combination passed dynamically such as </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">('NEERAJ', 'JACOB')</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> or </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">"('NEERAJ', 'JACOB')"</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> or </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'NEERAJ', 'JACOB'</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> for the adapter tries to interpret the incoming argument as a bind parameter and not SQL string.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<b style="font-family: 'Trebuchet MS', sans-serif;">Solution :</b><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> If the parameters can be passed as a result of an inner query the problem can be solved. The following query uses </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">CONNECT BY LEVEL</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> clause to separate the individual parameter values separated by a <b><span class="Apple-style-span" style="color: blue;">: </span></b>and represents them as individual result row.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT SALARY FROM EMPLOYEES WHERE F_NAME </span></div>
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">IN<span class="Apple-style-span" style="line-height: 18px;"> (WITH BIND_PARAM_LIST AS</span></span></div>
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">(SELECT #val FROM dual)<br />
SELECT SUBSTR(#val1, (decode(LEVEL, 1, 0, instr(#val2, ':', 1, LEVEL -1)) + 1), (decode(instr(#val3, ':', 1, LEVEL) -1, -1, LENGTH(#val4), instr(#val5, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(#val6, ':', 1, LEVEL -1)) + 1) + 1) a<br />
FROM </span></span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace; line-height: 18px;">BIND_PARAM_LIST </span><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">CONNECT BY LEVEL <=<br />
(SELECT(LENGTH(#val7) -LENGTH(REPLACE(#val8, ':', NULL)))<br />
FROM </span></span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace; line-height: 18px;">BIND_PARAM_LIST</span><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">) + 1)</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">To understand the query one needs to be familiar with the </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">CONNECT BY</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> clause which is used for hierarchical queries. If you simply do </span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">, it prints 1 to 5 in each row like</span></span><br />
<br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">LEVEL</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">=====</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">1</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">2</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">3</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">4</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">5</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">The same logic is being used in the above query to separate the various </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">F_Names</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. It just sub-strings the incoming string </span></span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace; line-height: 18px;">NEERAJ:JACOB:ROBERT,</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif; line-height: 18px;"> <span class="Apple-style-span" style="color: blue;">: </span>wise for each LEVEL to feed the result in the IN clause as </span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="line-height: 18px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">NEERAJ</span></span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">JACOB</span></span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">ROBERT</span></span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="line-height: 18px;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><b style="font-family: 'Trebuchet MS', sans-serif;">Usage :</b><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> Just add this query in the DB Adapter. Be careful to use </span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;"><b># </b></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">instead of </span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;"><b>? </b></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">as in 11g the bind variable name can be defined as </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">#bind_variable</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">, in our case </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">#val1, </span></span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace; line-height: 18px;">#val2 ... </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace; line-height: 18px;">#val8.</span><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> Also you'll have to assign the same input string to these multiple </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">val</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">s. The val variables get exposed as input parameters while assigning and can be assigned as </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">'NEERAJ:JACOB:ROBERT'(your parameter list)</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. You can use any other separator too e.g. ; instead of : by customizing the query a bit. </span></span><br />
<br />
<i><span style="color: red;">Note:The above solution requires to assign the same value to bind parameter <span style="font-family: 'Courier New', Courier, monospace;">#val</span> 9 times. This limitation is addressed by a more-refined Query as suggested <a href="http://neeraj-soa-tips.blogspot.com/2012/05/multiple-in-parameters-in-db-adapter.html" target="_blank">here</a>.</span></i><br />
<div>
<br /></div>
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Just deploy the project and it is done. The same thing can also be achieved by using </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">query-database()</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> function as described <a href="http://neeraj-soa-tips.blogspot.com/2011/04/getting-xml-output-from-oraextquery.html">here</a>.</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></div>
Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com18tag:blogger.com,1999:blog-5083674265912730417.post-17673710933081856812011-06-27T10:00:00.000-07:002012-05-15T09:22:25.197-07:00Delete Data from MDS - Single Files<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">In case an artefact is wrongly published into MDS, it can be deleted from the MDS using 2 ways.</span><br />
<ol style="text-align: left;">
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><a href="http://forums.oracle.com/forums/thread.jspa?threadID=1260814">Deleting the entire folder</a></span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Deleting the Selective Files</span></li>
</ol>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">This post details about the latter.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">A file can be deleted from the MDS using Weblogic Scripting Tool. However, in upgraded versions of AIA, this may be available through </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">UpdateMetaDataDP.xml</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> also, very much the same way as we update the artefacts in MDS. </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">To delete a file from MDS follow the following steps.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>1.</b> Run WLST command from </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"><</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">MW_HOME>/oracle_</span><mw_home><oracle_common_home><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">common/bin </span><span class="Apple-style-span" style="font-family: inherit;">or </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"><SOA_HOME/common/bin></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. Note there is same command available in Weblogic Server home directory too but MDS related commands can be accessed through </span></oracle_common_home></mw_home><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">wlst.sh/exe </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">residing in above locations only </span><br />
<div>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">$ cd $MW_HOME/oracle_common/common/bin</span><br />
<div>
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> $ wlst.sh</span><br />
<br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b> 2.</b> Type the connect command to connect to</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Admin Server </span></div>
<div>
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> $ connect('weblogic', 'welcome1', 't3://localhost:7001')</span> <br />
<br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b> 3.</b> Once it is connected, delete the undesired file by typing the following command (Note the path starting from </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">/apps</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"> $ deleteMetadata(application='soa-infra',server='soa_server1',docs='/apps/AIAMetaData/AIAComponents/ApplicationConnectorServiceLibrary/Generic.wsdl')</span><br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">You can use asterisk(*) to specify more than one files e.g. to delete all files with names starting with Gen use </span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">deleteMetadata(application='soa-infra',server='soa_server1',docs='/apps/AIAMetaData/AIAComponents/ApplicationConnectorServiceLibrary/Gen*.wsdl')</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Exception:</u></b></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">You may get the following exception</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><i>MDS-91002: MDS Application runtime MBean for "soa-infra" is not available. "deleteMetadata" operation failure</i>.</span><br />
<div style="text-align: left;">
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Solution:</u></b></span></div>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">- Verify that your </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">soa-infra</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> is up and running and is on the correct server (Admin, soa_server1) as specified in the deleteMetadata() command </span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">- Verify that you've not fired the command to connect to other managed server during the steps to delete the file. If you connected firstly to SOA server, then to Admin Server and now you try to fire deleteMetadata(), it will give above error. Hence start again.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Happy Learning...</span></div>
</div>
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com1tag:blogger.com,1999:blog-5083674265912730417.post-22138867985530734532011-05-10T14:43:00.000-07:002011-06-28T06:49:43.652-07:00Weblogic and MS SQL Configurations to run projects using SQL Server Connections<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><a href="http://neeraj-soa-tips.blogspot.com/2011/03/how-to-connect-to-mysql-server-through.html">Creating </a>the SQL Server connection from Jdeveloper is easy. However, after the project is developed, it has to be deployed on the Weblogic Server, a JNDI needs to be created to connect to the MS SQL Server. Oracle has provided 2 default drivers for MS SQL Server.</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"></span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;">*Oracle's MS SQL Server Driver (Type 4 XA) Versions:7.0, 2000, 2005, 2008</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;"></span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;">*Oracle's MS SQL Server Driver (Type 4) Versions:7.0, 2000, 2005, 2008</span><br />
<div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"></span><br />
<div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">The difference between 2 drivers is that one is Transaction Aware and other one is not. There are other drivers also present but they would need specific jars to be placed in the server lib directory, but default drivers would work without any additional configuration on the Weblogic Server. Hence a JNDI to connect to MS SQL Server can be created using any of these drivers very much the same way as you create for Oracle Database.</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">However, following MS SQL Server configurations also need to be done to be able to connect to MS SQL Server from Weblogic Server.</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Create the JTA Procedures</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Follow the steps as given <a href="http://download.oracle.com/docs/cd/E12840_01/wls/docs103/jdbc_drivers/mssqlserver.html#wp1075232">here</a>.</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></span></div><div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Also if you are using the XA driver, you also need to enable XA Transactions on MS SQL Server. Follow the following steps for that. </span></span></div><br />
<ol style="text-align: left;"><li style="font-family: 'Trebuchet MS', sans-serif;"><b>Turn on support for XA transactions: </b></li>
<ul><li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Go to </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Control Panel > Administrative tools > Component Services</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">.</span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Expand the tree view to locate the computer where you want to turn on support for XA transactions: for example, </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Component Services > Computers > My Computer</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. </span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Right-click the computer name, then click </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Properties</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. </span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Click the </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">MSDTC </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">tab, and then click </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Security Configuration.</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Under </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Security Settings</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">, click the check box for XA Transactions to turn on this support. (</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Enable XA Transactions</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">) </span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Click </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">OK</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">, and then click </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">OK </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">again. </span></li>
</ul><li style="font-family: 'Trebuchet MS', sans-serif;"><b>Create a registry named-value: </b></li>
<ul><li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Use Registry Editor and navigate to registry key: </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. </span></li>
<li style="font-family: 'Trebuchet MS', sans-serif;">Create a new registry named-value:</li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Name is the file name of the </span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;">XA DLL </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">(in the format </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">dllname.dll</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">).</span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Type is </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">String (REG_SZ)</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">.</span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Value is the full path name (including the file name) of the DLL file. for example, </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">C:\Program Files\Microsoft SQLServer\ MSSQL.1\ MSSQL\ Binn\ sqljdbc.dll</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. </span></li>
</ul><li><b><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Go to </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Control Panel ->Administrative tools->Services</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. </span></b></li>
<ul><li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> Start "</span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">Distributed Transaction Coordinator</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">."</span></li>
</ul><li style="font-family: 'Trebuchet MS', sans-serif;"><b>Please restart the server to make these changes take effect.</b></li>
</ol><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"></span><br />
<div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b><u>Note:</u></b></span></span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">While making the JNDI for data sources on WLS Console, specify the value of property </span><a href="http://download.oracle.com/docs/cd/E12839_01/integration.1111/e10231/adptr_db.htm#BABCDGJG"><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">platformClassName</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></a><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">as </span><span class="Apple-style-span" style="font-family: monospace; font-size: 13px; white-space: pre;"><span class="Apple-style-span" style="color: blue;">oracle.toplink.platform.database.SQLServerPlatform.</span></span><br />
<br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">And that's all. Happy Development!</span></span></div></div></div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com7tag:blogger.com,1999:blog-5083674265912730417.post-62463454103998869612011-04-05T02:57:00.000-07:002011-04-08T15:05:03.214-07:00How to check the Email Notification History in SOA Suite 11g FMW Console<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>Problem :</b> I want to check email notification history in SOA Suite 11g FMW Console. When was an email sent, to whom and from whom and blaw blaw blaw.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>Solution :</b> Just go to <span class="Apple-style-span" style="color: blue;">FMW Console > Farm_Domain > User Messaging Service > Right Click usermessagingserver(soa_server1) > Message Status</span>. It shows you the email notifications details. You can use the search criteria also to filter the data.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-22vAmGmr5UQ/TZrnFurTVbI/AAAAAAAAASc/nLE2BgdWkQw/s1600/Email+History.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="170" src="http://1.bp.blogspot.com/-22vAmGmr5UQ/TZrnFurTVbI/AAAAAAAAASc/nLE2BgdWkQw/s320/Email+History.JPG" width="320" /></a></div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com1tag:blogger.com,1999:blog-5083674265912730417.post-30172933332923365402011-04-05T02:40:00.000-07:002012-04-18T08:46:21.019-07:00How to get XML output from the oraext:query-database() function<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">oraext:query-database()</span></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> is a wonderful function that can be used to</span><br />
<ol style="text-align: left;">
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Fetch the data in the Assigns/Transformations on the fly without the use of DB Adapter</span></li>
<li><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Can be used to run any Dynamic Custom SQL including variable IN parameters and GROUP BY clauses.</span></li>
</ol>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">However, the output returned from this function is always a long string depending on the no.of resultant records. But we can always transform the same into XML format to traverse it in an easier way across the process.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>1.</b> While calling the function, use the 2nd and 3rd parameters as </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">true()</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> rather than </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">false()</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. It will ensure that </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">ROWSET </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">and </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">ROW </span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">tags get generated in the resultant String.</span><br />
<br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><b>2.</b> Now create a schema with ROWSET as root element and ROW as unbounded repeating element. The best way to do this is to copy the resultant XML string into an XML file and use </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">Schema from XML file</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> option to create the schema. This will create the schema for your resultant output string. Now you can create a variable of this schema to store the returned string in the XML format. </span><br />
<i><span class="Apple-style-span" style="color: red; font-family: 'Trebuchet MS', sans-serif;">Note: The returned string from </span><span style="color: blue; font-family: 'Courier New', Courier, monospace;">query-database()</span><span class="Apple-style-span" style="color: red; font-family: 'Trebuchet MS', sans-serif;"> function does not contain any namespace, whereas the schema created above would contain a namespace. Hence remove the following attributes explicitly from your schema so created. It is also advisable to validate/create your schema using tools like XMLSpy etc. for better confidence.</span></i><br />
<script class="brush: xml; " type="syntaxhighlighter">
<![CDATA[
xmlns="http://www.example.org"
targetNamespace="http://www.example.org"
elementFormDefault="qualified">
]]>
</script>
<br />
<b style="font-family: 'Trebuchet MS', sans-serif;">3.</b><span style="font-family: 'Trebuchet MS', sans-serif;"> Now in the </span><span class="Apple-style-span" style="color: blue; font-family: 'Trebuchet MS', sans-serif;">Assign </span><span style="font-family: 'Trebuchet MS', sans-serif;">activity use this function as follows to assign the result to Schema element just created in above step. You can use any different query or the variable that contains your query in the below example.</span>
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">oraext:query-database('select first_name, last_name from employees', true(),true(),'jdbc/EBS_BOLINF')
</span><br />
<span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;"><br /></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-am-Z8F4FHa8/TZrYw2-q8LI/AAAAAAAAASY/mcaTztPIriI/s1600/query-database.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-05a1M4GvzOc/T47gLUHze9I/AAAAAAAAAWQ/7_2AZM99ce4/s1600/query-database.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="200" src="http://3.bp.blogspot.com/-05a1M4GvzOc/T47gLUHze9I/AAAAAAAAAWQ/7_2AZM99ce4/s320/query-database.JPG" width="320" /></a></div>
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">And you get the result in the XML format in the <span class="Apple-style-span" style="color: blue;">TempEmpResults </span>variable that you can use anywhere in the process!</span><br />
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com10tag:blogger.com,1999:blog-5083674265912730417.post-44104532801878573912011-03-03T04:55:00.000-08:002011-05-10T14:44:11.947-07:00How to connect to MS SQL Server through Jdeveloper 11.1.1.3<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">To connect to MS SQL Server you need to follow the following steps.</span><br />
<b style="font-family: 'Trebuchet MS', sans-serif;"><br />
</b><br />
<b style="font-family: 'Trebuchet MS', sans-serif;">Pre-requisite:</b><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> You need to have a </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">sqljdbc4.jar</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> file placed in the </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">jdev/lib/</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">folder</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. It can be downloaded from <a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707">this</a> (1033\sqljdbc_3.0.1301.101_enu.tar.gz) path and extracted to get </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">sqljdbc4.jar</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. Finally it needs to be at </span><span class="Apple-style-span" style="color: blue; font-family: 'Courier New', Courier, monospace;">jdev/lib</span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> location.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">1. Go to New Database Connection and create a Database Connection by providing all the required details. Remember to select <span class="Apple-style-span" style="color: blue;">SQLServer </span>as <span class="Apple-style-span" style="color: blue;">Connection Type</span>.</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">2. In the Library section click Browse and add the Library entry as shown below. </span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSJJJ6SW22reNotuNl1z_jBofJ-JfEm42Vh4BozcZoqrZ6PpIqFe0AjmAdGQjRQhDAslWueVEjFAv1NuvzERvVow98oZQIr_74LAfI2iYmDnhuWpCMiCBRA8jO8o6me12qOhwgjy-scZL7/s1600/Add+New+Entry.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSJJJ6SW22reNotuNl1z_jBofJ-JfEm42Vh4BozcZoqrZ6PpIqFe0AjmAdGQjRQhDAslWueVEjFAv1NuvzERvVow98oZQIr_74LAfI2iYmDnhuWpCMiCBRA8jO8o6me12qOhwgjy-scZL7/s400/Add+New+Entry.JPG" width="400" /></a></div><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">3. Click on Test and it says <b>Success</b>....</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">However, for the projects using MS SQL Server connection deployed on Weblogic server, some Weblogic and MS SQL Server configurations also need to be done that can be found <a href="http://neeraj-soa-tips.blogspot.com/2011/05/weblogic-and-ms-sql-configurations-to.html">here</a>.</span><br />
<br />
</div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com5tag:blogger.com,1999:blog-5083674265912730417.post-88284331212274340522010-11-28T02:43:00.000-08:002010-11-28T02:43:18.411-08:00Validating incoming String of Date format using Regular Expression<div class="MsoNormal"><span style="font-size: x-small;"><span style="font-size: 10pt;"><span class="Apple-style-span" style="font-family: Tahoma;">I was recently trying to restrict the format of the incoming variable so as to adhere it to the date format like </span><b style="font-family: Tahoma;">27-Nov-2010 04:17:37 PM</b><span class="Apple-style-span" style="font-family: Tahoma;">. Well, this had to be pretty simple by using the </span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><b>xp20:matches</b></span><span class="Apple-style-span" style="font-family: Tahoma;"> function that can be used to match a string pattern against a regular expression. I used my function as <o:p></o:p></span></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="font-family: Tahoma; font-size: x-small;"><span style="font-family: Tahoma; font-size: 10.0pt; mso-bidi-font-family: Arial;"><b>xp20:matches(</b><span class="Apple-style-span" style="color: #b45f06;">bpws:getVariableData('inputVariable','payload','/ns3:PropertyDetails/ns3:PropertyValueToUpdate')</span>, <span class="Apple-style-span" style="color: #674ea7;">'^(0[1-9]|[12][0-9]|3[01])[- /.](Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[- /.](19|20)\d\d (0[1-9]|1[012])[:](0[1-9]|[12345][0-9])[:](0[1-9]|[12345][0-9]) (AM|PM)$'</span><b>)</b><o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="font-size: x-small;"><span style="font-family: Tahoma; font-size: 10pt;">Ooops. This was not working and but telling me <b>Internal Xpath Error</b>. Well the reason was that </span><span style="font-size: 10pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">getVariableData()</span></span><span style="font-family: Tahoma; font-size: 10pt;"> function gets the data from the XML schema element in </span><span style="font-size: 10pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Object </span></span><span style="font-family: Tahoma; font-size: 10pt;">format rather than in </span><span style="font-size: 10pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">String</span></span><span style="font-family: Tahoma; font-size: 10pt;">. Finally, all I did was that I used the </span><span style="font-size: 10pt;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">string()</span></span><span style="font-family: Tahoma; font-size: 10pt;"> function to convert the object from variable data and it worked without any issue.<o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="font-family: Tahoma; font-size: x-small;"><span style="font-family: Tahoma; font-size: 10.0pt; mso-bidi-font-family: Arial;"><b>xp20:matches(</b><span class="Apple-style-span" style="color: blue;">string(</span><span class="Apple-style-span" style="color: #b45f06;">bpws:getVariableData('inputVariable','payload','/ns3:PropertyDetails/ns3:PropertyValueToUpdate')</span><span class="Apple-style-span" style="color: blue;">)</span>, <span class="Apple-style-span" style="color: #134f5c;">'^(0[1-9]|[12][0-9]|3[01])[- /.](Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[- /.](19|20)\d\d (0[1-9]|1[012])[:](0[1-9]|[12345][0-9])[:](0[1-9]|[12345][0-9]) (AM|PM)$'</span><b>)</b><o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="font-family: Tahoma; font-size: x-small;"><span style="font-family: Tahoma; font-size: 10.0pt; mso-bidi-font-family: Arial;">Just a small learning…<o:p></o:p></span></span></div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com1tag:blogger.com,1999:blog-5083674265912730417.post-86436789413692037972010-11-27T13:53:00.000-08:002011-03-28T04:07:39.686-07:00Provider ABCS not able to read the data while transform, Default Part Name not correct<div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">I started working on AIA 3.0 with SOA 11.1.3.3 and developed a simple Worker interface that fetches the data from a table and transfers it to Provider ABCS.</span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><br />
</span></div><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_3yudTWgGlSg/TPF5t9FSauI/AAAAAAAAARI/HQs1u9kcuKc/s1600/AIA+Worker+Interface.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-style-span" style="color: black;"><img border="0" height="132" src="http://1.bp.blogspot.com/_3yudTWgGlSg/TPF5t9FSauI/AAAAAAAAARI/HQs1u9kcuKc/s400/AIA+Worker+Interface.JPG" width="400" /></span></span></a></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">But while running it, I faced the 2 errors.</span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">The first <a href="http://forums.oracle.com/forums/message.jspa?messageID=9116612"><span class="Apple-style-span" style="color: black;">error</span></a></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> that I faced was at the time of compilation. Well, that was seemingly a bug which was worked around. <o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">Later, when I was testing the process, it behaved strange in a way that the all records travelled till Provider ABCS <b>Receive</b> activity properly but did not get <b>Transformed</b>. The <b>Transform </b>activity payload looked empty as shown below. <o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="t"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> <Invoke_WriteAllWorkersFile_InputVariable</span></span></span><span class="m"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">></span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"><o:p></o:p></span></span></div><div class="MsoNormal"><span class="t"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> <part</span></span></span><span class="apple-converted-space"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> </span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> <span class="t">name</span><span class="m">="</span><b><span style="font-weight: bold;">XxbbIntgWorkersCollection</span></b><span class="m">"></span><o:p></o:p></span></span></div><div class="MsoNormal"><b><span style="font-weight: bold;"><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> <</span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">XxbbIntgWorkersCollection</span></span></span></b><span class="m"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> /></span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"><o:p></o:p></span></span></div><div class="MsoNormal"><span class="m"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> </</span></span></span><span class="t"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">part</span></span></span><span class="m"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">></span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"><o:p></o:p></span></span></div><div class="MsoNormal"><span class="m"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"> </</span></span></span><span class="t"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">Invoke_WriteAllWorkersFile_InputVariable></span></span></span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;"><o:p></o:p></span></span></div><div class="MsoNormal"><o:p><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></o:p><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">I tried various things for this. But later found that the problem was with Provider ABCS. While constructing the PABCS using service constructor, it created the </span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SyncWorkerProvABCSImpl.WSDL (<ProjectName.WSDL>)</span></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> message part as <o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;"> </span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <</span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">message name="SyncWorkerReqMsg"></span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <part name="SyncWorkerEBM" </span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> element="eboebo:SyncWorkerListEBM"/></span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <</span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">/message></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">However, looking at the transformation I noticed that actual data is coming into the part with name </span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SyncWorkerListEBM </span></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">instead of </span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SyncWorkerEBM</span></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">. Hence as my transform by default was trying to access the data from the </span><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="color: blue;">SyncWorkerEBM </span></span><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">part, it was not getting any data resulting in empty transformation output variable. I changed the above code as following and it worked like a charm. <o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> <message name="SyncWorkerReqMsg"><o:p></o:p></span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><</span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">part name="SyncWorkerListEBM" </span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> element="eboebo:SyncWorkerListEBM"/></span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><</span></span><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">/message></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="Apple-style-span" style="font-family: 'Trebuchet MS', sans-serif;">The same has been raised to Oracle but till they come up with something, we need not stop. Let’s be going…</span><span class="Apple-style-span" style="color: navy; font-family: Tahoma; font-size: 10pt;"><o:p></o:p></span></div></div>Neeraj Sehgalhttp://www.blogger.com/profile/00710423700808591499noreply@blogger.com0