Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: stored proc to create sequence, insufficent priv

Re: stored proc to create sequence, insufficent priv

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 24 Apr 2003 19:51:34 -0700
Message-ID: <3EA8A2B5.84215DDE@exxesolutions.com>


Ken Chesak wrote:

> >Daniel Morgan wrote
> > You do not have privileges in the RIDES_DEV schema to create a sequence explicitly
> > granted to your current schema. And, quite frankly, you should not be writing a stored
> > procedure to do this. Just execute the code at the command line.
> >
> > The above is overcomplication without redeeming benefit.
>
> I would agree it an overcomplicated proc to create just a sequence.
> But actually this procedure will be very usefull during development.
> I have many tables and sequences, as the data is loaded and reloaded
> in development, things can get out of order. This proc would check
> each sequence and make sure it is in sync with the corresponding
> table. Hopefully this proc wont be required in production.
>
> The "create sequence" is granted via a role, that might be the
> problem. I was able to use "alter sequence" in the proc.
>
> thanks

I would go further than "hopefully" with respect to production. It would be a violation of what I have learned over the decades is good practice to do so. And I still question its use here. I fail to see why, if you are dropping and recreating a schema for development and testing you would want to do this.

Take a good look in /rdbms/admin at sql.bsq, catalog.sql, catproc.sql and its subscripts. Do you see Oracle, even once, doing what you are doing? No! And there is a very good reason.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
(remove one 'x' from my email address to reply)
Received on Thu Apr 24 2003 - 21:51:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US