JDBC stored procedure / java.lang.IllegalArgumentException

[Former Member]

Hi,

I have created a JDBC adapter to access for a Oracle DB and the connection works successfully. I have tested the posting into the DB via xml-format and it works also fine.

Accordingly i added a stored procedure (SP) into the xml-format and i get following error in the communication channel (in Pi monitoring the message is fine - no error):

com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request

in sax parser: Error when executing statement for table/stored proc.

'em_end_load' (structure 'STATEMENT_StoreProcedure'):

java.lang.IllegalArgumentException

SP in DB:

PROCEDURE em_end_load

(

p_1 IN VARCHAR2,

p_2 IN VARCHAR2 DEFAULT NULL,

p_3 IN NUMBER,

p_4 IN VARCHAR2 DEFAULT NULL,

p_time IN DATE DEFAULT NULL

)

AS

I'm not sure if i have to use an output parameter, because in the description of the SP i read following:

-

---

USAGE

To compile from the SQL*Plus prompt:

SQL> start [folder spec]/sp_em_end_load.pls

To run from the SQL*Plus prompt:

SQL> exec em_em_end_load

PARAMETERS

INPUT

p_1

p_2

p_3

p_4

p_5

p_6

OUTPUT

p_error_msg

-

---

I have created in PI the data type for SP as follow:

value

MT_JDBC

STATEMENT_SP 0..1

action required EXECUTE

table 0..1 em_end_load

p_1 1..1 010

type optional VARCHAR

p2 1..1 main

type optional VARCHAR

p3 1..1 100

type optional VARCHAR

p4 1..1 load

type optional VARCHAR

p5 1..1 26.04.2010

type optional DATE

p_error_msg 0..1

isInput optional 1

type otpional VARCHAR

What is wrong with the processing of this SP? Could anyone help me?

Regards,

Lutz

[Former Member]

can you please post your exact procdure using in DB. so that we can able to trace the error.

I can see some contradiction in the posted SP.

Procdure name you mentioned is --- em_end_load

but your executing the em_em_end_load from the prompt.

I strongly the problem with your passing parameters.

Regards

Ramg

[Former Member]

I could posted the message (without SP) within the value of <28.04.2010> - that works fine.

I'm not sure if the problem is the date format.

[Former Member]

Hi

As per your SP you are having only 5 i/p parameters, but in XI ur using 6 parameters. It seems the no. of parameters passing from XI are not correct.

Regards

Ramg

[Former Member]

Correct - but the last parameter as an output parameter for the message from the db.

I guess i have to create it into the PI structure.

[stefan_grube]

it seems that the input parameters of your SP and the structure have different field names.

Besides check if the date format is like it is requested.

[Former Member]

Sorry the explanation of the data type was a little bit misunderstanding:

data type in PI:

MT_JDBC

-STATEMENT_SP 0..1

--action required EXECUTE

--table 0..1 em_end_load

--p_1 1..1 010

---type optional VARCHAR

--p_2 1..1 main

---type optional VARCHAR

--p_3 1..1 100

---type optional VARCHAR

--p_4 1..1 load

---type optional VARCHAR

--p_time 1..1 26.04.2010

---type optional DATE

--p_error_msg 0..1

---isInput optional 1

---type optional VARCHAR

...but the names of the parameters are identical - that i have checked several times.

[stefan_grube]

I assume that the date format is not correct.

it should be something like this:

2009-06-30 00:00:00.0

Did you date format work when I did not use stored procedure?