ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: [2.2.0] Support for Oracle Ref Cursor
Date Tue, 07 Nov 2006 14:29:58 GMT
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.
>
>

Mime
View raw message