Re: SHow a cursor
From: joel garry <joel-garry_at_home.com>
Date: Wed, 8 Feb 2012 15:49:53 -0800 (PST)
Message-ID: <77cb2209-56ae-4251-a08a-e31012b12e7a_at_v6g2000pba.googlegroups.com>
On Feb 8, 12:37 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Peter Schneider wrote:
> > Am 08.02.2012 19:39, schrieb Gerard H. Pille:
> >> Tim Slattery wrote:
> >>> Tim Slattery<Slatter..._at_bls.gov> wrote:
>
> >>>> The "select" statement works when I run it from the SQL developer
> >>>> console. But when I call the function I'm told "table or view does not
> >>>> exist". But it *does* exist.
>
> >>> I saw a web post that may have the answer. He says that a common cause
> >>> of this is that select permission on the table has been granted to a
> >>> role, and that role has then been assigned (granted? not sure of the
> >>> proper terminology) to me. But that role does not propagate to
> >>> functions that I write. The result is just what I'm seeing: I can
> >>> select from the table directly, but the function can't find it.
>
> >>> Seems an irrational way for things to work, but then I'm only a
> >>> developer...
>
> >> If you'd like to become a good developer, don't use dynamic SQL.
>
> > If you'd like to become a superb developer, learn how to use dynamic SQL at great depth, know
> > what bind variables are good for, learn what excessive parsing means to your application
> > performance...
>
> > And then you learn when dynamic SQL is appropriate and when not.
>
> > DBMS_SQL, EXECUTE IMMEDIATE and OPEN ... FOR '...' can do magic in the hand of PL/SQL wizards ;-)
>
> > Regards
> > Peter
>
> And bring a production database to its knees.
Date: Wed, 8 Feb 2012 15:49:53 -0800 (PST)
Message-ID: <77cb2209-56ae-4251-a08a-e31012b12e7a_at_v6g2000pba.googlegroups.com>
On Feb 8, 12:37 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Peter Schneider wrote:
> > Am 08.02.2012 19:39, schrieb Gerard H. Pille:
> >> Tim Slattery wrote:
> >>> Tim Slattery<Slatter..._at_bls.gov> wrote:
>
> >>>> The "select" statement works when I run it from the SQL developer
> >>>> console. But when I call the function I'm told "table or view does not
> >>>> exist". But it *does* exist.
>
> >>> I saw a web post that may have the answer. He says that a common cause
> >>> of this is that select permission on the table has been granted to a
> >>> role, and that role has then been assigned (granted? not sure of the
> >>> proper terminology) to me. But that role does not propagate to
> >>> functions that I write. The result is just what I'm seeing: I can
> >>> select from the table directly, but the function can't find it.
>
> >>> Seems an irrational way for things to work, but then I'm only a
> >>> developer...
>
> >> If you'd like to become a good developer, don't use dynamic SQL.
>
> > If you'd like to become a superb developer, learn how to use dynamic SQL at great depth, know
> > what bind variables are good for, learn what excessive parsing means to your application
> > performance...
>
> > And then you learn when dynamic SQL is appropriate and when not.
>
> > DBMS_SQL, EXECUTE IMMEDIATE and OPEN ... FOR '...' can do magic in the hand of PL/SQL wizards ;-)
>
> > Regards
> > Peter
>
> And bring a production database to its knees.
Good is relative. When the choice is, solve a correlated update ORA-1779 by adding a primary key constraint with all the testing and design changes that implies, versus a one time script of 10K update statements generated by a korn shell script, "on yer knees, beeyatch, gettin' mah moneys worth tonight."
jg
-- _at_home.com is bogus. http://www.utsandiego.com/news/2012/feb/07/viasat-files-lawsuit-against-satellite-builder/Received on Wed Feb 08 2012 - 17:49:53 CST