Re: Q: 'insufficient privileges' from DBMS_SQL.PARSE call

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 15 Apr 1999 06:15:57 GMT
Message-ID: <3716840c.1363961@192.86.155.100>

A copy of this was sent to hlh_nospam_at_excite.com (if that email address didn't require changing) On Wed, 14 Apr 1999 18:48:30 GMT, you wrote:

>I would like to create a view at runtime. I can easily do this from the
>SQL*Plus command line, e.g.,
>
>SQL> CREATE OR REPLACE VIEW sec_psf_effective_view
> 2 AS SELECT * FROM sec_psf
> 3 WHERE (psf_date_stop > TO_DATE('08/20/1998','MM/dd/yyyy')
> 4 OR psf_date_stop IS NULL) AND
> 5 (psf_date_start < TO_DATE('08/20/1998','MM/dd/yyyy')
> 6 OR psf_date_start IS NULL) AND
> 7 psf_part <> psf_assy;
>
>View created.
>
>But, when I try to build the same command line (actually, a much simpler one,
>as shown below), I get the following error message:
>

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant CREATE VIEW to <OWNER>;

>SQL> CREATE OR REPLACE PROCEDURE BENG0008 2 IS 3 cid INTEGER; 4
>effectivity DATE; 5 strdate VARCHAR2(50); 6 command VARCHAR2(512); 7
>BEGIN 8 effectivity := TO_DATE('08/20/1998','MM/DD/YYYY'); 9 strdate :=
>TO_CHAR(effectivity,'MM/dd/yyyy'); 10 command := 'CREATE VIEW
>sec_psf_effective_view AS SELECT * FROM sec_psf ' || 11 'WHERE psf_part <>
>psf_assy'; 12 DBMS_OUTPUT.PUT_LINE(command); 13 cid :=
>DBMS_SQL.OPEN_CURSOR; 14 DBMS_SQL.PARSE(cid, command, dbms_sql.v7); 15
>DBMS_SQL.CLOSE_CURSOR(cid); 16 END; 17 /
>
>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?
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 15 1999 - 01:15:57 CDT