7 Replies Latest reply on Sep 3, 2010 7:13 PM by 794356

    SQL Loader invalid packed decimal

      Hi. Could someone please help me with a couple of errors I am getting from SQL Loader? After examining the data and definitions, I can't see why these errors would come up. I have reduced the data down to 2 rows with 2 fields each to make is easier. We are running Linux 2.6.5-7.283-smp #1 (HOSTTYPE = x86_64) on a server model HL-DT-STCD-RW/DVD-ROM GCC-4244N with Oracle 10g and SQL*Loader

      Here is what the input data file looks like in the pico editor and hex dump:
      (the ^L is actually one character)
      $ pico loadtest.csv
      " 7&|","A "

      $ hd loadtest.csv
      00000000 22 0C 22 2C 22 41 22 0A 22 20 20 20 37 26 7C 22 " ","A" " 7&|"
      00000010 2C 22 41 20 22 0A ,"A "

      -- This is the SQL to create the target table:
      create table loadtest
      (FLD1 NUMBER(12,2)
      ,FLD2 VARCHAR2(2) );

      -- This is the control file used by SQL*Loader:
      options (errors=9999999999)
      into table LOADTEST
      ( FLD1 decimal(12,2) "trim(:fld1)"
      , FLD2 terminated by whitespace

      -- $inputdb is the schema-name/password
      -- The command below is used to load the data from the CSV into the schema.
      $ sqlldr $inputdb control=loadtest.ctl data=loadtest.csv log=loadtest.log

      -- Both records are rejected with these errors:
      Record 1: Rejected - Error on table LOADTEST, column FLD1.
      Invalid packed decimal nibble.
      Record 2: Rejected - Error on table LOADTEST, column FLD2.
      no terminator found after TERMINATED and ENCLOSED field

      -- The "terminated by whitespace" clause makes no difference.
      -- If the trim is left off of FLD1 in the control, the second message is
      "Value larger than specified precision allows for this column"

      The "invalid packed decimal nibble" makes no sense to me because the hex dump clearly shows that the value in the first record inside the quotes for FLD1 is 0C which is ascii hex for the form feed (FF). In packed decimal terms this is valid because the first 4 bits (x0 = 0000) represent the zero value, and the second 4 bits (xC = 1100) represent the positive sign.

      The "no terminator found" makes no sense to me because the hex dump clearly shows each data line being terminated by (hex) 0A which is ascii hex for line feed (LF). According to documentation, this is the default terminator for stream data.

      It is worth noting that this exact CSV file was copied to a VAX/VMS server and run through a COBOL program, which correctly translated the data as
      "+000372.67","A "

      (VAX/VMS is not where the data originated, but is convenient for COBOL programs)

      Can anyone see why these errors are being thrown?
      Thanks in advance.
        • 1. Re: SQL Loader invalid packed decimal
          Hello 79+,

          ... because the first 4 bits (x0 = 0000) represent the zero value, and the second 4 bits (xC = 1100) represent the positive sign.

          That statement along with this statement below might be the source of the problem:

          DECIMAL data is in packed decimal format: two digits per byte, except for the last
          byte, which contains a digit and sign.

          This is from the SQLLoader documentation, note that it says the last byte not the first one. If I recall correctly (which is not guaranteed ;) ) packed decimal means you have 2 binary digits in one byte not their ascii representation. What you posted is an unpacked form.

          From what you've said, it sounds like in COBOL you had a PIC s9(06).99 as your format. I believe (though not sure) that may map to a zoned in SQL Loader.

          At any rate, dealing with numbers represented in binary form is fraught with problems, not only because it is not always easy to map the representations from one system to another but, in addition to that the endian-ness of the machines involved can often cause additional problems.

          If I were you, I'd convert all the columns that have binary data into plain ascii and create the appropriate control file for SQL Loader. I think that is the method that will cause you the least number of headaches, not to mention that it will likely be faster (when your time is taken into account). In addition to that, it allows you to visually inspect the data (instead of a hex dump) to determine what may have gone wrong.



          Edited by: 440bx - 11gR2 on Aug 27, 2010 7:37 PM added missing "cause"

          Edited by: 440bx - 11gR2 on Aug 27, 2010 7:41 PM - corrected PIC
          • 2. Re: SQL Loader invalid packed decimal
            Hi John.
            Thanks for the response. But I am afraid you may have misunderstood my example. The data is already coming in as plain ascii, as represented visually just under where I have "$ pico loadtest.csv". I included the binary representation and a hex dump only to show exactly what is in the loadtest.csv (ascii) file. The code I show can actually be cut and pasted into a Linux system and SQL*Plus to see how it works (except the exact packed decimal character must be created in an editor).

            The posting does show the packed form. The hex dump (hd) on Linux shows the bytes and nibbles in a left to right format. So on the first line, the packed number is in one byte with a hex value of 0C. Being the last (and only) byte, it complies with the requirement that the "last byte ... contains a digit and sign". On the second line, the packed number is six bytes, is trimmed, with the last byte having a hex value of 7C, which also complies.

            In the test COBOL program used to read the same data file, the field in question used PIC S9(9)V99 COMP-3, which is a packed decimal definition. You are correct that without the "COMP-3" it would be a zoned (right overpunch) in VAX-VMS, but "COMP-3" was used, and successfully read the packed decimal data.

            I wonder if anyone can use my example code to reproduce the problem?

            Any thoughts on the second error for "no terminator found"?

            • 3. Re: SQL Loader invalid packed decimal
              Sven W.
              What seems to be missing in your control file is an indication about the record structure.

              maybe like this.
              LOAD DATA APPEND
              into table LOADTEST
              RECORDS DELIMITED bY newline
              FIELDS TERMINATED BY ','
              OPTIONALLY ENCLOSED BY '"'
              ( FLD1 decimal(12,2) "trim(:fld1)"
              , FLD2 terminated by whitespace
              This could explain the second error. However I can't see how it leads to the first one. So this might be a different problem.
              I guess the first error just means: This is not a valid number. I wouldn't care whether cobol can read that. This has nothing to do with the file spec for the oracle loader. 
              A file like {code}"0.000","A" 
              "+0.00345","A "{code} would probably be correctly read.
              Edited by: Sven W. on Aug 30, 2010 5:16 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: SQL Loader invalid packed decimal
                Thanks for the hint.
                Unfortunately, it gave me an error ...

                SQL*Loader-350: Syntax error at line 4.
                Expecting "(", found "RECORDS"
                RECORDS DELIMITED BY newline
                • 5. Re: SQL Loader invalid packed decimal
                  Sven W.
                  I typed that from memory. You might check the correct syntax in the documentation. I guess it is RECORD instead of RECORDS.
                  • 6. Re: SQL Loader invalid packed decimal
                    I tried both ways, as plural and singular, adding this line:

                    I checked the Oracle SQL*Loader Syntax diagram, and this clause is not anywhere in the valid syntax:

                    I also could not find it in the Oracle Database Utilities manual ... though on page 12-2 that particular line is used in an example for "CREATE TABLE" under the clause for "TYPE ORACLE_LOADER" instead of SQL*Loader. It appears to be something completely different.

                    Yes, the goal is to get the data that is loaded into the schema table to look like 0.00 and 372.67. The whole point of using SQL*Loader is to translate the data from Packed Decimal in the input file into real numbers in the schema fields. I cannot translate it first into 0.00 and 372.67 because that is what I need to have SQL*Loader do.

                    The only reason I used COBOL was to demonstrate that the input is valid. SQL*Loader, using the "DECIMAL" clause, is advertised to be able to handle this data. So far it fails.

                    Would anyone care to use my example to reproduce the problem?

                    • 7. Re: SQL Loader invalid packed decimal
                      We were not able to get the (packed) decimal to work in SQL*Loader, but we did come up with a work-around. We will likely need to do something similar with zoned (overpunched) numbers.

                      This is the contents of the new control file, loadtest.ctl :

                      options (errors=9999999999)
                      LOAD DATA
                      APPEND into table LOADTEST
                      FIELDS TERMINATED BY ','
                      OPTIONALLY ENCLOSED BY '"'
                      TRAILING NULLCOLS
                      ( FLD1 "to_number( translate(
                      substr( rawtohex(ltrim(:fld1)),
                      length(rawtohex(ltrim(:fld1))), 1),
                      '0123456789ABCDEF', 'xxxxxxxxxx - - ')
                      || trim( translate( rawtohex(ltrim(:fld1)), 'ABCDEF',' ')))
                      / 100"
                      , FLD2 terminated by whitespace "trim(:fld2)"

                      We could not indent the lines as much as we wanted. Otherwise we got the error "Token longer than max allowable length of 258 chars".

                      The trailing sign in the last nibble of the field must be moved to the first character so SQL Loader will handle it as a number (also to_number function). In a legal packed decimal number, the last nibble must have a hexadecimal representation of A,B,C,D,E or F. If not, then it must be rejected. In the first translate call above, the purpose of replacing the digits only with "x" is so the resulting string is not a legal number, and the record is rejected as bad.

                      Ltrim is used above because we just don't know if leading spaces are part of the number or not. We are awaiting word from the data provider. Leading spaces (hex 20) are legal in a packed decimal, but not in the last byte.

                      The following data was used for testing: (^L is actually one byte = hex 0C)
                      " 7&|","A "
                      " 4 h","h"
                      " 4 ","S"
                      " 4 k","k"
                      " 4 n","n"

                      The target table is defined as ...
                      FLD1 NUMBER(12,2)
                      FLD2 VARCHAR2(2)

                      Records 3 and 4 were correctly rejected, resulting in the following rows in the table with values that are correct:
                      FLD1 FL
                      ---------- --
                      0 A
                      372.67 A
                      -342.06 k
                      342.06 n

                      If anyone is able to get DECIMAL to work, we would be very interested. (same with ZONED).