how to create Procedure

Md Akmal

8y879.NET

plz guid me

how to create procedure in oracle databae

and how to use in asp dotnet

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Agarwal8y ago
You can use this online tool to convert SQL syntax to Oracle syntax.
0
Sameer Shaik8y ago
http://www.c-sharpcorner.com/article/calling-oracle-stored-procedures-from-microsoft-net/
+2
Rajneesh Chaubey8y ago
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.
+2
Manav Pandya8y ago
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
+2