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