How to call database procedure from jdbc adapter service.

, , ,
1

We have database procedures which needs to be called, but it takes input parameter which has the database record type.

How to pass the value to database procedure which takes input parameter of a database record type

2

If you are referring to calling a database stored procedure, there is a stored procedure template you can use from the JDBC Adapter.

Also, can you explain what a database record type is? What type of database are you connecting to?

3

i’m afraid wm can’t call the stored procedure with input parameter.

4

What database are you using? The JDBC Adapter provides tempate services for invoking stored procedures. You have to declare the input parametes for the stored proc. If you are using Oracle, you can use the stored procedure with signature adapter service. This services will introspect your stored procedure parameters and generate the required input and output parameters according to what is declared in the stored procedure.

Mark

5

thanks mark!Calling a procedure with parameters is a really headache for me.
I’m using oracle.
But how to set procedure name pattern.For instance, the procedure name is benq_edi_pkg.so_prc(…), what should i input in the procedure name pattern filed if i wanna find this procedure quickly.
many thanks
Nancy

6

Hi,

Can you provide me the steps to create the database record type as input parameter as I couldn’t find the section to do the same in procedure call template.

I am using oracle as database

7

new adapter service->
select a jdbc adaptor →
select connection->
storedProcedureWithSignature->
enter a name
in call tab, you can select catalog Name and schema Name or using default(current schema),
procedure Name pattern, i don’t know how to write the pattern but at least you can select all procedure,
and then in procedure name list, you can find the procedure stored on ur oracle database. I’m afraid it is not alfabetic order, so look for carefully, yon won’t miss it.
After ur selected the procedure, the input output parameters will generate automatically.
I hope this will help.
Nancy

8

Hi,

After selecting the procedure it not populating the parameters…

I was communicating to webMethods and was provided with following details:

=============================================

Sorry to inform you that the support for “RECORD” type is currently not available. So it is not possible to call a Stored Procedure which has a “RECORD” type as it’s IN parameter.

Though the Stored Procedure with Signature might auto-configure some parameters, since the support for the RECORD type is not available the Stored Procedure Adapter Service will not work.

=============================================

9

I think the Record type is user-defined right?
I’m afraid wm won’t work well that way

10

Anil,

You will have to “wrap” your stored procedure with one that calls the original proc and returns its results in multiple variables not in an Oracle record type.

It really would help if you would identify the database you are working with in future questions. We can all eventually guess the right one, but you could have gotten your answer much quicker by mentioning that you were using Oracle in your original question.

Mark

11

Hi Nancy,
I didn’t find ‘storedprocedurewithsignature’ in template as you said.
I found only ‘storedprocedure’.
i’m usisng webMethods 6.x.
Bcoz i’m facing a problem calling 4argument ref cursor out variable stored procedure of Oracle.

PROCEDURE ARCHIVEDATA_PROC(BIZDOCCURSOR OUT BIZDOCCUR,
BIZDOCCONTENTCURSOR OUT BIZDOCCONTENTCUR, ACTIVITYLOGCURSOR OUT ACTIVITYLOGCUR,DELIVERYJOBCURSOR OUT DELIVERYJOBCUR)

How do you invoke this kind of sp in JDBC adapter service?
How to pass those 4 cursor variables from adapter service?
i’m not able to pass more than one oraclecursor? why?

Could you please let me know the solution?

Thanks in advance,
Ganesh