Solved

Direct path SQLLDR and implied decimals

Posted on 1998-06-04
3
996 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

635 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