To_number, ORA-01722: invalid number error

oracle

I have a batch job which is trying to load data from a .dat file into an Oracle table.

Below is the script within our control file:

LOAD DATA
INTO TABLE TABLE_1
WHEN (1:1)="1"
(
A_id                       POSITION(0002:0005)    CHAR             ,
c_code                     POSITION(0006:0008)    CHAR             ,
status                     POSITION(0009:0009)    CHAR             ,
FTC                        POSITION(0011:0024)    char
                           "to_number(:FTC,'S99,999,999.99')"      ,
TCER                       POSITION(0029:0038)    decimal external ,
ATC                        POSITION(0039:0052)    char
                           "to_number(:ATC,'S99,999,999.99')"      ,
FTR                        POSITION(0053:0066)    char
                           "to_number(:FTR,'S99,999,999.99')"      ,
TRER                       POSITION(0067:0080)    decimal external ,
ATR                        POSITION(0081:0094)    char
                           "to_number(:ATR,'S99,999,999.99')"
)

Records in the file:

1GSXBGBPNR        384.66    0.60801391        632.00       -384.66    0.60801391       -632.00
1GSXBEURNR         10.42    0.70548409         14.00          0.00    0.00000000          0.00
1GSXBEURRE        136.01    0.70533631        192.00          0.00    0.00000000          0.00
1GSXBUSDRE         95.91    0.90472597        106.00        -95.91    0.90472597       -106.00

While the job runs we get the ORA-01722: invalid number error error on the column FTC. How can I fix it?

Best Answer

Your format picture, 'S99,999,999.99', requires that the number begin with a sign -- either "+" or "-" -- which your actual data does not contain.

Remove the S from the format picture.

Related Solutions

ORA-01410: invalid ROWID

In your code, you are locking the following records in the c1 cursor result set for the column sal:

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            9451 ACCOUNTING
      7839 KING            12001 ACCOUNTING
      7934 MILLER           8301 ACCOUNTING

For example, from another session this query

SELECT sal FROM emp FOR UPDATE;

will block until the next commit or rollback statement will be issued (check out v$session).

I guess the problem is that you are updating the sal column values of table emp1 (and not emp one), but these records are not locked. You plan on updating emp1 records that have not been referenced by the previous SELECT FOR UPDATE statement, so their ROWIDs become invalids. From here: The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

In fact, if you change the code like this:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp1
    SET    sal = sal + 1000;
END IF;
...

or if you replace emp1 with emp:

...
IF c2.dname = 'ACCOUNTING' THEN
    UPDATE emp
    SET    sal = sal + 1000;
    WHERE  CURRENT OF c1;
END IF;
...

it would work.

Convert string to number from xml at server side

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.

Best Answer

Related Solutions

ORA-01410: invalid ROWID

Convert string to number from xml at server side

Related Question