Re: Result Sets from stored procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/11
Message-ID: <339e8908.13491629@newshost>#1/1

On 10 Jun 1997 21:35:49 GMT, Steven Whatley <swhatley_at_blkbox.com> wrote:

>To: tkyte_at_us.oracle.com
>Subject: Re: Result Sets from stored procedures
>Cc: swhatley
>Newsgroups: comp.databases.oracle.misc
>In-Reply-To: <01bc7080$83c54680$02010101_at_johna.teamsinc.com> <3398b41b.2014216_at_newshost>
>
>In article <3398b41b.2014216_at_newshost> you wrote:
>: On 3 Jun 1997 17:44:00 -0700, "Superman" <amir_at_neta.com> wrote:
>: >Does 7.3 allow result sets to be returned from stored
>: >procedures/functions/packages?
>:
>: With 7.2 and 7.3 you have cursor variables. Cursor variables are cursors opened
>: by a pl/sql routine and fetched from by another application or pl/sql routine
>: (in 7.3 pl/sql routines can fetch from cursor variables as well as open them).
>: The cursor variables are opened with the privelegs of the owner of the procedure
>: and behave just like they were completely contained within the pl/sql routine.
>: It uses the inputs to decide what database it will run a query on.
>
>This is interesting. Didn't know about referenced cursors. But, I can't
>get the line:
>
>SQL> variable C refcursor
>Usage: VARIABLE [ variable [ NUMBER | CHAR | CHAR (n) |
> VARCHAR2 | VARCHAR2 (n) ] ]
>SQL>
>

You've got a mismatch between versions of plus and the database. You should have 3.2 with 7.2, for example:

SQL*Plus: Release 3.2.3.0.0 - Production on Wed Jun 11 06:54:43 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to:
Oracle7 Server Release 7.2.3.0.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.2.3.0.0 - Production  

SQL> variable c refcursor
SQL> works but

SQL*Plus: Release 3.1.3.7.1 - Production on Wed Jun 11 07:07:15 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Connected to:
Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.1.6.2.0 - Production  

SQL> variable c refcursor
Usage: VARIABLE [ variable [ NUMBER | CHAR | CHAR (n) |

                             VARCHAR2 | VARCHAR2 (n) ] ]

SQL> So, your database (7.2) can do refcursors but your sql*plus (the 7.1.6 compatible version of plus) cannot. Update your sqlplus and you'll be able to do this.

>to work. The start of my SQL*Plus is as follows:
>
>SQL*Plus: Release 3.1.3.7.1 - Production on Tue Jun 10 16:30:15 1997
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>
>Connected to:
>Oracle7 Server Release 7.2.3.0.0 - Production Release
>With the distributed, replication and parallel query options
>PL/SQL Release 2.2.3.0.0 - Production
>
>SQL>
>
>Should REFCURSOR work with this version of SQL*Plus?
>
>Thanks,
>Steven
>http://www.blkbox.com/~swhatley/
> _|_ | _|_ "I am the way and the truth and
>Steven Whatley | --|-- | the life. No one comes to the
>swhatley_at_blkbox.com | | | Father except through me."
>Houston, Texas | -- Jesus Christ (John 14:6 NIV)
> |

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 11 1997 - 00:00:00 CDT