Skip to Main Content
  • Questions
  • Materialize Hint not working on CLOB data in Oracle

Breadcrumb

Question and Answer

Connor McDonald

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 100+ 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 we 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

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.