SP Struct Input

Luis_Henrique1 · April 6, 2016, 12:59am

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!

Venkata_V · April 6, 2016, 1:41am

what is the webMethods, database version Ur using?

Luis_Henrique1 · April 6, 2016, 2:38am

Hello Venkata, thanks for helping.

webMethods: 9.8
Oracle 9

Thanks!

Venkata_V · April 6, 2016, 2:43am

Did you try using StoredProcedureWithSignature Adapter service in JDBC Adapter

Luis_Henrique1 · April 6, 2016, 2:48am

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?

Venkata_V · April 6, 2016, 2:54am

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.

Luis_Henrique1 · April 6, 2016, 3:01am

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.

Venkata_V · April 6, 2016, 3:21am

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.

Venkata_Vennamareddy · April 6, 2016, 4:51am

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

Holger_von_Thomsen · April 6, 2016, 1:12pm

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

Luis_Henrique1 · April 6, 2016, 2:41pm

Hello Holger von Thomsen,

JDBC adapter version: 9.0

Thanks

Holger_von_Thomsen · April 6, 2016, 5:30pm

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

Venkata_V · April 7, 2016, 1:32am

HI Luis,

Any luck with what I and Holger suggested?

Thanks,
Venkata

Venkata_V · April 7, 2016, 2:59am

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.

Luis_Henrique1 · April 18, 2016, 3:51pm

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!

MR_as173d · April 18, 2016, 8:34pm

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

Thanks,

Luis_Henrique1 · April 18, 2016, 9:20pm

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.

MR_as173d · April 18, 2016, 9:46pm

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

Thanks,

Luis_Henrique1 · April 18, 2016, 10:03pm

MR as173d,

Still the same error!

Thanks.

MR_as173d · April 19, 2016, 6:08pm

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

Thanks,