CLOB datatype error

sspreethi · Post by **sspreethi** » Fri Jan 06, 2006 4:37 am

Hi

Two fields in my source Oracle table are of CLOB datatype. When I import this table into Datastage it gets converted to Longvarchar. This job is a direct load into target table. When I run the same it gives me the following error: -- ORA-24801: illegal parameter value in OCI lob function.

I tried executing the same by manually changing the datatype to Varchar in source. Then I get -- Abnormal termination. It doesnt provide me more information here.

Can someone let me know a solution for this.

Thanks,
Preethi.

ArndW · Post by **ArndW** » Fri Jan 06, 2006 4:57 am

Per the Oracle OCI documentation, you need to declare the datatype as LongVarChar in DataStage for CLOB database columns. Could you try that and see if you still have an error?

chulett · Post by **chulett** » Fri Jan 06, 2006 7:34 am

Yes, as Arnd notes you'll need to use a datatype of LongVarChar (and a large size) in order to stand a chance of making this work. Make sure you keep the array size at 1 or at least raise it at your own risk.

The other 'trick', if you know the data in the CLOB that you are selecting will fit in a Varchar, is to CAST the field as a VARCHAR2 in the source query.

sspreethi · Post by **sspreethi** » Mon Jan 09, 2006 7:44 am

It didnt work either.

I did go through other threads on this discussion & tried Varchar(20000+) with To_Char. This works in a very random fashion. It succeeds during few runs & fails during the rest. I have no clue on what basis this is happening.

Finally I have decided to go for a SQL script as I am not sure when this would abort.

Please let me know if there's any other way I can try...

Thanks a lot Arnd & Chulett

Regards,
Preethi.

chulett · Post by **chulett** » Mon Jan 09, 2006 8:19 am

Did you try a LongVarChar (not a VarChar) of "20000+"? No TO_CHAR.

loveojha2 · Post by **loveojha2** » Mon Jan 09, 2006 9:22 pm

We also found similar problem working with CLOB datatype.
The workaround was using Longvarchar with a lesser size, in our case we chose 2000, the possible reason of the problem could be of memory (may be Array Size * Record Length)(Experts plz correct me if my assumption is wrong).

Try it with LongVarchar and a smaller size and let us know the outcome.

sspreethi wrote:It didnt work either.

I did go through other threads on this discussion & tried Varchar(20000+) with To_Char. This works in a very random fashion. It succeeds during few runs & fails during the rest. I have no clue on what basis this is happening.

Finally I have decided to go for a SQL script as I am not sure when this would abort.

Please let me know if there's any other way I can try...

Thanks a lot Arnd & Chulett

Regards,
Preethi.

chulett · Post by **chulett** » Mon Jan 09, 2006 10:04 pm

Another reason why I suggested you stick with an Array Size of 1.

Some of the advise here can be tuned if you 'know' what the maximum size of the data is in the CLOB. For example, we've got one table with a CLOB field that holds wildy different data in the CLOB. For some datatypes, I know that size won't exceed 200 bytes so can treat it much like a normal Varchar. Other datatypes can go up to 16K in size and so require playing LongVarChar games.

kris007 · Post by **kris007** » Wed Jul 19, 2006 7:25 am

I am facing the exact problem as the OP. I have played with the arraysize and the length of the LongVarchar datatype. I have varied it from 2000 to 30000 and yet my Job won't run and fail with the same error

Code: Select all

ORA-24801: illegal parameter value in OCI lob function

Anything else I need to try? I was able to run this job properly in Development but its failing in Test. The data in test and development are different.So its not that I was not able to run it at all. Any suggestions would be great.

The maximum size of the data in the CLOB is 3500bytes.

So 2000 wont work...but 4000 didnt work either.

Thanks

chulett · Post by **chulett** » Wed Jul 19, 2006 7:53 am

If you know it won't exceed 3500, then there's no reason to use LongVarchar. What happens when you declare it as a simple Varchar of say, size 4000? If that still doesn't work, add a 'CAST' to your source query to cast the CLOB to that type/size.

kris007 · Post by **kris007** » Wed Jul 19, 2006 8:49 am

Craig,

I am not sure it's not going to exceed 3500. That is the maximum lenght at this moment. It can always go up. Who knows

I'll try the methods suggested and see if they work though I am not really positive about Varchar(4000).

My other question would be, why would it not fail in development and fail only in testing. The maximum size is 3500 in both dev and test. Is there anything I would need to look for?

chulett · Post by **chulett** » Wed Jul 19, 2006 9:50 am

You'd have to check and see what is different from dev to test. Version - client or db? Server settings? Anything other than the data itself, which I'm assuming is different. And if you don't see anything, I'd focus on the nature of the data then.

kris007 · Post by **kris007** » Thu Jul 20, 2006 8:18 am

chulett wrote:You'd have to check and see what is different from dev to test. Version - client or db? Server settings?

Yes. They are not the same. In BTW, the maximum size of the data was more than 25000 bytes. Using a CAST function and changing it to Varchar2(4000) did the trick.