Oracle number converted to decimal(28,7)

dataservices job design
1

Hi,

we are using DI 6.5 on Oracle databases.
When using a table as source with a type of number DI converts this to decimal(28,7).
Since number can (and will contain in some instances) contain 41 characters this evidently leeds to errors in the process.

Question, can I tell DI NOT to do this?

Thx for any suggestions,
SLOWBSURE

slowbsure (BOB member since 2004-09-27)

2

I know, I know, sooner or later we have to deal with that correctly. Import the table, go the object library, open the table there, select to column in question, rightclick to get to the popup menu, select properties and change the datatype to double. That should do the trick.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Well, that works only until importing the table again off course. (I tested that and it is sad but true)
And it is a pain to do for app. 400 columns!!!

Am I wrong to understand this is a bug in DI? If so, a patch would be a reasoanble thing to expect IMO.

slowbsure (BOB member since 2004-09-27)

4

I have raised that issue inside the product group. Please file a bug for it so we get an official case number and a priority. Also, ask to add my name as a sidenote.

It seems we made this decision once, but maybe we haven’t thought about all the impacts as carefully as we should - under investigation.

Reimporting is a problem, which is less a pain in DI 11.

Changing all 400 is easy: export all tables/DFs(?) as ATL file and run a search/replace before importing it again.

Werner Daehn :de: (BOB member since 2004-12-17)

5

Ok, dirty is my second name, but I’m not creating huge file formats anymore : I generate ATL and modify it right after manually using UltraEdit (have no time to create generator).

Why can you not do the same ?

Import the table, export it from your repository to file (ATL).
Open this atl file in a correct (means no notepad, wordpad, etc) text editor [Important that the editor will not replace LineFeeds (0x0A) by the windows standard [0x0D0A].
Replace all DECIMAL(28, 7) by DOUBLE.
Import using DI’s menu.

And it works.
Of course it’s dirty but if I remember well about the license it talks about voiding warrenty support if editing manually the repository but do not talk about ATL files.

TRS-80 :belgium: (BOB member since 2005-03-29)

6

Hm, a good thought, but I am afraid it is not usefull.

basically the filed contains 41 characters and if you change it to double, it still tells you it does not fit ! And I use table comparison to update the tables.

Annoying enough, the first time it finds no rows, so all is just inserted! But the second time it wants to compare 41 characters with 41 characters.
And then we get the annoying message :
|Dataflow dfl_lpa_src_interactions|Reader Query
A numeric’s maximum number of digits is <28>. You are attempting to store value or constant <99900000000000000000000000000000000000000>, whose <41> digits exceed the maximum>

So, all of this is nice, but a catch22, I either take out the long values or make varchars of them. neither is very appealing.

So, I will just have to file a bug report and hope for improvement.

Thx,
Slowbsure

PS,

I actually bought UltraEdit (the best tool) and dirty is my first name!!!

slowbsure (BOB member since 2004-09-27)

7

Is there a resolution to this in DS 4.0 other than changing the source field data types in Local Object Library one by one ? I see that this corrects view data, however, the issue remains while execution…

suneer333 :us: (BOB member since 2010-07-15)

8

suneer, please provide more information. What is the data type in Oracle? What is the data type in Data Services?

eganjp :us: (BOB member since 2007-09-12)

9

Its a NUMBER data type in Oracle and in DS, it is getting defaulted to decimal (28,7).

I changed precision and scale in data store to a higher value as I have been receiving numbers up to 41 digits. But I am concerned about the larger impact on all the columns including those which do not actually require this.

If I change the data type for only those specific columns by right clicking the local object library as explained above, that does not get saved. The data type will change to the default decimal when logging out and logging in. Not sure if this is a bug. On BODS 4.0 SP3.

suneer333 :us: (BOB member since 2010-07-15)

10

I think the ‘Calculate Column Mapping’ is updating the columns back to DECIMAL(28,7) i guess?

ganeshxp :us: (BOB member since 2008-07-17)

11

I am not sure. Does that happen by itself while log out and log in ? I am not doing it

suneer333 :us: (BOB member since 2010-07-15)

12

Okay forget about the stuff I had mentioned!!!

Now, when you EDIT your datastore, you would see a Tree - 'Oracle Miscellaeous" and it has 2 items under that - ‘Default precision for Number’ and ‘Default Scale for Number’…Set them up and you are all set for a re-import!!!

Let me know if that works…

ganeshxp :us: (BOB member since 2008-07-17)

13

Thanks Ganesh. I had done that. However, the issue is - it would also affect those fields for which I do not want the precision and scale to be increased. Say, if the table has around 100 fields, I cannot selectively do this for 5 fields.

suneer333 :us: (BOB member since 2010-07-15)

14

NUMBER in Oracle defaults to NUMBER(38). So if you have a value with 41 digits it won’t fit (I would like to see this number!). Always, always, always provide explicit data types. In your case maybe you should use NUMBER(45).

DS doesn’t quite understand NUMBER so it gives up and goes with its internal default of decimal(28,7).

eganjp :us: (BOB member since 2007-09-12)

15

This is the value I get from NUMBER field in Oracle which is defaulted to decimal (28,7) in DS

99900000000000000000000000000000000000000.0000000000

suneer333 :us: (BOB member since 2010-07-15)

16

Your Oracle table must be NUMBER(38). Here’s what I get:

CREATE TABLE TEST_NUM(C1 NUMBER(38));
INSERT INTO TEST_NUM VALUES (99900000000000000000000000000000000000000.0000000000);
SQL Error: ORA-01438: value larger than specified precision allowed for this column

Change this around to use NUMBER instead of NUMBER(38):
DROP TABLE TEST_NUM;
CREATE TABLE TEST_NUM(C1 NUMBER);
INSERT INTO TEST_NUM VALUES (99900000000000000000000000000000000000000.0000000000);

1 rows inserted.

eganjp :us: (BOB member since 2007-09-12)

17

Hi Egan,

This is an ERP table and those are not in my hands. Not sure if they will change those fields to NUMBER (38 ) instead of NUMBER. I will try for sure. I am concerned here is why am I not able to change the data type of only those columns in the Local object library :hb:

suneer333 :us: (BOB member since 2010-07-15)