Oracle - Java - wrong number or types of arguments in call to 'SP_GET_MENU_PARENTS'

Question

Hi,

Below is my Oracle Procedure. When i call this procedure using java, it throws error like this.

CREATE OR REPLACE PROCEDURE sp_Get_Menu_Parents
(
  v_inMenuID IN VARCHAR2 DEFAULT NULL,
  cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN

   OPEN cv_1 FOR
       SELECT MenuItemId,
              MenuItemName,
              MenuItemDisplayName,
              MenuItemDescription,
              ParentMenuItem,
              MenuLevel,
              MenuOrder,
              CreatedBy,
              CreatedOn,
              LastModifiedBy,
              LastModifiedOn,
              IsValid
         FROM MenuItems
       START WITH MenuItemId = v_inMenuID
       CONNECT BY PRIOR ParentMenuItem  = MenuItemId
         ORDER BY MenuItemId;
END;

Exception is,

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

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1094)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2132)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2015)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2877)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
    at com.savant.securityadmin.MenuConstructor.getValidResourceAndMenuIDs(MenuConstructor.java:349)
    at com.savant.securityadmin.MenuConstructor.getMenuItemDetails(MenuConstructor.java:227)
    at com.savant.securityadmin.MenuConstructor.getConstructedMenuScripts(MenuConstructor.java:497)
    at org.apache.jsp.index_jsp._jspService(index_jsp.java:59)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)

And this is my java code:

CallableStatement cs = objDBConnection.getCallableStatement("{CALL sp_Get_Menu_Parents(?)}");
                    ResultSet rsDetails = null;
                    cs.setInt(1, menID);

Where i am going wrong?

Answer 1

Your procedure

CREATE OR REPLACE PROCEDURE sp_Get_Menu_Parents
(
  v_inMenuID IN VARCHAR2 DEFAULT NULL,
  cv_1 IN OUT SYS_REFCURSOR
)

expects two arguments but you only provide one in your java code. You have to provide the second parameter SYS_REFCURSOR too.

Answer 2

The sproc take two parameters - you're only supplying one to the Callable statement.

1) Change the procedure into a function:

CREATE OR REPLACE FUNCTION sp_Get_Menu_Parents(IN_MENUITEMID IN MENUITEMS.MENUITEMID%TYPE) 
RETURN SYS_RECURSOR IS

  results_cursor SYS_REFCURSOR;

BEGIN

   OPEN results_cursor FOR
       SELECT t.menuitemid,
              t.menuitemname,
              t.menuitemdisplayname,
              t.menuitemdescription,
              t.parentmenuitem,
              t.menulevel,
              t.menuorder,
              t.createdby,
              t.createdon,
              t.lastmodifiedby,
              t.lastmodifiedon,
              t.isvalid
         FROM MENUITEMS t
       START WITH t.menuitemid = IN_MENUITEMID
       CONNECT BY PRIOR t.parentmenuitem = t.menuitemid
         ORDER BY t.menuitemid;

   RETURN results_cursor;

END;

2) Java:

CallableStatement cs = objDBConnection.getCallableStatement("{CALL sp_Get_Menu_Parents(?)}");
cs.setInt(1, menID);
ResultSet rsDetails = cs.executeQuery();