Cheers,
Clinton
On 11/6/06, navrsale <[EMAIL PROTECTED]> 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
>>
>> [EMAIL PROTECTED]
>> 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.
