ORACLE BLOB Datatype

thirupri · Post by **thirupri** » Mon Jul 10, 2006 8:58 am

Dear Gurus,
I am trying to transfer a set of data to one column in oracle with the data type blob. When I define as a longvarchar I am getting oracle error message. So any one can help me how to handle this BLOB Data type.

Our Current version is 7.1

Thanks with Regards,

Thiruma Valavan

kcbland · Post by **kcbland** » Mon Jul 10, 2006 9:08 am

I do believe you can't move BLOBS using the Oracle OCI and regular DML. But I could be wrong.

thirupri · Post by **thirupri** » Mon Jul 10, 2006 9:13 am

Actually, I changed the column to Varchar with size of 2000 and I tried to transfer the data by concatenate the set of data. But I need the beter way for handling this. If any done please share your ideas.

kris007 · Post by **kris007** » Mon Jul 10, 2006 9:21 am

Change the datatype to LongVarchar and you should be able to move the data using OCI stage. I have done it successfully in the past on 7.5. Not sure on 7.1 though.

What happens when you try to import the table defintion of that particular table through Manager or Desingner? How does the datatype come into DataStage? The default was set to LongVarchar with a Length of 4000 for me when I tried to import it through Manager. Did you try doing that? Also, what is the error message you are getting?

kcbland · Post by **kcbland** » Mon Jul 10, 2006 9:28 am

Oracle's VARCHAR2(4000) is the maximum, so you're limiting yourself to the first 4000 characters selecting and inserting. It's BLOB, so that means you're corrupting your data, since BLOB means BINARY LARGE OBJECT, the first 4000 characters of binary data is useless.

Proceed at your own risk.

thirupri · Post by **thirupri** » Mon Jul 10, 2006 9:29 am

Hi Krish,

Thanks for you tips. When I imported the structure for this table, it came as LongVarChar but with out limit. When I put the structure for the Oracle9i stage. It gives me a error. I think 7.5 is good to handle this. Let me check with our vendor for this new version.

Thanks,

kris007 · Post by **kris007** » Mon Jul 10, 2006 9:38 am

I do apologize for giving a wrong suggestion.

I gave the suggestion assuming the datatype as CLOB instead of BLOB. Ken's post made me re-read the post again.

chulett · Post by **chulett** » Mon Jul 10, 2006 9:41 am

thirupri wrote:It gives me a error.

Any particular error? Or are you keeping that a secret?