Home » RDBMS Server » Server Utilities » SELECT in SQL Loader
SELECT in SQL Loader [message #144667] Thu, 27 October 2005 11:05 Go to next message
chinnu_e
Messages: 1
Registered: October 2005
Location: Hyderabad
Junior Member
Hi,
It is required to load data from a .csv file to a table with few additional columns which have to be selected from other tables based on data in .csv file.
Say for ex. the file has VendorID column in it and I want to populate pary_id from hz_parties table simultaneously while loading data.

Any help would be appreciable.

Thanks in advance!

Report message to a moderator

Re: SELECT in SQL Loader [message #144672 is a reply to message #144667] Thu, 27 October 2005 11:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Load the data as-is into a staging table.
then use sql methods (joins to query another_table and staging_table , insert into target_table).

Report message to a moderator

Re: SELECT in SQL Loader [message #145682 is a reply to message #144667] Fri, 04 November 2005 15:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Here's another way:

create functions that return the values you need and call those functions in the SQL*Loader control file as such:

.
.
vendor_id,
pary_id "function1(:vendor_id)",
hz_parties "function2(:vendor_id)",
.
.
.

Note that this is 2 extra hits on the database for each row loaded, so it may be very slow. Also, if any row being inserted may be used by another rows being loaded, you'll need to set BINDSIZE to 1 so that a commit happens after every row (even slower).

Report message to a moderator

Re: SELECT in SQL Loader [message #254257 is a reply to message #144667] Thu, 26 July 2007 05:42 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Err...i see that this is 2005 doubt but i am also facing the same.

Will anyone redirect me on how to write the 'functions' ? i mean where to write them ..in control file ?

if one could compelte the syntax, i will hughly appreciate it.

thanks in advance.

Regards
Nilesh

Report message to a moderator

Re: SELECT in SQL Loader [message #254262 is a reply to message #144667] Thu, 26 July 2007 05:49 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Adding more clarification to my problem:

I have an input .csv file for 4 columns which might contain blanks for the 4th column. the first 3 also form composite primary key for another table say Table B.

My issue is I wish to input the .csv data into table A. but when 4th column in .csv is null, it should find the unique value using the first 3 columns (none of them is null in the csv ...so no recursive issue) from Table B (as mentioned above) and insert it into Table A.

I know that I have to use functions but i am unable to find material for it.

Please help me along with syntax.

Thanks again.
Nilesh.

[Updated on: Thu, 26 July 2007 05:51]

Report message to a moderator

Re: SELECT in SQL Loader [message #254273 is a reply to message #254262] Thu, 26 July 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table then you have all the power of SQL and PL/SQL.

Regards
Michel

Report message to a moderator

Re: SELECT in SQL Loader [message #254286 is a reply to message #144667] Thu, 26 July 2007 06:32 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Hey Michel,
Thanks for the advice.That was way too quick.God Bless you.

But is it really not possible using SQlLoader ? I am asking this because I am trying to write some scripts which will upload data into the database which also generates Log of the activities done. My senior asked me to use any delivered tool prefereably since their funtionality and power are known and time tested. We wanted tracebility in whatever we are doing.

So what is you valuable advice in the matter ?

Thanks again for the quick reply.
Regards
Nilesh

Report message to a moderator

Re: SELECT in SQL Loader [message #254291 is a reply to message #254286] Thu, 26 July 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You generate log with PL/SQL procedure.
You can trace what you do with or on procedure.
It is easier to follow what it is inside a database than outside it.

How can you trace SQL*Loader activity?
I can trace SQL activity.

Regards
Michel

Report message to a moderator

Re: SELECT in SQL Loader [message #254299 is a reply to message #144667] Thu, 26 July 2007 06:44 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Ok.
Let me try convincing my senior and see what comes up.
Thanks for the advice, again, at a lightning speed.
God Bless You.

Regards
Nilesh

Report message to a moderator

Re: SELECT in SQL Loader [message #254323 is a reply to message #254299] Thu, 26 July 2007 07:38 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't mention your Oracle version; external tables were introduced with Oracle 9i, so - if your version is lower than that, you will not be able to use this feature.

Report message to a moderator

Re: SELECT in SQL Loader [message #254334 is a reply to message #144667] Thu, 26 July 2007 08:33 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Hi Littlefoot
I am currently using Oracle 10G.
So i think i shouldnt have any issues as such.

Report message to a moderator

Re: SELECT in SQL Loader [message #254425 is a reply to message #254334] Thu, 26 July 2007 13:45 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Even better!

Report message to a moderator

Re: SELECT in SQL Loader [message #254507 is a reply to message #254425] Fri, 27 July 2007 00:19 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Littlefoot wrote on Fri, 27 July 2007 00:15
Even better!


Sorry but I didnt get that.
Do you mean, using 10g is good?
or using external files is good?

Thanks.
Regards
Nilesh

Report message to a moderator

Re: SELECT in SQL Loader [message #254512 is a reply to message #254507] Fri, 27 July 2007 00:30 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Today, everything is good! Smile

I was referring to this sentence: "I shouldn't have any issues as such" so I thought that you can go to your colleagues and "try convincing your senior and see what comes up".

It would be a pity if you were thrilled with something new (external tables) and then disappointed when you realize that it is not supported in your database version (after disturbing senior colleague).

Report message to a moderator

Re: SELECT in SQL Loader [message #254626 is a reply to message #144667] Fri, 27 July 2007 05:22 Go to previous message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
haha..lol..
good one. Littlefoot.

Yeah, Seniors agreed when I portrayed more SQL possibilities in using External tables.

Thanks a lot Michel and Littlefoot.
God Bless You.

Regards
Nilesh

Report message to a moderator

Previous Topic: Error Handling In Sql Loader
Next Topic: sql loader
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 22 22:38:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.