I cannot find any examples of the mappings needed to use the pub.db:call in webMethods 8.0.1.  The documentation is helpful, but is barren of any examples.  Basically the provided documentation defines little more than name, rank, serial number and description of each built-in service.  In the absence of any examples, in the documentation or anywhere else on the web, here is my humble attempt at providing some help.

Example 1:  A procedure with a single IN parameter.

I have a stored procedure inside a package in Oracle 10 with this signature: 

CREATE OR REPLACE PACKAGE PKG_EDI_FUNCTIONS AS

    PROCEDURE APPLY_BUS_RULES_TO_RAWDATA (
            inGcsControlNumber VARCHAR2
    );

END;

In a MAP flow step, I have mapped the ControlNumber variable shown on the left to the data/inGcsControlNumber variable defined on the right. 

data is defined as shown:

data	Document
inGcsControlNumber	String	maps to ControlNumber variable

In the pub.db:call flow step, I need to configure the call as follows:

$dbAlias	myDbAlias
$dbProc	PKG_EDI_FUNCTIONS.APPLY_BUS_RULES_TO_RAWDATA
$dbProcSig	See below
$dbParamsByOrder	true
$data	data document from above

To configure the $dbProcSig, follow these steps:

1) Right click on $dbProcSig and choose Set Value…

2) In the dialog that follows, click the Add button to add a row:

3)  In the next dialog, enter the parameter definition, like so:

4) Click OK to close the dialogs.  You now have the parameter defined for the procedure.

That’s it.  The call is defined and should execute as desired.

Example 2:  A procedure with IN and OUT parameters. 

I have a procedure with several OUT parameters that I want to call.  I then want to use the values of those parameters in an email sent by webMethods.

Here is the procedure’s signature:

PROCEDURE LOAD_MEASUREMENT (
        inGcsControlNumber VARCHAR,
        outRowCounter OUT NUMBER,
        outErrorCounter OUT NUMBER,
        outAccumulatedErrorMsg OUT VARCHAR2
    );

In a MAP flow step, prior to the db:call step, I set up the $data exactly as shown above in Example 1.  You only need to supply variables for the IN and INOUT parameters – do not supply values for the OUT parameters.

In the $dbProcSig, following the same process as in Example 1, I add four parameter definitions, like so:

So the Map step looks like this:

Now, that’s it.  The procedure is defined and should execute as expected.  However, we’re not done yet.  Where did the OUT values go? 

Getting at the OUT parameter values

It turns out that at run time, WM puts the OUT parameters in the Pipeline.  In this case, you will find outRowCounter, outErrorCounter and outAccumulatedErrorMsg in the Pipeline.

You can get to these in a MAP step, but there’s a little trick to doing so.  Try this:

1) After your db:call step, create a MAP step. 

2) In the Output channel of that MAP step, create a new String variable, name it something like the first OUT parameter.  In my case, I chose returnedRowCounter.  Repeating for each OUT parameter, I added returnedErrorCounter and returnedErrorMsg.

3) For each variable I just defined, right click and choose Set Value…  Set the value to the name of the OUT parameter, surrounded by %.  Then check the Perform variable substitution box.  Like this:

Now, the OUT parameter values are available to you to do further processing in subsequent flow steps.

Ta da!

Hopefully, this will help save someone a week of emails with softwareAG tech support.  They did, eventually, help me sort it out, and Deepa gets kudos for putting up with my nagging and for helping the blind to see.  Thanks.