Thanks for the question, Ravi .
Asked: April 14, 2021 - 4:28 pm UTC
Last updated: April 27, 2021 - 6:35 am UTC
Version: 12c
Viewed 1000+ times
You Asked
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
and Connor said...
Can we get a full test case please.
- create
- insert(s)
- then the query above
=======
I suspect you've hit on a bug because I can't reproduce that in a more recent version
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;
Speak to Support - patches may be available
Is this answer out of date? If it is, please let us know via a Comment