can't create function-based index

From: Paul G. Young <pgy_at_magma.ca>
Date: Fri, 22 Sep 2000 23:01:06 GMT
Message-ID: <SwRy5.6572$gi1.128403@news.magma.ca>

Problem:

    I am trying to create a function-based index but receive a permissions     error (ORA-1031).
    Suggestions greatly appreciated.

Environment:

    HPUX 11.0

    compatible                                8.1.5
    query_rewrite_enabled                     TRUE
    query_rewrite_integrity                   enforced

This is the table description for FARM_OPERATOR, the key is on STMPNO, OPERATOR.

    STMPNO                                    NOT NULL NUMBER(8)
    OPERATOR                                  NOT NULL NUMBER(1)
    FAMNAM                                    NOT NULL VARCHAR2(31)
    POSTCOD                                            VARCHAR2(6)

This is what I want to run. It works, but slowly. So I want to index it.

    SELECT TELENO FROM FARM_OPERATOR WHERE TO_RESPROV(POSTCOD) = 10; SQL script:

    CREATE OR REPLACE FUNCTION TO_RESPROV(ipostcod IN VARCHAR2) RETURN NUMBER
    DETERMINISTIC
    IS

       oresprov NUMBER := 0;
       c        CHAR(1);

    BEGIN

       c := SUBSTR(ipostcod,1,1);
       IF (c IS NOT NULL) THEN
              IF (c = 'A')  THEN oresprov := 10;
           ELSIF (c = 'C')  THEN oresprov := 11;
           ELSIF (c = 'B')  THEN oresprov := 12;
           ELSIF (c = 'E')  THEN oresprov := 13;
           ELSIF (c = 'G' OR c = 'H' OR c = 'J') THEN oresprov := 24;
           ELSIF (c = 'K' OR c = 'L' OR c = 'M' OR c = 'N' OR c = 'P') THEN
oresprov := 35;
           ELSIF (c = 'R') THEN oresprov := 46;
           ELSIF (c = 'S') THEN oresprov := 47;
           ELSIF (c = 'T') THEN oresprov := 48;
           ELSIF (c = 'V') THEN oresprov := 59;
           ELSIF (c = 'X') THEN oresprov := 60;
           ELSIF (c = 'Y') THEN oresprov := 61;
           ELSIF (c = 'Z') THEN oresprov := 62;
           END IF;
       END IF;

       RETURN (oresprov);

    END;
    /

    GRANT EXECUTE ON TO_RESPROV TO PUBLIC;     CREATE INDEX IX_RESPROV ON FARM_OPERATOR (to_resprov(postcod));

Results:

    Function created.

    Grant succeeded.

    CREATE INDEX IX_RESPROV ON FARM_OPERATOR (to_resprov(postcod))

                                                         *

    ERROR at line 1:
    ORA-01031: insufficient privileges

--
PGY (Paul G. Young)
http://www.magma.ca/~pgy

Received on Fri Sep 22 2000 - 18:01:06 CDT