how to load a single record into multiple tables using sql * loader and generate sequence to populate a primary key column in both the tables to link between those two tables?

0 pts.
Tags:
Application software
DataCenter
Development
Lifecycle development
Actually I and one of my frnds are doing a project in d2k,oracle as per our course of study.I'm using forms 6i and oracle 9i. I want to have a form on which there is a button whose work is to populate the database. Now I have got three files( one of them is in ?ctf format?,one of them is an ?etab? file and another one is a ?ssim? file. Anyway I can open them in Notepad.now I have to write a query in forms 6i(eg: when_button_clicked) on that button,so that by clicking on which the data in these three files will be loaded in to their respective tables.I want to use three different sql * loader script or 3 differnet ctl files. But the problem is in case of the ctf file. It?s basically a pairing of airlines. Here is a small section of the pairings.ctf file , I?m writing below. PERIOD:20050526 - 20050629 PLAN TYPE: DATED SECTION: PAIRING PAIRING: 3 109791 "109791" 0/0/0//0/0/6 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 3 109792 "109792" 0/0/0//1/0/0 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING This particular file will be loaded into two different tables. The sample you are seeing now is the starting of the pairing.ctf file I don?t want the first three lines in my tables for e.g., PERIOD:20050526 - 20050629 PLAN TYPE: DATED SECTION: PAIRING Now lets take the example of the first pairing i.e. PAIRING: 3 109791 "109791" 0/0/0//0/0/6 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING I want to skip the first word PAIRING then I want to load the rest of line i.e. 3 109791 "109791" 0/0/0//0/0/6 BHX in to one table lets say ?pairings_master? and for one row in the master table I want to have 3-4 rows (whatever is given in the file) into another table lets say ?pairings_detail? for e.g. for one row in the master table in the above shown example I want to have 3 rows like F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 In the pairings_detail table. Then it will skip the word EOPAIRING which means end of pairing and will load the next pairing . Now sometimes we might have 4 rows or 2 rows instead of three rows to be loaded into the pairings_detail table for one corresponding row in the pairing_master table. Now the problem is how I shall link these two tables, for that I need to have a unique sequence for both the tables. Lets say the sequence number will be one for the master table?s first record then for the first three records of the details table it will remain only one. Only when the loader goes to the next record the sequence will increase for both the master?s one record as well as detail?s multiple records as two in the above shown example and it will remain the same two for all of the detail?s corresponding records. Please Advice !!! Could you please write the ctl file as well as the necessary procedures/triggers for the success of this application ? Waiting for your reply !!! (I AM SENDING A SMALL PORTION OF THE SAMPLE FILE BELOW,PLEASE COPY TO THE NOTEPAD EDITOR FOR BETTER VIEWING.) Best regards, Subhakant PERIOD:20050526 - 20050629 PLAN TYPE: DATED SECTION: PAIRING PAIRING: 3 109791 "109791" 0/0/0//0/0/6 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 3 109792 "109792" 0/0/0//1/0/0 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 3 109793 "109793" 1/0/0//0/0/0 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 3 109794 "109794" 0/1/0//0/0/0 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 3 109795 "109795" 0/0/0//0/2/0 BHX F L * 20050524 BHX 0815 BY 0084 A 1 1720 POP 20050524 F L * 20050525 POP 1930 BY 0409 B 1 0350 MAN 20050526 T * * 20050526 MAN 1550 GD * * 1 1820 BHX 20050526 EOPAIRING PAIRING: 2 109796 "109796" 0/0/0//0/0/2 BOH F L * 20050526 BOH 1610 TOM 0733 * 1 1900 AGP 20050526 F L * 20050526 AGP 1930 TOM 0734 * 1 2220 BOH 20050526 EOPAIRING PAIRING: 2 109797 "109797" 0/0/0//0/0/2 BOH F L * 20050527 BOH 1250 TOM 0885 * 1 1400 CDG 20050527 F L * 20050527 CDG 1430 TOM 0886 * 1 1540 BOH 20050527 EOPAIRING
ASKED: May 19, 2005  10:53 AM
UPDATED: May 19, 2005  11:24 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your files containing data should be uniform in having records, then only it will be easy to use , SQL Loader.

You can also try, External Tables in Oracle 9i, which utilises sql losder to load data.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following