Mask of format in external table

[hpaille]

Hello,

I would like to know if we can use a mask for the type NUMBER in an external table.
I load an file with amount that are sign, and I want to be sure that they are always signed.
example +5555 -> in the DB : 5555
-123 -> -123 in the DB
However 234 should be rejected (no sign). How can I do that without having a specific field for the sign ?

 

[SantaMufasa]

Are the incoming signed numeric values in the external table represented on the table as signed numerals (i.e., ASCII numeric characters)? If so, then I would query the data using either the Oracle "instr" (In String) function or the "LIKE" comparison operator:

SELECT <expressions>
  FROM <table name>
 WHERE [b]<signed-number-column name> LIKE '-%'
    OR <signed-number-column name> LIKE '+%'[/b];

Such a query would return only the valid rows. To fix (or otherwise deal with) the invalid rows, you could use this query:

SELECT <expressions>
  FROM <table name>
 WHERE [b]<signed-number-column name> NOT LIKE '-%'
   AND <signed-number-column name> NOT LIKE '+%'[/b];

Let us know if this technique proves helpful for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hpaille]

The fields in the external table are INTEGER EXTERNAL. What I would like to do is to let Oracle reject the line, before I do a query on the table. If I receive some spaces in a date field, the line will not be loaded into the external table and will be rejected in the .bad file (if the option is set). This is what I would like to do if the amount received has got a psace instead of the sign.