This content has been marked as final. Show 11 replies
What about alphanumeric. Give a try
Sorry i cdnt see your last line not to change the datatype
Edited by: Bhabani on Jan 31, 2013 2:07 PM
Yes we dont want to change the datatype. I am trying to check the SNP tables for the exact issue.
Do we have seperate tables/columns to hold values of different datatypes? Can you please let me know if you have some idea.
use to_number() . It will work
like select to_number(seq.nextval) from dual
It didnot work. i am getting the same error.
Caused By: com.sunopsis.dwg.SQLWorkReposException: ORA-01438: value larger than specified precision allowed for this column
Have you tried Alphanumeric as Bhabani suggested?
Alphanumeric works. But we dont want to change the datatype.
Can we alter the SNP table SNP_SESS_STEP_LV? I am not sure if oracle allows this kind of tweeking :)
Edited by: Ashok R on Jan 31, 2013 3:38 AM
Hmm..Need to find some other way. If its production then do it at your own risk :D1 person found this helpful
alter table SNP_VAR_DATA modify (VAR_N number)
Now its working.
select VAR_V Alphanumeric_value , VAR_N Numeric_Value from SNP_VAR_DATA where VAR_NAME='PRO_SCOTT.test1'
Edited by: Bhabani on Jan 31, 2013 9:35 PM
ok :) Thanks for your time.
My lead raised a SR for this issue and this is what oracle said.
I see Numeric Variable's value is stored in SNP_VAR_DATA.VAR_N column which is a NUMBER (10,0).
That explains the limitation you run into. Then I checked our doc, I see we did documented the expected length:
Datatype Type of variable:
Alphanumeric (255 characters)
Date (This format is a Java date format that matches your machine's local
parameters Note that you may need to adapt the format depending to the RDBMS)
Numeric (Maximum 10 digits)
Text (Unlimited length)
and from my research I see we do already have an enhancement requested entered.
Alternative suggestion is also to use Alphanumeric Variable and we will increase the size of numeric variables in next ODI release,12.1.2 to 19 digits
Then you should ask in return that is there any side effect if directly alter the column like i said in my previous reply.
I read a oracle note yesterday somwhere in the net whcih says Oracle will not support if you change the SNP tables :).