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

From: Rui Zhang <jackchang1_at_gmail.com>
Date: Tue, 16 Mar 2010 12:33:15 -0700 (PDT)
Message-ID: <3104676a-70a3-4a0b-b711-b998238285b2_at_x12g2000yqx.googlegroups.com>

On Mar 16, 9:39 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Rui Zhang" <jackcha..._at_gmail.com> a écrit dans le message de news:
> 02013ebf-b54d-4782-8968-3fedbd9d6..._at_30g2000yqi.googlegroups.com...
> | 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;
> | /
>
> Since 10g CLOB are stored in AL16UTF16 if your character set is a multi-byte one
> otherwise it is in the database character set itself.
>
> Regards
> Michel

the query result is AL32UTF8
SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'

Does this mean the CLOB is saved in UTF8? when I try to work on column(convert it to a json object), I got a malformed UTF8 error. Received on Tue Mar 16 2010 - 14:33:15 CDT