ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: [2.2.0] Support for Oracle Ref Cursor
Date Tue, 07 Nov 2006 20:26:37 GMT
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="methodologyYear" jdbcType="VARCHAR" javaType="
java.lang.String" mode="IN"/>
</parameterMap>

Jeff Butler



On 11/7/06, navrsale <navrsalemile@yahoo.ca> 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 <navrsalemile@yahoo.ca> 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
> >> >>
> >> >> Jan.Vissers@cumquat.nl
> >> >> 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.
>
>

Mime
View raw message