Search code examples
oracle-databasecharacter-encoding

Dump function doesn't show character-set

I'm trying to check in which characterset DBMS_METADATA.GET_DDL() is returning the result. I tried to check it like that.

SELECT DUMP(
            CAST(
                  DBMS_METADATA.GET_DDL('FUNCTION',  'MyFUNCTION', 'SCHEME')  
                  AS VARCHAR2(4000)
                ), 
            16
           )  
  FROM dual

However it doesn't show me characterset. I'm only getting result like this:

Typ=1 Len=1739: a,2...

My NLS_CHARACTERSET returns WE8MSWIN1252. Does GET_DDL() returns also in this characterset?

Solution

  • Change the second dump argument from 16 to 1016:

    SELECT dump (CAST(DBMS_METADATA.GET_DDL('FUNCTION',  'MyFUNCTION', 'SCHEME')  AS VARCHAR2(4000)), 16)  FROM DUAL

    Typ=1 Len=126 CharacterSet=AL32UTF8: a ...

    db<>fiddle

    This is explained in the documentation:

    By default, the return value contains no character set information. To retrieve the character set name of expr, add 1000 to any of the preceding format values.

    You also asked:

    My NLS_CHARACTERSET returns WE8MSWIN1252. Does GET_DDL returns also in this characterset?

    You are casting the result of the GET_DDL call to varchar2, so dump will tell you the character set of that cast value, which will be in your NLS_CHARACTERSET. The value actually returned by GET_DDL is a CLOB, which will also be in your NLS_CHARACTERSET.