You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by bob robertson <bo...@yahoo.de> on 2006/02/01 10:45:55 UTC

calling stored functions

 
  Just read http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
  which seems to be a great solution to using ref cursors.
   
  Is it possible to call a stored function instead of a stored procedure, and to obtain the cursor as a return value rather than an OUT parameter.
   
  Maybe it sounds like I'm being picky, but you know, existing schemas, no permissions on DB etc etc blah blah blah.
   
  Thanks.

		
---------------------------------
Telefonieren Sie ohne weitere Kosten mit Ihren Freunden von PC zu PC!
Jetzt Yahoo! Messenger installieren!

Re: calling stored functions

Posted by Mike Fagan <mf...@tde.com>.

Ted,

This is standard callable statement JDBC syntax for a 
procedure/function. The "?" are assigned the parameter from the 
parameterMap in the order declared. Your sample might also work but I 
have never tried it :-[ .

Regards,
Mike Fagan


Ted Schrader wrote:
> Hi Mike,
>
> I'm simply a spectator on this one, but I'm a bit puzzled about
> something in your example.  You wrote:
> ----
> <parameterMap id="myParamMap" class="map" >
>        <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
>        <parameter property="userId" jdbcType="NUMERIC" mode="IN"/>
>  </parameterMap>
>
> <procedure id="mycursorfunction" parameterMap="myParamMap"
> resultMap="myResultMap" >
>         { ? = call myRefCursorFunction(? ) }
>  </procedure>
> ----
>
> Based on your parameter map config (and perhaps some inexperience on
> my part), I'd expect your procedure to be:
>
> <procedure id="mycursorfunction" parameterMap="myParamMap"
> resultMap="myResultMap" >
>         { #result# = call myRefCursorFunction(#userId#) }
>  </procedure>
>
> Is the usage of "?", (and the curly brackets, for that matter)
> specific to the <procedure> tag, or perhaps an Oracle thing?
>
> Thanks,
>
> Ted
>
>

Re: calling stored functions

Posted by Ted Schrader <ts...@gmail.com>.

Hi Mike,

I'm simply a spectator on this one, but I'm a bit puzzled about
something in your example.  You wrote:
----
<parameterMap id="myParamMap" class="map" >
       <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
       <parameter property="userId" jdbcType="NUMERIC" mode="IN"/>
 </parameterMap>

<procedure id="mycursorfunction" parameterMap="myParamMap"
resultMap="myResultMap" >
        { ? = call myRefCursorFunction(? ) }
 </procedure>
----

Based on your parameter map config (and perhaps some inexperience on
my part), I'd expect your procedure to be:

<procedure id="mycursorfunction" parameterMap="myParamMap"
resultMap="myResultMap" >
        { #result# = call myRefCursorFunction(#userId#) }
 </procedure>

Is the usage of "?", (and the curly brackets, for that matter)
specific to the <procedure> tag, or perhaps an Oracle thing?

Thanks,

Ted

Re: calling stored functions

Posted by Mike Fagan <mf...@tde.com>.

Here is a sample that will take the refcursor and map it automatically 
to your resultMap - No TypeHandler required.

<resultMap id="myResultMap" class="MyClass" >
    <result property="id" column="RECORD_ID" />
    <result property="name" column="NAME" />
    <result property="address" column="ADDRESS" />
 </resultMap>

 <parameterMap id="myParamMap" class="map" >
       <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
       <parameter property="userId" jdbcType="NUMERIC" mode="IN"/>
 </parameterMap>

<procedure id="mycursorfunction" parameterMap="myParamMap" 
resultMap="myResultMap" >
        { ? = call myRefCursorFunction(? ) }
 </procedure>

Regards,
Mike Fagan

bob robertson wrote:
>  
> Just read 
> http://opensource2.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions
> which seems to be a great solution to using ref cursors.
>  
> Is it possible to call a stored function instead of a stored 
> procedure, and to obtain the cursor as a return value rather than an 
> OUT parameter.
>  
> Maybe it sounds like I'm being picky, but you know, existing schemas, 
> no permissions on DB etc etc blah blah blah.
>  
> Thanks.
>
> ------------------------------------------------------------------------
> Telefonieren Sie ohne weitere Kosten mit Ihren Freunden von PC zu PC!
> Jetzt Yahoo! Messenger <http://de.messenger.yahoo.com> installieren!