Solved

Direct path SQLLDR and implied decimals

Posted on 1998-06-04
3
988 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:pmcget02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
sganta earned 200 total points
ID: 1081906
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
 

Author Comment

by:pmcget02
ID: 1081907
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
 
LVL 4

Expert Comment

by:sganta
ID: 1081908
Hi
   Thank you for your points. You'll be getting the answer which you had asked
   for 2 fields soon.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question