Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Loader

Raj_38
Occasional Advisor

SQL Loader

Hi,
I receive a data file with customer name and date of join.

Sample

Cus100 06/26/01
Cus200 07/23/02
Cus300 10/22/02
Cus400 10/21/03
Cus500 11/26/03

I want to load the above data into a table which has
3 columns (Custnum , Date of join, Status).

For Status column i need to update based on the date of join (DOJ). Like if DOJ >= 06/01/02 and DOJ <=12/31/02 then status = S1 else DOJ >= 01/01/03 and DOJ <=06/01/02 then status = S2 and so on ......

this i can do using an update statement after the sqlload. But i want it to be done while loading itself. Is there any option in sqlload ?

Thanks

5 REPLIES
Steven E. Protter
Exalted Contributor

Re: SQL Loader

I'm a lightweight with sqlloader, but our scripts are upload only, update after the data is on the database.

That is a good indicator that you should do things that way.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
James A. Donovan
Honored Contributor

Re: SQL Loader

If you have acces to it, take a look at these Metalink documents. They have examples of using the decode function (and other relevant stuff) within your SQL*Loader control file.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1083518.6

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=592224.999

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=171762.1

Remember, wherever you go, there you are...
Michael Schulte zur Sur
Honored Contributor

Re: SQL Loader

Hi,

here is an alternative in the form of an awk script:
BEGIN\
{
SQLFILE="custinsert.sql";
print "sqlplus /nolog< SQLFILE;
print "connect user/passwd" > SQLFILE;
}
{
DATE=substr($0,14,2) substr($0,8,2) substr($0,11,2);
if ((DATE >= "020601") && (DATE <= "021231"))
STATUS="S1";
if ((DATE >= "030101") && (DATE <= "030630"))
STATUS="S2";
if ((DATE >= "030701") && (DATE <= "031231"))
STATUS="S3";
print "*" DATE "*" STATUS "*";
print "insert into table values($1,$2,"STATUS")" > SQLFILE;
}
END\
{
print "EOF" >> SQLFILE;
}

this is only a rough idea, but should give you an idea how it works.

greetings,

Michael
R. Allan Hicks
Trusted Contributor

Re: SQL Loader

If you are running 9i, the file is a good candidate for an external table. As an external table you can access the two columns that you have and then do the steps for the status column as you insert into your internal table.

BTW external tables use sqlloadr in an transparent manner. So, you are accomplishing running the loader and doing the conditional processing at the same time.

External tables can be good if you receive the file periodically, but for a one time deal. Go with sqlloadr and do the post processing as the others have suggested.
"Only he who attempts the absurd is capable of achieving the impossible
Brian Crabtree
Honored Contributor

Re: SQL Loader

Raj, you could also use a trigger on the table as well. The following is some code that was modified from the Metalink website. Because the trigger is modifying a row that was already modified, it needs to be done in this way.

create or replace package password_pkg as
type password_tab_type is table of rowid index by binary_integer;
password_tab password_tab_type;
password_index binary_integer;
end password_pkg;
/

create or replace trigger password_bef_stm_all
before insert or update of password,expiretime,comments on password
begin
password_pkg.password_index := 0;
end;
/

create or replace trigger password_aft_row_all
after insert or update of password,expiretime,comments on password
for each row
begin
password_pkg.password_index := password_pkg.password_index + 1;
password_pkg.password_tab(password_pkg.password_index) := :new.rowid;
end;
/

create or replace trigger password_aft_stm_all
after insert or update of password,expiretime,comments on password
begin
for i in 1 .. password_pkg.password_index loop
update password set passdate = to_date(to_char(sysdate)) where rowid = password_pkg.password_tab(i);
dbms_output.put_line(password_pkg.password_tab(i));
end loop;
password_pkg.password_index := 0;
end;
/

Thanks,

Brian