Return-Path: 
 <user-cs-return-1755-apmail-ibatis-user-cs-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-cs-archive@www.apache.org
Received: (qmail 36047 invoked from network); 22 Sep 2006 14:40:10 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 22 Sep 2006 14:40:10 -0000
Received: (qmail 96208 invoked by uid 500); 22 Sep 2006 14:40:09 -0000
Delivered-To: apmail-ibatis-user-cs-archive@ibatis.apache.org
Received: (qmail 96168 invoked by uid 500); 22 Sep 2006 14:40:09 -0000
Mailing-List: contact user-cs-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-cs-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-cs-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-cs@ibatis.apache.org>
List-Id: <user-cs.ibatis.apache.org>
Reply-To: user-cs@ibatis.apache.org
Delivered-To: mailing list user-cs@ibatis.apache.org
Received: (qmail 96110 invoked by uid 99); 22 Sep 2006 14:40:08 -0000
Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org)
 (140.211.166.84)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 22 Sep 2006 07:40:08 -0700
Authentication-Results: idunn.apache.osuosl.org
 smtp.mail=don.dwoske@gmail.com; spf=pass
Authentication-Results: idunn.apache.osuosl.org
 header.from=don.dwoske@gmail.com; domainkeys=good
X-ASF-Spam-Status: No, hits=0.5 required=5.0 tests=DNS_FROM_RFC_ABUSE
Received-SPF: pass (idunn.apache.osuosl.org: domain gmail.com designates
 64.233.166.176 as permitted sender)
DomainKey-Status: good
X-DomainKeys: Ecelerity dk_validate implementing
 draft-delany-domainkeys-base-01
Received: from [64.233.166.176] ([64.233.166.176:31805]
 helo=py-out-1112.google.com)
	by idunn.apache.osuosl.org (ecelerity 2.1.1.8 r(12930)) with ESMTP
	id 36/2D-06791-3C5F3154 for <user-cs@ibatis.apache.org>;
 Fri, 22 Sep 2006 07:40:04 -0700
Received: by py-out-1112.google.com with SMTP id b29so1315756pya
        for <user-cs@ibatis.apache.org>; Fri, 22 Sep 2006 07:39:33 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth;
        b=D88Whul0eGfP+Mx1aUjIQ5G7jtVlInAh8/xf1ki+/SfKHcQ+oHg1KDFVlL10Pm5ztOmX5dnYonNEmTPCFKqbMJYplptbh7IYP0lzR7TxqkME79Dss3Q5eWAj6V84m5fL6G52xzWiH7MTKTBsmdLPn5A2+FOSDDOhQjeG+0kWgYI=
Received: by 10.35.76.10 with SMTP id d10mr1513286pyl;
        Fri, 22 Sep 2006 07:39:33 -0700 (PDT)
Received: by 10.35.89.20 with HTTP; Fri, 22 Sep 2006 07:39:32 -0700 (PDT)
Message-ID: <41fde74d0609220739k2364210u316f74668acb0daa@mail.gmail.com>
Date: Fri, 22 Sep 2006 10:39:32 -0400
From: "Don Dwoske" <don@loraxis.com>
Sender: don.dwoske@gmail.com
To: user-cs@ibatis.apache.org
Subject: oracle stored function
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Google-Sender-Auth: a16a56f27b0cd257
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

I searched the list, and tried many, many things to get iBatis to work
with a stored FUNCTION (not a PROCEDURE), and I finally came up with
something that works - but to me, it looks pretty hacked.

My function :

   FUNCTION GetID(
                i_origin IN VARCHAR2,
                i_process IN VARCHAR2,
                i_person IN VARCHAR2 DEFAULT NULL)
   RETURN NUMBER;

iBatis relevant info :

    <statement id="getid" parameterMap="getid-params">
        BEGIN ? := REMOTEREG.P_REMOTEREG_REGISTER_REMOTE.GetID(?, ?, ?); END;
    </statement>

    <parameterMap id="getid-params" class="Model.RemoteReg.GetId">
        <parameter property="regId"     direction="Output" column="param0" />
        <parameter property="origin"    direction="Input" />
        <parameter property="process"   direction="Input" />
        <parameter property="person"    direction="Input" />
    </parameterMap>

I tried with all my might (really, I did) to get it working using a
procedure instead of a statement, but I just couldn't.  I also tried
with partial might to figure out how to get rid of that ugly param0 in
as the output parameter name to have it named something nicer.. but no
luck... I only figured that out by stepping through the code and
seeing what the result OracleParameterCollection had inside of it.

I looked through the NUnit tests and see a function declared in the
SQL code (FUNCTION GET_ACCOUNTS_FUNCTION) - but didn't see it tested
anywhere for an example.

I'm really just glad that I got something that works, but if anyone
knows a better way to accomplish calling a stored function that
returns a simple value, please let me know.

-Don

-- 

---------------------------------------
Donald Dwoske
Software Journeyman
http://www.loraxis.com/ddwoske