Can this be done using SQL Loader? 1

[synapsevampire]

Oracle 9.

I have a csv file that has numerous values in one field, in particular email addresses, as you would expect to find in a TO, FROM, CC or BCC field:

lellison@oracle.com,gwbush@whitehouse.com

What I need help with is how I might create an additional row for each of these, along with their respective field of origin as a type field (TO, FROM, CC, BCC) along with the referenceid, which is another column in the table.

If this cannot be done using SQL Loader, does anyone have an example of the PL/SQL code that would allow me to parse this and insert each into a row?

Thanks in advance,

-k

 

[djbjr]

Post an example of the data

 

[djbjr]

Also are you using UNIX? Is it possible to use Shell to solve your problem?

How many rows total are you trying to insert?

 

[SantaMufasa]

What I need help with is how I might create an additional row for each of these, along with their respective field of origin as a type field (TO, FROM, CC, BCC) along with the referenceid, which is another column in the table.

Your data example ("lellison@oracle.com,gwbush@whitehouse.com") does not seem to typify your specifications...I do not see examples of "TO, FROM, CC, BCC".

I agree with djbjr: We need to see sample data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Plus, although you can do what you want in SQL*Loader, I would probably suggest your using something like Oracle's "External table" capability that allows you to treat a flat file as though it is an Oracle table. Once you can access the flat file as an Oracle table, you have a variety of Oracle tools to parse your flat-file data into the results you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[synapsevampire]

The extrenal table functionality sounds intriguing, thanks.

All I need is an examlpe of how to parse text, but here's an example:

DocID: 12345
TO: bob@oracle.com
CC: lellison@oracle.com,gwbush@whitehouse.com
FROM: gretchen@oracle.com

So as part of this, the field that one is parsing would identify which TYPE is to be used in the destination table.

So the destination has bascially 3 fields, and would be populated as:

DocID: 12345
email: bob@oracle.com
Type: 1

DocID: 12345
email: lellison@oracle.com
Type: 2

DocID: 12345
email: gwbush@whitehouse.com
Type: 2

DocID: 12345
email: gretchen@oracle.com
Type: 3

This then allows for me to easily query the people involved with the doc ids.

-k

 

[SantaMufasa]

K,

A couple of follow-up questions:

1) What type do you want for "BCC", or is that even an issue?

2) Is this exercise a one-time load of flat-file documents for which you want a resulting DOCUMENT_PARTICIPANT table, or will there be on-going loads of new documents with accompanying INSERTs into the DOCUMENT_PARTICIPANT table?

3) From the flat source file, are you interested in loading any information besides the DOCID, TO, CC, and FROM data?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[synapsevampire]

I don't care what type is used for the BCC, I didn't really need help with that aspect, but I appreciate it. Use a integer of 1 thru 4 or whatever.

It will be ongoing, and I will be loading all of the other columns into another table, I don't need help with that, thanks.

-k

 

[synapsevampire]

I looked into the external table, the only thing I'm surious about there is the speed as opposed to using SQL Loader, is there a significant difference?

-k

 

[SantaMufasa]

K,

Since EXTERNAL TABLE uses the SQL*Loader internals, I imagine that the speed is virtually identical.

Following is a proof-of-concept example of a solution for you. I've divided the example into sections for ease of understanding:

Section 1 -- Sample flat-file e-mail-message text file (named "d:\dhunt\sqldba\TempEmail2.txt"):

DocID:	1234
From: 	oracle@domain.com
To:	dba@domain.com, dhunt@domain.com
CC:	admin@domain.com
BCC:	cio@domain.com

Date: 	7/7/06 6:02AM
Subject: 	Daily Oracle DB status

Some Text here.

DocID:	5678
From: 	oracle@domain.com
To:	dba@domain.com, dhunt@domain.com
CC:	admin@domain.com
BCC:	cio@domain.com

Date: 	7/7/06 6:02AM
Subject: 	Daily Oracle DB status


Some Text here.

Section 2 -- Creating infrastructure for a table from an external flat-file:

create directory MyFlatFiles as 'd:\dhunt\sqldba'
/
create table flat_file
(  txt varchar2(1000)
)
organization external
(  type oracle_loader
   default directory MyFlatFiles
   access parameters
   (records delimited by newline
    fields terminated by '^'
   )
location ('TempEmail2.txt')
)
reject limit unlimited;

Section 3 -- Creation of your target DOCUMENT_PARTICIPANT table:

create table document_participant
    (docid varchar2(10)
    ,email varchar2(100)
    ,typeid number
    ,constraint Doc_Partic unique (docid,email,typeid))
/

Section 4 -- Loading your DOCUMENT_PARTICIPANT table from the flat file:

set serveroutput on
declare
    curr_docid        varchar2(20);
    curr_txt          varchar2(2000);
    prefix            varchar2(2000);
    hold_txt          varchar2(2000);
    hold_type         number;
    curr_piece        varchar2(2000);
    curr_email        varchar2(2000);
    delim_loc         number;
    dup_rec_found     exception;
    pragma exception_init(dup_rec_found,-1);
begin
    for x in (select * from flat_file) loop
        curr_txt := translate(x.txt,chr(9),' ');
        prefix := upper(substr(curr_txt,1,instr(curr_txt,' ')-1));
        if prefix in ('DOCID:','FROM:','TO:','CC:','BCC:') then
            hold_txt := trim(substr(curr_txt,instr(curr_txt,' ')+1));
            if prefix = 'DOCID:' then
                curr_docid := hold_txt;
            else
                hold_type := case prefix
                    when 'TO:' then 1
                    when 'CC:' then 2
                    when 'FROM:' then 3
                    when 'BCC:' then 4
                    else null
                    end;
                curr_piece := hold_txt||',';
                delim_loc := instr(curr_piece,',');
                while nvl(length(curr_piece),0) > 0 loop
                    curr_email := substr(curr_piece,1,delim_loc-1);
                    begin
                        insert into document_participant values
                            (curr_docid,curr_email,hold_type);
                    exception
                        when dup_rec_found then
                            dbms_output.put_line ('Rec. already exists: Doc. ID: '||
                                curr_docid||', Email: '||curr_email||', Type: '||
                                hold_type||'. Skipping.');
                    end;
                    if delim_loc+1 <= length(curr_piece) then
                        curr_piece := trim(substr(curr_piece,delim_loc+1));
                        delim_loc := instr(curr_piece,',');
                    else
                        curr_piece := null;
                    end if;                 
                end loop;
            end if;
        end if;
    end loop;
    commit;
end;
/

PL/SQL procedure successfully completed.

select * from document_participant;

DOCID      EMAIL                              TYPEID
---------- ------------------------------ ----------
1234       oracle@domain.com                       3
1234       dba@domain.com                          1
1234       dhunt@domain.com                        1
1234       admin@domain.com                        2
1234       cio@domain.com                          4
5678       oracle@domain.com                       3
5678       dba@domain.com                          1
5678       dhunt@domain.com                        1
5678       admin@domain.com                        2
5678       cio@domain.com                          4

10 rows selected.

Section 5 -- Error-trapping illustration showing that re-running the code against the same flat file prevents duplicate records:

SQL> /

Rec. already exists: Doc. ID: 1234, Email: oracle@domain.com, Type: 3. Skipping.
Rec. already exists: Doc. ID: 1234, Email: dba@domain.com, Type: 1. Skipping.
Rec. already exists: Doc. ID: 1234, Email: dhunt@domain.com, Type: 1. Skipping.
Rec. already exists: Doc. ID: 1234, Email: admin@domain.com, Type: 2. Skipping.
Rec. already exists: Doc. ID: 1234, Email: cio@domain.com, Type: 4. Skipping.
Rec. already exists: Doc. ID: 5678, Email: oracle@domain.com, Type: 3. Skipping.
Rec. already exists: Doc. ID: 5678, Email: dba@domain.com, Type: 1. Skipping.
Rec. already exists: Doc. ID: 5678, Email: dhunt@domain.com, Type: 1. Skipping.
Rec. already exists: Doc. ID: 5678, Email: admin@domain.com, Type: 2. Skipping.
Rec. already exists: Doc. ID: 5678, Email: cio@domain.com, Type: 4. Skipping.

PL/SQL procedure successfully completed.
************************************************************************************

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[synapsevampire]

Very impressive, as always, SantaMufasa, aka Sandy.

My example data was just that, examples of the data, as my original post stated the file is a CSV (comma seperated values), not a text file.

But your example is amazing, and more complex so I should be able to easily adjust it to fit a CSV file.

External files are fantastic, once again, thanks for the education!

-k