SQLLOADER trick

From: FC <flavio_at_tin.it>
Date: Tue, 21 May 2002 21:56:39 GMT
Message-ID: <rozG8.77390$zW3.1255257@news1.tin.it>

Folks,
I've got the following problem in a SQL loader script (Oracle PE on Win 98 8.1.7.0.0 and EE 8.1.7.0.0 on Win 2K) :

given the following source line

200203120101002000000400000101056395000000000000814030002W635 0000023053000028310

the statement

POS POSITION(52:53) INTEGER EXTERNAL "GREATEST(TO_NUMBER(:POS, '99'), 1)"

returns 3 (correct answer, from my viewpoint) while

POS POSITION(52:53) INTEGER EXTERNAL "GREATEST(:POS, 1)" returns 1 (wrong answer, from my viewpoint)

Column POS is defined as a number in my table.

Well, the trick lies either in the implicit type conversion performed by function GREATEST or in the missing type conversion performed by SQLloader on the data being read, whichever comes first.

Even if you declare the field of type integer, it is still converting the second number into a string, rather than converting :POS to a number, unless you explicitly force the conversion using the function TO_NUMBER.

In other words, be careful when converting between data types on the fly, you could end up spending hours trying to figure out why you are getting such funny results.

Flavio Received on Tue May 21 2002 - 16:56:39 CDT