• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3387
  • Last Modified:

How to load decimals

When i describe these fields as character, everything is fine.

CREATE TABLE units_XT
      (
     SECTION_KEY char (15)  ,
     STU_KEY char(10) ,
     TERM char (5) ,
     ENRL_WAIT char (1) ,
     GRADE_TYPE char (2)  ,
     GRADE char (3)  ,
     FTE_UNITS CHAR(5) ,
     HAY_FUNITS CHAR(5) ,
     CCC_FUNITS CHAR(5)  ,
     OPNU_UNITS CHAR(5) ,
     FROW_COUNT int ,
     WAITLIST_POSITION char (3)  ,
     stu_id char (9)
      )
      ORGANIZATION external
       (
        TYPE oracle_loader
        DEFAULT DIRECTORY data_dir
        ACCESS PARAMETERS
        (
         RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
         BADFILE log_dir: 'sh_units_%p.bad'
         LOGFILE log_dir: 'sh_units_%p.log_xt'
         FIELDS
         (  
          SECTION_KEY          POSITION (1:15)    CHAR  ,
       STU_KEY              POSITION (16:25)   CHAR ,
       TERM                 POSITION (26:30)   CHAR ,
       ENRL_WAIT            POSITION (31:31)   CHAR ,
       GRADE_TYPE           POSITION (32:33)   CHAR  ,
       GRADE                POSITION (34:36)   CHAR  ,
       FTE_UNITS            POSITION (37:41)   char ,
       HAY_FUNITS           POSITION (42:46)   CHAR,
       CCC_FUNITS           POSITION (47:51)   CHAR,
       OPNU_UNITS           POSITION (52:56)   CHAR,
       FROW_COUNT           POSITION (57:57)   INTEGER,
       WAITLIST_POSITION    POSITION (58:60)   CHAR  ,
       stu_id               POSITION (61:69)   CHAR
     )
    )
    location
    (
    'units.dat'
    )
)REJECT LIMIT UNLIMITED NOPARALLEL;


When i try using decimal,


CREATE TABLE units_XT
      (
     SECTION_KEY char (15)  ,
     STU_KEY char(10) ,
     TERM char (5) ,
     ENRL_WAIT char (1) ,
     GRADE_TYPE char (2)  ,
     GRADE char (3)  ,
     FTE_UNITS decimal ,
     HAY_FUNITS decimal ,
     CCC_FUNITS decimal  ,
     OPNU_UNITS decimal ,
     FROW_COUNT int ,
     WAITLIST_POSITION char (3)  ,
     stu_id char (9)
      )
      ORGANIZATION external
       (
        TYPE oracle_loader
        DEFAULT DIRECTORY data_dir
        ACCESS PARAMETERS
        (
         RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
         BADFILE log_dir: 'sh_units_%p.bad'
         LOGFILE log_dir: 'sh_units_%p.log_xt'
         FIELDS
         (  
          SECTION_KEY          POSITION (1:15)    CHAR  ,
       STU_KEY              POSITION (16:25)   CHAR ,
       TERM                 POSITION (26:30)   CHAR ,
       ENRL_WAIT            POSITION (31:31)   CHAR ,
       GRADE_TYPE           POSITION (32:33)   CHAR  ,
       GRADE                POSITION (34:36)   CHAR  ,
       FTE_UNITS            POSITION (37:41)   DECIMAL (5,2) ,
       HAY_FUNITS           POSITION (42:46)   DECIMAL (5,2),
       CCC_FUNITS           POSITION (47:51)   DECIMAL (5,2),
       OPNU_UNITS           POSITION (52:56)   DECIMAL (5,2),
       FROW_COUNT           POSITION (57:57)   INTEGER,
       WAITLIST_POSITION    POSITION (58:60) CHAR  ,
       stu_id               POSITION (61:69)   CHAR
     )
    )
    location
    (
    'units.dat'
    )
)REJECT LIMIT UNLIMITED NOPARALLEL;

i get error message
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-03003: absolute end(56) - start(52) != fieldlength (3) for field OPNU_UNITS
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1


A line of the data looks as so

20023ACCT225101052665595220023E      4.00 4.00  .00  .001226522033
Below i've quoted fields are the ones i'm trying to define as decimal
20023ACCT225101052665595220023E      " 4.00" " 4.00" "  .00" "  .00"1226522033


0
xoxomos
Asked:
xoxomos
  • 3
  • 2
  • 2
  • +2
1 Solution
 
asimkovskyCommented:
Use a TO_NUMBER function on the columns.


Andrew

0
 
konektorCommented:
if u want to store numbers with max 3 decimal numbers (eg. 123 or 123.1 or 123.123, but no 123.1234)
FTE_UNITS NUMBER(10,3) - means number can be stored with 10 digits and 3 of them after decimal point

FTE_UNITS NUMBER(10) - only integer numbers can be stored

FTE_UNITS NUMBER - any of number can be stored


FROM HELP :
---------------------------

The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, Oracle returns an error.

Specify a fixed-point number using the following form:

NUMBER(p,s)


where:

s  
 is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.  
 
 


Specify an integer using the following form:

NUMBER(p)  
 is a fixed-point number with precision p and scale 0. This is equivalent to NUMBER(p,0).  
 
 


Specify a floating-point number using the following form:

NUMBER  
 is a floating-point number with decimal precision 38. Note that a scale value is not applicable for floating-point numbers. (See "Floating-Point Numbers" for more information.)  
 
 


Scale and Precision
Specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, Oracle returns an error. If a value exceeds the scale, Oracle rounds it.

The following examples show how Oracle stores data using different precisions and scales.

7456123.89  
 NUMBER  
 7456123.89  
 
7456123.89  
 NUMBER(9)  
 7456124  
 
7456123.89  
 NUMBER(9,2)  
 7456123.89  
 
7456123.89  
 NUMBER(9,1)  
 7456123.9  
 
7456123.89  
 NUMBER(6)  
 exceeds precision  
 
7456123.89  
 NUMBER(7,-2)  
 7456100  
 
7456123.89  
 NUMBER(-7,2)  
 exceeds precision  
 
 


Negative Scale
If the scale is negative, the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Scale Greater than Precision
You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle returns an error message. If the value exceeds the scale, Oracle rounds the value. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision:

Actual Data   Specified As   Stored As  
.01234  
 NUMBER(4,5)  
 .01234  
 
.00012  
 NUMBER(4,5)  
 .00012  
 
.000127  
 NUMBER(4,5)  
 .00013  
 
.0000012  
 NUMBER(2,7)  
 .0000012  
 
.00000123  
 NUMBER(2,7)  
 .0000012  
 
 


Floating-Point Numbers
Oracle allows you to specify floating-point numbers, which can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.

You can specify floating-point numbers with the form discussed in "NUMBER Datatype". Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT  
 specifies a floating-point number with decimal precision 38, or binary precision 126.  
 
FLOAT(b)  
 specifies a floating-point number with binary precision b. The precision b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.  
 

0
 
mnicorasCommented:
hi,

the error is somewhere else ;-)

just use decimal(8,2) instead of decimal (5,2) and it will work.

keep in touch,
Marius Nicoras
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
pennnnCommented:
Marius,
Why should he use decimal(8,2) when the total field length is 5 (POSITION 52:56)?!?!
I think you should try using DECIMAL EXTERNAL (5,2).
As far as I remember the DECIMAL datatype expects binary data (2 digits per byte, plus additional byte for sign, I think), so the number 12345.67 would be represented by 4 bytes in your data file.
The EXTERNAL datatypes are pure CHAR fields, like what you have in your data file.
Hope that helps!
0
 
pennnnCommented:
Now when I think about it, maybe you can't specify the precision for EXTERNAL datatypes...
You can try it without it (just the length) or you can try using DOUBLE EXTERNAL (5) instead of DECIMAL EXTERNAL (5).
Hope that helps!
0
 
mnicorasCommented:
Hi,

using "decimal external" it works also, check the logfile and you will see the output message how oracle computes the data length. The message says "Unknown" for decimal "external data" type. But you get the error for column GRADE_TYPE which says: error processing column GRADE_TYPE in row 1 for datafile units.dat
ORA-01401: inserted value too large for column.
Too avoid that just add precision when you define the fields ...

so it should look like

CREATE TABLE units_XT
     (
    SECTION_KEY char (15)  ,
    STU_KEY char(10) ,
    TERM char (5) ,
    ENRL_WAIT char (1) ,
    GRADE_TYPE char (2)  ,
    GRADE char (3)  ,
    FTE_UNITS decimal ,
    HAY_FUNITS decimal ,
    CCC_FUNITS decimal  ,
    OPNU_UNITS decimal  ,
    FROW_COUNT int ,
    WAITLIST_POSITION char (3)  ,
    stu_id char (9)
     )
     ORGANIZATION external
      (
       TYPE oracle_loader
       DEFAULT DIRECTORY logfiles
       ACCESS PARAMETERS
       (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        BADFILE 'sh_units_%p.bad'
        LOGFILE 'sh_units_%p.log'
        FIELDS
        MISSING FIELD VALUES ARE NULL
        (  
      SECTION_KEY          POSITION (1:15) CHAR(15),
      STU_KEY              POSITION (16:25) CHAR(10),
      TERM                 POSITION (26:30) CHAR(5),
      ENRL_WAIT            POSITION (31:31) CHAR(1),
      GRADE_TYPE           POSITION (32:33) CHAR(2),
      GRADE                POSITION (34:36) CHAR(3),
      FTE_UNITS            POSITION (37:41) DECIMAL EXTERNAL,
      HAY_FUNITS           POSITION (42:46) DECIMAL EXTERNAL,
      CCC_FUNITS           POSITION (47:51) DECIMAL EXTERNAL,
      OPNU_UNITS           POSITION (52:56) DECIMAL EXTERNAL,
      FROW_COUNT           POSITION (57:57) INTEGER,
      WAITLIST_POSITION    POSITION (58:60) CHAR(3),
      stu_id               POSITION (61:69) CHAR(9)
    )
   )
   location
   (
   't.txt'
   )
)REJECT LIMIT UNLIMITED NOPARALLEL;


best regards,
Marius Nicoras
0
 
xoxomosAuthor Commented:
Nopes  that stu_id should be 9 characters.  When i select it six show up.  I'll try to formulate another question here in a few.
0
 
xoxomosAuthor Commented:
Ignore previous comment.  Waitlist_position should not be there.  Perfect!
What does
"the error is somewhere else ;-)  mean?
0
 
xoxomosAuthor Commented:
Hmm  this shows i have accepted and answer and unlocked?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now