Dump function doesn't show character-set-CodePudding

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?

CodePudding user response：

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.