Hello All,

I have a SP that receives a struct input. Which data I have to pass? I changed struct data type to object, but how can I transform my string or document to acceptable input?

Thanks!

what is the webMethods, database version Ur using?

Hello Venkata, thanks for helping.

webMethods: 9.8
Oracle 9

Thanks!

Did you try using StoredProcedureWithSignature Adapter service in JDBC Adapter

Yes, i’m using this one. So I putted in my flow service, but I need to know which parameter in pipeline I have to link with my SP input. I know that it’s an object, but how can I transform a string to a valid input?

I think U have to define output field type as java.sql.Struct

In JDBC Adapter 9.0,

Support for ARRAY and STRUCT Data Types with Oracle Databases

The adapter now supports the ARRAY and STRUCT data type objects as input and output parameters in adapter services. In earlier versions of the adapter there was no way to insert or retrieve a record or list as an input or output to an adapter service. To provide this capability, the adapter now supports the java.sql.STRUCT and java.sql.ARRAY data types as input and output parameters for adapter services.

Important Considerations When Using the Array and Struct Database
Specific Data Types
ï?® In an adapter service, when using the java.lang.Object as the output field type for a
database column of type array or struct, Adapter for JDBC returns the data as a
java.lang.Object array, provided that the array or struct data in the database table is
composed of primitive data types.
ï?® When using the java.sql.Array or java.sql.Struct as the output field type for a database
column of type array or struct, Adapter for JDBC returns the java.sql.Array and the
java.sql.Struct objects, respectively, as returned by the driver. However, when
serializing the data across the JVMs, this returned data may not be serializable and
may result into a java.io.NotSerializableException. Therefore, before serializing the
data across the JVMs, it is important that you use a Java or a flow service to process
the java.sql.Struct and java.sql.Array objects as required, and then drop them from
the pipeline.

But it isn’t possible to change a string to a object(sql.struct) in pipeline and than link to my input SP (struct) isn’t it?

PS: The parameter (Struct) it’s SP input.

U probably need to write java code to convert from String or Document(IData) to java.sql.Struct java type before passing to the stored procedure.

Another way suggested in empower is,

Create a dynamic sql service with SELECT query on some temporary table, that return “java.sql.Struct” object with values you pass in.

pass that output to the StoredProcedureWithSignature service as input

Hi Luis,

which version of JDBC Adapter are you using? 6.5 or 9.0?

Which version of the driver?

Are there any Fixes applied to the JDBC Adapter?

Additionally you should consider upgrading your database to 11g or 12c as Oracle 9i is quite old and already in EOL-State.

Regards,
Holger

Hello Holger von Thomsen,

JDBC adapter version: 9.0

Thanks

Hi Luis,

make sure you have the latest ojdbc7.jar from Oracle 12.1.0.2 placed under WmJDBCAdapter/code/jars.
Reload Adapter package afterwards.

Please check if applying latest JDBC-Adapter Fix (currently Fix8) will help here.

Regards,
Holger

HI Luis,

Any luck with what I and Holger suggested?

Thanks,
Venkata

Please find the sample in the empower Knowledge article, search in empower knowledge base with “1744339”

JDB 6.5 - 1744339 : webMethods JDBC Adapter - Utilize Java.sql.Array as input from Oracle Stored Procedure.

Hello Venkata, sorry for the reply time.

I don’t have access to empower, so I can’t find this documentation.

I’m still having problems to call this procedure with struct input.

Thanks!

Please share what sort of errors you are facing with screen shots if possible

Thanks,

Sure.

I have this SP that i’m calling using storedProcedureWithSignature. (print1.png)

On my flow service, I have a string “tese” that contains my XML. (print2.png)

Calling the adapter on next step, I mapped this string to adapter input. (print3.png)
PS: I tried using object type.

And now I’m getting this error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘P_INS_AVISO_SINISTRO’

But the number of arguments are right and I’m using storedProcedureWithSignature!

Using java.sql.struct on P_REC_AVISO (SP input) it didn’t work too.

print1.png690×522 22.9 KB

print2.png945×448 23 KB

print3.png850×327 20.9 KB

Can you please change Struct with Nvarchar and give a try. Please update me on the same.

Thanks,

MR as173d,

Still the same error!

Thanks.

My bad, did you try SQL ( STRUCT ) → JDBC/ Java ( java.sql.Struct ) ? If not, can you please

Thanks,

Yeah, I tried and didn’t work.

I’ve changed SP input to CLOB and JDBC adapter to CLOB (string) and worked.

Thank you very much!!!

Glad to hear, thanks for your update.

Thanks,