Q: 'insufficient privileges' from DBMS_SQL.PARSE call

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:

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 Received on Wed Apr 14 1999 - 13:48:00 CDT