PL/SQL (MOSC)

MOSC Banner

index creation error

in PL/SQL (MOSC) 3 commentsAnswered

I am trying to create index on table. index contains function regexp_replace. getting below error.

ORA-01743: only pure functions can be indexed01743. 00000 - "only pure functions can be indexed"*Cause: The indexed function uses SYSDATE or the user environment.*Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQLexpressions must not use SYSDATE, USER, USERENV(), or anythingelse dependent on the session state. NLS-dependent functionsare OK.

I have checked the oracle forums. its telling below:

Bug 20804063 - ORA-1499 as REGEXP_REPLACE is allowed to be used in Function-based indexes (FBI)

Is there any workaround for this?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center