Enter Value to Dynamic List Attribute Field Result in Running SQL Everytime For Each Character (Doc ID 2556790.1)

Last updated on DECEMBER 04, 2019

Applies to:

Goal

ACTUAL BEHAVIOR
SQL is being running everytime a word is entered to Dynamic List Attribute field.

Example :

a) When entering "D" to dynamic list field , it runs below SQL

select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO,ITEM_P2P3_QUERY.COMPLIANCY,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where ((ITEM_P2P3_QUERY.ENCODE_NAME LIKE 'D%' ESCAPE '\')) AND ITEM_P2P3_QUERY.SUBCLASS = 9141 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 21"

b) After a), next when entering "D0" to dynamic list field, it runs below SQL

select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO,ITEM_P2P3_QUERY.COMPLIANCY,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where ((ITEM_P2P3_QUERY.ENCODE_NAME LIKE 'D0%' ESCAPE '\')) AND ITEM_P2P3_QUERY.SUBCLASS = 9141 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 21


FACT
This behavior occurs when Dynamic List is set with Display Type : Search.

STEP

1. Set Criteria

Java Client
Settings > Data Settings > Criteria

Name : testAllDocument
Object Type : Document

Criteria : Document

2. Set List

Java Client
Settings > Data Settings > Lists

Name : testAllDocumentList
List Type : Dynamic
Display Type : Search

Criteria : testAllDocument

3. Set Page Three Attribute

Admin > Settings > Data Settings > Classes > Document > User Interface tabs > Page Three > Atrributes:Page Three > MultiList01

Name : MultiList01
List : testAllDocumentList
Attribute : PAGE_THREE.MULTILIST31

4. Login to Web Client and enable SQL debug
5. Open a Document Object : D00001
6. Edit
7. Enter D to MultiList01 field
8. check SQL debug log

<2019-XX-XX XX:XX:XX,XXX> <SqlLogger:DEBUG> executeQuery (Elapsed Time = 15 ms): "select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO,ITEM_P2P3_QUERY.COMPLIANCY,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where ((ITEM_P2P3_QUERY.ENCODE_NAME LIKE 'D%' ESCAPE '\')) AND ITEM_P2P3_QUERY.SUBCLASS = 9141 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 21"

9. Enter D0 to MultiList01 field

10. check SQL debug log

<2019-XX-XX XX:XX:XX,XXX> <SqlLogger:DEBUG> executeQuery (Elapsed Time = 15 ms): "select /*+ ALL_ROWS */ ITEM_P2P3_QUERY.ID,ITEM_P2P3_QUERY.CLASS,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.FLAGS,ITEM_P2P3_QUERY.REV_FLAGS,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.ITEM_NUMBER,ITEM_P2P3_QUERY.DESCRIPTION,ITEM_P2P3_QUERY.DESC_REV,ITEM_P2P3_QUERY.RELEASE_TYPE,ITEM_P2P3_QUERY.REV_NUMBER,ITEM_P2P3_QUERY.LATEST_RELEASED_ECO,ITEM_P2P3_QUERY.COMPLIANCY,ITEM_P2P3_QUERY.SUBCLASS,ITEM_P2P3_QUERY.CREATE_USER,ITEM_P2P3_QUERY.CREATE_USER,NULL,NULL,ITEM_P2P3_QUERY.ITEM_NUMBER from ITEM_P2P3_QUERY where ((ITEM_P2P3_QUERY.ENCODE_NAME LIKE 'D0%' ESCAPE '\')) AND ITEM_P2P3_QUERY.SUBCLASS = 9141 AND (NVL(ITEM_P2P3_QUERY.DELETE_FLAG,0) = 0) ORDER BY 21"

 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.