SQL Loader

[Raj_38]

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

[Steven E. Protter]

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

[James A. Donovan]

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

[Michael Schulte zur Sur]

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]

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.

[Brian Crabtree]

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