An SDO_SAM.AGGREGATES_FOR_GEOMETRY Query Returns Inconsistent Results Run As Different Users (Doc ID 1351042.1)

Last updated on JUNE 28, 2012

Applies to:

Oracle Spatial - Version 11.2.0.2 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms


In 11.2.0.2.0, a SDO_SAM.AGGREGATES_FOR_GEOMETRY query gives inconsistent results when run as different users in SQL*Plus.

connect user1/user1
-- Connected.

set serveroutput on
Declare
sd_point MDSYS.SDO_GEOMETRY := SDO_GEOMETRY(2001,  
8307,SDO_POINT_TYPE(-100.321655,25.708362,NULL),NULL, NULL);
      c_geometry MDSYS.SDO_GEOMETRY;
      Z1 NUMBER (10,0);
      Z2 NUMBER (10,0);
      Z3 NUMBER (10,2);
      Z4 number (10,0);
      Z5 NUMBER (10,0);
      Z6 NUMBER (10,2);
      Z7 NUMBER (10,2);
      Z8 NUMBER (10,2);
      Z9 NUMBER (10,2);
      Z10 NUMBER (10,2);
      Z11 NUMBER (10,2);
      Z12 NUMBER (10,2);
      Z13 NUMBER (10,2);
      Z14 NUMBER (10,2);
      Z15 NUMBER (10,0);
      Z16 NUMBER (10,2);
      Z17 NUMBER (10,2);
      Z18 NUMBER (10,2);
      Z19 NUMBER (10,0);
      Z20 NUMBER (10,0);
   Begin
    c_geometry := sdo_geom.sdo_buffer(sd_point, 3, 0.005, 'unit=km,
    arc_tolerance=0.005');
    select
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY', 'sum', 'POB_2005' ,c_geometry ,null) POB_2005
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY', 'sum', 'PROY_POB_2' ,c_geometry ,null) PROY_POB_2
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'CY_POP_DENSITY' ,c_geometry ,null) CY_POP_DENSITY
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_VIV_2' ,c_geometry ,null) PROY_VIV_2
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_CY_POP_GROWTH' ,c_geometry ,null),2) PCT_CY_POP_GROWTH
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','AVG', 'CY_AVG_AGE' ,c_geometry ,null) CY_AVG_AGE
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_EDUHS_PLUS',c_geometry ,null),2) PCT_POP_EDUHS_PLUS
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'AVG_PERS_PER_HH' ,c_geometry ,null),2) AVG_PERS_PER_HH
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'TV_HOT_MOT' ,c_geometry ,null) TV_HOT_MOT
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'TV_HOSP_SA' ,c_geometry ,null) TV_HOSP_SA
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_IDPT_HOUSING' ,c_geometry ,null),2) PCT_IDPT_HOUSING
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_BEN_IMSS' ,c_geometry ,null),2) PCT_POP_BEN_IMSS
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_BEN_ISSSTE',c_geometry ,null),2) PCT_POP_BEN_ISSSTE
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_PVT_INST' ,c_geometry ,null),2) PCT_POP_PVT_INST
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_AB' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) AB
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_CPLS' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) Cpls
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_C' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) C
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_DPLS' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) Dpls
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_D' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) D
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_E' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) E
       into Z1,Z2, Z3,Z4,Z5,Z6,Z7,Z8,Z9,Z10,Z11,Z12,Z13,Z14,Z15,Z16,Z17,Z18,Z19,Z20
     from dual;
 dbms_output.put_line(Z1||';'||Z2||';'||Z3||';'||Z4||';'||Z5||';'||Z6||';'||Z7||';'||Z8||';'||Z9||';'||Z10||';'||Z11||';'||Z12||';'||Z13||';'||Z14||';'||Z15||';'||Z16||';'||Z17||';'||Z18||';'||Z19||';'||Z20);
   End;
 /

145701;144752;64070241.39;43686;0;32.35;.18;2.36;1;0;60.72;42.46;2.37;2.68;1;12.2;15.05;45.41;22;4

--PL/SQL procedure successfully completed.

connect user2/user2
--Connected.
set serveroutput on
Declare
sd_point MDSYS.SDO_GEOMETRY := SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE(-100.321655,25.708362,NULL),NULL, NULL);
      c_geometry MDSYS.SDO_GEOMETRY;
      Z1 NUMBER (10,0);
      Z2 NUMBER( 10,0);
      Z3 NUMBER (10,2);
      Z4 number (10,0);
      Z5 NUMBER (10,0);
      Z6 NUMBER (10,2);
     Z7 NUMBER (10,2);
     Z8 NUMBER (10,2);
     Z9 NUMBER (10,2);
     Z10 NUMBER (10,2);
     Z11 NUMBER (10,2);
     Z12 NUMBER (10,2);
     Z13 NUMBER (10,2);
     Z14 NUMBER (10,2);
     Z15 NUMBER (10,0);
     Z16 NUMBER (10,2);
     Z17 NUMBER (10,2);
     Z18 NUMBER (10,2);
     Z19 NUMBER (10,0);
     Z20 NUMBER (10,0);
   Begin
     c_geometry := sdo_geom.sdo_buffer(sd_point, 3, 0.005, 'unit=km, arc_tolerance=0.005');
     Select
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY', 'sum', 'POB_2005' ,c_geometry ,null) POB_2005
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY', 'sum', 'PROY_POB_2' ,c_geometry ,null) PROY_POB_2
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'CY_POP_DENSITY' ,c_geometry ,null) CY_POP_DENSITY
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_VIV_2' ,c_geometry ,null) PROY_VIV_2
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_CY_POP_GROWTH' ,c_geometry ,null),2) PCT_CY_POP_GROWTH
       ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','AVG', 'CY_AVG_AGE' ,c_geometry ,null) CY_AVG_AGE
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_EDUHS_PLUS',c_geometry ,null),2) PCT_POP_EDUHS_PLUS
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'AVG_PERS_PER_HH' ,c_geometry ,null),2) AVG_PERS_PER_HH
        ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'TV_HOT_MOT' ,c_geometry ,null) TV_HOT_MOT
      ,sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'TV_HOSP_SA' ,c_geometry ,null) TV_HOSP_SA
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_IDPT_HOUSING' ,c_geometry ,null),2) PCT_IDPT_HOUSING
      ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_BEN_IMSS' ,c_geometry ,null),2) PCT_POP_BEN_IMSS
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_BEN_ISSSTE',c_geometry ,null),2) PCT_POP_BEN_ISSSTE
       ,Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','AVG', 'PCT_POP_PVT_INST' ,c_geometry ,null),2) PCT_POP_PVT_INST
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_AB' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) AB
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_CPLS' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) Cpls
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_C' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) C
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_DPLS' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) Dpls
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_D' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) D
       ,(Round(sdo_sam.aggregates_for_geometry('TMC_TEMP_BG', 'GEOMETRY','sum', 'SES_E' ,c_geometry ,null),0)*100)/
       sdo_sam.aggregates_for_geometry ('TMC_TEMP_BG', 'GEOMETRY','sum', 'PROY_POB_2' ,c_geometry ,null) E
       into Z1,Z2, Z3,Z4,Z5,Z6,Z7,Z8,Z9,Z10,Z11,Z12,Z13,Z14,Z15,Z16,Z17,Z18,Z19,Z20
     from dual;
 dbms_output.put_line(Z1||';'||Z2||';'||Z3||';'||Z4||';'||Z5||';'||Z6||';'||Z7||';'||Z8||';'||Z9||';'||Z10||';'||Z11||';'||Z12||';'||Z13||';'||Z14||';'||Z15||';'||Z16||';'||Z17||';'||Z18||';'||Z19||';'||Z20);
 End;
 /
1881;1881;1881.16;1881;1;.96;.96;.96;1881.16;1881.16;.96;.96;.96;.96;100;99.99;99.99;99.99;100;100

-- PL/SQL procedure successfully completed.


Note: This problem does not occur in 11.1.0.7.0.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms