aspose file tools*
The moose likes Oracle/OAS and the fly likes SQL Loader help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL Loader help" Watch "SQL Loader help" New topic
Author

SQL Loader help

Aryan Khan
Ranch Hand

Joined: Sep 12, 2004
Posts: 290

Hi All,
Hope you all are well...
I need an urgent suggestion for following question..pls help..
---------------------
I have an oracle database with 2 tables on AIX.
Subscribtion_Table and Dependent_Table
I have to load the data from a text file into these tables, but the format of that file is preventing me to use sqlloader.
Here is the format( It is a fixed length file).
Subscribtion_Table
-----------------
ID Number (10)
FirstName (15)
GENDER(1)
Country(2)
NO_OF_DEPENDENTS(3) --- This is an important field as it shows how many dependents this person has..and after this field the the following information repeated for each dependent once(Which should go to Dependent_table)
Dependent_Table:
----------------
Dependent_Name(15)
Dependent_Age(3)
Relationsship(1)
Here is a sample of the text file:
-----------------------------------
0000000000Ahmad MSA003Michael 019SChris 025BHayat 024D
0000000000David MSA002Andrew 030WRob 035S
0000000000Sanje MSA001Benet 030W
----------------------------------------
As u can see "Ahmad" has 3 dependents while "David" has 2 dependencies and "Sanje" only 1.
Those dependeices are repeated in same line of file.
Michael,Chris and Hayat are Ahmad's dependents
Andrew and Rob are David's dependets
Benet is Sanje's dependent.
The records for all dependents should go to Dependent_Table



How can I do it using sqlloader.
I thaught to have a java application inserting it into a temp table and then a stored procedure or a "TRIGGER"
parse it and insert it into corresponding tables(Pls tell a post insert trigger is better or stored procedure).
I have to check every one hour for new files which can contain upto 10000 rows.
Pls tell me the best possible and easy solution.
Pls help at earliest possible
Thank you very much & and appreciate your support
ASK

[edited to remove "urgent" from subject]
[ December 12, 2004: Message edited by: Jeanne Boyarsky ]

OCP/MCP/SCJP/SCWCD/IBM XML/SCMAD/SCEA-1
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30948
    
158

I would favor the stored procedure over the trigger because you know when the transformation needs to occur. However, this is just personal preference. Either solution is ok.

In the future, please don't put the word "urgent" into the subject. Everyone's questions are important to them and the word "urgent" sometimes turns people off.

Thanks,
Jeanne
Oracle Forum Bartender


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
MUSTAFA ELSHIEKH
Greenhorn

Joined: Feb 07, 2004
Posts: 15
Creating a procedure or trigger is a good idea,
but for myself i prefer cordinating the file on an excel sheet,
simply change what you need and then convert it by the sqlloader to your database.

Regards,
Mustafa.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Loader help