Home » RDBMS Server » Server Utilities » SQL Loader - How To Implement
icon4.gif  SQL Loader - How To Implement [message #464389] Wed, 07 July 2010 13:24 Go to next message
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 #464394 is a reply to message #464389] Wed, 07 July 2010 13:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Would fixing the datafile prior to load (using OS means) be an option?
If so, what is the OS?
Re: SQL Loader - How To Implement [message #464395 is a reply to message #464394] Wed, 07 July 2010 14:13 Go to previous messageGo to next message
felipe.vinturini
Messages: 5
Registered: February 2010
Location: Brasil
Junior Member
You meant with a perl/python script?

The OS is Windows.

Thanks.
Re: SQL Loader - How To Implement [message #464633 is a reply to message #464395] Thu, 08 July 2010 10:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I was hoping you would say Unix.Smile
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 #464634 is a reply to message #464633] Thu, 08 July 2010 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>Will give a try with PERL.
PERL is OS independent!
Re: SQL Loader - How To Implement [message #464635 is a reply to message #464634] Thu, 08 July 2010 10:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10621
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes. But my PERL skills are very limited. Have not touched in a while.
Re: SQL Loader - How To Implement [message #464649 is a reply to message #464389] Thu, 08 July 2010 10:48 Go to previous messageGo to next message
CajunVarst
Messages: 54
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....
Re: SQL Loader - How To Implement [message #464650 is a reply to message #464649] Thu, 08 July 2010 10:52 Go to previous message
felipe.vinturini
Messages: 5
Registered: February 2010
Location: Brasil
Junior Member
Ok! Thanks a lot for the suggestions and the code!

I will give a try and let you know the result and the solution...
Previous Topic: need help with partition export
Next Topic: IMPORT ERROR
Goto Forum:
  


Current Time: Sat Aug 30 11:29:37 CDT 2014

Total time taken to generate the page: 0.09798 seconds