I think the order of your <parameter> elements is wrong - the first one should be the OUT parameter, and the second one should be the IN parameter. Like this:
<parameterMap id="postLoadValidationParam" class="Map">
<parameter property="postLoadValidationReport" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="report_line_mapping"/>
<parameter property="postLoadValidationReport" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" resultMap="report_line_mapping"/>
<parameter property="methodologyYear" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
</parameterMap>
Jeff Butler
On 11/7/06, navrsale <[EMAIL PROTECTED]> wrote:
Here is the driver info from the manifest file:
Manifest-Version: 1.0
Specification-Title: Oracle JDBC driver classes for use with JDK14
Sealed: true
Created-By: 1.4.2_08 (Sun Microsystems Inc.)
Implementation-Title: ojdbc14.jar
Specification-Vendor: Oracle Corporation
Specification-Version: Oracle JDBC Driver version - "10.2.0.2.0 "
Implementation-Version: Oracle JDBC Driver version - "10.2.0.2.0"
Implementation-Vendor: Oracle Corporation
Implementation-Time: Tue Jan 24 08:55:21 2006
Name: oracle/sql/converter/
Sealed: false
Name: oracle/sql/
Sealed: false
Name: oracle/sql/converter_xcharset/
Sealed: false
Clinton Begin wrote:
>
> You guys will need to use the Oracle 10g drivers, even if you're using
> Oracle 9i. The driver is backward compatible, and is more compliant with
> the JDBC spec so it works properly (only took them 10 years). ;-)
>
> Cheers,
> Clinton
>
> On 11/6/06, navrsale < [EMAIL PROTECTED]> wrote:
>>
>>
>> I also tried the other example on the same Wiki, the one that uses stored
>> procedure and not stored function.
>> I also used physical table this time to reproduce example exactly.
>>
>> There were no error this time but the List returned from stored procedure
>> was empty!?
>> Again, I checked stored procedure and the content of the table and the
>> cursor returns rows.
>>
>> The version of iBatis I am using is August 2006 2.2 Beta.
>>
>> Anybody has any clue what is wrong with these two examples?
>>
>>
>>
>>
>> navrsale wrote:
>> >
>> > I tried this example but got following error.
>> > I am using 2.2 iBatis, Oracle 9i database and OC4J application server.
>> >
>> > Runtime Error:
>> > =========
>> >
>> > Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
>> > PLS-00306: wrong number or types of arguments in call to
>> > 'GET_POST_VALIDATION_RE
>> > PORT'
>> > ORA-06550: line 1, column 7:
>> > PL/SQL: Statement ignored
>> > [See nested exception:
>> > com.ibatis.common.jdbc.exception.NestedSQLException :
>> > --- The error occurred in mappings/StoredProcedures.xml.
>> > --- The error occurred while applying a parameter map.
>> > --- Check the Grouper.postLoadValidationParam.
>> > --- Check the statement (update procedure failed).
>> > --- Cause: java.sql.SQLException: ORA-06550: line 1, column 13:
>> > PLS-00306: wrong number or types of arguments in call to
>> > 'GET_POST_VALIDATION_RE
>> > PORT'
>> > ORA-06550: line 1, column 7:
>> > PL/SQL: Statement ignored
>> >
>> > I have following definition in StoredProcedures.xml:
>> >
>> > <typeAlias alias="ReportLine" type=" ca.cihi.grouper.bean.ReportLine" />
>> > <resultMap class="ReportLine" id="report_line_mapping">
>> > <result property="reportLine" column="report_line" />
>> > </resultMap>
>> >
>> > <parameterMap id="postLoadValidationParam" class="Map">
>> > <parameter property="methodologyYear" jdbcType="VARCHAR"
>> > javaType="java.lang.String" mode="IN"/>
>> > <parameter property="postLoadValidationReport"
>> > javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"
>> > resultMap="report_line_mapping"/>
>> > </parameterMap>
>> >
>> > <procedure id="getPostLoadValidationReport"
>> > parameterMap="postLoadValidationParam" >
>> > { ? = call
>> ipgrouper_it2.load_validation.get_post_validation_report(
>> ?
>> > ) }
>> > </procedure>
>> >
>> >
>> > ReportLine.java is:
>> >
>> > public class ReportLine implements Serializable
>> > {
>> >
>> > private String reportLine;
>> >
>> >
>> > public ReportLine()
>> > {}
>> >
>> >
>> > public void setReportLine(String reportLine)
>> > {
>> > this.reportLine = reportLine;
>> > }
>> >
>> >
>> > public String getReportLine()
>> > {
>> > return reportLine;
>> > }
>> >
>> >
>> > }
>> >
>> >
>> > and here is the call of stored function:
>> >
>> > Map spParam = new HashMap();
>> > spParam.put( "methodologyYear", "2007" );
>> > ...
>> > SqlMapClient sqlMap_Output = ...
>> > ...
>> > try
>> > {
>> > sqlMap_Output.queryForObject(
>> "getPostLoadValidationReport",
>> > spParam );
>> > }
>> > catch( SQLException sqle )
>> > {
>> > JobExecutionException jee = new JobExecutionException(
>> sqle
>> > );
>> > throw( jee );
>> > }
>> >
>> > java.sql.ResultSet rset = (java.sql.ResultSet)spParam.get(
>> > "postLoadValidationReport" );
>> > ...
>> >
>> >
>> > And stored procedure specification is:
>> >
>> > CREATE OR REPLACE
>> > PACKAGE LOAD_VALIDATION AS
>> >
>> > type ref_cursor_t is ref cursor;
>> >
>> > function get_post_validation_report( p_methodology_year in varchar2
>> )
>> > return ref_cursor_t;
>> >
>> > END LOAD_VALIDATION;
>> >
>> > In stored function get_post_validation_report() I just open ref cursor
>> > over Nested Table defined as:
>> >
>> > CREATE OR REPLACE
>> > type Validation_tab_t is table of varchar2( 80 );
>> >
>> > and stored procedure works when I test it with PL/SQL client, i.e. it
>> > returns result set.
>> >
>> > Am I doing something wrong?
>> > Is there an alternative syntax, or should I use stored procedure
>> instead
>> > of stored function?
>> >
>> >
>> >
>> > Jan Vissers wrote:
>> >>
>> >> Excuse me the DOCTYPE decl is deprecated, it should have been:
>> >>
>> >> <!DOCTYPE sqlMap
>> >> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>> >> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>> >>
>> >>
>> >> Jan Vissers wrote:
>> >>> Well, this seems to work out of the box. A small example show below.
>> >>>
>> >>> In Oracle:
>> >>>
>> >>> CREATE TABLE REFS (
>> >>> ID NUMBER NOT NULL PRIMARY KEY
>> >>> ,NAME VARCHAR2(50) NOT NULL
>> >>> );
>> >>>
>> >>> CREATE OR REPLACE PACKAGE REFS_PCK AS
>> >>> TYPE REF_CURSOR_T IS REF CURSOR;
>> >>> FUNCTION GET_REFS RETURN REF_CURSOR_T;
>> >>> END REFS_PCK;
>> >>> /
>> >>>
>> >>> CREATE OR REPLACE PACKAGE BODY REFS_PCK IS
>> >>> FUNCTION GET_REFS RETURN REF_CURSOR_T
>> >>> IS
>> >>> L_CURSOR REF_CURSOR_T;
>> >>> BEGIN
>> >>> OPEN L_CURSOR FOR SELECT * FROM REFS;
>> >>> RETURN L_CURSOR;
>> >>> END GET_REFS;
>> >>> END REFS_PCK;
>> >>> /
>> >>>
>> >>> Your mapping file:
>> >>>
>> >>> <?xml version=" 1.0" encoding="UTF-8" ?>
>> >>> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
>> >>> " http://www.ibatis.com/dtd/sql-map-2.dtd">
>> >>> <sqlMap>
>> >>> <typeAlias alias="Ref" type="refs.Ref" />
>> >>>
>> >>> <resultMap class="Ref" id="Refs-map">
>> >>> <result property="id" column="ID" />
>> >>> <result property="name" column="NAME" />
>> >>> </resultMap>
>> >>>
>> >>> <parameterMap id="output" class="map">
>> >>> <parameter property="o" javaType=" java.sql.ResultSet"
>> >>> jdbcType="ORACLECURSOR" mode="OUT" resultMap="Refs-map"/>
>> >>> </parameterMap>
>> >>>
>> >>> <procedure id="getRefs" parameterMap="output">{ ? = call
>> >>> refs_pck.get_refs }</procedure>
>> >>> </sqlMap>
>> >>>
>> >>>
>> >>> The test code:
>> >>>
>> >>> public static void main(String[] args) throws Exception {
>> >>> String resource;
>> >>> Reader reader;
>> >>> SqlMapClient sqlMap;
>> >>> resource = "refs/SqlMapConfig.xml";
>> >>> reader = Resources.getResourceAsReader(resource);
>> >>> sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
>> >>> Map map = new HashMap();
>> >>> sqlMap.queryForObject("getRefs", map);
>> >>> System.out.println(map.get("o"));
>> >>> }
>> >>>
>> >>>
>> >>> Jan Vissers wrote:
>> >>>> From the release notes:
>> >>>>
>> >>>> o Added support for ResultSet OUT params from stored procs (e.g.
>> >>>> Oracle Ref Cursor)
>> >>>>
>> >>>> My question; is this out of the box or do we (still) have to create
>> >>>> our own TypeHandler?
>> >>>> If out of the box - who can provide us with an example?
>> >>>>
>> >>>>
>> >>>
>> >>
>> >> --
>> >> Cumquat Information Technology
>> >> De Dreef 19
>> >> 3706 BR Zeist
>> >> T +31 (0)30 - 6940490
>> >> F +31 (0)30 - 6940499
>> >> http://www.cumquat.nl
>> >>
>> >> [EMAIL PROTECTED]
>> >> M +31 6 51 169 556
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>> --
>> View this message in context:
>> http://www.nabble.com/-2.2.0--Support-for-Oracle-Ref-Cursor-tf2167822.html#a7212525
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/-2.2.0--Support-for-Oracle-Ref-Cursor-tf2167822.html#a7226226
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
