[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Direct path SQLLDR and implied decimals

Posted on 1998-06-04
3
Medium Priority
?
1,001 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
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
sganta earned 600 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

608 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