Oracle - Java - 调用“SP_GET_MENU_PARENTS”时的参数数量或类型错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1671988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Java - wrong number or types of arguments in call to 'SP_GET_MENU_PARENTS'
提问by Srinivasan
Below is my Oracle Procedure. When i call this procedure using java, it throws error like this.
下面是我的 Oracle 程序。当我使用 java 调用这个过程时，它会抛出这样的错误。
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:
这是我的java代码：
CallableStatement cs = objDBConnection.getCallableStatement("{CALL sp_Get_Menu_Parents(?)}");
ResultSet rsDetails = null;
cs.setInt(1, menID);
Where i am going wrong?
我哪里出错了？
回答by OMG Ponies
The sproc take two parameters - you're only supplying one to the Callable statement.
sproc 有两个参数 - 您只向 Callable 语句提供一个参数。
1) Change the procedure into a function:
1）将过程改成函数：
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:
2) 爪哇：
CallableStatement cs = objDBConnection.getCallableStatement("{CALL sp_Get_Menu_Parents(?)}");
cs.setInt(1, menID);
ResultSet rsDetails = cs.executeQuery();
回答by tangens
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_REFCURSORtoo.
需要两个参数，但您只在 Java 代码中提供一个。您也必须提供第二个参数SYS_REFCURSOR。
