Re: how to know the internal representation of a column of clob type

From: ddf <oratune_at_msn.com>
Date: Tue, 16 Mar 2010 08:50:52 -0700 (PDT)
Message-ID: <453371f3-1d74-4361-8a1b-7bfa5f7d532c_at_x1g2000prb.googlegroups.com>

On Mar 16, 9:30 am, Rui Zhang <jackcha..._at_gmail.com> wrote:
> Hi group,
>
> I was trying to figure out the internal representation of the clob
> type column to see if it is UTF8 encoded, but couldn't find any
> functions (like dump function provided for varchar2 type colomn) or
> packages. Here is the procedure I used, but I am not sure if it gives
> me the actual internal representation. I read the clob to a varchar2
> variable and dump it to hex code. Thank you!
>
> DECLARE
>  i1    CLOB;
>  len   NUMBER;
>  my_vr varchar2(1000);
>  my_vr2 varchar2(1000);
> BEGIN
>   SELECT action_log
>   INTO i1
>   FROM table1
>   WHERE id = '742';
>
>   len := dbms_lob.getlength(i1);
>   dbms_output.put_line('Column Length: ' || TO_CHAR(len));
>   dbms_lob.read(i1, len,1,my_vr);
>
>   select dump(my_vr, 16)
>   into my_vr2
>   from dual;
>
>   dbms_output.put_line(my_vr2);
> END;
> /

What national character set are you using for this database? CLOB and VARCHAR2 columns (among others) use the 'normal' character set defined at database creation and the N-named counterparts (NCLOB, NVARCHAR2) use UTF-8 (or whichever multi-byte characterset is defined as the NLS_NCHAR_CHARACTERSET). If NLS_CHARACTERSET is not defined as UTF8 or one of the other UTF-8 compatible variants then your CLOBs are not likely to be using UTF-8.

David Fitzjarrell Received on Tue Mar 16 2010 - 10:50:52 CDT