Skip to Content
author's profile photo Former Member
Former Member

Oracle JDBC Issue

We are currently experiencing an issue with JDBC Drivers. When we are trying to execute a stored procedure in oracle via middleware SAP Exchange Infrastructure we get an error saying

07:55:36 (4332): Error parsing message content: Technical description "java.sql.SQLException: 'Unsupported parameter type 'REF' for parameter 'outResult' found"

07:55:36 (4333): Error processing message: "java.lang.Exception: Message '37d1da51-5611-0940-8fd1-f44d874abfd4' preparation for database insert failed" - rollback DB

The above error is an exception raised by JDBC driver.

In XI configuration we directly use ODBC Driver as shown below

db.jdbcDriver=oracle.jdbc.driver.OracleDriver

db.connectionURL=jdbc:oracle:oci8:

Here is the store procedure code

PROCEDURE CCREP

( inSapSoldTo in string, --like '120120', no leading zeroes

inYtdOrMtdMode in string, --'YTD' will select a year summary, otherwise a month summary

inDateOrYear in string, --like 'JAN04' for month or '2004' for year

--outResult out integer)

outResult out types.t_cursor)

as

c_ccrep types.t_cursor;

dateSpec varchar(15);

BEGIN

begin

if (inYtdOrMtdMode = 'YTD' ) then -- Return a Year To Date Report

dateSpec := indateOrYear || ' Y-T-D'; -- e.g. '2004 Y-T-D'

else --Return a Month To Date report

dateSpec := indateOrYear; -- e.g. 'JAN04'

end if;

open c_ccrep for

SELECT SEQ, NAME, PCT, COUNT, AMOUNT, AMOUNT_TOTAL

FROM CCREPORT_DETAIL

WHERE SAP_SOLDTO=inSapSoldTo and ccr_month in dateSpec

ORDER BY SEQ;

end;

--outResult:=1;

outResult:=c_ccrep;

END; -- Procedure

Currently the work around we have is we are executing a SELECT via XML from XI and returns the result set. But we don’t want to build SELECT SQL in XI.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 07, 2004 at 02:59 PM

    Hi Rajan,

    With oci8, try CURSOR instead of REF, and make sure the datatype defined in the procedure is REF CURSOR...

    Regards,

    <b>Venky Varadadesigan</b>

    SAP NetWeaver consulting

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 07, 2004 at 03:41 PM

    Hello Venky,

    I tried changing to CURSOR and looks like that is not supported either..

    10:37:55 (4332): Error parsing message content: Technical description "java.sql.SQLException: 'Unsupported parameter type 'CURSOR' for parameter 'outResult' found"

    10:37:55 (4333): Error processing message: "java.lang.Exception: Message '889d2fa4-7ff5-6747-9e4b-ae2c0f56a395' preparation for database insert failed" - rollback DB

    When we write a java code which uses the same driver, we dont get any exceptions, we Oracle supplied API's to extract data from cursor. So am not too sure if this is a limitation in Adapter or JDBC Driver when recieves SQL via XML for store procs returning cursor type.

    Thanks,

    Rajan Kidambi

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.