Return-Path: <rdare@comerxia.com>
Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Delivered-To: mailing list ibatis-user-java@incubator.apache.org
Received: (qmail 30832 invoked by uid 99); 16 Dec 2004 20:22:41 -0000
X-ASF-Spam-Status: No, hits=0.1 required=10.0
	tests=FORGED_RCVD_HELO
X-Spam-Check-By: apache.org
Received-SPF: pass (hermes.apache.org: local policy)
Received: from mail.corp.comerxia.com (HELO mailserver.corp.douwantit.com)
 (64.80.61.4)
  by apache.org (qpsmtpd/0.28) with ESMTP; Thu, 16 Dec 2004 12:20:43 -0800
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0
Subject: Calling Oracle Stored Functions:
Date: Thu, 16 Dec 2004 15:07:08 -0500
Message-ID: 
 <9BB982807A3CD94E88B7DD3CA7042D4216E16A@mailserver.corp.douwantit.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Calling Oracle Stored Functions:
Thread-Index: AcTjqNvfFi9nZbzkRneA9RBjS4nC/AAAG5Gw
From: "Robert Dare" <rdare@comerxia.com>
To: <ibatis-user-java@incubator.apache.org>
X-Virus-Checked: Checked


Hi All,

Is there a way to call Oracle functions (not stored procedures)
from iBATIS?

I have iBATIS and Oracle Stored Procedures working without issue
on Oracle 8i.  I have to support some Legacy procedures that are
written as Oracle stored functions. =20

When I tried accessing these functions as stored procedures, I=20
got an Oracle invalid SQL exceptions:  (tried this with a=20
parameterMap, and parameter class with inline mapping with
same results):

    <procedure id=3D"confirmOrder" parameterMap=3D"confirmation_params"
               resultClass=3D"java.lang.Long" >
        { ?=3Dcall porders.createconfirmation (?,?,?)}=20
    </procedure>


I tried accessing the function like this:

    <statement id=3D"confirmOrderStmt" =20
               parameterClass=3D"OrderConfirmation"
               resultClass=3D"java.lang.Long">
        <![CDATA[=20
            begin
            ? :=3D porders.createconfirmation (
                #orderId#,
                #vendorId#,
                #vendorOrderId#);=20
            end;
        ]]>
    </statement>

The above fails, Oracle complains about unbound parameters.
Changing the ? to #confirmationId# (a field in the resultClass),
results in an "invalid column type" sql exception.  I also tried
some inline mapping as in #confirmationId:mode=3DOUT#, but that
didn't help.

Regards,

Rob.