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