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

See embeddec comments ...

hlh_nospam_at_excite.com 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:
>
> 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 /

Easier to debug if you format and look at it in that way.

1. Where is your exception handler?
2. What value does cid have after the call to PARSE?
3. Did OPEN_CURSOR succeed?

Regarding use of DBMS_SQL - it does not work like a call to system() in C or host uin SQL*Plus. You cannot build a command string and execute it. Look at the docs again, paying close attention to the procedure BIND_VARIABLE. I suggest as reading material for PL/SQL, the book "Oracle PL/SQL Programming" by Steven Feuerstein.

>
>
> 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.

>
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Wed Apr 14 1999 - 14:26:29 CDT