Search code examples
sqloracle-databaseplsqlplsqldeveloper

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

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

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 ?

Solution

  • 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"
  )
)