Re: CREATE TABLE in stored procedure

Hi David,

David Lane schrieb:
>
> In article <366E5834.AF86A84C_at_solution42.de>, Heins.Sven_at_solution42.de
> (Sven Heins) wrote:
>
> > Hi,
> >
> > I'd like to create a table in a package or stored procedure with dynamic
> > SQL using DBMS_SQL package.
> >
> > Problem:
> > I have rights to create a table and I can use the coding in an anonymous
> > PL/SQL block (with the same USER connected to the database) but I can't
> > execute a procedure with this coding successfully.
> > The owner of the procedure is the same USER and the execution of the
> > procedure is made with this USER but I have still right problems!!!
> >
> > What is wrong???
> >
> >
> > Here's the coding:
> >
> > ...
> > nCursor := DBMS_SQL.OPEN_CURSOR;
> > cStatement := 'create table s_dummy (ID number NOT NULL, NAME
> > VARCHAR2(50) ) ' ||
> > 'tablespace TBSPACE_TEST (initial 1 M next 100 K )';
> > DBMS_SQL.PARSE(nCursor , cStatement, dbms_sql.native);
> > DBMS_SQL.CLOSE_CURSOR(nCursor);
> > ...
> >
> > Thanks for any help!
> >
> >
> > Sven Heins
> >
> > solution42 IT systems & consulting Gmbh & Co KG
> > Ernst-Abbe-Str. 10
> > 25451 Quickborn / Germany
> >
> > Tel: +49 4106 768165
> > Fax: +49 4106 768199
> > Email: Heins.Sven_at_solution42.de
> > WWW: www.solution42.de
>
> What error message do you get?
>
> Dave Lane (dlane_at_pt.lu)

I got the answer: the right CREATE TABLE was granted to the role but has to be granted to the user (see the posting of Steffen Stellwag).

Besides, the error message was: ORA-01031 insufficient rights

But thanks for your help.

Sven Received on Wed Dec 09 1998 - 09:13:09 CST