Re: HELP! Create Table in stored procedure??

Sure, all the time.
Just to make sure this is clear
You can't use DDL *directly*.
However you can setup a string and process that string through a) dbms_sql (7.3, 8.0)
b) execute immediate (8i)

If you a smart you create a procedure
create or replace procedure do_ddl(sqlstr in varchar2) is cur_handle number := dbms_sql.open_cursor; res number;
begin
dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native); res := dbms_sql.execute(cur_handle);
dbms_sql.close_cursor(cur_handle);

exception
when others then
if cur_handle > 0 then

   dbms_sql.close_cursor(cur_handle);
end if;
end;
/

Hth,

Sybrand Bakker, Oracle DBA

"Brian F. Haddock" <bhaddock_at_yahoo.com> wrote in message news:fPXJ5.9429$NP.752894_at_news.flash.net...
> 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 - 12:14:59 CDT