Re: REF CURSOR
From: ddf <oratune_at_msn.com>
Date: Mon, 30 Mar 2009 07:04:14 -0700 (PDT)
Message-ID: <a28dac79-d584-455e-82c0-2f5f277e3e20_at_e15g2000vbe.googlegroups.com>
On Mar 28, 8:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Mar 27, 4:41 pm, John Schaeffer <ame..._at_iwc.net> wrote:
>
>
>
>
>
> > On Mar 27, 1:50 pm, ddf <orat..._at_msn.com> wrote:
>
> > > Comments embedded.
>
> > > On Mar 27, 9:58 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > > > Ok, this is probably a dumb question, but, I have this code:
>
> > > > CREATE OR REPLACE PROCEDURE cj_trials_log (
> > > > p_newsletter_id NUMBER,
> > > > p_conversion_date VARCHAR2,
> > > > p_data OUT REF_CRS) IS
>
> > > > TYPE ref_crs IS REF CURSOR;
> > > > .
> > > > .
> > > > .
>
> > > > I think it is upset because I use the REF_CRS before it is defined.
>
> > > I'm doing my best to be civil ... and, yes, it IS 'upset' because of
> > > your coding practices.
>
> > > > But how can you do this in a procedure?
>
> > > You don't outside of a package.
>
> > > > Since the parameter
> > > > declaration comes first?
>
> > > This is one reason there are packages, as well as procedures. Declare
> > > this in the package, then use it in the packaged procedire, it's
> > > fairly simple.
>
> > > > Do you create a public type? I'm not sure
> > > > how to do that......
>
> > > You also can't search the newsgroup for "ref cursor" and see what you
> > > find? Is it THAT difficult to use a search engine???
>
> > >http://lmgtfy.com/?q=ref+cursor
>
> > > David Fitzjarrell
>
> > The other thing I was looking to do is to create some public REF
> > CURSOR type, and sort of include it anywhere I need it......
>
> > But if it must be a package, then it must....- Hide quoted text -
>
> > - Show quoted text -
>
> You can use a ref_cursor in a procedure without defining it first in a
> package by using the Oracle provided pre-defined SYS_REFCURSOR
> definition as in "FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ; "
>
> The quoted example is from the 9.2 PL/SQL manual section on
> SYS_REFCURSOR. See the manual for more information.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 30 Mar 2009 07:04:14 -0700 (PDT)
Message-ID: <a28dac79-d584-455e-82c0-2f5f277e3e20_at_e15g2000vbe.googlegroups.com>
On Mar 28, 8:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Mar 27, 4:41 pm, John Schaeffer <ame..._at_iwc.net> wrote:
>
>
>
>
>
> > On Mar 27, 1:50 pm, ddf <orat..._at_msn.com> wrote:
>
> > > Comments embedded.
>
> > > On Mar 27, 9:58 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > > > Ok, this is probably a dumb question, but, I have this code:
>
> > > > CREATE OR REPLACE PROCEDURE cj_trials_log (
> > > > p_newsletter_id NUMBER,
> > > > p_conversion_date VARCHAR2,
> > > > p_data OUT REF_CRS) IS
>
> > > > TYPE ref_crs IS REF CURSOR;
> > > > .
> > > > .
> > > > .
>
> > > > I think it is upset because I use the REF_CRS before it is defined.
>
> > > I'm doing my best to be civil ... and, yes, it IS 'upset' because of
> > > your coding practices.
>
> > > > But how can you do this in a procedure?
>
> > > You don't outside of a package.
>
> > > > Since the parameter
> > > > declaration comes first?
>
> > > This is one reason there are packages, as well as procedures. Declare
> > > this in the package, then use it in the packaged procedire, it's
> > > fairly simple.
>
> > > > Do you create a public type? I'm not sure
> > > > how to do that......
>
> > > You also can't search the newsgroup for "ref cursor" and see what you
> > > find? Is it THAT difficult to use a search engine???
>
> > >http://lmgtfy.com/?q=ref+cursor
>
> > > David Fitzjarrell
>
> > The other thing I was looking to do is to create some public REF
> > CURSOR type, and sort of include it anywhere I need it......
>
> > But if it must be a package, then it must....- Hide quoted text -
>
> > - Show quoted text -
>
> You can use a ref_cursor in a procedure without defining it first in a
> package by using the Oracle provided pre-defined SYS_REFCURSOR
> definition as in "FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ; "
>
> The quoted example is from the 9.2 PL/SQL manual section on
> SYS_REFCURSOR. See the manual for more information.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Thank you, I forgot about SYS_REFCURSOR.
David Fitzjarrell Received on Mon Mar 30 2009 - 09:04:14 CDT