Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <25082929.post@talk.nabble.com>
Date: Fri, 21 Aug 2009 09:18:28 -0700 (PDT)
From: M V Chetan <chetan.minajagi@tcs.com>
To: user-java@ibatis.apache.org
Subject: Problem calling Stored function in Oracle
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_70411_9153857.1250871508680"

------=_Part_70411_9153857.1250871508680
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi,

I'm facing certain problems in getting my ibatis mapping to work with an
oracle stored function


Here is my mapping
http://www.nabble.com/file/p25082929/query1.jpg 


 	 { ? = call PKG_SEARCH_LIST.F_COMPANY_LIST(?,?,?,?,?,?,?,?) } 	
 

The error that I get is 

PLS-00306: wrong number or types of arguments in call to 'F_COMPANY_LIST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


The stored function is as follows


CREATE OR REPLACE
PACKAGE BODY PKG_SEARCH_LIST
AS
FUNCTION F_COMPANY_LIST(p_Company varchar2,p_EntFromDt date, p_EntToDt date,
p_LstModFromdt date, P_LstModToDt date, p_TransStatus varchar2, p_sort
varchar2, p_QueryType varchar2)
 RETURN pkg_crs_util.ref_cursor
 IS
 ret_cursor pkg_crs_util.ref_cursor;
 BEGIN
 OPEN ret_cursor for select    1 parent_flg,  'Company1'company, null
active_status ,null role, null security, null Deal_Fr_Dt, null Deal_To_Dt,
null Deal_Title, null Deal_type from dual
union
 select  0 parent_flg , 'Company1' company, 'Y' active_status ,1 role, 'xyz'
security, sysdate Deal_Fr_Dt, sysdate Deal_To_Dt, 'Mydeal'
Deal_Title,'Financing' Deal_type from dual 
 order by parent_flg desc ;

 
RETURN ret_cursor;
 END;
END PKG_SEARCH_LIST;


Can someone please point out where I am going wrong?

Regards,
Chetan

-- 
View this message in context: http://www.nabble.com/Problem-calling-Stored-function-in-Oracle-tp25082929p25082929.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.

------=_Part_70411_9153857.1250871508680
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi,

I'm facing certain problems in getting my ibatis mapping to work with an oracle stored function


Here is my mapping
<img src="http://www.nabble.com/file/p25082929/query1.jpg" border="0" />
<br><hr>
<p>
<typeAlias alias="DealSearchResultsVO" type="com.jpmorgan.crs.vo.DealSearchResultsVO" />

	<resultMap class="DealSearchResultsVO" id="result-mapping">
		<result property="parentFlg" column="PARENT_FLG" />
		<result property="company" column="COMPANY" />
		<result property="activeStatus" column="ACTIVE_STATUS" />
		<result property="role" column="ROLE" />
		<result property="security" column="SECURITY" />
		<result property="dealFromDt" column="DEAL_FR_DT" />
		<result property="dealToDt" column="DEAL_TO_DT" />
		<result property="dealTitle" column="DEAL_TITLE" />
		<result property="dealType" column="DEAL_TYPE" />		
	</resultMap>
	
	
    <parameterMap id="inputMap" class="map">
        <parameter property="companies" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
        <parameter property="enteredFromDate" jdbcType="DATE" javaType="java.util.Date" mode="IN" />
        <parameter property="enteredToDate" jdbcType="DATE" javaType="java.util.Date" mode="IN" />
        <parameter property="lastModifiedFromDate" jdbcType="DATE" javaType="java.util.Date" mode="IN" />
        <parameter property="lastModifiedToDate" jdbcType="DATE" javaType="java.util.Date" mode="IN" />
        <parameter property="transactionStatus" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
		<parameter property="sortByLastModifiedDate" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />        
		<parameter property="queryType" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />		
        
        <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
             
    </parameterMap>

 <procedure id="dealSearchProc1" parameterMap="inputMap" resultMap="result-mapping">
 	 { ? = call PKG_SEARCH_LIST.F_COMPANY_LIST(?,?,?,?,?,?,?,?) } 	
 </procedure>
</p>
<hr>
The error that I get is 
<b>
PLS-00306: wrong number or types of arguments in call to 'F_COMPANY_LIST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
</b>

The stored function is as follows
<hr>
<b>
CREATE OR REPLACE
PACKAGE BODY PKG_SEARCH_LIST
AS
FUNCTION F_COMPANY_LIST(p_Company varchar2,p_EntFromDt date, p_EntToDt date, p_LstModFromdt date, P_LstModToDt date, p_TransStatus varchar2, p_sort varchar2, p_QueryType varchar2)
 RETURN pkg_crs_util.ref_cursor
 IS
 ret_cursor pkg_crs_util.ref_cursor;
 BEGIN
 OPEN ret_cursor for select    1 parent_flg,  'Company1'company, null active_status ,null role, null security, null Deal_Fr_Dt, null Deal_To_Dt, null Deal_Title, null Deal_type from dual
union
 select  0 parent_flg , 'Company1' company, 'Y' active_status ,1 role, 'xyz' security, sysdate Deal_Fr_Dt, sysdate Deal_To_Dt, 'Mydeal' Deal_Title,'Financing' Deal_type from dual 
 order by parent_flg desc ;

 
RETURN ret_cursor;
 END;
END PKG_SEARCH_LIST;
</b>
<hr>


Can someone please point out where I am going wrong?

Regards,
Chetan

<br><hr align="left" width="300">
View this message in context: <a href="http://www.nabble.com/Problem-calling-Stored-function-in-Oracle-tp25082929p25082929.html">Problem calling Stored function in Oracle</a><br>
Sent from the <a href="http://www.nabble.com/iBATIS---User---Java-f370.html">iBATIS - User - Java mailing list archive</a> at Nabble.com.<br>

------=_Part_70411_9153857.1250871508680--