Skip to Content
avatar image
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 comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded