Home » SQL & PL/SQL » SQL & PL/SQL » External table query (compare number records in file with external table) (Oracle 11g)
External table query (compare number records in file with external table) [message #575473] Wed, 23 January 2013 08:57 Go to next message
scrapjack
Messages: 2
Registered: January 2013
Location: UK
Junior Member
Hi there

I wonder if someone could help me please.

I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.

I have attached the code that creates the external table and populates it.

Thanks
  • Attachment: forum.sql
    (Size: 1.90KB, Downloaded 60 times)
Re: External table query (compare number records in file with external table) [message #575474 is a reply to message #575473] Wed, 23 January 2013 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

[Updated on: Wed, 23 January 2013 09:27]

Report message to a moderator

Re: External table query (compare number records in file with external table) [message #575484 is a reply to message #575473] Wed, 23 January 2013 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Investigate in the different options that exist when you create or alter an external table, for instance REJECT LIMIT or BADFILE or PROJECT COLUMN...

Regards
Michel

Re: External table query (compare number records in file with external table) [message #575508 is a reply to message #575473] Wed, 23 January 2013 13:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
So what's the problem? You can set reject limit to 0. Then selecting from external table will fail if at least one record is rejected. And external table allows you to indicate where to put log, bad, discard files, which you can then read (e.g. using UTL_FILE) and analyze.

SY.
Re: External table query (compare number records in file with external table) [message #575509 is a reply to message #575508] Wed, 23 January 2013 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which you can then read (e.g. using UTL_FILE)


or another external table which allows to do easily all the stuff with SQL and PL/SQL.

Regards
Michel
Re: External table query (compare number records in file with external table) [message #575510 is a reply to message #575473] Wed, 23 January 2013 14:09 Go to previous message
scrapjack
Messages: 2
Registered: January 2013
Location: UK
Junior Member
Michael / Solomon

Thanks so much for pointing me in the right direction. I will post again when I've looked into it.

Kind Regards
Lisa
Previous Topic: Materialized view Refresh
Next Topic: SQL- Outer join 3 tables
Goto Forum:
  


Current Time: Mon Oct 20 00:33:37 CDT 2014

Total time taken to generate the page: 0.10523 seconds