Working with Cursors

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server and other platforms » Oracle » Working with Cursors

Working with Cursors

Rate Topic

Display Mode

Topic Options

Author

Message

Lynn Pettis

Posted Wednesday, November 10, 2010 8:35 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

I need to write some tests scripts to test a stored procedure in Oracle that returns a reference cursor to the user.

create or replace procedure my_oracleProc (
    results_cursor OUT sys_refcursor ,
    p_remote_user    IN VARCHAR2 ,
    p_remote_address IN VARCHAR2 )
...

The Oracle documentation sucks, and doesn't help me figure out how to write a script to call my procedure and then test the return values of the results_cursor.

Any help would be greatly welcomed.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1018676

Lynn Pettis

Posted Friday, November 12, 2010 3:35 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

Okay, figured out a way to test the stored procedure and check the result set returned in the sys_refcursor without writing any sql code. Used the TEST feature in PS/SQL Developer. You are able to inspect the values in the cursor by selecting the ellipses next to that parameter.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1020233

John Rowan

Posted Friday, November 12, 2010 4:20 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

Gotta love it when you have to answer your own question Smile

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #1020250

Lynn Pettis

Posted Friday, November 12, 2010 5:19 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:17 PM
Points: 21,832, Visits: 27,855

John Rowan (11/12/2010)

Gotta love it when you have to answer your own question Smile

Yep, unfortunately it doesn't solve the overall problem, which is being able to write scripts to test code programmatically. Would still like to know how to do that using Oracle, but as I said, the Oracle documentation sucks.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1020261

John Rowan

Posted Monday, November 15, 2010 9:42 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

Wish I could help, I haven't touched (or thought about) Oracle in 6 years....but back when I worked in Oracle, I used the metalink site for support...learned alot there.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

Post #1020886

UMG Developer

Posted Monday, March 21, 2011 12:37 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 2,163, Visits: 2,150

Did you figure this out?

If not here is a link that should get you going: http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

You gotta love Oracle... Wink

Post #1081092

« Prev Topic | Next Topic »

Permissions