Re: Cursor variables in packages

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 05 Jul 1999 13:07:41 GMT
Message-ID: <3782ada0.4420205@newshost.us.oracle.com>

A copy of this was sent to waltdnes_at_interlog.com (Walter Dnes) (if that email address didn't require changing) On Mon, 05 Jul 1999 00:10:27 GMT, you wrote:

> According to Oracle's very own authorized
>"ORACLE8 PL/SQL Programming"... 'In PL/SQL 2.2, cursor variables
>cannot be declared in a package [...deletia...] This has been
>lifted in PL/SQL 2.3". So ***WHY***, when I log on with client
>version 8 onto a server running v8, does the following happen
>when I execute dexpkg.sql, which tries to create a package spec
>containing a cursor variable???
>===============================================================
>SQL*Plus: Release 8.0.4.0.0 - Production on Sun Jul 4 19:38:46 1999
>

that book is wrong. that was never lifted. You must declare cursor variables as local variables only or in a client application.

SQL> create package x
2 as

  3     type y is ref cursor;
  4     a y;

5 end;
6 /

Warning: Package created with compilation errors.

SQL> show err
Errors for PACKAGE X:

LINE/COL ERROR

-------- -----------------------------------------------------------------
4/6      PLS-00994: Cursor Variables cannot be declared as part of a
         package

4/6      PL/SQL: Declaration ignored

SQL> select * from v$version;

BANNER

Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for Solaris: Version 8.1.5.0.0 - Production NLSRTL Version 3.4.0.0.0 - Production

>(c) Copyright 1997 Oracle Corporation. All rights reserved.
>
>
>Connected to:
>Oracle8 Enterprise Edition Release 8.0.4.1.0 - Production
>With the Partitioning and Objects options
>PL/SQL Release 8.0.4.1.0 - Production
>
>SQL> @dex\dexpkg
>
>Warning: Package created with compilation errors.
>
>Errors for PACKAGE DEX:
>15/11 PLS-00994: Cursor Variables cannot be declared as part
> of a package
>
>15/11 PL/SQL: Declaration ignored
>SQL>
>===============================================================

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jul 05 1999 - 08:07:41 CDT