ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From navrsale <navrsalem...@yahoo.ca>
Subject Re: [2.2.0] Support for Oracle Ref Cursor
Date Tue, 07 Nov 2006 19:51:36 GMT

Unfortunatelly I still get the error. I replaced classes12.jar driver with
ojdbc14.jar, recompiled, redeployed
and again the same error:

* Nested Exception (Underlying Cause) ---------------
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

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

        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQu
eryWithCallback(GeneralStatement.java:185)
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQu
eryForObject(GeneralStatement.java:104)
        at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(S
qlMapExecutorDelegate.java:565)
        at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(S
qlMapExecutorDelegate.java:540)
        at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMap
SessionImpl.java:106)
        at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapC
lientImpl.java:84)
        at
ca.cihi.grouper.util.LoadMethodologyJobExecutor.executeLoadMethodolog
yTask(LoadMethodologyJobExecutor.java:210)
        at
ca.cihi.grouper.quartz.LoadMethodologyJob.execute(LoadMethodologyJob.
java:76)

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

        at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:137)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:304)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:271)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625)
        at
oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.
java:180)
        at
oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableS
tatement.java:869)
        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:1153)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
aredStatement.java:2932)
        at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStat
ement.java:3023)
        at
oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStat
ement.java:4132)
        at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(
SqlExecutor.java:273)
        at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExec
uteQuery(ProcedureStatement.java:34)
        at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQu
eryWithCallback(GeneralStatement.java:173)
        ... 9 more

Thanks for looking into this issue!




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#a7225701
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message