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 04:44:31 GMT

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