0

I'm using SQL Oracle to build a stored procedure. I'm trying to build a stored procedure of the SQL-query below.And I want to return those data to a C# program.

select * from employee_master

I have tried following. Is this correct?

CREATE OR REPLACE PROCEDURE EMPLOYEE_SELECTALL (p_recordset OUTSYS_REFCURSOR)AS 
BEGIN
OPEN p_recordset FOR
SELECT
 *
FROM
EMPLOYEE_MASTER;

END EMPLOYEE_SELECTALL;
Tom
  • 1,343
  • 1
  • 18
  • 37
  • It's correct except of space required between out and sys_refcursor. But the most correct would be don't do such terrible procedure at all and just select records required. – Sanders the Softwarer May 26 '15 at 16:12
  • But how can I view the results after executing SP. (Im new to Oracle SQL Developer) – Tom May 26 '15 at 16:21
  • You said you want to return the data to a C# program, but then you mention SQL Developer; so do you want to view them in SQL Developer? (Where you can do [this](http://stackoverflow.com/a/3527037/266304) to check the results). You [still haven't explained](http://stackoverflow.com/q/30442927/266304) why you're wrapping your query in a procedure in the first place. – Alex Poole May 26 '15 at 16:51
  • I want to return data to c# program. I just want to check values in db level like sql. Is there any way? – Tom May 26 '15 at 16:53
  • I'm still not sure if you're asking how to call and use this from C#, which may be too broad and would need different tags really; or *only* how to run it from SQL Developer to test it, which would be a duplicate. – Alex Poole May 26 '15 at 16:55

2 Answers2

0

If you wish to build a stored procedure that return such resultset first of all you should check if you really need to do this. It's incidental and not recommended way for Oracle. But if you really need so, you should use REF CURSOR.

Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28
0

after executing your stored procedure in SQL Developer, it automatically brings back any output for you to view, including one or more ref cursors.

Example code and screenshots here

enter image description here

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120