Re: 'insufficient privileges' from DBMS

From: <fanda_at_bigfoot.com>
Date: Thu, 15 Apr 1999 14:54:33 GMT
Message-ID: <7f4uj4$j9j$1@nnrp1.dejanews.com>

> >
> > Procedure created.
> >
> > SQL> execute beng0008;
> > CREATE VIEW sec_psf_effective_view AS SELECT * FROM sec_psf WHERE psf_part
<>
> > psf_assy
> > begin beng0008; end;
> >
> > 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 "CENTRA3.BENG0008", line 14
> > ORA-06512: at line 1
> >
> > Is this an Oracle configuration problem? How do I get over it?
>
> You need execute privilege on DBMS_SQL. Talk to your DBA.
>

I got the same problem, and execute priv. on DBMS_SQL doesn't help. The error stack indicates that the problem is in the DBMS_SYS_SQL object, and line 239 is the same place as in my appl. I have to execute all my create statements as sys when I'm using dbms_sql inside a procedure.
Other ddl-statements like 'drop view' or 'drop table' works allright!

BUT, the statements parses without errors when it's outside a procedure. For instance, this works fine:
sqlplus> exec dbms_sql.parse(dbms_sql.open_cursor,'Create sequence tmpseq', dbms_sql.v7);

but this one fails:

sqlplus> CREATE OR REPLACE PROCEDURE tmp
sqlplus> BEGIN
sqlplus>   dbms_sql.parse(dbms_sql.open_cursor,'Create sequence tmpseq',

dbms_sql.v7);
sqlplus> END;
sqlplus> /

sqlplus> exec tmp;

Annoying ...

fanda

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 09:54:33 CDT

Re: 'insufficient privileges' from DBMS_SQL.PARSE call