• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Loader help  RSS feed

Aryan Khan
Ranch Hand
Posts: 290
Java Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
ID Number (10)
FirstName (15)
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)
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

[edited to remove "urgent" from subject]
[ December 12, 2004: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
author & internet detective
Posts: 37181
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Oracle Forum Bartender
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!