Re: HELP! Create Table in stored procedure??

Have you actually done this before? I'm curious because I've seen oracle doc that specifially says you can't use DDL in store procedures. Whether this means you absolute *cannot* or that it is simply undesirable is what I'm wondering about.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:972471603.22382.2.pluto.d4ee154e_at_news.demon.nl...
> Sorry to say so and to contradict you, but your reply is completely
> incorrect and is actually misinformation.
>
> You can use DDL in stored procedures, whether it is desirable or not is a
> different issue.
> His problem is that he needs to be granted directly instead of by means of
 a
> roles, as roles are not being used during compilation of a stored
 procedure,
> roles being volatile.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
>
> "Akram JARO" <ajaro_at_synavant.com> wrote in message
> news:hUyJ5.61$Mo2.3853_at_nreader1.kpnqwest.net...
> > hi
> >
> > you can't use DDL in Stored Procedures :-((
> >
> > ciao
> > Akram JARO
> > Vienna - Austria
> > Charles McDonald <cmcdon12_at_ford.com> schrieb in im Newsbeitrag:
> > 39F6AAA8.CC70BC95_at_ford.com...
> > > Hello there all
> > >
> > > Could anyone tell me the cause of the following
> > >
> > > ERROR at line 1:
> > > ORA-01031: insufficient privileges
> > > ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> > > ORA-06512: at "SYS.DBMS_SQL", line 32
> > > ORA-06512: at "MARGMOD.BUILDENTITYTABLE", line 24
> > > ORA-06512: at line 2
> > >
> > > I create a stored procedure to write dynamic SQl using the dbms_sys
> > > stuff below
> > >
> > > vCursor := dbms_sql.Open_Cursor;
> > > dbms_sql.parse(vCursor, vSQL,dbms_sql.v7);
> > > vReturn := dbms_sql.Execute(vCursor);
> > > dbms_sql.close_cursor(vCursor);
> > >
> > > now, if I execute the Body of the stored procedure in SQL Plus as an
> > > anonymous block, it works. If I create the procedure and call it from
> > > SQL Plus it does not work???
> > >
> > > Can anyone explain this?
> > >
> > > For what it is worth, all the work has been done during one session,
 so
> > > I have access to the stored procedure (having created it) have the
> > > required create table rights (which is what I am trying).
> > >
> > > Please help
> >
> >
>
>
Received on Thu Oct 26 2000 - 10:10:03 CDT