Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: View creation!!Please help!

Re: View creation!!Please help!

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: 30 Mar 2000 07:02:55 GMT
Message-ID: <01bf9a15$bdaf3470$2c289a0a@apollo>


Deepa Kamat <dkamat_at_questone.com> skrev i artiklen <KeBE4.14723$QJ3.2268921_at_dfiatx1-snr1.gtei.net>...
> Hi all,
> I tried using the DBMS_SQL package to execute the query .But, a new
> problem has arisen. After creating the procedure, when I try to execute
it,
> it gives me the following error.ORA-01031: insufficient privileges
> Can anyone please tell me which priveleges are to be set ??
> Thanking u in anticipation.
>
>
>
> CREATE OR REPLACE PROCEDURE try AS
> 2 cursor1 INTEGER;
> 3 str varchar(200);
> 4 BEGIN
> 5 str := 'create view pd_view as select pd_id, pd_parent,
pd_hierarchy,
> pd_id_val,pd_parent_val
> from product';
> 6 cursor1 := dbms_sql.open_cursor;
> 7 dbms_sql.parse(cursor1,str, dbms_sql.v7);
> 8 dbms_sql.close_cursor(cursor1);
> 9 end;
> 10 /
>
> Procedure created.
>
> execute try;
> BEGIN try; END;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "SYSTEM.TRY", line 7
> ORA-06512: at line 1
>

Hi Deepa

You must grant the owner of the procedure the rights needed, and you must grant them directly, as roles are not in effect during procedure execution. I still not sure what version you are on? In 8.0.5 procedures always runs with the rights of the owner not the user that executes it! In 8.1 you can set it to what you want. Ohh <dbms_sql.v7> I guess its 7.3 then. Thomas Kyte has has a website with more info. I cant recall the url, but he post often in this group - its in his signature.

/Peter Laursen Received on Thu Mar 30 2000 - 01:02:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US