Hello Jan, Unfortunately I have no stored procedure/function example that does an UPDATE.
However, here's the information I have about a PL/SQL FUNCTION that does a SELECT and returns a REFCURSOR. [EMAIL PROTECTED] posted a message to this forum December 22, 2004 with an attached TypeHandler implementation. Subject was "Re: Problems with registering OUT parameter in Oracle stored procedure". Here's the link: http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/msg00287.h tml >From his example I created one against the Oracle scott/tiger schema. See attached files. The attachments include the entire project including the script to create the test FUNCTION (in SCOTT schema). The exception is that the JAR file dependencies are not attached. See build.xml for the JAR file versions. After building the project you can run RefCursorTest. It will output the records from table EMP. My Environment -------------- Client - Windows 2000 - JDK 1.4.2_06 - SqlMaps 2.0.9 - Oracle thin JDBC driver 9.0.3 Server - Windows 2000 - Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production As for CLOB, BLOB, XMLType I have no experience with these using ibatis. I've done BLOB/CLOB stuff using JDBC directly but I was using Oracle JDBC API instead of java.sql.*. -Jerome > -----Original Message----- > From: Jan Vissers [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 04, 2005 1:06 PM > To: ibatis-user-java@incubator.apache.org > Subject: Re: IBatis and Oracle stored functions > > > For instance: > > ... > CallableStatement cstmt = conn.prepareCall("{? = call > pkg_refcur.f_refcur(?)}"); > cstmt.registerOutParameter(1, OracleTypes.CURSOR); > cstmt.setString(2, i_etype); > cstmt.executeUpdate(); > ResultSet rset = ((OracleCallableStatement)cstmt).getCursor(1); > ... > > I've seen *part* of an example for a custom type handler, but no real > complete example including call sequence. This would help a great deal! > > Thx, > Jan. > > Clinton Begin wrote: > > >How would you call them from JDBC? > > > >iBATIS supports pretty much anything supported by the standard JDBC APIs. > > > >Vendor specific extensions typically require a custom type handler, or > >may not be supportable at all. > > > >Clinton > > > > > >On Tue, 04 Jan 2005 18:43:52 +0100, Jan Vissers > <[EMAIL PROTECTED]> wrote: > > > > > >> Hi, > >> > >> I'm evaluating IBatis sqlMap/DAO wrt Oracle functionality, like: > >> > >>"RETURNING ... INTO ..." Clause > >>CLOB (oracle.sql.CLOB) >32K > >>BLOB (oracle.sql.BLOB) > >>XMLType (oracle.xdb.XMLType) > >>CallableStatement... It looks to me that none of these are > really supported. > >>I, for instance have a packaged function: > >> > >> > >>function insert_record( p_i_values in pck2.refcursortype) > >> return pck2.refcursortype; > >> > >> Is there any way to call these types of objects from IBatis? > >> > >> Thx. > >> Jan. > >> > >>-- > >> > >> And > >> > >> > > > > > > > > -- > Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=0&t=70> > And Get Thunderbird <http://www.mozilla.org/products/thunderbird/>
commons-logging.properties
Description: Binary data
jdbc.properties
Description: Binary data
log4j.properties
Description: Binary data
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd";>
<sqlMap>
<parameterMap id="noInputsSingleResult" class="map">
<parameter property="resultCode"
jdbcType="NUMERIC"
mode="OUT"/>
<parameter property="result"
jdbcType="ORACLECURSOR"
mode="OUT"
typeHandler="ResultSetToResultHandler"/>
</parameterMap>
<procedure id="proc1" parameterMap="noInputsSingleResult">
<![CDATA[
{?= call IBATIS_REFCURSOR_TEST(?)}
]]>
</procedure>
</sqlMap>
RefCursorTest.java
Description: Binary data
ResultSetToResultHandler.java
Description: Binary data
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd";>
<!-- Be sure to always use the appropriate header as above! Note the 2.0 DTDs. -->
<sqlMapConfig>
<!-- Using the properties element you can declare an external properties resource
either from the classpath (resource) or from a URL (e.g. file:// or http://)
The properties can be referred to throughout the rest of the file using
the syntax "${propName}". There can be only one properties element. -->
<properties resource="jdbc.properties"/>
<!-- The settings element has a number of configuration switches and properties
that configure the SQL Maps engine. Most are performance tweaks or resource
management. They are pretty self explanatory. The defaults are shown here.
There can be only one settins element. -->
<settings
lazyLoadingEnabled="true"
cacheModelsEnabled="true"
enhancementEnabled="true"
maxRequests="512"
maxSessions="128"
maxTransactions="32"
/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
<property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
<property name="JDBC.Username" value="${jdbc.username}"/>
<property name="JDBC.Password" value="${jdbc.password}"/>
</dataSource>
</transactionManager>
<sqlMap resource="MySqlMap.xml" />
</sqlMapConfig>CREATE OR REPLACE FUNCTION IBATIS_REFCURSOR_TEST (RESULT OUT SYS_REFCURSOR)
RETURN NUMBER
IS
tmpVar NUMBER;
BEGIN
OPEN RESULT FOR
SELECT *
FROM EMP;
tmpVar := 0;
RETURN tmpVar;
END IBATIS_REFCURSOR_TEST;
/
<?xml version = '1.0' encoding = 'windows-1252'?>
<!--Ant buildfile generated by Oracle JDeveloper 10g-->
<!--Generated Jan 4, 2005 4:54:39 PM-->
<project name="RefCursor" default="make" basedir=".">
<!--Set the output directories-->
<property name="compile.outdir" value="classes"/>
<!--Set the classpath-->
<path id="classpath">
<pathelement location="${compile.outdir}"/>
<!-- Oracle JDBC Thin driver 9.0.3 -->
<pathelement location="lib/classes12.jar"/>
<!-- Apache Jakarta Commons Logging 1.0.3 -->
<pathelement location="lib/commons-logging.jar"/>
<!-- ibatis 2.0.9 -->
<pathelement location="lib/ibatis-common-2.jar"/>
<pathelement location="lib/ibatis-sqlmap-2.jar"/>
<!-- JSTL 1.0.3 -->
<pathelement location="lib/jstl.jar"/>
<!-- Apache Log4J 1.2.8 -->
<pathelement location="lib/log4j-1.2.8.jar"/>
<!-- Apache Standard Taglibs 1.0.6 -->
<pathelement location="lib/standard.jar"/>
</path>
<!--Set the source path-->
<property name="src.dir" value="src"/>
<path id="srcpath">
<pathelement location="${src.dir}"/>
</path>
<target name="init">
<tstamp/>
</target>
<target name="compile" depends="init">
<mkdir dir="${compile.outdir}"/>
<!--Compile Java source files-->
<javac destdir="${compile.outdir}" debug="on" encoding="Cp1252">
<classpath refid="classpath"/>
<src refid="srcpath"/>
<include name="ResultSetToResultHandler.java"/>
<include name="RefCursorTest.java"/>
</javac>
<!--Copy files to output directory-->
<copy file="${src.dir}/sql-map-client.xml" tofile="${compile.outdir}/sql-map-client.xml"/>
<copy file="${src.dir}/MySqlMap.xml" tofile="${compile.outdir}/MySqlMap.xml"/>
<copy file="${src.dir}/log4j.properties" tofile="${compile.outdir}/log4j.properties"/>
<copy file="${src.dir}/jdbc.properties" tofile="${compile.outdir}/jdbc.properties"/>
<copy file="${src.dir}/commons-logging.properties" tofile="${compile.outdir}/commons-logging.properties"/>
</target>
<target name="clean">
<!--Delete output directories-->
<delete dir="${compile.outdir}"/>
</target>
<target name="make" depends="compile"/>
<target name="rebuild" depends="clean,compile"/>
<target name="all" depends="compile"/>
</project>
