Executing stored procedure
-
Hi all, Could anybody please help me with the following lines of code to execute stored procedure that takes one parameter and returns a number...I tried all oraclecommnad class methods to execute this SP, I get error all the time. Dim cn As New System.Data.OracleClient.OracleConnection(connStr) Dim cmd As New OracleCommand cn.Open() cmd.Connection = cn cmd.CommandText = "{? = call Employees.EmpDept('" & empID & "')}" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("retVal", OracleType.Number) cmd.Parameters("retVal").Direction = Data.ParameterDirection.ReturnValue cmd.ExecuteNonQuery() --- this statement creates following error PLS-00103: Encountered the symbol "{" when expecting one of the following: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
-
Hi all, Could anybody please help me with the following lines of code to execute stored procedure that takes one parameter and returns a number...I tried all oraclecommnad class methods to execute this SP, I get error all the time. Dim cn As New System.Data.OracleClient.OracleConnection(connStr) Dim cmd As New OracleCommand cn.Open() cmd.Connection = cn cmd.CommandText = "{? = call Employees.EmpDept('" & empID & "')}" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("retVal", OracleType.Number) cmd.Parameters("retVal").Direction = Data.ParameterDirection.ReturnValue cmd.ExecuteNonQuery() --- this statement creates following error PLS-00103: Encountered the symbol "{" when expecting one of the following: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe
replace your command text with the name of the proc only ( EmpDept, from the look of it ) and add your ingoing parameter to the parameters collection.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
replace your command text with the name of the proc only ( EmpDept, from the look of it ) and add your ingoing parameter to the parameters collection.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
Thanks for quick response. I will give it a try...but what is wrong with specifying input variable like this. I used to do the same in asp.
-
Thanks for quick response. I will give it a try...but what is wrong with specifying input variable like this. I used to do the same in asp.
This is not asp, this is a real framework. Your asp code just executed SQL, these classes are taking the proc name and attempting to run a proc named exactly what you typed in, with the parameters you provided. This allows the framework to do things like sanitise the input to protect from injection attacks.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
This is not asp, this is a real framework. Your asp code just executed SQL, these classes are taking the proc name and attempting to run a proc named exactly what you typed in, with the parameters you provided. This allows the framework to do things like sanitise the input to protect from injection attacks.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
Great!..Thanks for the explaining it in deatil. Now I understand. Well, I tried the way you have suggested. I still get error :-(. I have defined parameters like cmd.CommandText = "Employees.EmpDept" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("userid", OracleType.VarChar) cmd.Parameters("userID").Direction = Data.ParameterDirection.Input cmd.Parameters("userID").Value = sUserID cmd.Parameters.Add("retVal", OracleType.Number) cmd.Parameters("retVal").Direction = Data.ParameterDirection.ReturnValue Now I get following error ORA-06550: line 1, column 17: PLS-00306: wrong number or types of arguments in call to 'EMPDEPT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored I checked the definition of procedure, its like CREATE OR REPLACE PACKAGE BODY EMPLOYEE IS FUNCTION EMPDEPT(sID IN VARCHAR2) RETURN NUMBER IS vDept NUMBER; BEGIN ...select statement here ... END EMPDEPT; END; / Now, what I am missing here?
-
Great!..Thanks for the explaining it in deatil. Now I understand. Well, I tried the way you have suggested. I still get error :-(. I have defined parameters like cmd.CommandText = "Employees.EmpDept" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("userid", OracleType.VarChar) cmd.Parameters("userID").Direction = Data.ParameterDirection.Input cmd.Parameters("userID").Value = sUserID cmd.Parameters.Add("retVal", OracleType.Number) cmd.Parameters("retVal").Direction = Data.ParameterDirection.ReturnValue Now I get following error ORA-06550: line 1, column 17: PLS-00306: wrong number or types of arguments in call to 'EMPDEPT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored I checked the definition of procedure, its like CREATE OR REPLACE PACKAGE BODY EMPLOYEE IS FUNCTION EMPDEPT(sID IN VARCHAR2) RETURN NUMBER IS vDept NUMBER; BEGIN ...select statement here ... END EMPDEPT; END; / Now, what I am missing here?
peacefulmember wrote:
cmd.CommandText = "Employees.EmpDept"
Again, don't scope it, put EmpDept. It's not a proc, it's a function ? Not sure if the syntax is the same then.... It does look like you're claiming your variable called userID, when it's in fact called sID.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
-
peacefulmember wrote:
cmd.CommandText = "Employees.EmpDept"
Again, don't scope it, put EmpDept. It's not a proc, it's a function ? Not sure if the syntax is the same then.... It does look like you're claiming your variable called userID, when it's in fact called sID.
Christian Graus - Microsoft MVP - C++ "I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
Yes, you answered it right. I changed the variable from userID to sID, w/o changing the scope or syntax, it worked. Thanks a lot for your help. :-)