 SQL Loader - How To Implement [message #464389] |
Wed, 07 July 2010 13:24  |
felipe.vinturini
Messages: 5
Registered: February 2010
Location: Brasil
|
Junior Member |
|
|
Hi All,
A better title for this post would be:
SQL Loader - How to implement/Best solution.
I have 3 tables with their columns:
- MASTER_TABLE - MASTER_ID, DATA;
- PARENT_TABLE_A - MASTER_ID, DATA;
- PARENT_TABLE_B - MASTER_ID, DATA.
And the file I need to import has lines like the ones below:
MMMASTER_TABLE1
PAPARENT_TABLE_A1
PBPARENT_TABLE_B1
MMMASTER_TABLE2
PAPARENT_TABLE_A2
PBPARENT_TABLE_B2
MMMASTER_TABLE3
PAPARENT_TABLE_A3
PBPARENT_TABLE_B3
The line means:
- 1 - M or P: indicates which table to insert: MASTER or PARENT;
- 2 - M or A or B: indicates MASTER, PARENT_A, PARENT_B;
- 3:18 - DATA.
Based on the values above, what I need to do is:
1. Load a line to MASTER_TABLE;
2. Load a line to PARENT_TABLE_A pointing to its relative line in MASTER_TABLE;
3. Load a line to PARENT_TABLE_B pointing to its relative line in MASTER_TABLE;
4. In the original file line, there is nothing I can use to join a MASTER line with a PARENT line.
The result would be:
MASTER_ID PARENT_DATA
1 PARENT_TABLE_A1
1 PARENT_TABLE_B1
2 PARENT_TABLE_A2
2 PARENT_TABLE_B2
I tried to use both: SEQUENCE and Sequence.NextVall (CurrVal) but they only work when using ROWS=1 and the file I need to load has millions of rows, so I need direct path loading.
Also, I read about External Table, but it does not suit my needs because the Application server is not the same as Database server, which is needed by external tables.
I would like you to share your experience, if you already had such problem, if, in this case is better load the data to a temporary table and then insert to the other tables, if there is a better way to do this.
I found almost the same question in the topic pointed by the link below:
www.orafaq.com/forum/t/47839/2/
As it is an old topic I thought I could find something new.
Attached are the files needed to test.
Thanks for your attention.
Regards!
CM: swapped the [code] tags around the url for [url] tags
[Updated on: Wed, 07 July 2010 16:13] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Loader - How To Implement [message #464633 is a reply to message #464395] |
Thu, 08 July 2010 10:04  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
I was hoping you would say Unix.
Looking again, seems fixing the datafile may not work.
You are using position based load and for a huge datafile the generated first column could vary a lot.
Since you have already ruled out all the 3 native methods I know, got nothing more to say.
Probably our in-house sqlldr expert Barbara can help.
Will give a try with PERL.
thanks
[Updated on: Thu, 08 July 2010 10:11] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Loader - How To Implement [message #464649 is a reply to message #464389] |
Thu, 08 July 2010 10:48 |
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
|
Member |
|
|
if you want to prepend an incremental number, you can do this with vbscript:
dim fso, iFile, oFile, iLine, oLine, oNo, x
set fso = CreateObject("Scripting.FileSystemObject")
set iFile = fso.opentextfile("<path_to_current_file",1,True)
set oFile = fso.opentextfile("<path_to_new_file",2,True)
oNo = 1000000000
Do While Not iFile.AtEndOfStream
oNo = oNo + 1
For x = 1 to 3
iLine = iFile.ReadLine
oLine = oNo & iLine
oFile.WriteLine oLine
Next
Loop
iFile.Close
oFile.Close
Save this script at something like filefix.vbs, then run from Command Windows as cscript filefix.vbs
It will create a new file with your groups of three records prepended with a number. The number is quite large so that it does not move the position in the file.
Maybe this will help, maybe not....
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
