Doubt in Stored Procedure

[haifriends]

Hi everyone,

I want to create a stored procedure which returns result set. Then i've to access it from ASP.NET.

How to create a procedure which returns result set? and how to call this from ASP.NET?

Database is Oracle.

Can anyone guide me?


-
haifriends

[ITGURU]

Dear haifriends,

To Create a Stored Procedure please read the documentation provided with Oracle but for your reference please find below a sample stored procedure which return a result set.


create or replace procedure MyStoredProcedure
is
begin
select * from YOURTABLE
end;


And to Call above stored procedure please follow following steps (VB.NET syntax is used for example):
1. Add System.Data.OleDb into your ASP.NET code behind file using following statment at the top of the file:
Imports System.Data.OleDb

2. Write following code wherever you want to call your stored procedure:


Dim conn As New OleDbConnection(str)
conn.Open()
Dim cmd As New OleDbCommand("MyStoredProcedure", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim dtMyTable As DataTable
Dim da As New OleDbDataAdapter(cmd)
da.Fill(dtMyTable)

conn.Close()



In above example dtMyTable contains all the rows returned by Oracle stored Procedure MyStoredProcedure.

Hope this will help u out in understanding your requirement.