OraFAQ Forum: Client Tools » Which ETL method should be used

Home » Other » Client Tools » Which ETL method should be used - SQL Loader or External Table (OWB) (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Which ETL method should be used - SQL Loader or External Table (OWB) [message #400597]

Wed, 29 April 2009 00:56

dbguy
Messages: 3
Registered: April 2009

Junior Member

I have a doubt regarding which ETL method will be useful and better for below given scenario

• We must present a solution with ORACLE 10G and Java.
• It is going to be created a Data Base in Oracle, that mainly is conformed by tables that are loaded from a files generated from others applications.
• Source files (mainly ".CSV" file) may correspond with two different target tables.
• The loadings of these files must be done with programmed tasks and through ETLs.
• Each loading done must generate a registry in a LOG which indicates the Success or Failure of the loading.
• In case of a failure, it must be sent an alert (can be a mail) to whom the bank indicates, informing the error.
• In any Success case or failure this LOG can be consultable.

As per my understanding, there are 2 ways to handle above given scenario in Oracle.
1. Using SQL Loader (need to write scripts and procedures)
2. Using External Tables (using OWB)

With this information, I hope you can suggest me which approach would be better. If any other information is needed, let me know.

While suggesting a solution, please also keep in mind that I am new to Oracle.

TIA.

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #400601 is a reply to message #400597]

Wed, 29 April 2009 01:32

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, you'll need to "write scripts and procedures" whichever approach you take. "External tables" doesn't necessarily mean "OWB" (I presume you mean "Oracle Warehouse Builder" by that) because they can be used from (PL/)SQL as well.

Anyway: as you need to log success or failure and send an e-mail message, perhaps external tables are a better choice because SQL*Loader is incapable of doing that (directly, that is). You can take a look at its log/bad/discard file(s), write database triggers and/or procedures and/or submit jobs that will check whether everything was OK, but I still think that external tables provide better flexibility.

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #400638 is a reply to message #400601]

Wed, 29 April 2009 03:20

dbguy
Messages: 3
Registered: April 2009

Junior Member

Thanks so much.
I am attaching a flowchart with my reply. Please confirm if my understanding with ETL is correct.
Since I have no experience with Oracle, you may find my queries little bit useless. In such a case, I am sorry for that.

Thanks again.

Attachment: ETL.jpg
(Size: 109.31KB, Downloaded 1327 times)

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #400646 is a reply to message #400638]

Wed, 29 April 2009 03:49

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Ummm ... no, you have two SQL*Loaders. Once loading is done, you don't use it any more. It should / might be as follows:

         SQL*Loader
CSV file ----------> target table

                                     SQL or PL/SQL procedure
CSV file (used as an external table) -----------------------> target table

"External table" is, actually, the "original" (CSV) file. You do create it (as an Oracle object), but don't "store values" in there.

SQL*Loader produces log file; it says whether everything was OK or not (for example, "5 records not loaded due to some errors") but you have to check it manually (or write some script that will do that for you).

(PL/)SQL procedure is simpler; basically, it might require just a simple query, such as

INSERT INTO target_table
SELECT * FROM external_table;

Of course, you may add additional logic, certain functions, exception handlers, blabla to the process, put everything into a PL/SQL procedure which will then send an e-mail etc. However, the main principle remains the same:

         SQL*Loader or (PL/)SQL
CSV file ----------------------> target table

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #400704 is a reply to message #400646]

Wed, 29 April 2009 05:43

dbguy
Messages: 3
Registered: April 2009

Junior Member

Thanks again.
I have updated that flow chart based on your latest comment. I am enclosing it herewith.
Please look into it and correct me If my understanding is wrong.

In addition, as far as scheduler is concerned, do we need to write a cron shell script or it can be done by Oracle scheduler?

Thanks

Attachment: ETL_Updated.jpg
(Size: 108.05KB, Downloaded 1314 times)

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #400729 is a reply to message #400704]

Wed, 29 April 2009 06:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It still looks a little bit complicated; why would you first "verify data (SP)" and then "transform data with the same SP"? This can/should be done in one step.

Once again: CSV file is your external table. You do not "upload data to external table"; you may, though "upload data into a temporary table using SQL*Loader", and then insert data from this "temporary" table into a target table. However, why two steps if you can do that in one step? Also, note that "temporary" tables are not to be used as in some other databases (where people create and drop these "temporary" tables all the time). If you enjoy in temporary tables, search for Oracle's "global temporary tables".

Your stored procedure is capable of doing the whole job:
- read external table
- transform these data (if necessary)
- insert data into a target table
- handle exceptions (insert failures into a LOG table)
- send notification

/forum/fa/6114/0/

Scheduler? Check DBMS_JOB and/or DBMS_SCHEDULER.

Attachment: orafaq_2.png
(Size: 40.43KB, Downloaded 4214 times)

Report message to a moderator

Re: Which ETL method should be used - SQL Loader or External Table (OWB) [message #401112 is a reply to message #400704]

Fri, 01 May 2009 12:32

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>• Source files (mainly ".CSV" file) may correspond with two different target tables.

How do you get these CSV Files? Generated by other apps?
Be aware that External Tables are server side only.
These files should be visible in database server.

All other concerns are already addressed by LittleFoot.

Report message to a moderator

Previous Topic:	Schema Manager
Next Topic:	commant to reconnect session in sqlplus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 03 02:32:33 CST 2025