Parsing SOAP response with Oracle PL/SQL

 

About 2 years ago I posted “how to invoke web-service from PL/SQL” post Post is here.

Since then the technology has changed and I will show a new technique to parse SOAP response with Oracle XMLTable.

So, suppose you got the following response from the server

 

And you want to show “Value” attribute of each Lov node.

If you check my previous post you will find that result XML is stoted into CLOB object.

Lets assume that I put it into a table called my_clob_table into a column named “xml”.

The following SQL will return the desired result

select VAL
   from my_clob_table,
   xmltable(xmlnamespaces(
                       --namespaces that are defined in 
                       'http://schemas.xmlsoap.org/soap/envelope/'  as "soap",       
                       'http://siebel.com/TevaOfflineBatchSync/Order' as "ns" 
                    ),
                    --XPath to specific node
                    '//ns:QueryEntity_Output/ns:QueryResult/ns:Lov'   passing             
                    --XPath from above should work on my_clob_table.xml
                    --which holds XML text inside CLOB column
                     XMLType(my_clob_table.xml)                         
                     columns VAL                 varchar2(1000) PATH '@Value'
                    ) header_data

Maybe it looks complicated, but it is really very simple. In the from clause you put your “my_clob_table” (it is obvious because you want to parse the data in “xml” column).

Next you create a result set (similar to ref cursor), by using preserved function xmltable.

It get several parameters.

You need to provide namespaces used in the source XML. In case of SOAP message it will always be at least 'http://schemas.xmlsoap.org/soap/envelope/' and I also have my custom namespace 'http://siebel.com/TevaOfflineBatchSync/Order'. It is very important to give them alias so you can refer them later in Xpath expression.

Next you specify the Xpath expression that will return a result node set from the source XML.

How do you connect it XML ? Note the key word “passing”  and immediately after it we access XML data which is stored as CLOB in “XML” column in “my_clob_table” table. Note also that we convert it implicitly to XMLType by XMLType(my_clob_table.xml)

Now we need to define what data do we want to present as column in the result set.

You do it by specifying columns key word.

In my case I wanted to present column named “VAL” and the value that I want this column to have is accessed by Xpath expression which specified just after a keyword PATH. In my case it is the value of “Value” attribute (Note the source XML structure in the attached image)

Solving “Missionaries and cannibals problem” with Prolog by using DFS and BFS

 

"Missionaries and cannibals problem” is well known problem in Computer Science.

The idea is the following: You have equal number of cannibals and missioners on one side of the river. You have to move them all to other side of river, by using boat. Boat can take 1 cannibal and 1 missioner or 1 cannibal and 0 missioners or 1 missioner and 0 cannibals. The problem is that at no point there should not be on some side of the river the number of missioners will be grater the number of cannibals , because missioners will convert cannibals religion (The original problem talks about a different case, when cannibals will eat missioners if there will be more cannibals then missioners, but I thing that my introduction of the problem is more ethic :) ).

This problem falls into AI (Artificial Intelligence) area which solved relatively easily with Prolog. The pattern is the following .

1. Define the “state”

2. Define now to move from state A to state B

3. Define the “Goal” state

4. Define the search , that checks all possible legal “moves” that bring from initial state to the goal

Ok. So lets define this points…

1. State I choose is to specify how many cannibals and missioners are currently on specific side of the river

I will assume that we need to move 3 cannibals and 3 missioners from the left side of the river to the right side

s(NumOfMissioners,NumOfCannibals,RiverSide).

2, Now we need to define move predicate

% move from left to right.
move(s(M1, C1, left), s(M2, C2, right),M,C) :-
        % obviously we cannot move more people then we have
        M1 - M >= 0,
        C1 - C >= 0,
        % calculate people on each side of the river after move
        M2 is 3-M1+M,
        C2 is 3-C1+C,
        M3 is M1-M,
        C3 is C1-C,
        % check exersize constraint
        noConvertion(M2, C2),
        noConvertion(M3, C3).

% move from right to left
move(s(M1, C1, right), s(M2, C2,left ),M,C) :-
       % obviously we cannot move more people then we have
       M1 - M >= 0,
       C1 - C >= 0,
       % calculate people on each side of the river after move
       M2 is 3-M1+M,
       C2 is 3-C1+C,
       M3 is M1-M,
       C3 is C1-C,
       % check exersize constraint
       noConvertion(M2, C2),
       noConvertion(M3, C3).

move is pretty simple . It says “I move from some state from the left side of the rivers to some state on the right side of the river.I move M missioners and C cannibals. After you move the people you have to adjust the numbers on both sides of the river and also check that there is no situation when missioners can convert cannibals”

noConvertion  actually checks that after move no conversion occurred.

% to make sure that there is no conversion
% need to make sure that we have an equal number of missioners and cannibals
% or , the number of cannibals is grater then
noConvertion(X,X).
noConvertion(X,Y):-Y>X ;Y=0.

 

3. Goal is obvious.

goal(s(3,3,right)).

4. Now the search implementation.

   I choose standard DFS/BSF implementation in prolog that you can find all over internet

This one for DFS

% the goal is that all peopel on the right side of the river
dephfirst(Node,[Node],_):-goal(Node).
dephfirst(Node,[Node|Sol],Visit) :-
        sail(X,Y),
        move(Node, Node1,X,Y),
        not(member(Node1, Visit)),
        dephfirst(Node1, Sol, [Node|Visit]).

and this one for BFS

% breadthfirst( [ Path1, Path2, ...], Solution):
%   Solution is an extension to a goal of one of paths

breadthfirst( [ [Node | Path] | _], [Node | Path])  :-
  goal( Node),!.

breadthfirst( [Path | Paths], Solution)  :-
  extend( Path, NewPaths),
  append( Paths, NewPaths, Paths1),
  breadthfirst( Paths1, Solution).
extend( [Node | Path], NewPaths)  :-
  findall( [NewNode, Node | Path],
         ( sail(X,Y),move( Node, NewNode,X,Y), not member( NewNode, [Node | Path] ) ),
         NewPaths).

 

You can see that implementation is straightforward. It creates a search tree for DFS or BFS  for all possible options to call move predicate until it finds the goal.

   One thing that still need to be mentioned is sail predicate. It actually stands for possible boat movement across the river sides.

% sail(X,Y) transfer by boat . X for missioners and Y for cannibals
sail(2, 0).
sail(0, 2).
sail(1, 0).
sail(0, 1).
sail(1, 1).

To start the program just use

For BFS

solve( s(3,3,left), Solution)  :-
  breadthfirst( [ [Start] ], Solution0),reverse(Solution0,Solution).

For DFS

solve(3,3,Solution):-
dephfirst( s(Misioners,Canibals,left), Solution,[]),!.

Read response from invoking Siebel CRM “EAI HTTP Transport” business service

 

I wrote a post some time ago about how to call URL from Siebel CRM. This post will deal with processing the response from remote page.

So here you go, follow the instructions from my previous post and submit data to remote page. Many times you expect not only to send data, but also receive a response back.

To remind you , in order to send data you have to invoke

oService.InvokeMethod("SendReceive", oInputs, oOutputs);

Common sense tells you that response is probably located in object.

But if you try to run

oOutputs.GetValue();

You will get the following error message

PropertySet GetValue call failed. PropertySet Value is marked as binary data, starting with….

And this is because Siebel can process data only in UTF-16 encoding. If it is not, Siebel “thinks” that it is a binary data.

Bellow is very simple code to convert the HTTP output to a proper format

var oTransService = TheApplication().GetService("Transcode Service");
var oTransOutputs = TheApplication().NewPropertySet();
oOutputs.SetProperty("ConversionMode", "EncodingToString");
oOutputs.SetProperty("SourceEncoding", "CP1252");
oTransService.InvokeMethod("Convert", oOutputs, oTransOutputs);
var sResponse = oTransOutputs.GetValue();

 

After you execute it. You will fine HTTP response string in sResponse variable.

 

P.S. You can find more information in 536101.1 metalink note

Prolog–remove duplicate list members without member predicate

 

This is completely not relevant to all my posts and a work I do. I am taking a “Prolog” class and find this language just amazing. So decided to post some of my assignments, because probably students all over a world also get similar one

My assignment is to write a program that removes duplicate list members, BUT you cannot use “member” predicate.

Lets start…

So if my list is empty there are no duplicate members for sure

rem([],[]).

The same is true if I have only one member in the list

rem([X],[X]).

Now comes the tricky part. If we recognize 2 members in the list with a same value, we will return the same list , but only with one member

rem([X,X|Tail],[X|Tail]).

And this is the start point for the program. Get the list and check it , by sending list tail to recursion

rem([X|Xs],Res):-rem(Xs,Res0),append([X],Res0,Res).

Ok, lets run it….

 

Well, we definitely got a correct answer, but what about incorrect answers? This is because of Prolog backtracking mechanism. After it find the correct answer it doesn’t stop and continue to check other options. So we add “red cut” to prevent backtracking

Now we have the correct answer 

Invoking web service from Siebel with EAI HTTP Transport Business Service

 

Some recent task I did was to invoke a web-service from Siebel to send SMS message.

I have to admit that I never did such a thing before. So I started to read Siebel “bookshelf”  documentation (you can throw this crap to the garbage . It is worthless), search Google. Found plenty bad written tutorials about invoking workflow process that can invoke web service or call http URL.

        Half of the staff I didn’t understand, the  other half didn’t like, but I understood one thing: at the end Siebel works with a Business Service (BC) called “EAI HTTP Transport “.

As I mentioned I am not to good with clicking on buttons in Siebel Tools, but pretty good with writing code. So if I know that Siebel uses a BC, why not to use it directly?

  The result is this simple function that defines XML message and uses POST method to submit data to remote web-service

function SendSms(Inputs, Outputs)
{
     //get Business Service
     var bs = TheApplication().GetService("EAI HTTP Transport");
     var inp = TheApplication().NewPropertySet();
     var outputs1 = TheApplication().NewPropertySet(); 
     // it is web-service, so use POST method
    inp.SetProperty("HTTPRequestMethod","POST");
     //Content type as in all HTTP request
    inp.SetProperty("HTTPContentType", "text/xml; charset=UTF-8"); 
    //web-service end point
    inp.SetProperty("HTTPRequestURLTemplate","https://**********/imsc/interfaces/largeaccount/la3.sms");
    //define the data to be send
    var reqVal = '<?xml version="1.0" encoding="utf-8" ?> '+
    '<request> '+
    '  <head> '+
    '    <auth> '+
    '      <account>CompanyName</account> '+
    '     <user>userName</user> '+
    '    <pass>Pass</pass> '+
    '</auth> '+
    '      <action>sendsms</action> '+
    '  </head> '+
    ' <body> '+
    '   <addr> '+
    '     <from>039535640</from> '+
    '    <to> '+
    '      <cli>97254545450</cli> '+
    '  </to> '+
    '</addr> '+
    '<data> '+
    '  <msgtype>text</msgtype> '+
    '   <text>This is SMS message text</text> '+
    ' </data> '+
    '  <billing> '+
    '      <port>0</port> '+
    '    </billing> '+
    '  </body> '+
    '</request>';
    //set the data
    inp.SetProperty("HTTPRequestBodyTemplate",reqVal);
    //invoke. The result can be found in "Outputs" 
    bs.InvokeMethod("SendReceive",inp,Outputs);
}    

Google Chrome Application Cache 5MB limit. No More!!!!

There is a grate feature in HTML5 that allows you to cache the content of the web-site locally and use it even if the server is down and there is no internet connection. All you need to do is to defined your manifest file and use the following syntax in HTML code

<html manifest="example.appcache">
  ...
</html>

Here example.appcache is the name of your manifest. I am not going to explain how do define the manifest or how to use it. There is plenty of blogs, articles about it. Just Google!! (e.g Good Post about caching with HTML5) I am going to explain how to solve a huge problem with this engine on Google Chrome.  Don’t know what guys from Google thought (actually I do  ), but they limit the size of the cache to 5MB only. Men!!! If I have 3 pictures on my site, I probably already use at least 1MB on the cache. And any way  - if you use offline manifest at some point you will realize that you need more space. Specially if you are developing mobile application 

    But Google doesn’t provide any configuration or setting to change this limit. Or at least not officially. This is how you can do it not-officially

First of all download some sqlite database client. I usually use Sqlite Administrator

Go to “YOUR USER DOCUEMENTS DIRECTORY”\AppData\Local\Google\Chrome\User Data\Default\Application Cache. For example on my Windows 7 (and yes , it is Legal )

It is C:\Users\mshapira\AppData\Local\Google\Chrome\User Data\Default\Application Cache\index

Open the file “index”

It is a database file. You need to update QUOTE table

By default it has no values. You need to insert 2 values. First one is the web-server that serves the page you want to cache and the second one is the size of the cache. Fill free to set it to some large value

 insert into quota values("http://localhost:80/", 100000000);

You can always view what is the size of your cache by simply typing in Chrome address bar :chrome://appcache-internal

Sending SOAP message as plain XML with java

 

There are many Java implementations for working with web services, but all of them request having some heavy framework (e.g. AXIS).  You need to generate proxy classes figure how to use them and eventually you finish with 20MB program that maybe does something very simple like calling a web  - service that converts money from US dollar to Euro.

So I will show how can you use only Java core classes without any additional libraries and send SOAP message as plain XML text.

//create URL object
URL url = new URL( "http://someurl.com/soap" );  
rc = (HttpURLConnection)url.openConnection();  
//you need to check if server expects POST or GET
//but to be honest I never saw web-service expecting GET reqiest

 rc.setRequestMethod("POST");  
 rc.setDoOutput( true );  
 rc.setDoInput( true );   
 //it is very important to specify the content type. Web-service will reject the request if it is 
 //not XML
 rc.setRequestProperty( "Content-Type", "text/xml; charset=utf-8" );  
//and here comes some dummy SOAP message
String reqStr = "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ord=\"http://someurl.com/OfflineBatchSync/Order\">"+
         "<soapenv:Header/>"+
         "<soapenv:Body>"+
         "   <ord:CreateOrderHeader_Input>"+
         "      <ord:CustomerPO></ord:CustomerPO>"+
         "      <ord:SubOrderType>VMI</ord:SubOrderType>"+
         "      <ord:PreSaleFlg></ord:PreSaleFlg>"+
         "      <ord:DistributorId></ord:DistributorId>"+
         "      <ord:AccountId>1-6T4N</ord:AccountId>"+
         "      <ord:OrderStatus></ord:OrderStatus>"+
         "      <ord:RequestedDate></ord:RequestedDate>"+
         "      <ord:ShipInstructions></ord:ShipInstructions>"+
         "      <ord:PriceListId></ord:PriceListId>"+
         "   </ord:CreateOrderHeader_Input>"+
         "</soapenv:Body>"+
         "</soapenv:Envelope>";
//several more definitions to request
int len = reqStr.length();  
rc.setRequestProperty( "SOAPAction:", "\"document/http://someurl.com/OfflineBatchSync/Order:CreateOrderHeader\"" );  
rc.setRequestProperty( "Content-Length", Integer.toString( len ) );
rc.setRequestProperty( "Connection:", "Keep-Alive" );
rc.connect();  
//write XML to the server
OutputStreamWriter outStr = new OutputStreamWriter( rc.getOutputStream() );   
outStr.write( reqStr, 0, len );  
outStr.flush();

/* Here is the important part, if something goes wrong and excetion will
* be thrown and you will have some meaningless exception saying blah.. blahh HTTP 500
* which actually doesn't tell you a lot about what happen.
* However most web-services provide as a response some error page that displays what 
* was wrong. It whould be nice to see this page instead of stupid HTTP 500.
* It is not difficult . All you need is actually read not the response stream , but the error stream
*/

try
{
  read = new InputStreamReader( rc.getInputStream() );  
}
catch(Exception exception)
{ 
  //if something wrong instead of the output, read the error
  read = new InputStreamReader( rc.getErrorStream() );  
}

//read server response
StringBuilder sb = new StringBuilder();     
int ch = read.read();  
while( ch != -1 ){  
  sb.append((char)ch);  
  ch = read.read();  
}  
String responseTr = sb.toString();

Basically this is all you need . After execution of the code above responseTr  variable will hold the response SOAP message in case of success or response error (In case of error it will be HTML script so you may want to display it in browser or to strip HTML tags)

Accessing remote Oracle DB from Siebel server script and executing insert with bind variables

 

It is very common to be asked to develop an interface between Siebel and other systems. It could be that other system has some kind of API or maybe it doesn’t . In my case , remote system doesn’t has any API, but you can access its database. So how can you do it from Siebel server side script?

First of all lets create  some dummy table that will be used as insert target

create table test_insert (data varchar2)

 

Now define in some Business Service new server script.

Explanation of the code can be found in the code comments

function Service_PreInvokeMethod (MethodName, Inputs, Outputs) {
   if (MethodName=="Connect")
   {
       //obtain connection object
       var oConnection = COMCreateObject("ADODB.Connection");
      //connection objects
        var cmd;
       var myparam;
       var res;
       var adVarChar =200;
       var adParamInput =1;
       var adCmdText = 1;

       try
       {
       //here I use TNS style connection string to connect to remote Oracle database 
       oConnection.ConnectionString = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=130.39.120.58)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));
uid=system;pwd=manager;";
       oConnection.Open();
      //create command object
       cmd = COMCreateObject("ADODB.Command");
      //assign connection to command
       cmd.ActiveConnection = oConnection;
      //this is SQL statement that I am about to execute
       var insertStmt = "INSERT INTO TEST_INSERT (DATA) VALUES (?)";
  
       cmd.CommandText = insertStmt;
       // command type is - text
       cmd.CommandType = adCmdText;
       //create and append new parameter
       // 1 = first parameter
       //  adVarChar = database column type
      //   adParamInput = parameter direction (input)
      //   5 - the lenght of the parameter value
      //   TEST2 - parameter value
       cmd.Parameters.Append(cmd.CreateParameter(1, adVarChar, adParamInput,5,"TEST2"));

       // Now, inserting into the data source...
       cmd.Prepared = true;
       //execute update - no commit is needed
       res = cmd.Execute();

        Outputs.SetProperty("DONE","DONE");


       }
       catch(e)
       {
          Outputs.SetProperty("DONE","NOT OK "+e.toString());
       }
       finally
       {
        //release variables
         adCmdText =null;
         adParamInput = null;
         adVarChar = null;
         myparam = null;
         cmd = null;
         oConnection = null;
       }
          return (CancelOperation);
   }
     return (ContinueOperation);
}

Dynamically Execute SQL statement stored in CLOB field with bind variables

 

  I saw many examples in the past when SQL statement was actually stored in CLOB field and programmer had to execute the statement dynamically.

All Oracle PL/SQL developers are familiar with “execute immediate” statement, but unfortunately  it cannot use CLOB field as an input.

But it is not the only way to execute the code dynamically.  Oracle also provides DBMS_SQL package that can execute the code dynamically and it can get the statement as an array data type. We will use it

   Here how you can do it.

Let assume that your SQL will be stored in the following variable

l_stmt         CLOB;

Now we need to split the CLOB to chunks that will became a member of array I mentioned previously

v_upperbound NUMBER;

This is array definition

v_sql        DBMS_SQL.VARCHAR2S;

Calculate array length

v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt)/256);

Fill array members:

FOR i IN 1..v_upperbound
LOOP
         v_sql(i) := DBMS_LOB.SUBSTR(l_stmt
                                  ,256 -- amount
                                  ,((i-1)*256)+1 -- offset
                                  );
END LOOP;

 

Create new cursor:

v_cur        INTEGER;

v_cur := DBMS_SQL.OPEN_CURSOR;

 

Prepare your statement for execution , but parsing array members

DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);

You can also use bind variables at this point

dbms_sql.bind_variable( v_cur, ':USER_NAME', ‘SYSADMIN’);

All what is left is to execute the statement

v_ret        NUMBER;

v_ret := DBMS_SQL.EXECUTE(v_cur);

 

Bellow if full example that uses approach described above

declare

  l_stmt       CLOB;
  v_upperbound NUMBER;
  v_cur        INTEGER;
  v_sql        DBMS_SQL.VARCHAR2S;
  v_ret        NUMBER;

begin
   -- get sql statement into CLOB
  select statement
    into l_stmt
    from XX_NEW_STATEMENTS_V --some view that holds CLOB 
                                         --with SQL
   where statement_type = 'SETUP'
     and seq_num = 10;

  v_upperbound := CEIL(DBMS_LOB.GETLENGTH(l_stmt) / 256);

  FOR i IN 1 .. v_upperbound LOOP
    v_sql(i) := DBMS_LOB.SUBSTR(l_stmt, -- clob statement
                                256,     -- amount
                                ((i - 1) * 256) + 1 
                                );
  END LOOP;
        
  v_cur := DBMS_SQL.OPEN_CURSOR;
        -- parse sql statement
  DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
        -- use bind variable if you need
  dbms_sql.bind_variable(v_cur, ':USER_ID', 235341);
  -- execute
  v_ret := DBMS_SQL.EXECUTE(v_cur);

end;

How to make Jdeveloper to display all messages in English only

 

If you want to make JDeveloper to display messages (console and messages on the web page) always in English, just pass 2 following parameters to the JVM

-Duser.language=en -Duser.country=US  

JDeveloper 9

 

JDeveloper 10

Downloading single segment of specific context of Oracle Application Descriptive Flex Field

 

So you have a DFF. Let say is is “Batch Information” of “Process Manufacturing Process Execution”. It has several contexts and each context has several segments.

What I want to do is to download FREEZE_BATCH segment inside “Global Data Elements” context

But how exactly go I get to this exact segment?

In the past Oracle provided detailed explanation about  how to use FNDLOAD utility to download all parts of DFF. You can check them in the following metalink notes:

Note: 735338.1 - Tips About FNDLOAD
Note: 274528.1 - How To Download Single Context Using FNDLOAD For Descriptive Flexfield
Note: 745689.1 - How To Download A Single Flexfield Structure Using FNDLOAD?
Note: 316600.1 - Download Multiple Flexfields From FNDLOAD
Note: 603036.1 - How Do I Determine the DESCRIPTIVE_FLEXFIELD_NAME to be Used During FNDLOAD

Well, it is all good , but at the moment of writing this post, non of them will work, because Oracle changed the structure of DFF lct file, but didn’t inform users about this. So if you on the last release of 11i (and accrding to Oracle there are not going to be any more patches for 11i) or R12, all the notes above are useless.

But you still have lct file, right? So I tried to understand how to assemble the download  command just by looking into lcf file.

vi $FND_TOP/patch/115/import/afffload.lct

and I found the following section in the file

 

 

Aha… Exactly what we need . Now when I have this information I can assemble the command in zero time

Here it is in details…..

FNDLOAD apps/apps 0 Y DOWNLOAD

$FND_TOP/patch/115/import/afffload.lct ./MY_DFF.ldt DESC_FLEX

-- Process Manufacturing Process Execution application

APPLICATION_SHORT_NAME="GME"

-- DFF title

DESCRIPTIVE_FLEXFIELD_NAME="BATCH_FLEX"

-- Context

DESCRIPTIVE_FLEX_CONTEXT_CODE="Global Data Elements"

-- Segment Code

END_USER_COLUMN_NAME=FREEZE_BATCH

-- Database column name

APPLICATION_COLUMN_NAME=ATTRIBUTE2

Here it is as a single line command

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ./MY_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME="GME" DESCRIPTIVE_FLEXFIELD_NAME="BATCH_FLEX" DESCRIPTIVE_FLEX_CONTEXT_CODE="Global Data Elements" END_USER_COLUMN_NAME=FREEZE_BATCH APPLICATION_COLUMN_NAME=ATTRIBUTE2