INSERT INTO cities(state_no, city_name) VALUES (1, 'Vizag');

INSERT INTO cities(state_no, city_name) VALUES (1, 'Kakinda');

INSERT INTO cities(state_no, city_name) VALUES (1, 'Tirupathi');

INSERT INTO cities(state_no, city_name) VALUES (2, 'Hyd');

INSERT INTO cities(state_no, city_name) VALUES (3, 'Chennai');
INSERT INTO cities(state_no, city_name) VALUES (3, 'Madurai');

I have the above rows in my cities table.

I have the below query

WITH t1
AS
(
SELECT /*+ materialize */ state_no,
DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(e1,city_name || ';')).extract('//text()').getclobval(),1) as col2
FROM cities
group by state_no
)
SELECT state_no, col2
FROM t1

my output is:
1 NULL
2 NULL
3 NULL

When I use the materialize hint , I m getting all values in col2 as NULL. When I removed the hint or using the inline hint I m getting the col3 values as col2 NULL.

when Materialized hint is removed.

my output is :
1 ' vizag,kakinada,tirupathi'
2 ' Hyd'
3 'Chennai, Madurai'

Why I m getting NULLS when using the materialize hint. What I need to do if I want to use materialize hint in this scenario.

Thanks in advance

SQL> create table cities (state_no int, city_name varchar2(50));

Table created.

SQL>
SQL> INSERT INTO cities(state_no, city_name) VALUES (1, 'Vizag');

1 row created.

SQL>
SQL> INSERT INTO cities(state_no, city_name) VALUES (1, 'Kakinda');

1 row created.

SQL>
SQL> INSERT INTO cities(state_no, city_name) VALUES (1, 'Tirupathi');

1 row created.

SQL>
SQL> INSERT INTO cities(state_no, city_name) VALUES (2, 'Hyd');

1 row created.

SQL>
SQL> INSERT INTO cities(state_no, city_name) VALUES (3, 'Chennai');

1 row created.

SQL> INSERT INTO cities(state_no, city_name) VALUES (3, 'Madurai');

1 row created.

SQL>
SQL> WITH t1
  2  AS
  3  (
  4  SELECT /*+ materialize */ state_no,
  5  DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(e1,city_name || ';')).extract('//text()').getclobval(),1) as col2
  6  FROM cities
  7  group by state_no
  8  )
  9  SELECT state_no, col2
 10  FROM t1;

  STATE_NO COL2
---------- --------------------------------------------------------------------------------
         1 Vizag;Tirupathi;Kakinda;
         2 Hyd;
         3 Chennai;Madurai;