Re: ORA-01031: Insufficient Priviledges when creating index

"André Hartmann" <andrehartmann_at_hotmail.com> a écrit dans le message de news:40220283$1_at_olaf.komtel.net...
> Hi,
>
> I am executing a SQL script having the following statements:
>
> drop table test;
> create table test (column1 varchar2(100) not null, column2 varchar2(100) not
> null);
> create index indx1 on test (column1);
> create or replace function sample(s varchar2) return varchar2 deterministic
> as begin return s; end;
> /
> show error;
> describe sample;
> select sample('abc') from dual;
> create index indx2 on test (sample(column2));
>
> And when it comes to the create index statement I get:
>
> SAMPLE('ABC')
> ----------------------------------------------------------------------------
> ----
> abc
>
> create index indx2 on test (sample(column2))
> *
> ERROR in line 1:
> ORA-01031: Insufficient Priviledges
>
> with the * placed under the letter "c" of "column2"... that just doesnt
> come out in the line above because if the font I am using in this message.
> Why ? As you can see Indx1 has been created okay, just when I use the
> function in Indx2 it complains. The function is okay though because I have
> this testing SELECT statement and as you can see it works fine.
>
> Here is the complete output from SQL*Plus... sorry its in german but I am
> sure you can cope.
>
> SQL> @e:\tmp\sample
>
> Tabelle wurde gelöscht.
>
>
> Tabelle wurde angelegt.
>
>
> Index wurde angelegt.
>
>
> Funktion wurde erstellt.
>
> Keine Fehler.
> FUNCTION sample RETURNS VARCHAR2
> Argument Name Typ In/Out Defaultwert?
> ------------------------------ ----------------------- ------ --------
> S VARCHAR2 IN
>
>
> SAMPLE('ABC')
> ----------------------------------------------------------------------------
> ----
> abc
>
> create index indx2 on test (sample(column2))
> *
> FEHLER in Zeile 1:
> ORA-01031: Unzureichende Berechtigungen
>
>
> SQL>
>
>

<quote from SQL Reference, section CREATE INDEX> To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. </quote>

Regards
Michel Cadot Received on Thu Feb 05 2004 - 03:55:37 CST