I’m hoping someone with some experience using pub.db:call to execute a Stored Procedure might be able to shed some light on its usage. The Built-in Services Guide says this service is supposed to output a string named $dbMessage that contains an operation status, and it does. However, the usage note at the end of the description says: “The output will also contain output parameters and procedure return values and keyed name (the return value is called RETURN_VALUE).” That’s exactly what I need, multiple output values resulting from the SP execution, but the only output from this service in the pipeline is $dbMessage. So how do I access these other outputs?!? I’m running IS 4.6, if that’s relevant. Any help would be greatly appreciated.

Mike,

In the output parameter of pub.db:call, you need to create the EXACT name of the parameter that the storedproc is going to return. Then, in the output side of the map create a variable by the same name and map the two together. If you do not map the new variable to the output side (right side) of the flow, you cannot capture.

So, now you have something called $dbMessage

Maybe you are going to return an unique ID for an order called OrderID.

Create an output variable under the output side of pub.db.call (in the middle of the flow screen and call it “OrderID”.

Then on the right side of the screen, create a string variable called “OrderID” and map these two together before you save. Make sure you SAVE.

Hope this helps.

ray

Ouch, that’s very, very bad for us. We were hoping this was a generic method to execute any SP, so it would blindly return whatever output the SP generated. Considering we have literally thousands of stored procs with different I/O sigs, there’s no way we can take this approach. Worse yet, although we’re in the process of converting these SP’s to PL/SQL, we can’t change their signatures at all without breaking our existing apps (not an option).

Does anyone know if this is any better with ES? I realize this is the wrong forum to ask, sorry. Thanks for the info though, Ray. BTW - is the Built-in Services Guide just batty?

Mike R.

ES is pretty much the same scenario.

In a previous project, I was able to successfully use pub.db:execSQL against SQL Server to call SPs.

The $dbSQL parm was of the form EXEC wm_GetAddressID ?,?,?

The $dbParamNames were set to identify the pipeline var names that corresponded to the proc params.

The $dbParamTypes were set to the proper types for each var.

execSQL has a record list output named results. I mapped this to a record reference that defined the output fields by the precise name that the proc returned.

It worked well as long as the proc explicitly returned the parms with something like:

SELECT @ErrNum AS Status, @StatusMsg AS StatusMsg, @Session AS Session, etc.

But this isn’t really any different from Ray’s suggestion.

I guess I’m unclear as to the specifics of your objection/concern. At some point, your services have to make reference to specific column names. pub.db:call has one way to “declare” the columns, while pub.db.execSQL has another. What did you have mind for the services that need to know what the SP outputs are? If you change SP signatures, the services that reference the outputs will break too.

Hi,
I am also facing a similar problem. i have a to call a Oracle stored Procedure from IS server.The stored procedure retuns a Pl/SQL record.I am using IS REF cursor to return the record.How should I map the return PL/SQL record to webMethods IS recordSet.
The stored procedure returns a EmpRec(PL/SQL record) with 20 fileds. I used pub.db.call service, it gives me error Invalid column type as it could not recognise EmpRec.
What shall i do to run the PL/SQL record.
The signature is
Procedure give_empDetails (emp_id IN NUMBER,emp_detail OUT EmpRecType)
Please help me out.
Regards
johnson

Rob’s suggestion to use pub.db.execSQL seems to be the better of the options here for everyone.

To call pub.flow.savePipeline immediately after the pub.db.call invocation will reveal more information about what is in the pipeline (use pub.flow.restorePipeline to view the results), but using pub.db.execSQL allows a user to get a generic result set as opposed to the string that pub.db.call wants to kick back.

By using Rob’s suggested course of action, the stored procedure outputs will be members of the generic result set named “results” in the pub.db.execSQL Service Out.

Hopefully, I am not just repeating what others have said.

OK, I’m beginning to see the perspective on this now. It might help to explain my context a bit more. We’re in the process of building a bridge between our current- and next-generation systems. The current-gen system uses a virtual DB layer to manage multiple distributed DB instances. When this current-gen system needs to execute an SP in the next-gen system it will use webMethods to do so.

So we actually don’t have other services handling the results of the SP execution. All we want to do is execute an arbitrary SP (name and params provided in the request) and return it’s results to the requestor.

We did also look at pub.db:execSQL, and it does appear to be more in line with our goal, since it allows for a generic record list to be returned. We gravitated toward pub.db:call however, since it seemed to be made for executing stored procs. All things considered, it looks like execSQL may suffice after all. So we’ll give that a try.

Thanks for everyone’s help. And by all means, if you have other comments about our usage of the platform, etc. feel free to let me know.

I tried Rob’s approach of calling a stored procedure via the pub.db:execSQL service; but this was unsuccessful. We are going against Oracle rather than SQL Server, so I don’t know if that makes a difference. I have set the value of $dbAlias, but have not mapped any inputs to the SP. My SP call is in $dbSQL:

exec wm_call_SP

This is returning an ‘ORA-900 Invalid SQL Statement’. However, when I execute the SP from Oracle’s sqlplus, the SP call works fine. Any ideas?

I found some Oracle documentation … Oracle doesn’t recognize EXEC as a valid SQL statement. However, you can set $dbSQL to:

CALL wm_call_SP();

and that will execute your SP.

The input of my stored procedure is an array of string. However this type does not exist in IS. Does anyone know how I can do to make this type recognized by IS?

Dai - StringList is an array of java.lang.Strings.

I’m trying to call an Oracle stored procedure which needs to accept a PL/SQL RECORD. (a WM record or record list)
The SP receives one parm - REC1, which is a pl/sql record with multiple text fields in it. If I build a recordlist in webMethods called REC1 and pass it using execSQL, what do I put in the $dbParamTypes? I’ve tried PL/SQL RECORD, which is what comes back from the GETPROCINFO service, but it keeps giving me a Java.sql.SQLException: Invalid column type message?
Does anyone have a flow that calls an Oracle SP which passes a table???

Thanks
MIke

Mike:

Tried to do the same…unsuccessfully. Been told that RECORD is not a JDBC compliant datatype. However, if anyone has found a way to do this I’d love some tips.

Tom

I am trying to call a oracle stored procedure from the Integration Server using the pub.db:call service.

The stored procedure signature is as follows
CREATE OR REPLACE PROCEDURE wm_testInsProc
(
v_test_varchar2 IN OUT VARCHAR2,
v_test_char IN CHAR,
v_test_number IN OUT NUMBER,
v_test_decimal IN OUT NUMBER,
v_test_date IN OUT DATE)
)

This procedure executes fine. But if I change v_test_char as ‘IN OUT’ in the above proc,then I get the following error

“java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at “DW.WM_TESTINSPROC”, line 11
ORA-06512: at line 1”

I am sure many of you might have already done a call to an SP from webMethods…Any pointers why I am getting this error and how I should call a Stored Proc ?

Thanks a Million
Biju

Hi Biju,
Did you change the v_test_char IN CHAR to v_test_char IN OUT CHAR ?
Thanks

Ravi,
u r rt…I modified
"v_test_char IN CHAR "
to
“v_test_char IN OUT CHAR”

Thanks
Biju

In this thread, I’ve seen mentiond of getting pub.db:call to invoke a stored procedure a that returns one row. I’ve been able to do that.

But is it possible for pub.db:call to invoke a stored procedure that returns multiple rows?

I tried getting pub.db:call to invoke a stored procedure that returns a PL/SQL temporary table. However it gets an “unknown column type” error.

I don’t believe you can get multiple rows returned from a stored procedure call. This caused us some grief too - you either have to call the stored proc from a java service, use execSQL instead (not good for performance, etc), or find a workaround (ie. remove the need to have multiple rows returned by using an alternate solution).

All,
FWIW - we ended up writing our own Java service to execute our SP’s. It basically, describes the SP first in order to dynamically generate the prepare call statement and then bind the input and output parameters. I’d ordinarily love to share, but this code will actually be owned by the client.

Interestingly, we’ve moved up to 6.0.1 now, but haven’t had a chance yet to try this with the JDBC Adapter. Anyway, HTH.

Here’s Java Service to return a result set from an Oracle stored procedure using JDBC and one input. You have to change the code for 2 inputs or 3 inputs and add more statements like this with a new variable name:
stmt.setString(1,inputVariable);

and change al1 the numbers 1-2-3-4- to increase them by one.
This was tested with Oracle 8.1.7, webMethods 4.6 and has 5 input fields
userId, passWord, dburl, storedProc and inputVariable.
Outputs are $dbMessage, $rowCount, results (recordset), errorCode, and errorText.
You would put your stored procedure name in the storedProc variable when you call this java service.

The shared tab includes oracle.java.driver.* as well as java.sql.*

You can copy this into the java service editor in Developer:

String userId=“”, passWord=“”, dburl=“”, storedProc=“”, inputVariable=“”;
int rowcount=0;
ArrayList al_results = new ArrayList();
Connection conn=null;
byte bytes=null;
int errorCode=0;
String errorText=“”;

IDataHashCursor id = pipeline.getHashCursor();
// only will use the first occurance of the message element
if (id.first(“userId”)) userId = (String)id.getValue();

if (id.first(“passWordword”)) passWord = (String)id.getValue();

if (id.first(“url”)) dburl = (String)id.getValue();

if (id.first(“storedProc”)) storedProc = (String)id.getValue();

if (id.first(“inputVariable”)) inputVariable = (String)id.getValue();

try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(dburl, userId, passWord);
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

CallableStatement stmt = null;
try
{
stmt = conn.prepareCall(storedProc);
stmt.setString(1,inputVariable);
stmt.registerOutParameter(2,OracleTypes.CURSOR);
stmt.registerOutParameter(3,java.sql.Types.INTEGER);
stmt.registerOutParameter(4,java.sql.Types.VARCHAR);
stmt.execute();
ResultSet rs = (ResultSet) ((OracleCallableStatement)stmt).getCursor(2);
errorCode = stmt.getInt(3);
errorText = stmt.getString(4);
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

while( rs.next() )  
{  
rowcount++;  
IData result = IDataFactory.create();  
IDataCursor resultCursor = result.getCursor();  

for( int i=1; i<=colCount; i++ )  
{  
if( rsmd.getColumnType(i) == java.sql.Types.BINARY || rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY || rsmd.getColumnType(i) == java.sql.Types.BLOB || rsmd.getColumnType(i) == OracleTypes.CURSOR )  
{  
resultCursor.insertAfter(rsmd.getColumnName(i), rs.getBytes(i));  
}  
else  
{  
resultCursor.insertAfter(rsmd.getColumnName(i), rs.getString(i));  
}  
}  
resultCursor.destroy();  
al_results.add(result&