SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Why is index not used on update of an updatable view?

zsysdbaOct 23 2013 — edited Oct 27 2013

I need help understanding why Oracle 11.2.0.3 ignores an index for an updatable view. When I reference the base table, offer$, I get index access. When I reference the view, offer, I get a full scan of offer$. There is an instead of trigger on the view. Does that factor into it somehow?

> EXPLAIN PLAN FOR

UPDATE OFFER$ SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )

plan FOR succeeded.

> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))

PLAN_TABLE_OUTPUT

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

Plan hash value: 3047622554

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

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

| 0 | UPDATE STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | OFFER$ | 1 | 25 | 1 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN | AK_OFFER_ASSIGNED_OFFER_ID | 1 | | 1 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| SOLICITATION$ | 1 | 20 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_SOLICITATION$ | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - UPD$1

2 - UPD$1 / OFFER$@UPD$1

3 - UPD$1 / OFFER$@UPD$1

4 - SEL$F5BB74E1 / SOLICITATION$@SEL$2

5 - SEL$F5BB74E1 / SOLICITATION$@SEL$2

Predicate Information (identified by operation id):

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

3 - access("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM KOHP."SOLICITATION$"

"SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))

5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))

Column Projection Information (identified by operation id):

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

2 - (upd=4,6,7; cmp=3,7; cpy=2,4,5) "OFFER$".ROWID[ROWID,10], "OFFER$"."DECISION_ID"[NUMBER,22],

"ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],

"OFFER$"."CURRENT_OFFER_FLAG"[CHARACTER,1], "PRESENTABLE_FLAG"[CHARACTER,1],

"OFFER$"."CHNG_DTTM"[TIMESTAMP,11]

3 - "OFFER$".ROWID[ROWID,10], "ASSIGNED_OFFER_ID"[VARCHAR2,255]

4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],

"SOLICITATION_REF_ID"[VARCHAR2,50]

5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]

40 rows selected

> EXPLAIN PLAN FOR

UPDATE OFFER SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )

plan FOR succeeded.

> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))

PLAN_TABLE_OUTPUT

Plan hash value: 1083159671

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

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

| 0 | UPDATE STATEMENT | | 958K| 987M| 9182 (1)| 00:01:51 |

|* 2 | VIEW | OFFER | 958K| 987M| 9181 (1)| 00:01:51 |

| 3 | TABLE ACCESS FULL | OFFER$ | 958K| 127M| 9181 (1)| 00:01:51 |

| 4 | TABLE ACCESS BY INDEX ROWID| SOLICITATION$ | 1 | 20 | 1 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_SOLICITATION$ | 1 | | 1 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - UPD$1

2 - SEL$1 / OFFER@UPD$1

3 - SEL$1 / OFFER$@SEL$1

4 - SEL$335DD26A / SOLICITATION$@SEL$3

5 - SEL$335DD26A / SOLICITATION$@SEL$3

Predicate Information (identified by operation id):

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

2 - filter("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM

KOHP."SOLICITATION$" "SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))

5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))

Column Projection Information (identified by operation id):

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

2 - (upd=13,45; cmp=1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,20,21,22,23,24,25,26,2

7,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,46,47,48,49,50)

"OFFER"."OFFER_ID"[NUMBER,22], "OFFER"."DECISION_ID"[NUMBER,22],

"OFFER"."OFFER_TYPE_CD"[NUMBER,22], "OFFER"."PROMO_ID"[NUMBER,22],

"OFFER"."PYMT_METHOD_TYPE_CD"[NUMBER,22], "OFFER"."CS_RESULT_ID"[NUMBER,22],

"OFFER"."CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "OFFER"."RATE_INDEX_TYPE_CD"[NUMBER,22],

"OFFER"."SUB_PRODUCT_ID"[NUMBER,22], "OFFER"."CAMPAIGN_ID"[NUMBER,22],

"OFFER"."MARKET_CELL_ID"[NUMBER,22], "ASSIGNED_OFFER_ID"[VARCHAR2,255],

"ACCEPTED_OFFER_FLAG"[CHARACTER,1], "OFFER"."CURRENT_OFFER_FLAG"[CHARACTER,1],

"OFFER"."OFFER_GOOD_UNTIL_DATE"[DATE,7], "OFFER"."RESCINDABLE_DAYS"[NUMBER,22],

"OFFER"."RESCINDED_DATE"[DATE,7], "OFFER"."AMOUNT"[NUMBER,22],

"OFFER"."MAX_AMOUNT"[NUMBER,22], "OFFER"."AMOUNT_FINANCED"[NUMBER,22],

"OFFER"."DOWN_PYMT"[NUMBER,22], "OFFER"."RATE"[NUMBER,22], "OFFER"."TERM_MM"[NUMBER,22],

"OFFER"."ORIGINATION_FEE_AMOUNT"[NUMBER,22], "OFFER"."ORIGINATION_FEE_RATE"[NUMBER,22],

"OFFER"."FINANCE_CHARGE"[NUMBER,22], "OFFER"."NBR_OF_PYMTS"[NUMBER,22],

"OFFER"."PYMT"[NUMBER,22], "OFFER"."TOTAL_PYMTS"[NUMBER,22],

"OFFER"."FIRST_PYMT_DATE"[DATE,7], "OFFER"."CONTRACT_DATE"[DATE,7],

"OFFER"."ACCT_NBR"[VARCHAR2,20], "OFFER"."ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11],

"OFFER"."ACCT_EXPIRATION_DTTM"[DATE,8], "OFFER"."OFFER_DESC"[VARCHAR2,255],

"OFFER"."MIN_RATE"[NUMBER,22], "OFFER"."MAX_RATE"[NUMBER,22],

"OFFER"."MIN_AMOUNT"[NUMBER,22], "OFFER"."ANNUAL_FEE_AMOUNT"[NUMBER,22],

"OFFER"."ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "OFFER"."LATE_FEE_PERCENT"[NUMBER,22],

"OFFER"."LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER"."OFFER_SALES_SCRIPT"[VARCHAR2,500],

"OFFER"."OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1],

"OFFER"."INDEX_RATE"[NUMBER,22], "OFFER"."ACTV_FLAG"[VARCHAR2,1],

"OFFER"."CORRELATION_ID"[VARCHAR2,64], "OFFER"."OFFER_STATUS_TYPE_CD"[NUMBER,22],

"OFFER"."PRESENTATION_INSTRUMENT_NBR"[VARCHAR2,20]

3 - "OFFER_ID"[NUMBER,22], "DECISION_ID"[NUMBER,22], "OFFER_TYPE_CD"[NUMBER,22],

"PROMO_ID"[NUMBER,22], "PYMT_METHOD_TYPE_CD"[NUMBER,22], "CS_RESULT_ID"[NUMBER,22],

"CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "RATE_INDEX_TYPE_CD"[NUMBER,22],

"SUB_PRODUCT_ID"[NUMBER,22], "CAMPAIGN_ID"[NUMBER,22], "MARKET_CELL_ID"[NUMBER,22],

"ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],

"CURRENT_OFFER_FLAG"[CHARACTER,1], "OFFER_GOOD_UNTIL_DATE"[DATE,7],

"RESCINDABLE_DAYS"[NUMBER,22], "RESCINDED_DATE"[DATE,7], "AMOUNT"[NUMBER,22],

"MAX_AMOUNT"[NUMBER,22], "AMOUNT_FINANCED"[NUMBER,22], "DOWN_PYMT"[NUMBER,22],

"RATE"[NUMBER,22], "TERM_MM"[NUMBER,22], "ORIGINATION_FEE_AMOUNT"[NUMBER,22],

"ORIGINATION_FEE_RATE"[NUMBER,22], "FINANCE_CHARGE"[NUMBER,22],

"NBR_OF_PYMTS"[NUMBER,22], "PYMT"[NUMBER,22], "TOTAL_PYMTS"[NUMBER,22],

"FIRST_PYMT_DATE"[DATE,7], "CONTRACT_DATE"[DATE,7], "ACCT_NBR$"[RAW,40],

"ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11], "ACCT_EXPIRATION_DTTM$"[RAW,40],

"OFFER_DESC"[VARCHAR2,255], "MIN_RATE"[NUMBER,22], "MAX_RATE"[NUMBER,22],

"MIN_AMOUNT"[NUMBER,22], "ANNUAL_FEE_AMOUNT"[NUMBER,22],

"ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "LATE_FEE_PERCENT"[NUMBER,22],

"LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER_SALES_SCRIPT"[VARCHAR2,500],

"OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1], "INDEX_RATE"[NUMBER,22],

"ACTV_FLAG"[VARCHAR2,1], "CORRELATION_ID"[VARCHAR2,64],

"OFFER_STATUS_TYPE_CD"[NUMBER,22], "PRESENTATION_INSTRUMENT_NBR$"[RAW,40]

4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],

"SOLICITATION_REF_ID"[VARCHAR2,50]

5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]

82 rows selected

CREATE OR REPLACE FORCE VIEW "OFFER" ("OFFER_ID","DECISION_ID","OFFER_TYPE_CD","PROMO_ID","PYMT_METHOD_TYPE_CD","CS_RESULT_ID","CS_RESULT_USAGE_TYPE_CD","RATE_INDEX_TYPE_CD","SUB_PRODUCT_ID","CAMPAIGN_ID","MARKET_CELL_ID","ASSIGNED_OFFER_ID","ACCEPTED_OFFER_FLAG","CURRENT_OFFER_FLAG","OFFER_GOOD_UNTIL_DATE","RESCINDABLE_DAYS","RESCINDED_DATE","AMOUNT","MAX_AMOUNT","AMOUNT_FINANCED","DOWN_PYMT","RATE","TERM_MM","ORIGINATION_FEE_AMOUNT","ORIGINATION_FEE_RATE","FINANCE_CHARGE","NBR_OF_PYMTS","PYMT","TOTAL_PYMTS","FIRST_PYMT_DATE","CONTRACT_DATE","ACCT_NBR","ACCT_NBR_ASSIGNED_DTTM","ACCT_EXPIRATION_DTTM","OFFER_DESC","MIN_RATE","MAX_RATE","MIN_AMOUNT","ANNUAL_FEE_AMOUNT","ANNUAL_FEE_WAIVED_MM","LATE_FEE_PERCENT","LATE_FEE_MIN_AMOUNT","OFFER_SALES_SCRIPT","OFFER_ORDER","PRESENTABLE_FLAG","INDEX_RATE","INSRT_DTTM","INSRT_USR_ID","CHNG_DTTM","CHNG_USR_ID","ACTV_FLAG","CORRELATION_ID","OFFER_STATUS_TYPE_CD","PRESENTATION_INSTRUMENT_NBR")

SELECT

OFFER_ID

,DECISION_ID

,OFFER_TYPE_CD

,PROMO_ID

,PYMT_METHOD_TYPE_CD

,CS_RESULT_ID

,CS_RESULT_USAGE_TYPE_CD

,RATE_INDEX_TYPE_CD

,SUB_PRODUCT_ID

,CAMPAIGN_ID

,MARKET_CELL_ID

,ASSIGNED_OFFER_ID

,ACCEPTED_OFFER_FLAG

,CURRENT_OFFER_FLAG

,OFFER_GOOD_UNTIL_DATE

,RESCINDABLE_DAYS

,RESCINDED_DATE

,AMOUNT

,MAX_AMOUNT

,AMOUNT_FINANCED

,DOWN_PYMT

,RATE

,TERM_MM

,ORIGINATION_FEE_AMOUNT

,ORIGINATION_FEE_RATE

,FINANCE_CHARGE

,NBR_OF_PYMTS

,PYMT

,TOTAL_PYMTS

,FIRST_PYMT_DATE

,CONTRACT_DATE

,CAST ( zcrypto.decrypt ( ACCT_NBR$ ) AS VARCHAR2 ( 20 ) ) ACCT_NBR

,ACCT_NBR_ASSIGNED_DTTM

,TO_DATE ( zcrypto.decrypt ( ACCT_EXPIRATION_DTTM$ ) ) ACCT_EXPIRATION_DTTM

,OFFER_DESC

,MIN_RATE

,MAX_RATE

,MIN_AMOUNT

,ANNUAL_FEE_AMOUNT

,ANNUAL_FEE_WAIVED_MM

,LATE_FEE_PERCENT

,LATE_FEE_MIN_AMOUNT

,OFFER_SALES_SCRIPT

,OFFER_ORDER

,PRESENTABLE_FLAG

,INDEX_RATE

,INSRT_DTTM

,INSRT_USR_ID

,CHNG_DTTM

,CHNG_USR_ID

,ACTV_FLAG

,CORRELATION_ID

,OFFER_STATUS_TYPE_CD

,CAST ( zcrypto.decrypt ( PRESENTATION_INSTRUMENT_NBR$ ) AS VARCHAR2 ( 20 ) ) PRESENTATION_INSTRUMENT_NBR

FROM

OFFER$;

CREATE OR REPLACE TRIGGER "OFFER_IO" INSTEAD OF

INSERT OR

UPDATE

ON OFFER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ( INSERTING ) THEN

INSERT

INTO

OFFER$

(

OFFER_ID

,DECISION_ID

,OFFER_TYPE_CD

,PROMO_ID

,PYMT_METHOD_TYPE_CD

,CS_RESULT_ID

,CS_RESULT_USAGE_TYPE_CD

,RATE_INDEX_TYPE_CD

,SUB_PRODUCT_ID

,CAMPAIGN_ID

,MARKET_CELL_ID

,ASSIGNED_OFFER_ID

,ACCEPTED_OFFER_FLAG

,CURRENT_OFFER_FLAG

,OFFER_GOOD_UNTIL_DATE

,RESCINDABLE_DAYS

,RESCINDED_DATE

,AMOUNT

,MAX_AMOUNT

,AMOUNT_FINANCED

,DOWN_PYMT

,RATE

,TERM_MM

,ORIGINATION_FEE_AMOUNT

,ORIGINATION_FEE_RATE

,FINANCE_CHARGE

,NBR_OF_PYMTS

,PYMT

,TOTAL_PYMTS

,FIRST_PYMT_DATE

,CONTRACT_DATE

,ACCT_NBR$

,ACCT_NBR_ASSIGNED_DTTM

,ACCT_EXPIRATION_DTTM$

,OFFER_DESC

,MIN_RATE

,MAX_RATE

,MIN_AMOUNT

,ANNUAL_FEE_AMOUNT

,ANNUAL_FEE_WAIVED_MM

,LATE_FEE_PERCENT

,LATE_FEE_MIN_AMOUNT

,OFFER_SALES_SCRIPT

,OFFER_ORDER

,PRESENTABLE_FLAG

,INDEX_RATE

,ACTV_FLAG

,CORRELATION_ID

,OFFER_STATUS_TYPE_CD

,PRESENTATION_INSTRUMENT_NBR$

)

VALUES

(

:NEW.OFFER_ID

,:NEW.DECISION_ID

,:NEW.OFFER_TYPE_CD

,:NEW.PROMO_ID

,:NEW.PYMT_METHOD_TYPE_CD

,:NEW.CS_RESULT_ID

,:NEW.CS_RESULT_USAGE_TYPE_CD

,:NEW.RATE_INDEX_TYPE_CD

,:NEW.SUB_PRODUCT_ID

,:NEW.CAMPAIGN_ID

,:NEW.MARKET_CELL_ID

,:NEW.ASSIGNED_OFFER_ID

,:NEW.ACCEPTED_OFFER_FLAG

,:NEW.CURRENT_OFFER_FLAG

,:NEW.OFFER_GOOD_UNTIL_DATE

,:NEW.RESCINDABLE_DAYS

,:NEW.RESCINDED_DATE

,:NEW.AMOUNT

,:NEW.MAX_AMOUNT

,:NEW.AMOUNT_FINANCED

,:NEW.DOWN_PYMT

,:NEW.RATE

,:NEW.TERM_MM

,:NEW.ORIGINATION_FEE_AMOUNT

,:NEW.ORIGINATION_FEE_RATE

,:NEW.FINANCE_CHARGE

,:NEW.NBR_OF_PYMTS

,:NEW.PYMT

,:NEW.TOTAL_PYMTS

,:NEW.FIRST_PYMT_DATE

,:NEW.CONTRACT_DATE

,zcrypto.encrypt ( :NEW.ACCT_NBR )

,:NEW.ACCT_NBR_ASSIGNED_DTTM

,zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )

,:NEW.OFFER_DESC

,:NEW.MIN_RATE

,:NEW.MAX_RATE

,:NEW.MIN_AMOUNT

,:NEW.ANNUAL_FEE_AMOUNT

,:NEW.ANNUAL_FEE_WAIVED_MM

,:NEW.LATE_FEE_PERCENT

,:NEW.LATE_FEE_MIN_AMOUNT

,:NEW.OFFER_SALES_SCRIPT

,:NEW.OFFER_ORDER

,:NEW.PRESENTABLE_FLAG

,:NEW.INDEX_RATE

,:NEW.ACTV_FLAG

,:NEW.CORRELATION_ID

,:NEW.OFFER_STATUS_TYPE_CD

,zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )

);

ELSE

UPDATE

OFFER$

SET

DECISION_ID = :NEW.DECISION_ID

,OFFER_TYPE_CD = :NEW.OFFER_TYPE_CD

,PROMO_ID = :NEW.PROMO_ID

,PYMT_METHOD_TYPE_CD = :NEW.PYMT_METHOD_TYPE_CD

,CS_RESULT_ID = :NEW.CS_RESULT_ID

,CS_RESULT_USAGE_TYPE_CD = :NEW.CS_RESULT_USAGE_TYPE_CD

,RATE_INDEX_TYPE_CD = :NEW.RATE_INDEX_TYPE_CD

,SUB_PRODUCT_ID = :NEW.SUB_PRODUCT_ID

,CAMPAIGN_ID = :NEW.CAMPAIGN_ID

,MARKET_CELL_ID = :NEW.MARKET_CELL_ID

,ASSIGNED_OFFER_ID = :NEW.ASSIGNED_OFFER_ID

,ACCEPTED_OFFER_FLAG = :NEW.ACCEPTED_OFFER_FLAG

,CURRENT_OFFER_FLAG = :NEW.CURRENT_OFFER_FLAG

,OFFER_GOOD_UNTIL_DATE = :NEW.OFFER_GOOD_UNTIL_DATE

,RESCINDABLE_DAYS = :NEW.RESCINDABLE_DAYS

,RESCINDED_DATE = :NEW.RESCINDED_DATE

,AMOUNT = :NEW.AMOUNT

,MAX_AMOUNT = :NEW.MAX_AMOUNT

,AMOUNT_FINANCED = :NEW.AMOUNT_FINANCED

,DOWN_PYMT = :NEW.DOWN_PYMT

,RATE = :NEW.RATE

,TERM_MM = :NEW.TERM_MM

,ORIGINATION_FEE_AMOUNT = :NEW.ORIGINATION_FEE_AMOUNT

,ORIGINATION_FEE_RATE = :NEW.ORIGINATION_FEE_RATE

,FINANCE_CHARGE = :NEW.FINANCE_CHARGE

,NBR_OF_PYMTS = :NEW.NBR_OF_PYMTS

,PYMT = :NEW.PYMT

,TOTAL_PYMTS = :NEW.TOTAL_PYMTS

,FIRST_PYMT_DATE = :NEW.FIRST_PYMT_DATE

,CONTRACT_DATE = :NEW.CONTRACT_DATE

,ACCT_NBR$ = zcrypto.encrypt ( :NEW.ACCT_NBR )

,ACCT_NBR_ASSIGNED_DTTM = :NEW.ACCT_NBR_ASSIGNED_DTTM

,ACCT_EXPIRATION_DTTM$ = zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )

,OFFER_DESC = :NEW.OFFER_DESC

,MIN_RATE = :NEW.MIN_RATE

,MAX_RATE = :NEW.MAX_RATE

,MIN_AMOUNT = :NEW.MIN_AMOUNT

,ANNUAL_FEE_AMOUNT = :NEW.ANNUAL_FEE_AMOUNT

,ANNUAL_FEE_WAIVED_MM = :NEW.ANNUAL_FEE_WAIVED_MM

,LATE_FEE_PERCENT = :NEW.LATE_FEE_PERCENT

,LATE_FEE_MIN_AMOUNT = :NEW.LATE_FEE_MIN_AMOUNT

,OFFER_SALES_SCRIPT = :NEW.OFFER_SALES_SCRIPT

,OFFER_ORDER = :NEW.OFFER_ORDER

,PRESENTABLE_FLAG = :NEW.PRESENTABLE_FLAG

,INDEX_RATE = :NEW.INDEX_RATE

,ACTV_FLAG = :NEW.ACTV_FLAG

,CORRELATION_ID = :NEW.CORRELATION_ID

,OFFER_STATUS_TYPE_CD = :NEW.OFFER_STATUS_TYPE_CD

,PRESENTATION_INSTRUMENT_NBR$ = zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )

WHERE

OFFER_ID = :NEW.OFFER_ID;

END IF;

END;

This post has been answered by Jonathan Lewis on Oct 27 2013

Jump to Answer

Jose Aróstegui

Please find a simple test case with HR Schema here:

https://dl.dropboxusercontent.com/u/18609389/TestSequenceError.zip

Thanks!

Jose Aróstegui

Hi,

I've reproduced the same test case in Jdev 12.1.2 and everything works fine. The main difference between the EO.xml is that 12.1.3 adds trustMode:

<TransientExpression trustMode="untrusted">
               <![CDATA[(new oracle.jbo.server.SequenceImpl("DEPARTMENTS_SEQ", adf.object.getDBTransaction())).getSequenceNumber()]]>
</TransientExpression>

Which seems to be the reason of the exception. Any idea why?

Regards,

Jose.

Timo Hahn

You are right. The normal operation to set the PK with a sequence via a groovy expression does not work any longer.

And yes the reason is the added trustMode="untrusted property. If you look at the error message for the exception

JBO-25152: Calling the constructor for class {0} is not permitted.
Cause: An attempt was made within an untrusted expression to call a constructor not on the list of allowed constructors.

Action:Add the constructor to the list of allowed constructors in adf-config.xml.
Level: 1
Type: ERROR
Impact: Security

it suggests that you can add the constructor (not sure which one) to the allowed constructors. I have not found any information on this, bit I'll see what comes up when I log a bug with your test case.

Timo

Message was edited by: Timo Hahn --------------------------------------------------------------------------------------------------------- Workaround: edit the trustMode property for the attribute and set it to 'trusted'

1 person found this helpful

Timo Hahn

Added https://java.net/jira/browse/ADFEMG-241 for this

Timo

kdario

It seems that expressions are executed by default in untrusted mode.

So you should change trustMode="untrusted" to "trusted"(in xml or through Property Inspector).

If you invoke java method from expression then you can also use @AllowUntrustedScriptAccess annotation on such method.

Dario

User_HWHT9

I've bounced this issue internally to at the very least get it documented, if not more, will update the ADFEMG issue once I have an update.

Chris Muir

Oracle ADF Product Management

Jose Aróstegui

Thanks everybody for your help. This forum is great :-)

Jose Aróstegui

... I guess I deserve some points for my status, no? :-)

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 24 2013

Added on Oct 23 2013

#index

1 comment

508 views

SQL & PL/SQL

Why is index not used on update of an updatable view?

Comments

Post Details