Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

Simple Oracle "select all" stored proc into a C# application.

Looking for a little assistance as I'm new to Oracle (11g).  

Say I create the following Stored Proc:

create or replace PROCEDURE sp_test 
( 
 P_CURSOR OUT sys_refcursor
)

IS

BEGIN
	OPEN P_CURSOR FOR
    SELECT columnA, columnB
    FROM table 

EXCEPTION
    WHEN OTHERS THEN RAISE;

END sp_test;

Open in new window


1. What is the syntax to execute this proc in SQL Developer to view the results?  

I know "exec GET_CORR_ALL();" would work in SQL Server but not in Oracle.

create or replace PROCEDURE sp_test 
( 
  columnA OUT table.columnA%TYPE,
  columnB OUT table.columnB%TYPE
)

IS

BEGIN
    
    SELECT columnA, columnB
    INTO columnA, columnB
    FROM table
    EXCEPTION
	
    WHEN OTHERS THEN RAISE;

END sp_test;

Open in new window


Any help would be appreciated.
0
Nic_Nic
Asked:
Nic_Nic
  • 7
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>1. What is the syntax to execute this proc in SQL Developer to view the results?

Never used SQL Developer:  I believe sql developer will accept sqlplus syntax

In sqlplus it is:
var mycur refcursor
exec sp_test(:mycur);
print mycur

The second version:
just declare the two variables with the correct datatype and call it in similar fashion.
0
 
sdstuberCommented:
The oracle procedure simply opens the cursor.  There is not data to be returned by the procedure.
You have to actually fetch the rows from the cursor.

Note, in Oracle 12c,  you can write procedures like the T-SQL version where you simply execute a query with no target for the data
0
 
Nic_NicAuthor Commented:
I should mention the results are being used in a C# application.  I'd like to use the following syntax and just execute a reader on the results... Ex:

var command = new OracleCommand("sp_test", oracleCommand)
                {
                    CommandType = CommandType.StoredProcedure
                };

                oracleCommand.Open();

using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    { ...... }

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Nic_NicAuthor Commented:
@sdstuber - Would the second option make more sense then?  Where I specify the columns I care about... ?
0
 
slightwv (䄆 Netminder) Commented:
>> C# application

If you aren't using Oracle's ODP.Net, I strongly encourage you to convert.

It comes with a cursor example.  There are also many examples on the web.  If you cannot get any just let me know and I'll try to come up with one.

ODP.Net is part of the ODAC:
http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html
0
 
slightwv (䄆 Netminder) Commented:
>>Where I specify the columns I care about... ?

Not sdstuber but:
The second option will only return a single value not an 'array'.
0
 
Nic_NicAuthor Commented:
@sdstuber - Sorry to nag... Could I trouble you with how to accomplish returning all the rows.  I've looked at tons of examples on the Oracle site but nothing gave me a conclusive answer that made sense.... at least syntactically.  I have a strong SQL Server background.  In SQL Server, this would return your results and allow the calling code to iterate over them.  Oracle is a little confusing to me... still an Oracle newbie.
0
 
slightwv (䄆 Netminder) Commented:
>>@sdstuber - Sorry to nag... Could I trouble you with how to accomplish returning all the rows.

Again, I'm not sdstuber...

>> how to accomplish returning all the rows

The ref cursor should work.  The ODAC should come with an example.

Here's an example from oracle.com:
http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleRefCursorClass.htm
0
 
sdstuberCommented:
what version of the database do you have?

If you don't have 12c then attempting to do procedural implicit result sets won't work
0
 
sdstuberCommented:
If you do have 12c, try writing your procedure like this


create or replace PROCEDURE sp_test
IS
    v_CURSOR sys_refcursor;
BEGIN
      OPEN v_CURSOR FOR
    SELECT columnA, columnB
    FROM table ;

dbms_sql.return_result (v_cursor);  -- this only applies to 12c

END sp_test;
0
 
sdstuberCommented:
ah, I just saw you already included your version above,
unless you can upgrade to 12c nevermind my posts.

slightwv can take it from here.
0
 
Nic_NicAuthor Commented:
Sorry slightwv!!!  It was a fast copy/paste in my comments...  I appreciate all your help.  That example you linked was one of the better ones I've seen.  

Are you saying it's best, in this situation, to create a function versus a stored proc?
0
 
slightwv (䄆 Netminder) Commented:
>>Are you saying it's best, in this situation, to create a function versus a stored proc?

I have no real suggestion either way.  There isn't a real big difference on the .Net code side.
0
 
Nic_NicAuthor Commented:
Thanks again slightwv!  You've been most helpful.  Last two minor questions, I promise!

I used the example you provided and modified the code above:

CREATE OR REPLACE PROCEDURE sp_test (refcur_out OUT SYS_REFCURSOR)

RETURN SYS_REFCURSOR IS refcur_ret SYS_REFCURSOR;

BEGIN
    OPEN refcur_ret FOR
    SELECT columnA, columnB
    FROM table
EXCEPTION
    WHEN OTHERS THEN RAISE;

END sp_test;

Open in new window


This fails on the "RETURN SYS_REFCURSOR IS refcur_ret SYS_REFCURSOR;"  

1.  Any glaring / obvious issues?  Perhaps this isn't the correct syntax for procs.
2.  How would I execute this in SQL Developer to view the results.
0
 
sdstuberCommented:
remove that entire line.

the RETURN clause is only for Functions
0
 
slightwv (䄆 Netminder) Commented:
Procedures don't 'return' they just have OUT parameters.  Functions return.
0
 
Nic_NicAuthor Commented:
Thanks for the link.  I think that helped.  I still don't have a clear example of how to make it all gel syntactically but the principles described in the various comments helped a great deal.
0
 
Nic_NicAuthor Commented:
I still couldn't figure out how to get this working in a stored proc but I did get it working as a function.  I've provided the code for those of us who learn by looking at syntax:

    create or replace FUNCTION sp_test 
    
    RETURN SYS_REFCURSOR AS ret_cursor SYS_REFCURSOR;
    
    BEGIN
        OPEN ret_cursor FOR
          SELECT columnA, columnB
          FROM table
        RETURN ret_cursor;
    EXCEPTION
        WHEN OTHERS THEN RAISE;
    
    END sp_test ;

Open in new window


To execute the query in SQL Developer, I used:

variable v_ref_cursor refcursor;
exec :v_ref_cursor := sp_test; 
print :v_ref_cursor;

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now