Home » SQL & PL/SQL » SQL & PL/SQL » checking rows in a flat file
checking rows in a flat file [message #230952] Fri, 13 April 2007 09:32 Go to next message
AGARWALNIRU
Messages: 13
Registered: March 2007
Junior Member
I am transferring data from flat file to an external table using PL/SQL procedure. I can check the external table for how many rows were there after the transfer took place. My question is that I need to know how many rows are there in the flat file so that i can tally that the exact transfer happened??? or if there's any other logic to tally the results from the source to the table???
Please help
Re: checking rows in a flat file [message #230968 is a reply to message #230952] Fri, 13 April 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REAL external table. There is no transfert, no PL/SQL. All rows are there.

Regards
Michel
Re: checking rows in a flat file [message #230982 is a reply to message #230968] Fri, 13 April 2007 11:09 Go to previous messageGo to next message
AGARWALNIRU
Messages: 13
Registered: March 2007
Junior Member
can u please elaborate your answer.....I appreciate your help.Thank you!!!
Re: checking rows in a flat file [message #230985 is a reply to message #230982] Fri, 13 April 2007 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=external+table&tab_id=&format=ranked
Regards
Michel
Re: checking rows in a flat file [message #230988 is a reply to message #230985] Fri, 13 April 2007 11:26 Go to previous messageGo to next message
AGARWALNIRU
Messages: 13
Registered: March 2007
Junior Member
thanks again!!!
Re: checking rows in a flat file [message #231043 is a reply to message #230985] Fri, 13 April 2007 14:20 Go to previous messageGo to next message
AGARWALNIRU
Messages: 13
Registered: March 2007
Junior Member
sorry!! i cudnt find answer to my question..I looked at the link you sent butit didnt help
Re: checking rows in a flat file [message #231047 is a reply to message #231043] Fri, 13 April 2007 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way could be creating two external tables, both looking at the same flat file.

The first, original one, would look like it really should and have all columns. The second one should have only one large column (something like VARCHAR2(4000) - large enough to store the whole flat file record).

Then you'd be able to compare values from

SELECT COUNT(*) FROM fist_original_external_table

and

SELECT COUNT (*) FROM long_record_external_table.
Re: checking rows in a flat file [message #231245 is a reply to message #231043] Mon, 16 April 2007 01:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What Michael is saying is that instead of using your lovingly handcrafted piece of Pl/Sql to load data from an external file into the database, you shoud instead create that external file as an External Table and let Oracle take care of the data load for you. Once the external data is available as a table, you can use normal SQL on it to transform the data however you wish.

If you still want to do it the hard way, you can use utl_file to step through the original flat file one line at a time and count the rows in it like that.
Previous Topic: Check write access on a folder/file
Next Topic: Need Help With SQL Statement
Goto Forum:
  


Current Time: Tue Dec 06 11:57:07 CST 2016

Total time taken to generate the page: 0.11951 seconds