Re: Package Cursor Question

Sybrand Bakker wrote:

> On Fri, 28 Mar 2003 08:26:50 -0800, DA Morgan
> <damorgan_at_exxesolutions.com> wrote:
>
> >E Richard Leiman & Eva wrote:
> >
> >> Dear Daniel,
> >>
> >> Hi.
> >>
> >> No, it was not to populate a block with a ref cursor.
> >>
> >> It's my guess that the cursor in the package can only be called from a
> >> procedure, or function in that package.
> >>
> >> Can you tell me if there is an advantage to creating the cursor as shown in
> >> the book, or simply creating a cursor in the global area of the package?
> >>
> >> > If I understand where you are heading it can not be done. If the intent is
> >> to
> >> > populate a datablock use a REF CURSOR as the OUT parameter from a
> >> procedure.
> >> >
> >> > Daniel Morgan
> >> >
> >> >
> >
> >Where a cursor is defined bears on two things ... scope and security.
> >
> >If you define it in a procedure it is only visible within that procedure. If you
> >define it in the package header it is available globally ... with obvious
> >security implications.
> >
> >I would consider it most likely a case of bad design to define a cursor in a
> >package header. Leaving open the possibility that some specific design might
> >make it useful.
> >
> >Daniel Morgan
>
> Sorry to disagree with you. Global scope means global *within the
> session*. Hence I fail to see *any* security implication, especially
> if the package is secured by proper grants. If the possible offender
> has access to the v$ views he can see the statement anyway.
>
> Actually a cursor in a package header is *good* design, as you now can
> define the cursor *only once*, you don't have to code your lookup
> cursors in every procedure, and you save memory.
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

I don't disagree about global within the session. But it does open it up for access by code that might not have been intended to see it. I'll grant you it is not likely ... but then were scoping not a concern all variables would be global to a session.

I also agree that if a cursor is going to be used by multiple procs it makes sense to define it in the header. But that is why I indicated that I would question the design. If more than one proc is opening the exact same cursor I would question the design implications. Not that it indicates a bad design ... but rather it does open a question worth asking.

Daniel Morgan Received on Fri Mar 28 2003 - 14:14:58 CST