Hi,

I never used stored procedures in asp.net and can anyone give example how to create stored procedure in oracle to display users table in grdiview.

Any help highly appreciated.

I wrote something like this, and its giving errors in sql plus.
create or replace PROCEDURE sp_users (out types.cursor)
 AS
CURSOR c1 IS
SELECT * FROM Users
  ;
  rs c1%ROWTYPE;
BEGIN
END;

__________________
Rams

You can simply create like below:

CREATE OR REPLACE PROCEDURE sp_users
IS
BEGIN
  SELECT * FROM Users;
END;

Regards
Mike
===================================
 To the world you might be one person,
 but for someone you may be the whole world.
===================================
^^Though the above is non-technical, but there is no harm thinking emotionally.

Mike.. Thank you very much. It works. Now i am stuck with following problem when iam using this with Where statement.

I passed one parameter in that procedure sp_users (sUserFilter as VARCHAR2(64))

In my asp.net code, earlier i was using sql query like this.

sSQL = "SELECT * FROM Users WHERE CreatedOn = to_date('1/1/2007','mm/dd/yyyy') "

If sUserFilter <> "" Then sSQL &= " AND UserID LIKE '%" & sUserFilter & "%'"

Now in stored procedure, how can i write above query, if parameter is null and parameter is not null ?

Any help is great.

Thanks

You need to use the Oledb class to access the oracle data, try something like below:

string queryString = "SELECT * FROM Table1 WHERE Field1 LIKE ?";
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a";
OleDbDataReader reader = command.ExecuteReader();


Regards
Mike

Thanks, will this query works even if i don't want to pass parameter? because parameter is not required field.

If parameter is not required, do not pass it. I have just given you an example, so modify the sql string accordingly.

Regards
Mike

Thanks.
parameter is required field only when user click on search filter. Do we need to create 2 stored procedures for 2 conditions (with parameter and no parameter)? OR Do we need to change sql query inside sp, if yes how can we?