2 Replies Latest reply: Mar 5, 2013 12:23 AM by jeneesh RSS

    Unable to create function based Index

    994787
      Hi All,

      I created a function as below:

      ----------------------------------------------------------------------------------------------------------------------------

      create or replace function eqx_oklb_term_date_nvl(pin_term_date date)
      return date
      deterministic
      is
      l_sub_date date := sysdate+1;
      l_return_value date := l_sub_date;
      begin
      l_return_value := nvl(pin_term_date, l_sub_date);
      return l_return_value;
      exception
      when others
      then
      l_return_value := l_sub_date;
      return l_return_value;
      end eqx_oklb_term_date_nvl;

      ----------------------------------------------------------------------------------------------------------------------------

      Now trying to create a function based index using below code:

      ----------------------------------------------------------------------------------------------------------------------------
      create index EQX_OKC_K_LINES_B_N4 on OKC.OKC_K_LINES_B(EQX_OKLB_TERM_DATE_NVL(DATE_TERMINATED))
      logging
      tablespace EQIXDATA
      noparallel;
      ----------------------------------------------------------------------------------------------------------------------------
      Encountered Error:

      SQL Error: ORA-00904: "EQX_OKLB_TERM_DATE_NVL": invalid identifier
      00904. 00000 - "%s: invalid identifier"
      *Cause:   
      *Action:
      ------------------------------------------------------------------------------------------------------------------------------
      I can successfully query dba_objects for the function.

      Owner Object_name object_id object_type last_ddl_time status
      APPS     EQX_OKLB_TERM_DATE_NVL     11764623     FUNCTION     3/4/2013 9:44:57 PM     VALID
      ------------------------------------------------------------------------------------------------------------------------------
      I can also query the function using dual.

      select eqx_oklb_term_date_nvl(null) nvl_date from dual;

      nvl_date
      3/5/2013 9:53:59 PM
      ------------------------------------------------------------------------------------------------------------------------------
      I have given grants of the function to both schemas APPS/OKC

      grant all on eqx_oklb_term_date_nvl to okc;

      grant all on eqx_oklb_term_date_nvl to apps;
      ------------------------------------------------------------------------------------------------------------------------------
      The column date_terminated in table okc_k_lines_b is a date. I've checked that.
      ------------------------------------------------------------------------------------------------------------------------------

      Stuck here. Please help.

      Thanks in advance,
      Rahul