[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2014-01-23
18
Medium Priority
?
659 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 78

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 930 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 78

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 78

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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

608 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