CLOB dbms_lob.substr ORA-01461

mavrick21 · Post by **mavrick21** » Mon Dec 19, 2011 4:14 pm

DSGurus,

After reading up on all the posts, related to this issue, I'm posting this new thread.

I've a job with Oracle DB table as source and target. The source table has a CLOB field with a maximum of 7600 chars. We want the job to typecast CLOB to Varchar, split them and populate it in two separate Varchar fields of 4000 length each.

In the source DRS stage I've all the source fields plus two Varchar(4000) fields (say TGTfield1 & TGTfield2) with derivation as below:

Code: Select all

NVL(CAST(dbms_lob.substr(CLOBfield, 4000,1) AS VARCHAR(4000)),'-')
NVL(CAST(dbms_lob.substr(CLOBfield, 4000, 4001) AS VARCHAR(4000)),'-')

I've a transformer between source & target DRS stage. I've tried with Varchar (4000 byte) & Varchar (4000 char) in target table but unless I specify the below derivations in transformers I get ORA-01461 error.

Code: Select all

Source_Load.TGTfield1[1:1999]
Source_Load.TGTfield2[1:1999]

I've also set the target DRS stage to UTF-8 which shows up in Loaded Maps Only.

Is this a DataStage issue (NLS?) or Oracle DBMS_LOB package issue?

Kindly advise. Thanks.
-Mav

ray.wurlod · Post by **ray.wurlod** » Mon Dec 19, 2011 7:13 pm

The fact there's an Oracle error code suggests that there's an Oracle issue. Did you try CAST .. AS VARCHAR2 instead of VARCHAR ?

That said, your substring syntax is incorrect; you need a comma rather than a colon. string[1,1999] returns the first 1999 characters from string. You could also specify Left(string,1999) which I think is more self-documenting.

mavrick21 · Post by **mavrick21** » Mon Dec 19, 2011 7:40 pm

Ray,

CAST .. AS VARCHAR2, CAST .. AS VARCHAR, string[1,xxxx] and Left(string, xxxx) work as long as xxxx value is 1999 and below. Any number above 1999 results in ORA-01461 error ( ORA-01461: can bind a LONG value only for insert into a LONG column)

I tried dbms_lob.substr on the source table and it splits the CLOB column at correct start & end positions. I need to implement this in DS job since source & target tables are on different Databases.

Thanks
-Mav

chulett · Post by **chulett** » Mon Dec 19, 2011 9:34 pm

That error implies that your substringed CLOB value is greater than 4000 bytes so guessing an NLS issue. Perhaps you can use Long Varchar as the intermediate value?

mavrick21 · Post by **mavrick21** » Mon Dec 19, 2011 10:18 pm

Craig,

LongVarchar(4000) for the 2 columns in source DRS stage and transformer, and Varchar(4000) in target DRS stage?

Thanks

chulett · Post by **chulett** » Mon Dec 19, 2011 10:20 pm

Not sure... perhaps in all three, and you may need to bump the internal field size up as well. Play.

mavrick21 · Post by **mavrick21** » Mon Dec 19, 2011 10:28 pm

But don't I have to stick with 4000 Chars/Bytes in the target DRS stage since Varchar2 supports only a maximum of 4000 Chars/Bytes?

chulett · Post by **chulett** » Mon Dec 19, 2011 10:33 pm

No... setting it bigger just means DataStage will allow it to 'overflow' and get rejected by Oracle if anything turns out to not fit. And the limit is in bytes, not characters.

mavrick21 · Post by **mavrick21** » Mon Dec 19, 2011 10:55 pm

I'll try it first thing tomorrow morning and update the post.

I don't have any/much knowledge about NLS like on which tiers (OS, DB, DS) it has to enabled, how to check if they are enabled or how to enable them. If you can please share some good links, PDFs and tutorials to enlighten me I would be very grateful.

Thanks Ray & Craig! I appreciate it very much.

mavrick21 · Post by **mavrick21** » Tue Dec 20, 2011 4:39 pm

Craig,

I bumped up internal size for both fields in the DS job and ended up with ORA-01461 error in the target stage.

Could it be the NLS issue?
The source & target DB have the same NLS setting: select * from nls_database_parameters -- AL16UTF16. The dsenv file doesn't contain NLS_LANG or ORA_NLS environmental variables. Also just echoing them at the UNIX prompt returns blank result.

Now by just entering ORA_NLS=AL16UTF16 and NLS_LANG=AL16UTF16 in dsenv mean that all the settings are correct for the NLS data to flow through? Do I've to restart DS engine for any dsenv changes? Since NLS maps show up in DRS stage does it mean that NLS support was installed when DS was installed?

Thanks

mavrick21 · Post by **mavrick21** » Wed Dec 21, 2011 11:06 am

Also wanted to check - Should OS also be NLS enabled during its installation?

Thanks.

mavrick21 · Post by **mavrick21** » Tue Dec 27, 2011 2:18 pm

Found an alternate solution: split the source CLOB field to four varchar fields, each 1999 chars long, in the target.

Hope this info is useful to others.

chulett · Post by **chulett** » Tue Dec 27, 2011 2:43 pm

I think Arnd posted a similar solution some time ago. Whatever works.