The Lounge

This Blog

Home

Syndication

Bytes of Wisdom

September 2007 - Posts

Calling .Net Web Services from Oracle PL/SQL

I'm working relatively closely these days with an Oracle DBA on this super-cool mainframe integration project (anyone want to join me? LOL). The old batch ops were managed through cron'd jobs in Oracle and I was asked to take a different approach. I haven't found anything like Control-M here and was toying with the idea of rolling my own scheduling services. The management wanted the integration pieces written in VB.Net, not PL/SQL scripts as had been done in the past, fair enough. In a later meeting, the DBA had said that he REALLY wanted to replace the cron jobs with Oracle Jobs...hmmm...what to do, what to do...

I'd already planned on publishing .Net web services to support calls to the actual service objects. A scheduler would be configured to call the web services at set intervals and kick of the batch processing. I did a little research and found the utl_http library in Oracle. I ran a quick test if it was installed and working and was delighted.

With utl_http it's pretty easy to call an XML web service. It supports using PL/SQL to utilize HTTP requests and responses. To test it I threw together a very simple web service that allows anonymous callers to write to a custom event log. Now the DBA can automate sending me nasty grams (none received yet, I'm a little disappointed).

Here's the PL/SQL script that calls the web service. All you have to do is define the requesting soap envelope, set the appropriate HTTP header info, point to your target using the right protocol and fire!

declare
     http_req  utl_http.req;
     http_resp utl_http.resp;
     request_env varchar2(32767);
     response_env varchar2(32767); 
begin

request_env:='
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <LogMessage xmlns="http://tempuri.org/">
      <message>This is my message</message>
    </LogMessage>
  </soap:Body>
</soap:Envelope>
';
dbms_output.put_line('Length of Request:' || length(request_env));
dbms_output.put_line ('Request: ' || request_env);

http_req := utl_http.begin_request('http://wsXXXX/Test_WebService/Service.asmx', 'POST', utl_http.HTTP_VERSION_1_1);
utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Length', length(request_env));
utl_http.set_header(http_req, 'SOAPAction', '"http://tempuri.org/LogMessage"');
utl_http.write_text(http_req, request_env);

dbms_output.put_line('');

http_resp := utl_http.get_response(http_req);
dbms_output.put_line('Response Received');
dbms_output.put_line('--------------------------');
dbms_output.put_line ( 'Status code: ' || http_resp.status_code );
dbms_output.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );

utl_http.read_text(http_resp, response_env);
dbms_output.put_line('Response: ');
dbms_output.put_line(response_env);

utl_http.end_response(http_resp);

end;

See? Easy as PI! It's practically self explanatory (and the web service itself gives the client pretty much the info they need to get wired in). As you see, I used varchars to declare vars for the request and the response. With larger SOAP messages you'd want to take a different approach (reading chunks into a buffer) because the varchar is so small.

Here's the console output showing what was sent and received (reformatted a bit for readability):

Length of Request:324
Request:
<soap:Envelope 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <LogMessage xmlns="http://tempuri.org/">
      <message>This is my message</message>
    </LogMessage>
  </soap:Body>
</soap:Envelope>


Response Received
--------------------------
Status code: 200
Reason phrase: OK
Response:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope 
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <LogMessageResponse xmlns="http://tempuri.org/">
            <LogMessageResult>Logged Message: [This is my message]</LogMessageResult>
        </LogMessageResponse>
    </soap:Body>
</soap:Envelope>

Now how cool is that? This was really my first time consuming (or starting to anyway) .Net web services from a disparate technology and hadn't really spent much quality time with SOAP since around 2002. Now kicking of the jobs through Oracle will be a breeze...after I iron out authentication, of course.

Now, to learn how to use the 'out of the box' XML parser that comes with Oracle...

Posted Sep 14 2007, 06:04 PM by jlockwood with 9 comment(s)

Filed under: Oracle, Web Services, SOAP, SQL

Subversion Tip of the Day - Moving Files

Many of you are beginning to use Subversion for your source control repository these days. I am also sure that you have found SVNTortoise to be a welcome addition to Subversion and use it also. Moving files and directories at the command line is a pretty straight-forward affair, but doing so with Tortoise is a little counterintuitive (but never-the-less simple).

Here's the easiest way that I've found to move a file or subdirectory around using Tortoise...

Before I begin, here is folder/file structure for my demonstration

folders are named based on initial hierarchy

documents are likely named...the following are documents in folder1.1

and here are the contents of folder1

Moving a file/directory

Many times (especially early in projects) we may find that we've decided that a file should be in another directory. How to move a file was not readily apparent to me and during the error phase of my trial and error I often ended up exporting the file and adding it to the new location. This is bad because I lost history on the file at it's new location.

Moving the Wrong Way:

Here's the revision graph for doc1.1.1.txt before a bad move:

Now I'll drag and drop it to folder 2 by using cut and paste:

This is the revision graph after committing the move:

As you can see the revision history is lost for this item because Subversion was unaware of the move.

Moving the right way:

To move files the right way using Tortoise, highlight the file(s) you want to move and drag and drop to the new location using the RIGHT mouse button.

Revision graph of doc1.1.3.txt before the move:

When you drop the file(s) to the new location you will be presented with a popup menu

Here you can select a SVN move, copy, or export. I selected 'SVN Move versioned files here'.

Notice the 'Add' symbol for the moved file.

The revision graph for the file hasn't changed (because the contents are unaltered)

The revision graph for the parent folder shows the file move

So, as you can see, the history is preserved. The same process is used to move multiple files and directories to new locations. You can also use 'Rename' to move individual files, but I'm not too fond of the approach (it's also not good if done at the folder level). I do wish that there was a hotkey for this functionality or at least inclusion of the move command into the normal SVN menu. The TortoiseSVN documentation does cover the subject briefly but without the pretty pictures. I sort of wish that I had RTFM before screwing up the history on some of my repositories, but where's the fun in that?

Posted Sep 12 2007, 11:18 AM by jlockwood with 4 comment(s)

Filed under: Tools, Subversion

VB.Net oddity of the day - Array Declarations

I ran into a recent issue today relating to array declarations in VB.Net. I looked up array declarations on the net and found something that gave me hope, but alas...

"Arrays

In VB6 declaring an array
   Dim Items(5) As String
gives you 6 items from index 0 to index 5. In VB.NET, this same declaration will yield 5 elements from index 0 through index 4. Be on the look out for "out of bounds" type errors. Also, all arrays in .NET must now be zero-based."

My personal favorite is the last line. He says it like it's a bad thing! In any case, I didn't include the source of the quote because the guys is wrong (or at least no longer correct for all versions after .Net beta1, I believe). True, all arrays in VB.Net must now be zero-based, and thank the computing gods for that, but for the sake of backwards compatibility VB.Net still supports the good(bad)-ole-way of declaring arrays.

Coming from C# (and C and Java...) this was a bit of a surprise. In particular because I have the habit of declaring know max sizes for arrays as constants. If I was only a VB programmer this wouldn't be so annoying, but it is. Also, handy little tools like Convert.Net don't handle this case at all, so if you used it to convert C# snippets to VB you would be serving yourself up an instant logical error. Those Visual Fred guys, always trying to be different...

So, "Dim Items(UpperBound) as String" is just the way things work, I'll just have to accept it. Thank God for TDD, that's all I have to say. If I wrote a bunch of code on my previous assumptions without coverage I would have hated life ferreting out all the places that I would have made the same mistake. I know that this is the way VB6 worked, but again, I thought that VB6 was pretty stupid when it came to arrays.

It's kind of sad though, why can't the VB folks conform a little to the masses?

I.E. Here are some other languages declaring arrays containing 10 elements (My COBOL is way weak, so excuse any syntax errors):

C/C++:

int myArray[10];

Java:

int[] myArray = new int[10];

C#:

int[] myArray = new int[10];

Ruby:

myArray = Array.new(10)

COBOL:

01 MY-ARRAY

05 ARRAY-VALUES PIC9(3) OCCURS 10 TIMES

FORTRAN:

DIMENSION myArray(10) ! Although arrays can also be defined with upper AND lower bounds similar to VB6

LISP:

(setq myArray (make-array 10 :element-type 'integer))

Posted Sep 11 2007, 03:05 PM by jlockwood with 2 comment(s)

Filed under: Programming, VB.Net