how to create Procedure

Question

plz guid me how to create procedure in oracle databae and how to use in asp dotnet

Manav Pandya · Answer

Hello You can create procedure in oracle as below : create or replace procedure count_emp_by_dept(pin_deptno number, pout_count out number) is begin select count(*) into pout_count from scott.emp where deptno=pin_deptno; end count_emp_by_dept; And also can call from .net itself as : OracleCommand objCmd = new OracleCommand(); // Using actually oracle as backend objCmd.Connection = objConn; objCmd.CommandText = "count_emp_by_dept"; // Calling oracle SP objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20; objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output; For more reference you can go through : http://www.c-sharpcorner.com/article/calling-oracle-stored-procedures-from-microsoft-net/ https://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c Thanks

Sameer Shaik · Answer

http://www.c-sharpcorner.com/article/calling-oracle-stored-procedures-from-microsoft-net/

Rajneesh Chaubey · Answer

Hi @Md Akmal, To create a procedure in sql we do the following:- CREATE PROCEDURE ProcSample ( @param1 Datatype(size), @Param2 Datatype ) AS BEGIN --For ex you want to insert Insert into tblSample() values where column1= @param1 AND column2= @param2 END --Similarly you can create procedure for update delete and fetching from sql server. Now when we call that procedure from our code behind(C#), we have to specify name of that procedure and passing the appropriate parameters that we have declared in stored procedure Like This:- SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "Usp_RxActivity" ; cmd.CommandType = CommandType.StoredProcedure; //Declaring parameters cmd.Parameters.Add( "@param1" , SqlDbType.NVarChar, 200).Value = txtname.text; cmd.Parameters.Add( "@Param2" , SqlDbType.NVarChar, 200).Value = txtpas.text; Here Nvarchar is just a datatype which I used. Note:-Datatype must be same while specifying in SP and while calling from code behind. Hope you get it.

Piyush Agarwal · Answer

You can use this online tool to convert SQL syntax to Oracle syntax.

how to create Procedure

Answers (4)