Solved

Direct path SQLLDR and implied decimals

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loading flat file data in tables 2 98
sql server store procedure contains temp tables need to convert oracle? 3 51
Password_rules_securitty.. 12 46
oracle query 4 29
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 …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

739 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