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 03:04:45 GMT

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


Mime
View raw message