Newbie Problems...
-
Hi all, Am trying to call a simple function held against an Oracle database. I've included the function at the bottom of this post. All it does is return a varchar(3) value along the lines of 'ROW' or 'ENG', depending on what value was passed in so one IN parameter and one OUT. The C# code used to call this is as follows: OracleCommand cmd = new OracleCommand("SMD2EUP.get_lan_id", con); cmd.CommandType = CommandType.StoredProcedure; OracleParameter cv_Ref = new OracleParameter("cv_ref", OracleType.Number, 5); cv_Ref.Value = item.CV.Ref; cv_Ref.Direction = ParameterDirection.Input; OracleParameter lan_ID = new OracleParameter("this_lan_id", OracleType.VarChar, 3); lan_ID.Direction = ParameterDirection.Output; cmd.Parameters.Add(cv_Ref); cmd.Parameters.Add(lan_ID); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); string result = cmd.Parameters["this_lan_id"].Value.ToString(); The SMD2eup is the name of the package that the function is stored in and get_lan_id is the name of the function. All code compiles successfully. When I run the application I get ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_LAN_ID' What am I missing as I can't see anything wrong with the code, 1 IN paramter of type Number (or int) and 1 OUT parameter of type VARCHAR(3) (or string) with the directions being declared. Please can some one help? Thanks very much... FUNCTION get_lan_id(cv_ref eup_comm_veh.xvol_region_ref%TYPE) RETURN eup_comm_veh.lan_id%TYPE IS this_lan_id eup_comm_veh.lan_id%TYPE; BEGIN SELECT lan_id INTO this_lan_id FROM eup_comm_veh WHERE cvh_id IN (SELECT MIN(cvh_id) FROM eup_comm_veh WHERE xvol_region_ref = cv_ref); RETURN(this_lan_id); EXCEPTION WHEN NO_DATA_FOUND THEN this_lan_id := LAN_ERROR; RETURN(this_lan_id); WHEN OTHERS THEN this_lan_id := LAN_ERROR; RETURN(this_lan_id); END get_lan_id;
-
Hi all, Am trying to call a simple function held against an Oracle database. I've included the function at the bottom of this post. All it does is return a varchar(3) value along the lines of 'ROW' or 'ENG', depending on what value was passed in so one IN parameter and one OUT. The C# code used to call this is as follows: OracleCommand cmd = new OracleCommand("SMD2EUP.get_lan_id", con); cmd.CommandType = CommandType.StoredProcedure; OracleParameter cv_Ref = new OracleParameter("cv_ref", OracleType.Number, 5); cv_Ref.Value = item.CV.Ref; cv_Ref.Direction = ParameterDirection.Input; OracleParameter lan_ID = new OracleParameter("this_lan_id", OracleType.VarChar, 3); lan_ID.Direction = ParameterDirection.Output; cmd.Parameters.Add(cv_Ref); cmd.Parameters.Add(lan_ID); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); string result = cmd.Parameters["this_lan_id"].Value.ToString(); The SMD2eup is the name of the package that the function is stored in and get_lan_id is the name of the function. All code compiles successfully. When I run the application I get ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_LAN_ID' What am I missing as I can't see anything wrong with the code, 1 IN paramter of type Number (or int) and 1 OUT parameter of type VARCHAR(3) (or string) with the directions being declared. Please can some one help? Thanks very much... FUNCTION get_lan_id(cv_ref eup_comm_veh.xvol_region_ref%TYPE) RETURN eup_comm_veh.lan_id%TYPE IS this_lan_id eup_comm_veh.lan_id%TYPE; BEGIN SELECT lan_id INTO this_lan_id FROM eup_comm_veh WHERE cvh_id IN (SELECT MIN(cvh_id) FROM eup_comm_veh WHERE xvol_region_ref = cv_ref); RETURN(this_lan_id); EXCEPTION WHEN NO_DATA_FOUND THEN this_lan_id := LAN_ERROR; RETURN(this_lan_id); WHEN OTHERS THEN this_lan_id := LAN_ERROR; RETURN(this_lan_id); END get_lan_id;
Try changing the direction of the output parameter to Return.
Deja View - the feeling that you've seen this post before.