Error in webi report - ORA-06502

[former_member539471]

Dear All,

I have create an object in Universe "description" , I am using this object in WEBI report.

When I run the WEBI report I am getting below error:

Database error occurred :the databasr error text is ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 1 . (Error: WIS 10901)

This object is working fine in Dev and Quality.

But it is getting above error in the Prod environment in WEBI report.

Actually i think its this "description" object which is the reason

dbms_lob.substr( xyz.DESCRIPTION, 4000, 1 )

Any help is highly appreciated

[Former Member]

Is every value in the xyz.DESCRIPTION object at least 4000 characters long..?

Edited by: lucid_lunatic on Dec 21, 2011 11:18 PM

[former_member539471]

no some of them are less and some much more than 4000

[Former Member]

Then I think that's the issue. If the value is fewer than 4000 characters, and your substring is set to start at the 4000th, then the formula hits a wall.

[former_member539471]

yes, i thought so too.But...this is happening only in production.In dev and quality we have description of exact same length(over 4000 char ) working fine.

it only is happening in production.

Do you think the below substring should work??? beyond 4000 char limit??

dbms_lob.substr(table.field, dbms_lob.getlength(table.field), 1)

Thanks

[Former Member]

It should... Or, another option (assuming that you're looking for the same pattern in each value) would be to use the Pos() function as your starting point in the substring.

[former_member539471]

thanks.

will try out and let you know.

But, do you have any idea on why its happening only in Production environment??

[former_member539471]

Hi,

i know why this is breaking.This is breaking due to special charatericstics....in the description.

[Former Member]

This may seem a silly question, but are your dev/test environments running on the same Oracle version/patchset as your production environment?

[former_member539471]

hello,

yes the DB across the landscapes is same version ...

the failure is due to....see the below test in the field

"cess Code |ZCCONSI| configured "

These 'bar' looking characters are what is causing the failure:

Any ideas on how to fix this.....will string function work at all for this?

Thanks