Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: stored proc to create sequence, insufficent priv
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