Solved

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

Posted on 2014-01-23
18
633 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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39803241
>>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 74

Expert Comment

by:sdstuber
ID: 39803249
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
ID: 39803260
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
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.

 

Author Comment

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39803271
>> 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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39803278
>>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
ID: 39803288
@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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 310 total points
ID: 39803297
>>@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 74

Expert Comment

by:sdstuber
ID: 39803299
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39803318
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 74

Expert Comment

by:sdstuber
ID: 39803323
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
ID: 39803348
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39803359
>>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
ID: 39803394
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 74

Expert Comment

by:sdstuber
ID: 39803405
remove that entire line.

the RETURN clause is only for Functions
0
 
LVL 77

Expert Comment

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

Author Closing Comment

by:Nic_Nic
ID: 39803431
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
ID: 39803947
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't Access My Database 57 77
Oracle DBLINKS From 11g to 8i 3 49
Field name with special character (Ñ) in Oracle 11 96
setting local variables in a cursor block 3 20
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 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