Solved

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

Posted on 2014-01-23
18
626 Views
Last Modified: 2014-01-23
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
Comment
Question by:Nic_Nic
  • 7
  • 6
  • 5
18 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:Nic_Nic
Comment Utility
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
 

Author Comment

by:Nic_Nic
Comment Utility
@sdstuber - Would the second option make more sense then?  Where I specify the columns I care about... ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Where I specify the columns I care about... ?

Not sdstuber but:
The second option will only return a single value not an 'array'.
0
 

Author Comment

by:Nic_Nic
Comment Utility
@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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 310 total points
Comment Utility
>>@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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:Nic_Nic
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 

Author Comment

by:Nic_Nic
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
remove that entire line.

the RETURN clause is only for Functions
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Procedures don't 'return' they just have OUT parameters.  Functions return.
0
 

Author Closing Comment

by:Nic_Nic
Comment Utility
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
 

Author Comment

by:Nic_Nic
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now