CLOBS in ora 8.1.7

Not quite sure which forum this should go in, sorry for the crosspost:

I've used the sqlldr LOBFILE feature to load a modest sized document (which in real life is actually a longish xml datagram) from an operating system file into
a CLOB column.

All seems well because:
When I go to sql plus, set long 10000 (or some such longish value) then select the_clob_col from the_table;

 I see the document I think I've loaded all nice and pretty.

NOW I try to select dbms_lob.substr( the_clob_col, 100) From the_table

Yuck! The system shows me funny characters looking something like "yyyyyyy".

How can I select bits and pieces (or even the whole CLOB, assuming the size
of the CLOB is less than or equal to the size of a pl/sql varchar2) into

a pl/sql
varchar2 so that we can manipulate it?

I thought dbms_lob.substr(...) was the way, but I'm running into some sort of character set problem, things look great if I select the CHAR directly from sql plus
but look ungood if I try to use the dbms_lob functions.

Anybody have a clue? Received on Mon Dec 10 2001 - 14:32:03 CST