Oracle - PL/SQL Developer shows column name in a PIVOT query-CodePudding

In PLSQL Developer, a pivot query is OK when column name is 9 characters long but garbled when 10 characters long.

Character set for the database and environment variable is AMERICAN_AMERICA.AL32UTF8. PLSQL Developer version is 14.0.5.1986(64bit) and Oracle version is 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production. Is it because of exceeding column name length limit for oracle ? Why there's no exception or error popped up ?

CodePudding user response：

The limit is 30 bytes until the version 12.2 when it was changed to 128. https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9522337900346184952
Additionaly, chinese characters are multibyte characters. Try to give shorter alias names to the pivoted columns to avoid the problem. You can detect multibyte characters using something like this:


WHERE Length(column) < LengthB(column)

Regards,

CodePudding user response：

You can give the columns an alias:

SELECT *
FROM   (SELECT 'your string' AS name FROM DUAL)
PIVOT (
  COUNT(1)
  FOR name IN (
    'your string' AS alias1,
    'other string' AS alias2
  )
)

or, if you want to use a non-ASCII alias then use quoted identifiers:

SELECT *
FROM   (SELECT 'your string' AS name FROM DUAL)
PIVOT (
  COUNT(1)
  FOR name IN (
    'your string' AS "non-ASCII alias1",
    'other string' AS "non-ASCII alias2"
  )
)