JDBC COMMUNICATION CHANNEL ERROR

[former_member200386]

Dear Experts,

I am doing Jdbc to Proxy scenario using storedprocedure.

i created the comm channel.we are using a function to fetch the data from the table.

i am getting the below error.

Database-level error reported by JDBC driver while executing statement 'SELECT * FROM TABLE(GetOrderData_Respond (1))'. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-22905: cannot access rows from a non-nested table item '. For details, contact your database server vendor.

[baskar_gopalakrishnan2 · Accepted solution]

What database you use?  Sender jdbc does not work well for SP using oracle database. You might want to see the michal blog for the solution. Please don't forget to rate it .

http://scn.sap.com/community/pi-and-soa-middleware/blog/2011/10/04/pixi-sender-jdbc-adapter-for-orac...

[former_member200386 · Accepted solution]

Hi Baskar,

Thanks for your Reply. I am using Oracle11g. I read in some threads that Jdnc sender channel supports Stored pocedures  From Oracle 10g onwards .

Our Database team created a FUNCTION. From PI end we need to write a query to execute the function & fetch the data.

please give me suggestions. on creating of jdbc adapter. is still avoidable & we can achieve this in a normal way?

Regards,

Pavan

[Former Member · Accepted solution]

Hi Pavan,

Use the following query, I think this will be helpful for you.

SELECT GetOrderData_Respond (1) FROM DUAL;

Best Regards,

Sagarika Mishra

[former_member200386]

Thanks to all,

I tried with all options.( functions,procedure, table funcions) in Stored procedure.

no use for oracle database.

now the DB team agreed to create  intermediate table. now the issue was solved.

thanks you all for your valuable inputs.

Regards,

Pavan

[former_member200386]

Hi Experts,

Now i have changed the query

to

CALL GetOrderData_Respond (1)

i am getting this error

Database-level error reported by JDBC driver while executing statement 'CALL GetOrderData_Respond (1)'. The JDBC driver returned the following error message: 'java.sql.SQLException: ORA-06576: not a valid function or procedure name '. For details, contact your database server vendor.

[Former Member]

Hi Pavan,

Can you please give me the following details.

• Table name from which you want to fetch data.
• Stored procedure name and for which value you want to fetch data.

Please try the following  If your stored procedure is GetOrderData_Respond and for the value 1, you want yo fetch data, you can use the following query. If your value is character then put 1 in side apostrophe.

EXECUTE GetOrderData_Respond 1;

Best Regards,

Sagarika

[former_member200386]

Hi Sagarika,

Thanks for your Quick Response.

• Table name from which you want to fetch data-------------->no table----------(i am not using any tables)
• Stored procedure name and for which value you want to fetch data.( we are using FUNCTION
  )

GetOrderData_Respond (vOrderRange);

Order Range is the no. of Order we need to process, For example we want to process 1 order then GetOrderData_Respond (1);

This will reply the 1 order from main order table with no. of order in error table.

i used the query . i am getting  this error. please find the screen shots

[Former Member]

Hi Pavan,

Can you please try the following query.

SELECT * FROM GetOrderData_Respond (1);

Best Regards,

Sagarika Mishra

[former_member200386]

HI Sagarika,

I Tried your Query, Still no Use.Our Data base team created a function in n Oracle 11g.

in jdbc sender channel we need to write a query to execute the function & fetch data,

i am using PI 7.3

Regards,

Pavan