Re: Please HELP !!!(It makes me crazy)

THANK YOU EVERYONE !
Who has answered for my problem.
I was confused the CREATE TABLE privilege. I could (!) CREATE TABLE foo(x INTEGER), but my procedure produced nothing, and, when I did GRANT CREATE TABLE TO username, the procedure's done. Thank you again.
Regards,
Mike.

>
>

> Thomas Kyte wrote:
>
> > On Wed, 14 Jan 1998 21:50:06 GMT, stiger_at_chat.ru (Mikhail) wrote:
> >
> > [snip]
> >
> > >>>Hello, everyone,
> > >>>I try create a storage procedure that produces a table_name and
> > >>>creates a table with this table_name in run time.
> > How about the runtime error message? In the code below you should put a RAISE
> > in the exception block to allow the error to propagate back up to sql*plus so we
> > can see that this is probably an Insufficient privelege error.
> >
> > Are you sure you have create table granted to you? I bet you have the ability
> > to create a table via a ROLE and roles are never enabled stored procedures. to
> > see if you can create a table in a stored procedure, do this in sqlplus:
> >
> > SQL> set role none;
> >
> > Role set.
> >
> > SQL> create table foo ( x int );
> > create table foo ( x int )
> > *
> > ERROR at line 1:
> > ORA-01031: insufficient privileges
> >
> > -----------------------------------------------
> >
> > If that happens, then
> >
> > SQL> grant create table to USERNAME;
> >
> > and try it again. Once you can create table with no roles enabled, you can
> > create a procedure that creates tables.
> >
> > [snip]
> >
> > >BEGIN
> > >cursor_name:= DBMS_SQL.OPEN_CURSOR;
> > >SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'MON_YY') INTO table_name FROM
> > >dual;
> > >command_run:='DROP TABLE ' || table_name;
> > >DBMS_OUTPUT.PUT_LINE('Table name is: ' || table_name);
> > >DBMS_SQL.PARSE(cursor_name, command_run, DBMS_SQL.V7);
> > >row_processed:= DBMS_SQL.EXECUTE(cursor_name);
> > >DBMS_SQL.CLOSE_CURSOR(cursor_name);
> > >EXCEPTION
> > > WHEN OTHERS THEN
> > > DBMS_SQL.CLOSE_CURSOR(cursor_name);
> >
> > -- put a raise here to allow the error to propagate to sqlplus
> > -- so you can see what the error is, or just comment out
> > -- the exception block for now
> > raise;
> >
> > >END;
> > >
> >
> > [snip]
> >
> > Thomas Kyte
> > tkyte_at_us.oracle.com
> > Oracle Government
> > Bethesda MD
> >
> > http://govt.us.oracle.com/ -- downloadable utilities
> >
> > ----------------------------------------------------------------------------
> > Opinions are mine and do not necessarily reflect those of Oracle Corporation
> >
> > Anti-Anti Spam Msg: if you want an answer emailed to you,
> > you have to make it easy to get email to you. Any bounced
> > email will be treated the same way i treat SPAM-- I delete it.
Received on Thu Jan 15 1998 - 00:00:00 CST