Thanks

Thanks I added Create Table to the user and it worked.

Thanks.

Gary Corless, Oracle DBA 'ish

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:t6dv8ih6a6ua80_at_beta-news.demon.nl...
> You have privilege through a role and you need *direct* privileges, as
 roles
> do not work in stored procedures.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> "Gary Corless" <g_corless_at_yahoo.ie> wrote in message
> news:sKC96.2741$r17.10168_at_news.iol.ie...
> >
> > I am trying to use an stored procedure to create a table using the
 DBMS_SQL
> > package but I am getting insufficient privileges error. I can create the
> > table using the SQL statement by itself and I can delete table using the
> > procedure.
> >
> > Any Ideas.
> >
> > Thanks
> > Gary
> >
> >
> > CREATE OR REPLACE PROCEDURE RunSQL(ps_SQL IN VARCHAR2) AS
> > li_Cursor INTEGER;
> > BEGIN
> > li_Cursor := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(li_Cursor, ps_SQL, DBMS_SQL.native);
> > DBMS_SQL.CLOSE_CURSOR(li_Cursor);
> > END RunSQL;
> >
> > Statement processed.
> >
> > EXECUTE RunSQL('CREATE TABLE temp (temp_field NUMBER(1,0))');
> >
> > ORA-01031: insufficient privileges
> > ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> > ORA-06512: at "SYS.DBMS_SQL", line 32
> > ORA-06512: at "RUNSQL", line 9
> > ORA-06512: at line 2
> >
> > CREATE TABLE temp (temp_field NUMBER(1,0));
> > Statement processed.
> >
> > EXECUTE RunSQL('DROP TABLE temp');
> > Statement processed.
> >
> >
> >
>
>
Received on Fri Jan 19 2001 - 02:58:27 CST