ORA-06550: Wrong number or type of arguments error when calling a function inside an oracle package in ASP.NET
11,010
You're using a different name for the parameter in your code. Try changing
ename.ParameterName = "ename";
to
ename.ParameterName = "P_ename";
ALSO
You need to add a parameter for the output value:
OracleParameter result = new OracleParameter();
result.OracleType = OracleType.VarChar;
result.Direction = ParameterDirection.ReturnValue;
orclCmnd.Parameters.Add(result);
And get the value from the parameter after calling the function with ExecuteNonQuery:
orclCmnd.ExecuteNonQuery();
strResult = result.Value.ToString();
Comments
-
shresthaal almost 2 years
I have a function inside an oracle package called TEST in an Oracle 10g database
FUNCTION GetEname(P_ename IN VARCHAR2) RETURN VARCHAR2 AS retVal VARCHAR2(10); BEGIN retVal := SUBSTR(P_ename, 3, INSTR(P_ename, ':', 1, 2) - 3); RETURN RetVal; END GetEntDefIEIDFromEname;
I have created an ASP.net page and I have the following code in the page_load:
String strResult = ""; try { oracleConn.ConnectionString = ConfigurationManager.ConnectionStrings["OracleDatabase"].ConnectionString; oracleConn.Open(); OracleCommand orclCmnd = new OracleCommand(); orclCmnd.Connection = oracleConn; orclCmnd.CommandText = "TEST.GetEname"; orclCmnd.CommandType = CommandType.StoredProcedure; OracleParameter ename = new OracleParameter(); ename.ParameterName = "ename"; ename.OracleType = OracleType.VarChar; ename.Direction = ParameterDirection.Input; ename.Value = "0:490330"; orclCmnd.Parameters.Add(ename); strResult = (String)orclCmnd.ExecuteOracleScalar(); oracleConn.Close(); oracleConn.Dispose(); lbl1.Text = "Result of " + strResult; } catch (Exception ex) { Console.Out.WriteLine(ex.ToString()); oracleConn.Close(); }
When I run the code I get the following error message:
"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GETENAME'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"} System.Exception {System.Data.OracleClient.OracleException}
-
shresthaal almost 12 yearsI tried that and now I get this error "ORA-06550: line 1, column 7:\nPLS-00221: 'GETENAME' is not a procedure or is undefined\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"} System.Exception {System.Data.OracleClient.OracleException}"
-
shresthaal almost 12 yearsD Stanley thank you for ur help. I added the result output parameter and ran the application. I received an error message stating "Parameter 'Parameter 1': No size set for variable length data type: String." So I added result.Size = 1024. I ran the application again, but I receive the original error message of "wrong number of types"
-
shresthaal almost 12 yearsThank you that worked. Still needed to add the result.Size to it.