6 Replies Latest reply: Jan 4, 2013 2:13 PM by L-MachineGun RSS

    Loading unpacked Zoned data into NUMBER data types

    982758
      I need to use SQL*Loader to load data created on an MVS machine (EBCDIC) which is FTP'd to AIX (converted to ASCII during the FTP). Some of the data elements have signed unpacked numeric data. These need to be loaded into two types of fields in Oracle - 1) defined as data type NUMBER(19) 2) - defined as data type NUMBER (38,2) (i.e. dollar field with 2 decimal positions). For the dollar data I am loading there is an implied decimal. I need to know how to code SQL*Loader statements to handle these two situations. Because I have zoned unpacked source the resulting data loaded into the table must have a trailing sign (only if negative). Also the dollar fields need to have an explicit decimal inserted - and these dollar fields could also end up being negative.

      Any help would be appreciated. Various google searches have not given me the answers I need.
        • 1. Re: Loading unpacked Zoned data into NUMBER data types
          L-MachineGun
          Did you try to use the ZONED SQL*Loader field definition?
          :p
          • 2. Re: Loading unpacked Zoned data into NUMBER data types
            982758
            Being a novice with SQL*Loader, I will say no.

            Is this something I can specify in the control file? If so, an example would be helpful.
            • 3. Re: Loading unpacked Zoned data into NUMBER data types
              L-MachineGun
              979755 wrote:
              Being a novice with SQL*Loader, I will say no.

              Is this something I can specify in the control file? If so, an example would be helpful.
              Yes, check the link I posted to the fine Oracle® Database Utilities - Specifying Columns and Fields.
              ;)
              • 4. Re: Loading unpacked Zoned data into NUMBER data types
                982758
                Most helpful. Pardon a few follow-on questions:

                1. The Zoned definition refers (I hope) to the data on the input file - regardless of the definition of the field field being loaded in the table (in my case the field in the table is defined as NUMBER) - is this a true statement?

                2. If I am correct on item 1, if I have a negative number in the input file (let's say a negative 10) is a trailing sign loaded into the database (so it will be 10-)?

                3. What happens to any leading zeroes when the data is loaded into the table?

                4. For dollar fields where the source has an implied decimal but I want an implicit decimal in loaded into the table and the number can be positive or negative, can I specify this as ZONED (10.2) in the control file?

                5. And finally, regarding item 4 my understanding is that ZONED (10.2) would result in 12345678.12 (with a negative sign if appropriate) - is this a correct understanding?

                Thanks again for your help.
                • 5. Re: Loading unpacked Zoned data into NUMBER data types
                  982758
                  One correction regarding the dollar fields - I need to load an explicit (not implicit) decimal into the table. The source has an implicit decimal.
                  • 6. Re: Loading unpacked Zoned data into NUMBER data types
                    L-MachineGun
                    979755 wrote:
                    Most helpful. Pardon a few follow-on questions:

                    1. The Zoned definition refers (I hope) to the data on the input file - regardless of the definition of the field field being loaded in the table (in my case the field in the table is defined as NUMBER) - is this a true statement?
                    True, in your controlfile you set (for example): , zonedCol POSITION(x:y) ZONED(precision,scale)
                    2. If I am correct on item 1, if I have a negative number in the input file (let's say a negative 10) is a trailing sign loaded into the database (so it will be 10-)?
                    NO, to deal with signed numbers, you set trailing signs as table column in a staging table and then apply to real table.
                    3. What happens to any leading zeroes when the data is loaded into the table?
                    Ignored.
                    4. For dollar fields where the source has an implied decimal but I want an implicit decimal in loaded into the table and the number can be positive or negative, can I specify this as ZONED (10.2) in the control file?
                    See answer #2.
                    ZONED (10,2) implies field is 10 characters long and the last two are decimals.
                    5. And finally, regarding item 4 my understanding is that ZONED (10.2) would result in 12345678.12 (with a negative sign if appropriate) - is this a correct understanding?
                    Nope, only the digits. That is why we use staging table to capture the sign.

                    PS: The best would be to create external table on the source file.
                    ;)

                    Edited by: L-MachineGun on Jan 4, 2013 3:12 PM