APEX

Hi,

I have an application I wrote with much data in it. The trouble is in the Asset window. Just showed up one day was working for a very long time.

I look at the query and do not fully understand the query that Apex generated. It works in our DEV environment but not in our production environment. DEV is 19c, Prod is 12c But also fails in the 19c version of Prod not yet released.

Please let me know if I have not provided any thing you need.  I do not think you will need the application because I have identified the error statement.  Below is a picture of the error.  A copy of the table and the LOV and the error statement.

Thank you for any assistance you can offer me.

Neil Chakrabarty

Neil.a.m.chakrabarty@wv.gov

The error comes from this Query that Apex generates.

The query references tables and a Static LOV.

Here is the LOV.

Here is the table:

CREATE TABLE "ASSETS"
( "ASSET_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1596 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"CLASSIFICATION_LEVEL" VARCHAR2(50),
"ASSET_NAME" VARCHAR2(255),
"NUMBER_OF_FILES_RECORDS" NUMBER,
"NUMBER_FILE_NOTES" VARCHAR2(4000),
"DEPARTMENT_OFFICE" VARCHAR2(50),
"DATA_OWNER" VARCHAR2(4000),
"PHYSICAL_LOCATION" VARCHAR2(50),
"LAST_DATE_CLASSIFIED" DATE DEFAULT sysdate,
"MONTHS_TO_RECLASSIFY" NUMBER DEFAULT 12,
"DATE_TO_RECLASSIFY" DATE DEFAULT sysdate+12,
"LABELED" VARCHAR2(50),
"EMPLOYEE" VARCHAR2(50),
"SYSTEM_DESCIPTION" VARCHAR2(4000),
"TECHNICAL_CONTACT" VARCHAR2(100),
"ASSET_RELATED_ID" NUMBER,
"BUISNESS_OWNER" VARCHAR2(100),
"MS_PRODUCT_IN_USE" VARCHAR2(50),
"LAST_UPDATE_USER_ID" VARCHAR2(30),
"ASSET_GROUP_CODE" VARCHAR2(40),
"LAST_UPDATE_USERID" VARCHAR2(100),
"DR_PRIORITY" NUMBER
)
/

ALTER TABLE "ASSETS" ADD CONSTRAINT "SYS_C00257988" PRIMARY KEY ("ASSET_ID")
USING INDEX ENABLE
/
ALTER TABLE "ASSETS" ADD CONSTRAINT "DEPARTMENT" FOREIGN KEY ("DEPARTMENT_OFFICE")
REFERENCES "DEPARTMENT_OFFICE" ("DEPARTMENT_OFFICE") DISABLE
/
ALTER TABLE "ASSETS" ADD CONSTRAINT "RELATED_ASSET" FOREIGN KEY ("ASSET_RELATED_ID")
REFERENCES "ASSETS" ("ASSET_ID") DISABLE
/
ALTER TABLE "ASSETS" ADD CONSTRAINT "ASSETS_CON" FOREIGN KEY ("CLASSIFICATION_LEVEL")
REFERENCES "CLASSIFICATION_LEVEL" ("CLASSIFICATION_CODE") DISABLE
/
ALTER TABLE "ASSETS" ADD CONSTRAINT "ASSET_GROUP_LIST" FOREIGN KEY ("ASSET_GROUP_CODE")
REFERENCES "ASSET_GROUP_CODE" ("ASSET_GROUP_CODE") DISABLE
/
ALTER TABLE "ASSETS" ADD CONSTRAINT "MS_PRODUCT" FOREIGN KEY ("MS_PRODUCT_IN_USE")
REFERENCES "MS_PRODUCT" ("MS_PRODUCT_IN_USE") DISABLE
/

CREATE INDEX "IX_RELATIONSHIP3" ON "ASSETS" ("MS_PRODUCT_IN_USE")
/

CREATE INDEX "IX_RELATIONSHIP6" ON "ASSETS" ("ASSET_RELATED_ID")
/

Here are some insert statements.

In the attached file.

select
*
from
(
select
grouping_id(
"CLASSIFICATION_LEVEL", "DEPARTMENT_OFFICE",
"PHYSICAL_LOCATION", "ASSET_GROUP_CODE",
"APX$LOVR16"
) "APX$GRPID",
"CLASSIFICATION_LEVEL" "APX$FLTV2",
count(*) "APX$FLTC2",
"DEPARTMENT_OFFICE" "APX$FLTV3",
count(*) "APX$FLTC3",
"PHYSICAL_LOCATION" "APX$FLTV4",
count(*) "APX$FLTC4",
"ASSET_GROUP_CODE" "APX$FLTV5",
count(*) "APX$FLTC5",
"APX$LOVR16" "APX$FLTV6",
count(*) "APX$FLTC6",
count(*) APX$ALLC,
row_number() over(
partition by grouping_id(
"CLASSIFICATION_LEVEL", "DEPARTMENT_OFFICE",
"PHYSICAL_LOCATION", "ASSET_GROUP_CODE",
"APX$LOVR16"
)
order by
(null)
) "APX$ITSEQ"
from
(
(
select
i.*
from
(
select
"CLASSIFICATION_LEVEL",
"ASSET_NAME",
"NUMBER_FILE_NOTES",
"DEPARTMENT_OFFICE",
"DATA_OWNER",
"PHYSICAL_LOCATION",
"LABELED",
"EMPLOYEE",
"SYSTEM_DESCIPTION",
"TECHNICAL_CONTACT",
"BUISNESS_OWNER",
"MS_PRODUCT_IN_USE",
"LAST_UPDATE_USER_ID",
"ASSET_GROUP_CODE",
"LAST_UPDATE_USERID",
"DR_PRIORITY",
"APX$LOVR16"
from
(
select

               /\*+ qb\_name(apex$inner) \*/  
               d."CLASSIFICATION\_LEVEL",   
               d."ASSET\_NAME",   
               d."NUMBER\_FILE\_NOTES",   
               d."DEPARTMENT\_OFFICE",   
               d."DATA\_OWNER",   
               d."PHYSICAL\_LOCATION",   
               d."LABELED",   
               d."EMPLOYEE",   
               d."SYSTEM\_DESCIPTION",   
               d."TECHNICAL\_CONTACT",   
               d."BUISNESS\_OWNER",   
               d."MS\_PRODUCT\_IN\_USE",   
               d."LAST\_UPDATE\_USER\_ID",   
               d."ASSET\_GROUP\_CODE",   
               d."LAST\_UPDATE\_USERID",   
               nvl(  
                 "APX$LOV16"."DISP", d."DR\_PRIORITY"  
               ) "DR\_PRIORITY",   
               "APX$LOV16"."DISP" "APX$LOV16\_DISPLAY",   
               "APX$LOV16"."VAL" "APX$LOVR16"   
             from   
               (  
                 select   
                   x.\*   
                 from   
                   "ASSETS" x  
               ) d,   
               (  
                 select   
                     
                   /\*+ cardinality(t 10) no\_result\_cache \*/  
                   disp,   
                   val   
                 from   
                   table(  
                     wwv\_flow\_lov\_api.get\_static\_lov\_data(2)  
                   ) t  
               ) "APX$LOV16"   
             where   
               "APX$LOV16"."VAL"(+) = d."DR\_PRIORITY"  
           ) i  
       ) i   
     where   
       1 = 1  
   )  
 ) APX$DSRC   

group by
grouping sets (
"CLASSIFICATION_LEVEL",
"DEPARTMENT_OFFICE",
"PHYSICAL_LOCATION",
"ASSET_GROUP_CODE",
"APX$LOVR16",
()
)
) d
where
"APX$ITSEQ" <= 5001

AssetData.sql