Home » SQL & PL/SQL » SQL & PL/SQL » Materialize hint strange behavior (?) (merged 7)
Materialize hint strange behavior (?) (merged 7) [message #387748] Fri, 20 February 2009 08:14 Go to next message
Yann_
Messages: 1
Registered: February 2009
Junior Member
Hello everybody,

Adding the /*+ Materialize */ hint into a subselect seems to have annoying border effect. The following query :

WITH mq AS ( 
     SELECT /*+ MATERIALIZE */ 'USD' FROM DUAL
)
SELECT *
FROM mq


returns the following string 'USD ' (USD + 6 trailing spaces).

Inserting the result of this query in a table alters the column format. This script :
CREATE TABLE IS_A_BUG (
       TEST_VAL VARCHAR2(3)
)

INSERT INTO IS_A_BUG 
WITH mq AS ( 
     SELECT /*+ MATERIALIZE */ 'USD' FROM DUAL
)
SELECT *
FROM mq

COMMIT

SELECT DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLS
WHERE TABLE_NAME = 'IS_A_BUG'


Return... VARCHAR2(9) !

Of course either removing the Materialize hint or using a trim() in the subselect solves the problem.

But... Is this a bug ? ? I was unable to find any informations on this problem (using Oradoc / google / some forums) : is there anything else I should know about this hint ?
Re: Materialize hint strange behavior (?) [message #387753 is a reply to message #387748] Fri, 20 February 2009 08:23 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Almost certainly multibyte character set conversion issues. Make sure your db and client are set to the same nls_character_set.
Re: Materialize hint strange behavior (?) (merged 6) [message #387756 is a reply to message #387748] Fri, 20 February 2009 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

STOP! Posting 6 times the same question is REALLY too much.
If you have any problem, FIRST check if your topic is in Forum and only then repost if it is not.

Regards
Michel
Re: Materialize hint strange behavior (?) [message #387761 is a reply to message #387748] Fri, 20 February 2009 08:44 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The bug is in your behaviour.
STOP SPAMMING WITH THE SAME QUESTION.

Regards
Michel
Previous Topic: Time difference between two task
Next Topic: How to update based on dependency? (merged 3)
Goto Forum:
  


Current Time: Tue Dec 06 12:28:04 CST 2016

Total time taken to generate the page: 0.15456 seconds