Direct path SQLLDR and implied decimals

Is there any way to read in a field with an implied decimal using direct path sqlldr?
I have tried using the zoned datatype but the signs are being ignored
e.g.  reading  0000012345-    from the datafile using sqlldr    test_amt position(1:11) zoned(10,2)
it is loaded as 123.45 rather than -123.45
pmcget02Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sgantaConnect With a Mentor Commented:
Hi pmcget02
 
By the Grace of God I have beautiful solution for your problem.
Here I am explaining below :

Let us assume that my datafile TEST.DAT contains data like this
0000012345-
0000034555
0001234553-
1233442222-
9999999999
0000002344

Now I need to insert using the above data by taking the sign which is at the last digit (i.e. 11)

Am I Right !
OK ! Fine .

Let us assume that I am having table called TEST which contains column name called
test_amt NUMBER

Now Write this control file
/* Control File Begins */

LOAD DATA
INFILE 'TEST.DAT'
INTO TABLE TEST
WHEN (11:11) = '-'
(
   test_amt POSITION(1:10) ZONED(10,2) "-:test_amt" )
INTO TABLE TEST
WHEN (11:11) != '-'
(
   test_amt POSITION(1:10) ZONED(10,2))

/* CONTRO FILE ends */

Execute this.

This is tested and it works fine. If you want to include some more columns you can include
inside the brackets.

I hope this is acceptable to you.
JESUS LOVES YOU -sganta
0
 
pmcget02Author Commented:
The solution you gave worked for the example I presented. Unfortunately I have several fileds on each record all with implied decimal format. I have expanded it to handle two fields - which is probably as far as I can go with it - and is all I actually require for this particular problem. But I would still like to know if there is a more general solution.


0
 
sgantaCommented:
Hi
   Thank you for your points. You'll be getting the answer which you had asked
   for 2 fields soon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.