Solved

How to load decimals

Posted on 2002-06-24
9
2,293 Views
Last Modified: 2007-11-27
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
Comment
Question by:xoxomos
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7106283
Use a TO_NUMBER function on the columns.


Andrew

0
 
LVL 9

Expert Comment

by:konektor
ID: 7106697
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
 
LVL 3

Expert Comment

by:mnicoras
ID: 7107887
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
 
LVL 11

Expert Comment

by:pennnn
ID: 7108279
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:pennnn
ID: 7108321
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
 
LVL 3

Accepted Solution

by:
mnicoras earned 100 total points
ID: 7108674
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
 

Author Comment

by:xoxomos
ID: 7108938
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
 

Author Comment

by:xoxomos
ID: 7109033
Ignore previous comment.  Waitlist_position should not be there.  Perfect!
What does
"the error is somewhere else ;-)  mean?
0
 

Author Comment

by:xoxomos
ID: 7109041
Hmm  this shows i have accepted and answer and unlocked?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now