CLOB Data Columns

[Former Member]

We would like to be able to insert, update, and select from an Oracle table that has a column that is of type CLOB (Character Large Object) because our data is more than 4000 characters (VARCHAR2 won't handle it).

Is this possible using the xMII Query Template Editor?

Thanks,

Mike

[Former Member]

Would only take a few minutes to try it out...in theory it should work since xMII should simply convert it to/from a string, but you might have issues at some point if the content is very, very large (particularly when inserting/updating).

Give it a try!

Rick

[Former Member]

Hi Rick,

Thanks for your response. I tried this and xMII does not return the value of the CLOB column. It returns three dashes where the column value should be like this:

<colNameHere>---</colNameHere>

Any ideas on a workaround for this?

Thanks,

Mike

[Former Member]

--- implies a null value in the column. If you run the same query from PL/SQL does it return text?

[Former Member]

Rick,

If I use TOAD and just do a "select * from table_name_here" it returns the string value I want.

Mike

[salvatore_castro]

Michael,

Another thing you may want to try is to call this using a stored procedure, you will have more power over the CLOB update size. If I remember correctly it's a JDBC limitation for CLOB update/retrieval size that can be circumvented using procedure calls. I suggest a Google search first to verify this, but it's worth looking into.

Hope this helps.

Sam

[Former Member]

Sam - AFAIK xMII doesn't have support in it for retrieving or storing CLOBS/BLOBS using JDBC unless the underlying JDBC driver does the translation (I know this doesn't work for Oracle, for example). For SQL Server, I think the JDBC driver converts CLOBS/BLOBS to strings (BLOBS look like base64 or something hex). There are specific JDBC extensions and APIs for handling these datatypes which xMII does not utilize directly.

[Former Member]

I'm struggling with this at the moment did you get anywhere?

There was another post last year I found with lots of useful links

It's all a bit technical though! So if anyone can give me an idiot's guide I'd be very grateful.

One thing I tried successfully, which I believe could increase the string length to 32767 without too much effort is to write a function in Oracle something like this:

CREATE OR REPLACE FUNCTION clob_to_text(XX clob) RETURN varchar2 IS
TMP VARCHAR2(32767);

BEGIN
TMP := XX;
RETURN TMP;
END clob_to_text;

Then the sql query becomes

select field1, clob_to_text(field2) from mytable

[Former Member]

Select

concat( to_char('') , to_char( FieldName1 ) ) FieldName1,

FieldName2

From

Table1

... another alternative to retrive the text.

Cheers,

Jai.