wrong number of type of argument while calling oracle procedure from java.

[Jigar Naik]

Hi,

I have written one function in oracle.

FUNCTION f_get_count_by_rel_media
     (i_territory_id              IN   NUMBER,
      i_release_id                IN   NUMBER)
   RETURN VARCHAR2 IS

   v_media_cnt_list            VARCHAR2(200);
   v_time_stamp                DATE;
   
   BEGIN

      v_time_stamp := util_func_pkg.f_get_local_date(i_territory_id);
...
...
...
   RETURN v_media_cnt_list;

   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;
   END f_get_count_by_rel_media;

and i am trying to all this function though java as below.

...
...
...
cstmt = conn.prepareCall("{call PRINT_FUNC_PKG.f_get_count_by_rel_media(?,?,?)}");
            cstmt.setLong(1,territoryId.longValue());
            cstmt.setLong(2,releaseId.longValue());
            cstmt.registerOutParameter(3,OracleTypes.VARCHAR);
            cstmt.execute();
            mediaTypeCommaSeperated = cstmt.getString(3);
        } catch (SQLException sqle) {
            throw new DataAccessException("Error while retrieving media types",sqle);
...
...
...

but i am getting error message wrong number of argument or type. Can anybody help me out with this. the argument seems like ok. and type also seems to be ok.

Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'F_GET_COUNT_BY_REL_MEDIA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

        at oracle.jdbc.driver.DatabaseError.throwSqlException(Ljava.lang.String;Ljava.lang.String;I)V(DatabaseError.java:125)
        at oracle.jdbc.driver.T4CTTIoer.processError(Z)V(T4CTTIoer.java:305)
        at oracle.jdbc.driver.T4CTTIoer.processError()V(T4CTTIoer.java:272)
        at oracle.jdbc.driver.T4C8Oall.receive()V(T4C8Oall.java:623)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(ZZZZ)V(T4CCallableStatement.java:180)
        at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(Z)V(T4CCallableStatement.java:783)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()V(OracleStatement.java:1027)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal()I(OraclePreparedStatement.java:2885)
        at oracle.jdbc.driver.OraclePreparedStatement.execute()Z(OraclePreparedStatement.java:2976)
        at oracle.jdbc.driver.OracleCallableStatement.execute()Z(OracleCallableStatement.java:4103)
        at weblogic.jdbc.wrapper.PreparedStatement.execute()Z(PreparedStatement.java:102)
        at com.sony.spe.ifds.dao.PrintsAndScreensCountDAO.getMediaTypes(Ljava.lang.Long;Ljava.lang.Long;)Ljava.lang.String;(PrintsAndScreensCountDA
        at com.sony.spe.ifds.business.printsandscreens.PrintsAndScreensServiceBean.searchPrintsAndScreens(Lcom.sony.spe.ifds.transferobject.PrintsA
        at com.sony.spe.ifds.business.printsandscreens.PrintsAndScreensService_bpwusw_EOImpl.searchPrintsAndScreens(Lcom.sony.spe.ifds.transferobje
        at jrockit.reflect.NativeMethodInvoker.invoke0(Ljava.lang.Object;ILjava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
        at jrockit.reflect.NativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
        at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
        at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown Source)
        at com.bea.wlw.runtime.core.control.EJBControlImpl.invoke(Ljava.lang.reflect.Method;[Ljava.lang.Object;)Ljava.lang.Object;(EJBControlImpl.j
        at com.bea.wlw.runtime.core.dispatcher.DispMethod.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(DispMethod.java:377)
        at com.bea.wlw.runtime.core.container.Invocable.invoke(Ljava.lang.Object;Ljava.lang.String;Lcom.bea.wlw.runtime.core.dispatcher.DispMethod;
        at com.bea.wlw.runtime.core.container.Invocable.invoke(Lcom.bea.wlw.runtime.core.dispatcher.DispMethod;[Ljava.lang.Object;)Ljava.lang.Objec
        at com.bea.wlw.runtime.core.container.Invocable.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.dispatcher.Invo
        at com.bea.wlw.runtime.jcs.container.JcsContainer.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.dispatcher.In
        at com.bea.wlw.runtime.core.bean.BaseContainerBean.invokeBase(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.dispatch
        at com.bea.wlw.runtime.core.bean.SLSBContainerBean.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.dispatcher.I
        at com.bea.wlwgen.StatelessContainer_ly05hg_ELOImpl.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.dispatcher.
        at com.bea.wlwgen.GenericStatelessSLSBContAdpt.invokeOnBean(Ljava.lang.Object;Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runti
        at com.bea.wlw.runtime.core.bean.BaseDispatcherBean.runAsInvoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.reques
        at com.bea.wlw.runtime.core.bean.BaseDispatcherBean.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.request.Res
        at com.bea.wlw.runtime.core.bean.SyncDispatcherBean.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.request.Res
        at com.bea.wlw.runtime.core.bean.SyncDispatcher_k1mrl8_EOImpl.invoke(Lcom.bea.wlw.runtime.core.request.Request;)Lcom.bea.wlw.runtime.core.r

Can anybody please help me out with this.

[Rob Spoor]

The function takes two parameters and returns a VARCHAR2. You call it as if the return value was a third parameter.