Home » SQL & PL/SQL » Client Tools » Which ETL method should be used - SQL Loader or External Table (OWB) (Oracle 10G)
Which ETL method should be used - SQL Loader or External Table (OWB) [message #400597] Wed, 29 April 2009 00:56 Go to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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.
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 Go to previous messageGo to next message
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 340 times)
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 
or
                                     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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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

./fa/6114/0/

Scheduler? Check DBMS_JOB and/or DBMS_SCHEDULER.
  • Attachment: orafaq_2.png
    (Size: 40.43KB, Downloaded 1640 times)
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 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: Schema Manager
Next Topic: commant to reconnect session in sqlplus
Goto Forum:
  


Current Time: Sun Dec 04 10:14:42 CST 2016

Total time taken to generate the page: 0.10223 seconds