Situation:

I'm on a 12g database

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I'm creating an XML from a master/detail relationship. I need to calculate a reproducable ID from the detail elements. The list of detail elements can become longer that 4000 chars so I use XMLAGG rather than LISTAGG.

But there also can be no details at all (which is the problematic situation for now).

For the same reason I decided for a sha1 from  DBMS_CRYPTO.hash().

here is a ssce:

CREATE OR REPLACE VIEW master_test
AS
      SELECT LEVEL AS master_id
        FROM DUAL
  CONNECT BY LEVEL < 5;
CREATE OR REPLACE VIEW detail_test
AS
  SELECT 10 AS master_id
       , 'test' AS detail_value
    FROM DUAL
   WHERE 1 = 2;
  SELECT master_id
       , DBMS_CRYPTO.hash( XMLAGG( XMLELEMENT( e
                                             ,    TO_CHAR( master_id )
                                               || detail_value
                                             , ''
                                              ).EXTRACT( '//text()' )
                                   ORDER BY detail_value
                                  ).getclobval( )
                         , 3
                          ) detail_hash
    FROM master_test LEFT JOIN detail_test USING( master_id )
GROUP BY master_id;
CREATE OR REPLACE PACKAGE dbms_crypto_test AS
  FUNCTION my_test
    RETURN VARCHAR2;
END dbms_crypto_test;
/
CREATE OR REPLACE PACKAGE BODY dbms_crypto_test AS
  FUNCTION my_test
    RETURN VARCHAR2 AS
    CURSOR crypto_hash_test_c IS
        SELECT master_id
             , DBMS_CRYPTO.hash( XMLAGG( XMLELEMENT( e
                                                   ,    TO_CHAR( master_id )
                                                     || detail_value
                                                   , ''
                                                    ).EXTRACT( '//text()' )
                                         ORDER BY detail_value
                                        ).getclobval( )
                               , 3
                                ) detail_hash
          FROM master_test LEFT JOIN detail_test USING( master_id )
      GROUP BY master_id;
    TYPE crypto_hash_test_t IS TABLE OF crypto_hash_test_c%ROWTYPE;
    crypto_hash_test  crypto_hash_test_t;
    output            VARCHAR2( 4000 );
  BEGIN
    OPEN crypto_hash_test_c;
    FETCH crypto_hash_test_c
      BULK COLLECT INTO crypto_hash_test;
    CLOSE crypto_hash_test_c;
    FOR my_row IN crypto_hash_test.FIRST .. crypto_hash_test.LAST LOOP
      output  :=
           output
        || 'master-'
        || crypto_hash_test( my_row ).master_id
        || ' detailHash='
        || crypto_hash_test( my_row ).detail_hash
        || CHR( 13 );
    END LOOP;
    RETURN output;
  END;
END dbms_crypto_test;
/
SELECT dbms_crypto_test.my_test
  FROM DUAL;

Problem:

while this SSCE works in my real application I get the same hash for each row but only in the PL/SQL package:

direct select:

  Master_01    2100010001    BD165A66337DA315DAF171BB84F8DD77CBC1A2E6
  Master_02    12300010001    14802186C09ACEA11BC97DFF5AA6752AC6716F20
  Master_03    50400010001    71857DCF3633AB4A34D429914245A2961478E504
  Master_04    18500010001    F6C9933D7290E55D731B6FA7BC1211B04B120934

fetch in package

Master_01 2100010001-<XmlElement  name="Master_01" structId="DA39A3EE5E6B4B0D3255BFEF95601890AFD80709">
Master_02 12300010001-<XmlElement  name="Master_02" structId="DA39A3EE5E6B4B0D3255BFEF95601890AFD80709">
Master_03 50400010001-<XmlElement  name="Master_03" structId="DA39A3EE5E6B4B0D3255BFEF95601890AFD80709">
Master_04 18500010001-<XmlElement  name="Master_04" structId="DA39A3EE5E6B4B0D3255BFEF95601890AFD80709">

how can I further analyse the strange behavior and finally resolve it?