topic SQL Loader in Operating System - HP-UX

SQL Loader

Raj_38 — Mon, 01 Dec 2003 14:21:07 GMT

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

Re: SQL Loader

Steven E. Protter — Mon, 01 Dec 2003 14:57:50 GMT

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

Re: SQL Loader

James A. Donovan — Mon, 01 Dec 2003 15:13:08 GMT

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

Re: SQL Loader

Michael Schulte zur Sur — Mon, 01 Dec 2003 15:26:57 GMT

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

Re: SQL Loader

R. Allan Hicks — Tue, 02 Dec 2003 08:37:35 GMT

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.

Re: SQL Loader

Brian Crabtree — Tue, 02 Dec 2003 17:38:55 GMT

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