Home » SQL & PL/SQL » SQL & PL/SQL » External Tables
External Tables [message #217302] Thu, 01 February 2007 10:33 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

I need a small favour from you,

I am creating an External table to read data from csv file. But when the csv file is saved it creates a blank row at the end of file. I am not able to match the file records from the database tables because of the last blank line. Last row is always rejected. I tried to trim the column but still it is taking the last row. Can you please suggest me.

ddl := 'CREATE TABLE EXTERNAL_SANCTIONS (
STRNAME1 VARCHAR2(100),
STRNAME2 VARCHAR2(100),
STRNAME3 VARCHAR2(100),
STRNAME4 VARCHAR2(100),
STRNAME5 VARCHAR2(100),
STRNAME6 VARCHAR2(100),
STRTITLE VARCHAR2(100),
DTBIRTH VARCHAR2(100))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ' || vs_sanctionsdir || '
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOLOGFILE
FIELDS TERMINATED BY' || '''' || vc_seperator || '''' ||
'missing field VALUES are NULL) LOCATION (' || '''' || vc_file_name || '''' || '))
PARALLEL 5
REJECT LIMIT UNLIMITED';
EXECUTE IMMEDIATE ddl;


Thank You.
Re: External Tables [message #217325 is a reply to message #217302] Thu, 01 February 2007 12:46 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
An empty row is created at the end of a file when the CSV is being saved.
Last row is always empty.
Last row is always rejected.

What is so important in the last row so that you'd want to bother with it? Why don't you just live it alone? It is empty, it is rejected - so what?

Not very helpful, eh?
Re: External Tables [message #217329 is a reply to message #217325] Thu, 01 February 2007 13:21 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

The problem is when i take the Secondlast row(Row Before the blank row) to check if it exists in database it does not match. Thats why it rejects the Second last row.
Re: External Tables [message #217387 is a reply to message #217329] Thu, 01 February 2007 23:43 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; so it may be that, at the "Save file" point, application adds a blank line. It might be a LF (line feed), but should be CR/LF (carriage return/line feed) instead. Could you check it using an editor which has an "Show line endings" option (Notepad2, a freeware, is one of them)?

What to do? I believe there is a smarter way to do it, but - could you search for CR and compare line contents before this sign with the table contents (and ignore the rest)?
Previous Topic: Use of Instead of trigger with example (merged)
Next Topic: Creation of materialized view
Goto Forum:
  


Current Time: Wed Dec 07 04:56:18 CST 2016

Total time taken to generate the page: 0.12393 seconds