Home » SQL & PL/SQL » SQL & PL/SQL » Processing data from flat files to base files via staging tables
Processing data from flat files to base files via staging tables [message #617506] Tue, 01 July 2014 00:12 Go to next message
CSR24
Messages: 2
Registered: June 2014
Junior Member
How is the process of validation of data done from flat files to base tables via staging tables using shell scripts.
Re: Processing data from flat files to base files via staging tables [message #617507 is a reply to message #617506] Tue, 01 July 2014 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not done unless you do it.

Re: Processing data from flat files to base files via staging tables [message #617509 is a reply to message #617506] Tue, 01 July 2014 01:57 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Steps at high level.

1. Using SQL loader load data from flat file to staging table (do a plain data load)
2. Apply validations and move only selctive records to base tables.

Other approach can be, use awk scripting and run validations on flat file and load only selective records to base table. Stage table load is not required in this case.
Re: Processing data from flat files to base files via staging tables [message #617510 is a reply to message #617509] Tue, 01 July 2014 02:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
elaiyavel wrote on Tue, 01 July 2014 12:27
Steps at high level.
1. Using SQL loader load data from flat file to staging table (do a plain data load)
2. Apply validations and move only selctive records to base tables.


I would rather say it is 20th century method. External tables should be given preference. Both your steps could be done at one go.
Re: Processing data from flat files to base files via staging tables [message #617512 is a reply to message #617509] Tue, 01 July 2014 03:30 Go to previous messageGo to next message
CSR24
Messages: 2
Registered: June 2014
Junior Member
"Steps at high level.

1. Using SQL loader load data from flat file to staging table (do a plain data load)
2. Apply validations and move only selctive records to base tables. "

Thanks for the reply its really helpful.
Can I get a more detailed information on how its done , any links.

Thanks again.

[Updated on: Tue, 01 July 2014 03:31]

Report message to a moderator

Re: Processing data from flat files to base files via staging tables [message #617513 is a reply to message #617512] Tue, 01 July 2014 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

external table

Re: Processing data from flat files to base files via staging tables [message #617532 is a reply to message #617510] Tue, 01 July 2014 06:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Lalit Kumar B wrote on Tue, 01 July 2014 03:02
I would rather say it is 20th century method.


And why would you say that? There are many reasons to use SQL*Loader in 21 century. And it's a shame if rumours Oracle is deprecating SQL*Loader are true. But I doubted they are true. Otherwise we would end up in a situation where Oracle is pushing Exadata and other appliances where we can't easily place data feeds and deprecating SQL*Loader at the same time. Another reason could be performance (database server load versus application server load) and security.

SY.
Re: Processing data from flat files to base files via staging tables [message #617533 is a reply to message #617532] Tue, 01 July 2014 06:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Solomon Yakobson wrote on Tue, 01 July 2014 16:31
Lalit Kumar B wrote on Tue, 01 July 2014 03:02
I would rather say it is 20th century method.


And why would you say that?


It was my response specific to the steps mentioned by elaiyavel. I would go with external table and do it at one go.

Quote:
There are many reasons to use SQL*Loader in 21 century.

I agree, and I use it quite often depending on the purpose. I work for a legacy banking application and we do not have external tables because our only purpose is to load and nothing else. And we don't have permissions to create a directory object. So sqlldr works ok for us just for loading purpose.
Re: Processing data from flat files to base files via staging tables [message #617654 is a reply to message #617533] Wed, 02 July 2014 09:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... which, actually, is its purpose.
Re: Processing data from flat files to base files via staging tables [message #617663 is a reply to message #617654] Wed, 02 July 2014 10:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes, agreed, if thats the only purpose.

If I was not clear, I meant OP wouldn't require staging table to completely dump the file data using ext tables, unless he really wants it. He could manipulate or filter it and have the required data in the main table. Ext table has lot of other advantages too.
Re: Processing data from flat files to base files via staging tables [message #617825 is a reply to message #617663] Thu, 03 July 2014 15:19 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It has one disadvantage when compared to SQL*Loader: can't load files stored locally.
Previous Topic: Another Hierarchical circular reference question
Next Topic: Pass sql file as argument in oracle stored procedure/package
Goto Forum:
  


Current Time: Fri Mar 29 06:13:33 CDT 2024