tag:blogger.com,1999:blog-382300682024-03-13T15:41:10.291-07:00There's always a new dimension!Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-38230068.post-10968056497538054062014-09-30T15:46:00.002-07:002014-09-30T15:46:40.431-07:00The ‘Other’ Wi-Fi in the room<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>JA</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
<w:UseFELayout/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="276">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]-->
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:Cambria;
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:minor-latin;
mso-fareast-language:JA;}
</style>
<![endif]-->
<!--StartFragment-->
<!--EndFragment--><br />
<div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;">
I
recently had the opportunity to troubleshoot a Wi-Fi issue on an iPhone. A
previously detected and accepted home Wi-Fi connection on the phone was now not
connecting to the network. Navigating to Settings->Wi-Fi and tapping on the specific
Wi-Fi network did not help. Power cycling the home router did not help either.
Then, the thought of playing around with the ‘Other’ networks on the settings
came to mind. Navigate to Settings-> Wi-Fi->Other on the phone and enter
in the Wi-Fi network name and select the appropriate security for the network.
In this case, it was WPA and I selected that as the security option for the
network. Then, I was prompted for the Wi-Fi password, which I duly entered and
lo and behold, the phone was able to re-connect to the Wi-Fi network.<o:p></o:p></div>
</div>
Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-15891401637362409082011-12-21T13:24:00.000-08:002011-12-21T13:24:58.676-08:00Circumvent a mutating trigger using INSTEAD OF trigger<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
Here is a new take on the classic mutating trigger issue in Oracle. Imagine a fictitious scenario where we have a requirement to do a cumulative sum on a column when an insert operation is performed on the table. Typically, we can write a trigger that looks up the table and uses the sum of the column to come up with a cumulative sum value for the row that is being inserted. The trouble with this approach is the moment we refer to the trigger table within the trigger itself, the chances are that the mutating trigger exception is waiting to happen.</div>
<br />
<div style="text-align: justify;">
So, how can we beat this problem? One way to do it is by a clever usage of INSTEAD OF trigger rather than a conventional trigger. Instead of allowing the insert to be performed on a table, if the insert were to be performed on a view of the table, that gives us the opening that we need to solve this problem!</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Note: This is not the only mechanism to deal with mutating trigger issues. Well established mechanisms like using autonomous transaction, maintaining package level variables in statement level triggers etc. are also equally effective in solving this problem. Choosing the right mechanism would vary based on the exact use case.</div>
<br />
Let us see how to solve this problem with an example:<br />
<br />
--Creating a sample table to test the no mutation theory<br />
<br />
create table no_mutation_t(running_num number, cum_sum number)<br />
/<br />
--Create a view on the table<br />
create view no_mutation_v as select running_num from no_mutation_t<br />
/<br />
--Create a dummy sample data table that drives inserts on no_mutation_t table<br />
create table no_mutation_sample_data_t(running_num number)<br />
/<br />
<br />
-- Insert rows in dummy sample data table<br />
<br />
insert into no_mutation_sample_data_t values(10)<br />
/<br />
insert into no_mutation_sample_data_t values(20)<br />
/<br />
<br />
--Now, we are ready to write the instead of trigger.<br />
<br />
create or replace trigger no_mutation_trg instead of insert on no_mutation_v<br />
DECLARE<br />
l_cum_sum NUMBER;<br />
<br />
BEGIN<br />
SELECT nvl(sum(running_num),0) into l_cum_sum<br />
from no_mutation_t;<br />
<br />
INSERT INTO no_mutation_t values(:NEW.running_num,:NEW.running_num + l_cum_sum);<br />
END;<br />
/<br />
<br />
-- Proof of the pudding lies in eating part. Test the trigger with an insert on view rather than the table<br />
insert into no_mutation_v select * from no_mutation_sample_data_t<br />
/<br />
<br />
<br />
<div style="text-align: justify;">
The insertion happens smoothly with no mutating trigger errors. A select on the no_mutation_t table produces the running_num and cumulative sum.</div>
<div style="text-align: justify;">
For completeness sake, you can actually write a trigger on the table with the same trigger code as above and test by inserting multiple rows into the table itself rather than the view. Doing so would result in a mutating trigger exception for the direct table insertion.</div>
<br />
</div>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-40946499326587251692011-01-19T17:36:00.000-08:002011-01-19T17:36:58.074-08:00Explore AIA 3.0 artifacts without installing AIA!If there is ever a need to take a closer look at the AIA 3.0 foundation pack artifacts without going thru the rigorous install process of AIA, then here is how to do it:<br />
<br />
1. Download the AIA 3.0 installer zip from edelivery.oracle.com<br />
2. Unzip the contents of the file<br />
3. Navigate to Disk1/stage/Components/oracle.aia.top/11.1.1.2.0/1/DataFiles<br />
4. There should be a jar file by the name filegroup1.jar<br />
5. Unjar the contents of the filegroup1.jar file<br />
6. Now, there should be a folder created with the name filegroup1<br />
7. Navigate to filegroup1/AIAMetaData/AIAComponents<br />
<br />
The AIAComponents folder is where all the artifacts like EBO, EBM, EBS are placed. The AIAMetaData folder also has seed versions of DVM & XREF files.Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-53088380935653937452010-07-09T12:31:00.000-07:002010-07-12T13:29:00.230-07:00SSLy Me!Here is a tip on deploying SOA composites from JDeveloper 11g to a SOA server that is SSL enabled with a self-signed certificate.<br />
<br />
Deploying a SOA composite from JDeveloper 11g would fail with the following exception if the SOA server is SSL enabled with a self-signed certificate:<br />
<br />
<i>javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found</i><br />
<br />
The same exception will also be raised when we use the B2B adapter wizard in a composite to pick a B2B document.<br />
<br />
We would have to do the following configurations in JDeveloper 11g to beat this error:<br />
<br />
<br />
<ul><li>Get a local copy of the keystore JKS file that holds the self-signed certificate</li>
<li>Navigate to Tools->Preferences->Credentials in JDeveloper 11g</li>
<li>Specify the keystore jks file including the path in the server keystore text-box</li>
<li>Specify the keystore password in the server keystore password text-box</li>
<li>Specify the private key in the server private key password text-box</li>
</ul><div>This would take care of the exception and the composites can then be successfully deployed directly from the JDeveloper 11g environment. </div><div><br />
</div><div>Sample JDeveloper screenshot that shows the Credentials configuration page:</div><div><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivcAQ9fptwp0FJBlc0fT6vwTFpm1d72k7g2SarP4VtNkx9Es8QRrxw2P7TK8aWdPRToH2DvvLYPFVkXV9j_soo3pUgAJ-0kDNdOx92fdAylDLXeCu7WLYYCukgh-fEvh8Osj3E/s1600/jdev_screenshot.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivcAQ9fptwp0FJBlc0fT6vwTFpm1d72k7g2SarP4VtNkx9Es8QRrxw2P7TK8aWdPRToH2DvvLYPFVkXV9j_soo3pUgAJ-0kDNdOx92fdAylDLXeCu7WLYYCukgh-fEvh8Osj3E/s400/jdev_screenshot.PNG" width="400" /></a></div><div><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
It turns out that even if we beat this error, the JDeveloper JDK compiler also needs to be able to identify this certificate when we compile/make a composite.<br />
<br />
The following steps would take care of adding the certificate to the JDK certificate store:<br />
<br />
<ul><li>Export the certificate using keytool utility from the JKS file into a certificate file</li>
<li>Import the certificate using keytool utility into cacerts file used by the JDeveloper JDK</li>
<li>The cacerts file would be in the following JDeveloper product path: \oracle\Middleware\JDeveloper\[jdk_directory]<jdk_directory>\jre\lib\security</jdk_directory></li>
</ul></div>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-66162592625324357562010-07-02T10:24:00.000-07:002010-07-02T11:29:11.340-07:00'Long' story shortRecently, I came upon a requirement to do a substring on a long column from a select statement in Oracle. This was for a table named fnd_documents_long_text and is used as part of Oracle eBusiness Suite.<br />
<div><br />
</div><div>What started out as a trivial exercise turned out to be not so trivial! I was baffled to note that no built-in functions could be used on a long column at-least with respect to referencing it from a select statement. It then dawned on me that we would have no option but to come up with a fast and furious pl/sql custom solution to address this requirement :-)</div><div><br />
</div><div>The objective is to execute a statement like the following:</div><div><br />
</div><div><i>select substr(long_text,1,255) from fnd_documents_long_text</i></div><div><i><br />
</i></div><div>Running this statement would give us an error exposing the limitations of using the long datatype! In this case, it is not a self-imposed limitation as this table comes part of the eBusiness Suite install! </div><div><br />
</div><div><br />
</div><div>Here is an outline of one such solution for this issue:</div><div><br />
</div><div><ul><li>Create a user-defined type with a structure similar to fnd_documents_long_text except for the fact that the datatype of long_text column would be set to CLOB in the user defined type</li>
<li>Create a table user-defined type from the user-defined type</li>
<li>Write a pipeline function that processes rows from fnd_documents_long_text, converts the long_text column to clob and returns the clob version of the column as part of the row</li>
<li>Create a view that selects rows from the pipelined function using the TABLE() function</li>
<li>Use the view instead of fnd_documents_long_text</li>
<li>Use dbms_lob.substr function on the long_text column from the view!</li>
</ul></div><div>The sample code for the solution can be downloaded from here: <a href="http://docs.google.com/leaf?id=0BwiKIUE4Jo0aMzQ4NTNiZGUtZGY5ZC00NDUzLWE3NzctMTBjNzRjY2IyZDNl&hl=en&authkey=CLeOgK0N">long_story_short.sql</a><br />
<br />
</div><div><br />
</div><div>Once the view is in place, the select statement can now be run like the following:</div><div><br />
</div><div><i>select dbms_lob.substr(long_text,255,1) from fnd_documents_long_text_v</i></div><div><br />
</div><div>That's it! A little fist pump and we can start using this solution!</div><div><br />
</div><div>I certainly hope that Oracle would come out with an upgrade solution for handling long datatype related issues. It would be great to see an upgrade that handles the conversion of these long typed columns to clob typed columns. </div>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-10225313682391579162010-06-11T13:31:00.000-07:002010-07-06T17:05:47.640-07:00Merry with XQuery<span class="Apple-style-span" style="font-family: arial, sans-serif;"><span class="Apple-style-span" style="border-collapse: collapse;"></span></span><br />
<span class="Apple-style-span" style="font-family: arial, sans-serif;"><span class="Apple-style-span" style="border-collapse: collapse;"><div>This post is about exploring the advantages of using xquery within a BPEL process to examine the contents of a payload. We are going to explore around this area for the following use cases:</div><div><ul><li>count the occurrence of unique values for a specific repeating node in a payload</li>
<li>search for the existence of a node value in a payload</li>
</ul></div><div>The components that would make up an XQuery solution are :</div><div><ul><li>XQuery file</li>
<li>Invocation of processXQuery() function within a bpel process</li>
</ul></div><div>The xquery file will be a part of the SOA project and can be easily created from within the JDeveloper 11g environment. Choose File->New->XML->XQuery file from the menu and a blank file with .xq extension will be created.</div><div><br />
</div><div>For our count node use case , the following code can be used as the contents of the xquery file. Let us name the file as count.xq.</div><div><br />
</div><div><span class="Apple-style-span" style="border-collapse: separate; font-family: Georgia, serif;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMzMFE47sFomIDuQ3UKC3sbga6H0SCE6GomG0pRLRlUkXhDpMLPbtZZ3aVBJquY_OQEavq9J3-HwSL0xwHsoYDm5oOa7edUliYlnK_B3aqeaz5MnDi5PxKJ9rnW9v3MWiUYz4m/s1600/code1.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5481621542727578210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMzMFE47sFomIDuQ3UKC3sbga6H0SCE6GomG0pRLRlUkXhDpMLPbtZZ3aVBJquY_OQEavq9J3-HwSL0xwHsoYDm5oOa7edUliYlnK_B3aqeaz5MnDi5PxKJ9rnW9v3MWiUYz4m/s320/code1.png" style="cursor: pointer; height: 86px; width: 320px;" /></a></span></div><div><br />
</div><div><br />
</div><div>The namespace mentioned in the xquery file must match the namespace of the element that we are going to pass as input to this xquey file.</div><div><br />
</div><div>Now that we have the xquery, let us take a look at how this can be used from a bpel process.</div><div><br />
</div><div>We will assume that an element with the following value will be passed to the xquery file and this is stored in a bpel variable named collectionvar. We are going to count the unique values of the node named Id.</div><div><br />
</div><div>Sample contents of the variable collectionvar:</div><div><br />
</div><div><textarea cols="50" rows="7"><ns1:collection xmlns:ns1="urn:collection"> <level1> <level2> <id>123</id> </level2> </level1> <level1> <level2> <id>125</id> </level2> </level1> <level1> <level2> <id>123</id> </level2> </level1> </ns1:collection></textarea><br />
Next an assign activity in bpel can be used to invoke the processXQuery() function.</div><div><br />
</div><div>ora:processxquery("count.xq", bpws:getVariableData('collectionvar'))</div><div><br />
</div><div>The result of this function can be assigned to another simple type integer variable in the assign activity.</div><div><br />
</div><div>Similar logic can be applied to search for the existence of a specific node value. We will assume that a bpel variable named collectionvar has both the search string node as well as the payload which is the target of our search.</div><div><br />
</div><div>We are going to assume that the variable collectionvar has the following sample data:</div><div><br />
<textarea cols="50" rows="7"><ns1:collectionelement xmlns:ns1="urn:collection"> <searchstr>14</searchstr> <targetelement> <level1> <id>19</id> </level1> <level1> <id>14</id> </level1> <level1> <id>12</id> </level1> </targetelement> </ns1:collectionElement></textarea></div><br />
<br />
<div>The targetelement node is is going to be the payload for this example. If there is a namespace associated with the variable, then the namespace needs to be declared in the XQuery file and used in the XQuery search.</div><div><br />
</div><div>Here is the XQuery code. Let us name this file as search.xq</div><div><br />
</div><div><span class="Apple-style-span" style="border-collapse: separate; font-family: Georgia, serif;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDknyx5FGPqx4xXAm7sNJCzOiQCqKNUAxQpSxvECak3z53_RGVi9i2z3TgLztZ568_zeUq-xYFntal6Fyv53ogy1M8R-194s5hJhTXDuWMuDZraOlFTnlcxI7RLv-Ij-HWNYRQ/s1600/code2.png"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5481622421446529858" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDknyx5FGPqx4xXAm7sNJCzOiQCqKNUAxQpSxvECak3z53_RGVi9i2z3TgLztZ568_zeUq-xYFntal6Fyv53ogy1M8R-194s5hJhTXDuWMuDZraOlFTnlcxI7RLv-Ij-HWNYRQ/s320/code2.png" style="cursor: pointer; height: 86px; width: 320px;" /></a><br />
</span></div><div><br />
</div><div><br />
</div><div>The processing takes place with the following invocation:</div><div><br />
</div><div>ora:processXQuery("search.xq",bpws:getVariableData('collectionvar'))</div><div><br />
</div><div>Again, this function should be assigned to another BPEL variable via an assign activity to capture the result of the XQuery computation. The result will be a true or false expression.</div><div><br />
</div><div>Now it's time to make merry with XQuery!</div></span></span>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com1tag:blogger.com,1999:blog-38230068.post-89099996753573649022009-12-21T13:15:00.000-08:002009-12-21T14:15:49.587-08:00Opening the gates to SOA gateway heavenWith the release of Integrated SOA gateway in R12, we can invoke a web service as a subscription to a business event. One of the pitfalls that we might encounter in the subscription definition is the lack of privilege to setup the web service invocation( Yes, we might get this error even if we are trying to set it up as SYSADMIN).<br /><br />When you select the action type in the subscription definition as 'Invoke a Web Service', you might get the dreaded 'insufficient privileges' error message. The reason for this message is that the FND user account through which the subscription is being set up does not have the privilege to the following set of functions:<br /><br /><ul><li>FND_WF_WS_CONSUME_WSDL</li><li>FND_WF_WS_SELECT_OPERATION</li><li>FND_WF_WS_SELECT_SERVICE</li><li>FND_WF_WS_SELECT_SERVICE_PORT</li><li>FND_WF_WS_SUBSCRIPTION</li></ul><br /><br />We can assign these privileges to the FND user (SYSADMIN or any other appropriate user) through the following steps:<br /><br />1) Create a menu in eBusiness Suite with these functions as components of the menu. It is important to note that the menu type should be set to 'Permission Set'<br />2) Switch to User Management responsibility in eBusiness Suite to create a user defined role and assign the newly created permission set to this role<br />3) Assign this new role to the FND user account<br /><br />This should take care of it. Our gateway to SOA integration heaven would then be completely open to us :-)Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-49474558106050032842009-12-16T14:53:00.000-08:002010-01-06T10:40:01.131-08:00Receiving Transaction Processor Conundrumwhat would we do if we are faced with a situation to execute a receiving transaction in oracle ebusiness suite from a BPEL process in Oracle Fusion Middleware?<br /><br /><br />There are no public APIs provided as of this moment to execute the transactions. The only option is to use the receiving transaction processor. We would not want to invoke a concurrent program from a BPEL process and write wait & watch logic to check the outcome of the concurrent program. Could there be a better way? Let us dig deeper into how Oracle internally handles the receiving transactions.<br /><br />Within the ebusiness suite system, there are 3 modes with which receiving can be done: Online, Immediate, Batch.<br /><br />Immediate mode calls the receiving transaction processor as a concurrent request while the batch mode simply inserts the transactions into the receiving interface which could then be processed by a scheduled run of the receiving transaction processor.<br /><br />What is of interest to us is this online mode. The online mode invokes the receiving transaction processor as a synchronous call bypassing the concurrent request submission. It is this mode that we can use to our advantage in a BPEL process to pull off our heist.<br /><br />Synchronous calls can be done using fnd_transaction.synchronous API in the ebusiness suite system. The synchronous call to receiving transaction processor would be like this:<br /><br />l_retvalue := fnd_transaction.synchronous( 300, -- timeout in seconds<br /> l_outcome, -- out variable indicating Success/Warning/Error<br /> l_message, -- out variable with a descriptive message<br /> 'PO',<br /> 'RCVTPO',<br /> 'ONLINE',<br /> l_group_id, -- group_id in rcv_transactions_interface<br /> l_organization_id, -- inventory organization_id,<br /> NULL, NULL, NULL, NULL, NULL,<br /> NULL, NULL, NULL, NULL, NULL, NULL,<br /> NULL, NULL, NULL, NULL, NULL, NULL);<br /><br />Please note that Oracle internally makes this call for Online receiving transactions using POR_RCV_ORD_SV.Call_Txn_Processor routine.<br /><br />There are a couple of things that we need to do before we make this synchronous call:<br /><br /><ul><li>Set the apps context</li><li>Insert rows into receiving interface tables</li><li>Specify processing_mode_code in rcv_transactions_interface as 'ONLINE'</li></ul><br />To debug this routine, set the 'CONC_DEBUG' profile to 'TC' and watch for errors in the fnd_concurrent_debug_info table.<br /><br />All of this could be wrapped up into a nice custom utility and we would be good to go!<br /><br />Enjoy the serving!Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com2tag:blogger.com,1999:blog-38230068.post-73308207589040292262009-11-20T12:01:00.000-08:002009-11-20T12:55:54.865-08:00Heads Up on Partner Link header setup in 11g BPEL process<div style="text-align: justify;">In today's SaaSy world, we come across requirements to integrate SaaS apps like SalesForce and NetSuite using a middleware stack. I have had the opportunity to play with such integrations using Oracle Fusion middleware on both the 10g and 11g stacks. One of the prime requirements for calling any SaaS based web services API from an invoke activity in BPEL is to pass in the session credentials information.<br /><br />In the 10g stack, we typically create a SaaS header-type variable in BPEL. We populate the contents of the variable with the appropriate credentials and set this variable as the input header variable in an invoke activity in BPEL. The invoke activity in turn is tied to a partner link operation like a login or add operation and the partner link is created based on the WSDL generated by the SaaS provider. In the BPEL designer environment of JDeveloper 10g, we can open the design window of the invoke activity and navigate to the Adapters tab to set the input header variable.<br /><br />Surprisingly, this option is not directly available in the 11g JDeveloper designer environment. If we open the 11g JDeveloper design window for the invoke activity, the Adapters tab is no longer available!<br /><br />How do we then pass in the input header variable for the invoke activity?<br /><br />We could switch to the source view in the 11g BPEL designer and directly modify the invoke xml element in the .bpel file. The invoke element should have the following additional entry:<br /><br />bpelx:inputHeaderVariable="<bpel_variable_name>bpel_variable_name"<br /><br />Here the "bpel_variable_name<bpel_variable_name>" should be substituted with the actual BPEL variable defined in the process.<br /></bpel_variable_name></bpel_variable_name></div>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com3tag:blogger.com,1999:blog-38230068.post-28124136309898778782009-08-06T10:34:00.000-07:002009-08-06T12:14:44.202-07:00Taming schema inheritance in BPEL<div style="text-align: justify;">An interesting use case on handling XSD schema definitions in BPEL involves the usage of schema inheritance in BPEL. Understanding how to handle schema inheritance in BPEL pays off handsomely when we deal with SaaS applications integration scenarios in BPEL. The web services framework of SaaS applications predominantly base their schema definitions on XSD inheritance.<br /><br />Let us assume that we have an operation defined in a web service whose input message accepts a schema type A. This schema type A would be defined as the base type and relevant well defined schema types can be extended from the base type A. Let B and C be the extended types derived from the base type A.<br /></div><div style="text-align: justify;"><br />The different scenarios in invoking the operation on the web service can be:<br /><br /></div><ul style="text-align: justify;"><li>#1 - Operation invoked with input message A</li><li>#2 - Operation invoked with input message B where B is an extension of A</li><li>#3 - Operation invoked with input message C where C is an extension of A</li></ul><div style="text-align: justify;">Scenario #1 can be handled in a straight forward manner in BPEL. Scenarios #2 & #3 would require additional manipulation in BPEL. We would have to type cast the incoming message into the respective B or C types.<br /><br />This point can be illustrated with a sample 'down-to-earth' BPEL process:<br /><br />1. Create a synchronous BPEL process<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk-tl8MvxvzQv88bSO1zrdr8RtoqnDvYziXNCR18i7qXmoTDFL2HcheHUSCzwKNUVkF-XOHC40n425lkhaXk_2vAhyCB99Q37Xpdk9bl0f7D9evuzan8LTqWcltlcf9C-JaYIz/s1600-h/sample_bpel.jpg"><img style="cursor: pointer; width: 430px; height: 287px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk-tl8MvxvzQv88bSO1zrdr8RtoqnDvYziXNCR18i7qXmoTDFL2HcheHUSCzwKNUVkF-XOHC40n425lkhaXk_2vAhyCB99Q37Xpdk9bl0f7D9evuzan8LTqWcltlcf9C-JaYIz/s320/sample_bpel.jpg" alt="" id="BLOGGER_PHOTO_ID_5366910525736303698" border="0" /></a><br /><br />2. Provide the following as the schema definition for the BPEL process:<br /><br /><br /><textarea rows="5" cols="40" readonly="readonly"><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"> <complextype name="rootType"> <sequence> <element name="objType" type="string" nillable="true" minoccurs="1" maxoccurs="1"> </sequence> </complextype> <complextype name="customerType"> <complexcontent> <extension base="tns:rootType"> <sequence> <element name="customerName" nillable="true" minoccurs="0" type="string"> <element name="customerPhone" nillable="true" minoccurs="0" type="string"> <element name="customerEMail" nillable="true" minoccurs="0" type="string"> </sequence> </extension> </complexcontent> </complextype> <complextype name="productType"> <complexcontent> <extension base="tns:rootType"> <sequence> <element name="productName" nillable="true" minoccurs="0" type="string"> <element name="productCategory" nillable="true" minoccurs="0" type="string"> <element name="productPrice" nillable="true" minoccurs="0" type="string"> </sequence> </extension> </complexcontent> </complextype> <element name="BPELInheritProcessProcessRequest"> <complextype> <sequence> <element name="input" type="tns:rootType"> </sequence> </complextype> </element> <element name="BPELInheritProcessProcessResponse"> <complextype> <sequence> <element name="result" type="string"> </sequence> </complextype> </element> <element name="Customer" type="tns:customerType"> <element name="Product" type="tns:productType"> </schema></textarea><br /><br /><br />In this definition, rootType is our A type, customerType is our B type derived from A and productType is our C type derived from A.<br /><br />The rootType is defined as the input message for the BPEL process invocation. With this definition, we can invoke the BPEL process either by passing an instance of A or B or C.<br /><br />The BPEL process should type cast the incoming message into the appropriate type during runtime if the incoming message is B or C.<br /><br /><br />3. There is an assign activity defined in the BPEL process for the Customer branch of a switch acitivity and the Product branch of the Switch activity. The incoming message is understood to be either a Customer related one or a Product related one by objType element defined in the rootType.<br /><br />4. The Rename operation is used in an assign activity to type cast the incoming messages into either a customer type or the product type.<br /><br /><img src="file:///C:/DOCUME%7E1/KARTHI%7E1.RAJ/LOCALS%7E1/Temp/moz-screenshot.png" alt="" /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGLh7L36NZGt73yslVqLYjkY5NJKh8oUa9cu2-oJYgogJFAW94KJ9grVl9-uofS21aZCQgVXXql05FrL-EkaZYtCd1zLY6bDeNErQQJF_E7S3VyfqX26PFZSktsa9E0TNVRz9s/s1600-h/type_cast_1.jpg"><img style="cursor: pointer; width: 399px; height: 312px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGLh7L36NZGt73yslVqLYjkY5NJKh8oUa9cu2-oJYgogJFAW94KJ9grVl9-uofS21aZCQgVXXql05FrL-EkaZYtCd1zLY6bDeNErQQJF_E7S3VyfqX26PFZSktsa9E0TNVRz9s/s320/type_cast_1.jpg" alt="" id="BLOGGER_PHOTO_ID_5366913573282565074" border="0" /></a><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType">5. The next step in the assign activity is to copy this type casted value into a BPEL variable that is explicitly defined with either the customer or product type.</element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf70B7eCZYyhgEyEDXSi_cWudz53hJmTmGoGv6iTsG05WdZMs8b1_NT4GFnYkEH8bMQSyhq2BVJ-PxOg42O3liY0Bxhf1efSXhLfw66MkMbJCNSgBgDVt5nUH7Ao_YBsWkQ2ql/s1600-h/type_cast_2.jpg"><img style="cursor: pointer; width: 383px; height: 235px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhf70B7eCZYyhgEyEDXSi_cWudz53hJmTmGoGv6iTsG05WdZMs8b1_NT4GFnYkEH8bMQSyhq2BVJ-PxOg42O3liY0Bxhf1efSXhLfw66MkMbJCNSgBgDVt5nUH7Ao_YBsWkQ2ql/s320/type_cast_2.jpg" alt="" id="BLOGGER_PHOTO_ID_5366914773522390898" border="0" /></a><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType">6. After this point, it is really easy to work with the actual definitions of the customer or product variable within the BPEL process.</element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType">Here's how the BPEL process is invoked with customer as the input message type:</element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"><textarea rows="5" cols="40" readonly="readonly"><soap:envelope soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:body ns1="http://xmlns.oracle.com/BPELInheritProcess"> <ns1:bpelinheritprocessprocessrequest> <ns1:input> <ns1:objtype>Customer</ns1:objType> <ns1:customername>XYZ</ns1:customerName> <ns1:customerphone>111-111-1111</ns1:customerPhone> <ns1:customeremail>xyz@nocompany.com</ns1:customerEMail> </ns1:input> </ns1:BPELInheritProcessProcessRequest> </soap:Body> </soap:Envelope></textarea></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType">Here's how the same BPEL process is invoked with product as the input message type:</element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"><textarea rows="5" cols="40" readonly="readonly"><soap:envelope soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:body ns1="http://xmlns.oracle.com/BPELInheritProcess"> <ns1:bpelinheritprocessprocessrequest> <ns1:input> <ns1:objtype>Product</ns1:objType> <ns1:productname>XYZ</ns1:productName> <ns1:productcategory>Misc</ns1:productCategory> <ns1:productprice>11111</ns1:productPrice> </ns1:input> </ns1:BPELInheritProcessProcessRequest> </soap:Body> </soap:Envelope></textarea></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema><br /><schema attributeformdefault="unqualified" elementformdefault="qualified" targetnamespace="http://xmlns.oracle.com/BPELInheritProcess" xmlns="http://www.w3.org/2001/XMLSchema" tns="http://xmlns.oracle.com/BPELInheritProcess"><complextype name="rootType"><complextype name="customerType"><complexcontent><extension base="tns:rootType"><complextype name="productType"><complexcontent><extension base="tns:rootType"><element name="BPELInheritProcessProcessRequest"><element name="BPELInheritProcessProcessResponse"><element name="Customer" type="tns:customerType"></element></element></element></extension></complexcontent></complextype></extension></complexcontent></complextype></complextype></schema></div>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com1tag:blogger.com,1999:blog-38230068.post-70246618835970849122009-07-09T12:27:00.000-07:002009-07-09T13:45:45.535-07:00Tinkering with the Database AdapterThe process of creating a connection factory for a database adapter in Oracle Weblogic Server can be quite baffling since it allows us to create the factory in the weblogic console but does now allow us to set the properties for the connection factory!<br /><br />Typically, the property that we would be interested in would be dataSourceName or XAdataSourceName. We would be giving the jndi name of our data source as a value for this property. If we cannot do it thru the weblogic console, then how do we do it?<br /><br />The way to do it is to tinker with the RAR file of the database adapter. The RAR file contains the actual jar file along with the configuration files of the adapter. For our use case, we would have to modify weblogic-ra.xml to set the property values.<br /><br />Here are the steps:<br /><br />1. Navigate to deployments section of soa server in the weblogic admin console<br />2. Click on DbAdapter resource adapter<br />3. Navigate to Configuration->Outbound connection pools<br />4. Create the connection factory and provide a jndi name for the connection factory<br />5. Do not try to set the properties of connection factory as it will not be saved!<br />6. Note down the source path for the RAR file from the Overview tab of the adapter<br />7. Go to the OS and navigate to the path of the folder that contains the RAR file<br />8. Take a backup of DbAdapter.rar since we have no intention of being heroic :-)<br />9. Un-jar the contents of DbAdapter.rar into a custom folder<br />10. Navigate to that folder and then to META-INF folder<br />11. Open up weblogic-ra.xml<br />12. Look for xml element 'connection-instance'<br />12. A sample setting for 'connection-instance' will be there in the file<br />13. Copy and paste the sample into the file itself<br />14. Modify the contents of this new 'connection-instance'<br />15. You would be providing the connection factory details and the jndi value for the dataSourceName or XAdataSourceName property inside the 'connection-instance' element<br />16. Save weblogic-ra.xml file<br />17. Re-Jar the DbAdapter.rar along with this new weblogic-ra.xml<br />18. Navigate back to weblogic admin console<br />19. Bring up the deployments section of soa server<br />20. Select the DB Adapter and click the Update button<br />21. Re-deploy the DbAdapter.rar file with the default settings<br />22. Re-start the SOA Server<br />23. Test your BPEL/composite process<br />24. Congratulate yourself!Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com2tag:blogger.com,1999:blog-38230068.post-55276234538856129162009-03-18T10:03:00.000-07:002009-03-19T10:11:49.753-07:00Audit Trail of WIP_DISCRETE_JOBSThere is a cool profile option named 'AuditTrail:Activate' in Oracle eBusiness Suite that allows us track the updates being done to wip_discrete_jobs table. If the profile option is set to 'Yes', then updates to the job status, item, scheduled start date will be captured in an audit table called wip_discrete_jobs_a. This table also captures the user and session that did the update. With this feature turned on, it really becomes easy for us to say 'I know what you did to that discrete job!' :-)<br /><br />The audit table also gets populated when a new job gets created but captures only the user and session information in this case. The audit works irrespective of whether the update is being done from the front-end or a direct table update since it is controlled as a trigger at the table level on wip_discrete_jobs.<br /><br />One caveat worth noting is that enabling the profile option entails the audit of a lot of tables within the Oracle eBusiness Suite environment. List of such tables audited can be obtained from the data dictionary by looking for table names that are suffixed with '_A'. An introspection of the structure of these tables would tell us if they are the audit versions of the actual tables.Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-13739527487854225002009-02-25T11:27:00.000-08:002009-02-25T11:57:43.618-08:00OA Framework & PO form integrationQuite recently, I allowed myself to be put back into a personal flashback mode and was thinking about the unique solutions that I had come up with to solve some interesting problems. Here is one such problem and its solution:<br /><br /><span style="font-weight:bold;">Problem statement</span><br />From an OA framework enabled web page, clicking on a hyper-linked purchase order number should open up the purchase order form in Oracle Purchasing module and also pre-populate the form with the specific purchase order details. Once the form is opened this way, the form should retain all of its default behavior too.<br /><br /><span style="font-weight:bold;">Solution</span><br />OA Framework allows us to programmatically open up a form using <span style="font-style:italic;">pageContext.forwardImmediatelyToForm</span> function. But the challenge was to pass the po number to the PO_POXPOEPO function to query the PO automatically upon opening the form.After looking into the internals of how the actual form works, I came to the conclusion that a new function can be created for the POXPOEPO form with the following parameters in the function definition:<br /><br />PO_HEADER_ID=p_value ACCESS_LEVEL_CODE=”MODIFY” POXPOEPO_CALLING_FORM=”OTHER_FORM”<br /><br />Here, p_value should be replaced with the actual po_header_id corresponding to the po number. The rest of the parameters can be left as they are. Setup the function this way and call it from OA Framework and the po form opens up with the details pertaining to the specified po number. The custom function has been created to allow the po form to open up automatically and query the po number in question. Since the default po form function does not handle this requirement, the custom function has to be created.<br /><br />This custom function can be made secure by making it available only for certain approved responsibilities. When the custom function is invoked from an OA framework web page, the invocation will fail if the user does not have access to the function.Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com2tag:blogger.com,1999:blog-38230068.post-70111738951453414092008-11-05T08:54:00.000-08:002008-11-05T09:15:30.756-08:00List of values comparison using XPath in BPELI was quite surprised to note that there was no easy way to compare an XPath expression to a static list of values in BPEL. <br /><br />For example, if we have to check for the condition that a given str_var variable in BPEL has one of the following values: ('A','B','C',D'), then the expression to be used can only be:<br /><br />bpws:getVariableData('str_var') = 'A' or bpws:getVariableData('str_var') = 'B' or bpws:getVariableData('str_var') = 'C' or bpws:getVariableData('str_var') = 'D'<br /><br />Since XPath does not provide an operator like the IN operator, we would be left with no option but to use multiple OR conditions to achieve the result.<br /><br />However, there is a neat workaround that we can build using regular expressions to achieve the functionality of the IN operator.<br /><br />XPath extension function matches() can be used to build the functionality.<br /><br />The same condition can be re-written now like this:<br /><br />xp20:matches(bpws:getVariableData('str_var'),'^(A|B|C|D)$') = true()<br /><br />This expression reduces into a single expression! We do not have to write multiple OR conditions any more! The matches function returns true if str_var variable contains any one of these values (A,B,C,D).<br /><br />Please note that this has been tested to work on BPEL processes in Oracle SOA Suite 10.1.3.x.Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-2163953327468559602008-10-01T16:40:00.000-07:002008-10-01T16:52:46.361-07:00Did I get my sequence right?Here is a little twister on Oracle sequence object. <br /><br />Typically, when we invoke a nextval operation on a sequence object, the current value of the sequence is incremented by 1 and the incremented value is returned. Our assumption here is that this happens irrespective of when and where the nextval operation is invoked.<br /><br />Turns out that we may be way wrong in our assumptions...<br /><br />Let us say we have a sequence object named DUMMY_S and its current value is 1.<br /><br />What would we expect to happen when we execute the following SQL statement?<br /><br />SELECT DUMMY_S.nextval, DUMMY_S.nextval FROM DUAL<br /><br />Think this is a no-brainer? Think again as the answer might be surprising.<br /><br /><br />The output will be 2,2 and NOT 2,3. <br /><br />Even though the nextval operation is called TWICE in the same statement, the actual increment is being done only ONCE. This behavior of the sequence object holds good for DML statements too.<br /><br />How is that for a twist?Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-61588213441367225742008-09-12T11:19:00.001-07:002008-09-12T11:19:44.846-07:00Copying BOMs/Routings across organizationsFor those of us who are mulling over the possibility of copying large sets of BOMs and Routings across inventory organizations, can consider using the following standard Oracle APIs:<br /><br />> BOM_COPY_BILL<br />> BOM_COPY_ROUTING<br /><br />These APIs are internally being used by Oracle in the 'Copy Organizations' feature that is supported in eBusiness Suite. But this feature would work only for creating new organization from a model organization. If we have to copy BOMs & Routings across already existing organizations, then we would have to tap into the APIs directly without using the 'Copy Organizations' feature.<br /><br />These APIs are PL/SQL packages and hence we can get in and understand the nuances of it before using it for any of our functional requirements. Enjoy exploring these APIs :-)Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com1tag:blogger.com,1999:blog-38230068.post-58234178376648750432008-09-12T10:44:00.000-07:002009-01-27T15:42:39.118-08:00iSupplier User Bulk Registration API<strong>Objective </strong><br /><br />The following is the outline for developing a bulk API utility that cancreate/register/approve isupplier user accounts. The utility will also be able to assign responsibility access to supplier user accounts, attach securing attributes including supplier, supplier sites to the user accounts. The securing attributes can contain multiple supplier site information. This utility can be developed in PL/SQL and registered to run as a concurrent program in Oracle eBusiness Suite.<br /><br /><strong>Overview</strong><br /><br />iSupplier uses 2 tables primarily for capturing the supplier details that is used as areference for the user account creation/ responsibility assignments/ securing attributes.<br /><br />> FND_REGISTRATIONS<br />> FND_REGISTRATION_DETAILS<br /><br />FND_REGISTRATIONS stores the supplier information including names, personal information etc. FND_REGISTRATION_DETAILS table stores attribute information related to thesupplier entry in FND_REGISTRATIONS. The attribute information includes SupplierId, one or more Supplier site Ids, responsibility Ids<br /><br /><strong>What should the utility do?</strong><br /><br />We will start with the assumption that the utility will be getting the supplier related data from a custom interface table. The information should include name, supplier number,supplier site code, operating unit name, first_name, last_name, email_address, phone.Email address will be used as the user name to create the account in FND_USER.<br /><br /><p>> The utility will read data from the interface table for each supplier</p><p>> It will insert the entries into FND_REGISTRATIONS table by using thefollowing standard Oracle API</p><p> o FND_REGISTRATION_PKG.insert_fnd_reg<br /></p><p>> The utility will make entries into FND_REGISTRATION_DETAILS table by using the following standard Oracle API</p><p> o FND_REGISTRATION_PKG.insert_fnd_reg_details<br /></p><p>> The utility should then launch an iSupplier business event by using this APIo FND_REGISTRATION_UTILS_PKG.publish_approval_event</p><br />> The utility should update the REGISTRATION_STATUS column in FND_REGISTRATIONS table for the supplier to ‘APPROVED’<br /><br /><strong></strong><br /><strong>Sample code for FND_REGISTRATIONS table insert</strong><br /><br />l_registration_id :=FND_REGISTRATION_PKG.insert_fnd_reg(p_application_id => l_app_id,p_party_id => l_party_id,p_registration_type => 'POS_REG',p_requested_user_name => l_interface.email_address,p_email => l_interface.email_address,p_assigned_user_name => NULL,p_registration_status => 'REGISTERED',p_exists_in_fnd_user_flag => 'N',p_first_name => l_interface.first_name,p_last_name => l_interface.last_name,p_phone => l_interface.phone_number);<br /><br />This API returns a registration Id which will be used as an input parameter for the rest ofthe API calls. The l_app_id variable holds the application_id value for ‘POS’ application.<br />l_party_id holds the party_id value for the supplier. The party_id can be retrieved using POS_VENDOR_UTIL_PKG.get_party_id_for_vendor API.<br /><br /><strong>Sample code for FND_REGISTRATION_DETAILS table insert</strong><br /><strong><br /></strong>We will initially insert 14 or more rows into this table for a corresponding supplier entry in FND_REGISTRATIONS. Each row provides value for one supplier attribute.3 Attributes are of particular interest here:SUPPLIER_ID0, SUPPLIER_SITE_ID0 and SUPPLIER_RESP_ID0.<br /><br />These attributes are used to provide value for supplier id, supplier site id and responsibility id respectively.If we have to tie the supplier user account to 3 supplier sites, then we can insert 3 rows into the table where each row will have the attribute name as SUPPLIER_SITE_ID0,SUPPLIER_SITE_ID1, SUPPLIER_SITE_ID2 with the corresponding supplier site idvalues. The same holds true for responsibilities also.<br /><br />Here is the sample code:<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Supplier Name',p_field_type => NULL,p_field_format => NULL,p_field_value_string => l_interface.supplier_name,p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Supplier Number',p_field_type => NULL,p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => l_supplier_id,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Sourcing',p_field_type => NULL,p_field_format => NULL,p_field_value_string => 'N',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'ISP',p_field_type => NULL,p_field_format => NULL,p_field_value_string => 'N',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Job Title',p_field_type => NULL,p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Note',p_field_type => NULL,p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'User Access',p_field_type => NULL,p_field_format => NULL,p_field_value_string => 'Any',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Restrict Access',p_field_type => NULL,p_field_format => NULL,p_field_value_string => 'Supplier',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Approver ID',p_field_type => NULL,p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => fnd_global.user_id,p_field_value_date => NULL);<br /><br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'Invited Flag',p_field_type => NULL,p_field_format => NULL,p_field_value_string => 'N',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'CollaborativePlanning',p_field_type => NULL,p_field_format => 'YN',p_field_value_string => 'N',p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'POS_SUPPLIER_SITE_ID0',p_field_type => 'NUMBER',p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => l_supplier_site_id,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'POS_SUPPLIER_ID0',p_field_type => 'NUMBER',p_field_format => NULL,p_field_value_string => NULL,p_field_value_number => l_supplier_id,p_field_value_date => NULL);<br /><br />FND_REGISTRATION_PKG.insert_fnd_reg_details(p_registration_id => l_registration_id,p_application_id => l_app_id,p_registration_type => 'POS_REG',p_field_name => 'POS_SUPPLIER_RESP_ID0',p_field_type => 'NUMBER',p_field_format => NULL,p_field_value_string =>to_char(l_resp_id)':'to_char(l_san_app_id), p_field_value_number => NULL,p_field_value_date => NULL);<br /><br />*<em> l_resp_id is the responsibility_id to be assigned, l_san_app_id is the application_id for the application tied to the responsibility</em><br /><br />Once these inserts are done, then the business event is called, which inserts 2 more rows into FND_REGISTRATION_DETAILS table. These rows hold the workflow item type and item key values. The successful execution of this workflow signifies that the user accounts are created and associated attributes are set.<br /><br /><strong>Conclusion</strong><br /><br />This mechanism can be used to load thousands of supplier user accounts with no manual user intervention. This mechanism also mimics the exact way that the iSupplier portal creates the user accounts from its OAF page. The supplier user accounts created thru this utility work the same way as a user account created from the supplier registration page of the isupplier portal.I am so glad that I hit upon this idea since we do not yet have an official Oracle API that can do this job for us :-)Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com1tag:blogger.com,1999:blog-38230068.post-39847332720633180972008-08-27T09:21:00.000-07:002008-08-27T09:50:42.813-07:00Look Ma! My Pseudo-Code uses XML!Have we ever wondered if there is a better way to document design/program logic for a large scale application?<br /><br />Typically we would consider using generic text based Pseudo-code techniques to lay out the flows. If the pseudo-code burgeons as time passes by, it becomes increasingly difficult to understand the flow from the myriad of statements that make up the pseudo-code. Indentation of statements can only help for small to medium scale designs. For large scale designs, it might be worthwhile to create or develop our own standards based XML language for documenting pseudo-code.<br /><br />The xml standard can be implemented using a well defined xml schema that we can develop for this purpose. The language should have well defined tags to capture assignments, initializations, scope, decision constructs, loops, text based CDATA sections etc.,<br /><br />Here are some of the advantages of an XML based pseudo-code:<br /><ul><li>Divides up the document into relevant sections based on xml nodes</li><li>The hierarchical node structure serves as an anti-dote to indentation of statements</li><li>Reader friendly since the different sections of the xml document can be expanded/collapsed</li><li>Cryptic logical flow statements are replaced with xml based name tokens</li><li>Validation of begin/end tags for for nested decision constructs, loops using well defined xml schema</li></ul>Of course, if the xml based pseudo-code is viewed from a normal text editor, it might make no difference from a text based pseudo-code. The xml based pseudo-code document should be viewed from a tool that can parse the xml document and serve up the document as a hierarchical tree of nodes and makes navigation easy. The tool should provide expand/collapse capability for the tree of xml nodes.<br /><br />A web browser like Mozilla firefox or Internet Explorer can readily serve up an xml document as a hierarchical tree of nodes and the pseudo-code could be viewed thru these browsers. Other industry leading xml toolkits can also be used to read the xml based pseudo-code.<br /><br />The beauty of xml is that we can create our own language to suit out professional needs and this can be put to good use for this requirement. Research based initiatives like CodeML( please check out www.omdoc.org) can also be adopted if it best serves our needs.<br /><br />Now, I leave you to mull over this XML :-)Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com2tag:blogger.com,1999:blog-38230068.post-37176433248039297062008-06-19T12:03:00.001-07:002009-03-24T17:27:15.055-07:00Hangover of FND_CONCURRENT.wait_for_requestIt is a well known fact that the following APIs are used in Oracle eBusiness Suite to control the submission of concurrent requests:<br /><br /><span style="font-weight: bold;">>fnd_request.submit_request</span><br /><span style="font-weight: bold;"> </span>This API is used to submit the concurrent request and get the request id for the submission<br /><span style="font-weight: bold;"><br /><span style="font-weight: bold;">>fnd_concurrent.wait_for_request<br /><span style="font-weight: bold;"> </span></span></span>This API is used to pause the execution of the rest of your application till the completion of the concurrent request or a specified timeout<span style="font-weight: bold;"> </span>interval<span style="font-weight: bold;"><br /><br /></span>One interesting fact about the usage of these 2 APIs in conjunction with each other is that the fnd_concurrent.wait_for_request API has a tendency to become totally unresponsive to the application.<br /><br />The remedy for the hangover is to issue a COMMIT after the fnd_request.submit_request API call and only then the fnd_concurrent.wait_for_request API should be called.<br /><br />This little nugget of info might save countless hours of debugging time!<br /><br />There are also times when we have seen that a parent request might in turn spawn sub-requests that can be termed as its children or worker requests. The fnd_concurrent.wait_for_request method does not wait for the sub-requests to complete. To know that, we would have to invoke fnd_concurrent.children_done method. This method returns true if the sub-requests have completed and false otherwise. The method also accepts a recursive_flag parameter that allows us to traverse all the sub-requests down the chain.<br /> <br /><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><span style="font-weight: bold;"><br /></span></span></span></span>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com9tag:blogger.com,1999:blog-38230068.post-54343139401065511902008-06-05T15:38:00.000-07:002008-06-05T16:14:34.887-07:00Email XML Publisher generated outputHow do we identify the output generated by xml publisher via a concurrent request in oracle ebusiness suite and email it to users?<br /><br />In order to implement this functionality, we would have to know the following:<br /><br />1. The concurrent request id of the program that is tied to an xml publisher defined layout<br />2. The path and name of the output file that is generated by the output post processor for the concurrent request<br />3. An email utility that can mail the generated output as an attachment<br /><br />It is quite obvious for us to know from where to pick up the request id. It would be interesting to know the name of the file and the path where the xml publisher generated output is stored.<br /><br />Let us assume that we have a concurrent request that is tied to an xml publisher layout which will format the output to a PDF document. When the concurrent request completes its run, it generates 2 output files in this case. The first file is the xml data file and the second file is the pdf file. Both the files get generated and stored in the usual $APPLCSF/$APPLOUT path.<br /><br />What is of interest here is the file name given to the pdf file. This file name looks to be a combination of the XML publisher definition code, the request id and a running number. How do we get to know this filename from within oracle?<br /><br />There is a table named FND_CONC_REQ_OUTPUTS that stores the request id, dir path and the file name, filesize and so on. This is the table that we use to read the file name from after looking it up with a particular request id. Once this is done, we are all set to email the file as an attachment. How do we do it from within oracle?<br /><br />Oracle provides us with a PL/SQL wrapper named SendMailJPkg for its internal java mail utility that is loaded into the database. We can use this to send the email and the contents of the pdf file as an attachment. The utility has a method named SendMail. This method has a list of parameters which are pretty straightforward to lookup and understand. The last parameter is a pl/sql table named attachments. We can send multiple attachments using this pl/sql table type parameter. In our case, to send the pdf as an attachment, we would simply have to instantiate one element in the pl/sql table and pass the file name including the path as a value to the element. Set all the other appropriate param values and call the method. The pdf file would land up nicely in the user's inbox.<br /><br />The final thing would be to piece all this together and make it into a nice little re-usable utility!Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com0tag:blogger.com,1999:blog-38230068.post-91935074837493652952008-05-12T10:48:00.000-07:002008-05-12T10:51:54.446-07:00The mystery of ORA-02069<p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">Recently, I stumbled upon the ORA-02069 error while trying to insert a row into a remote table referenced via a db link. True to the nature of the DML, the error indicates that the global_names parameter should be set to TRUE to complete the remote DML. The curious thing about this error is that it appears only when I try to do the insert via a concurrent program in Oracle eBusiness Suite. If the remote DML is performed thru a tool like TOAD, it works fine! </span></span> </p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">How could we workaround this error if we do not want to mess with the global_names parameter?</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">A simple solution unfolds...</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">Instead of attempting to write an insert statement directly in the concurrent program stored procedure, try wrapping the insert statement with an EXECUTE IMMEDIATE dynamic statement. </span></span> </p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">For example:</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">EXECUTE IMMEDIATE '</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;"> BEGIN</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;"> INSERT INTO remoteTbl <remotetbl> VALUES(<value>value list);</value></remotetbl></span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;"> COMMIT;</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;"> END;'</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">;</span></span></p> <p><span style="font-family:Times New Roman,serif;"><span style="font-size:100%;">Voila! It works like a peach.</span></span></p> <p><br /><br /></p>Poet in the mirrorhttp://www.blogger.com/profile/11822107510089745226noreply@blogger.com2