Home » SQL & PL/SQL » SQL & PL/SQL » 'records delimited by' in external tables
'records delimited by' in external tables [message #242820] Tue, 05 June 2007 04:05 Go to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Hi,

I have a question regarding the 'records delimited by' clause for the creation of external tables. I noticed, that although I specified the delimiter to be NEWLINE, it does not work correctly and I have to overwrite it with '\n' manually.

Now the problem is, what happens if in my ETL process I don't know if the file will be in unix or in windows format. How can I dynamically find out which record delimiter I need to specify when creating the external table?

Best regards,
moschen
Re: 'records delimited by' in external tables [message #242829 is a reply to message #242820] Tue, 05 June 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEWLINE indicates the newline character(s) for the server platform.
If your file comes from another platform you have to specify the character used for newline on it.
If your file may come from different platform, you have to modify your input files to always fit a unique rule.

Regards
Michel
Re: 'records delimited by' in external tables [message #249931 is a reply to message #242829] Fri, 06 July 2007 09:55 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Thanks for your reply!

Do you know if there is a way to determine the line terminator from within PLSQL. I tried:

declare
  lv_filehandle UTL_FILE.file_type;
  lv_buffer  RAW(32767) := '';
  lv_instr_index number; 
begin
  --lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_16.txt', 'r', 32767);
  --lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_mac.txt', 'r', 32767);
  --lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_unix.txt', 'r', 32767);
  lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_windows.txt', 'r', 32767);

  UTL_FILE.GET_RAW(lv_filehandle, lv_buffer, 32767);
  dbms_output.put_line('row in HEX: '|| lv_buffer);

  if instr(lv_buffer,'000D000A') > 0 then 
    dbms_output.put_line('16-WIN: '|| instr(lv_buffer,'000D000A'));
  elsif instr(lv_buffer,'000A') > 0 then
    dbms_output.put_line('16-UNX: '|| instr(lv_buffer,'000A'));
  elsif instr(lv_buffer,'000D') > 0 then
    dbms_output.put_line('16-MAC: '|| instr(lv_buffer,'000D'));
  elsif instr(lv_buffer,'0D0A') > 0 then
    dbms_output.put_line('WIN: '|| instr(lv_buffer,'0D0A'));
  elsif instr(lv_buffer,'0A') > 0 then
    dbms_output.put_line('UNX: '|| instr(lv_buffer,'0A'));
  elsif instr(lv_buffer,'0D') > 0 then
    dbms_output.put_line('MAC: '|| instr(lv_buffer,'0D'));
  end if;
  UTL_FILE.FCLOSE(lv_filehandle);
end;


Oracle crashes on Mac files:
Error at line 1
ORA-29284: file read error
ORA-06512: at "SYS.UTL_FILE", line 119
ORA-06512: at "SYS.UTL_FILE", line 1100
ORA-06512: at line 11


Unix files are ok.

Windows files are somehow internaly converted into unix files. So searching for '0D0A' does not work as Oracle is apparently converting it to '0A' when using UTL_FILE.GET_RAW.

Best regards,
M.

[Updated on: Fri, 06 July 2007 09:57]

Report message to a moderator

Re: 'records delimited by' in external tables [message #249945 is a reply to message #249931] Fri, 06 July 2007 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you care about that?
Oraclr knows it for you.
Just use NEWLINE keyword.

Regards
Michel
Re: 'records delimited by' in external tables [message #249948 is a reply to message #242820] Fri, 06 July 2007 10:33 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Like mentioned in my first post, Oracle doesn't know it. If I want to create an external table on a file I need to know the line terminator, as otherwise it 'assumes', like you mentioned before, the line terminator form the server platform, which might not be correct.

Therefore it happens, that the creation of the external table fails if the file is not delimited by the server platform default line terminator.

Best regards,
M.
Re: 'records delimited by' in external tables [message #249953 is a reply to message #249948] Fri, 06 July 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So use a file transfer program that handles correctly the end of line that is all those I know when you specify them it is an ascii transfer or the source is a text file.

You try to solve the problem from the wrong end.
Solve it at the source not at the final step.

Regards
Michel
Re: 'records delimited by' in external tables [message #249967 is a reply to message #249953] Fri, 06 July 2007 11:14 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Well the problem is that this is a costraint on customer side, so we are not free to choose how and with which application the files are transferred. But before asking to have an external application, which would cause a hugh problem in this project, I would like to know if it is solveable in PLSQL.

Best regards,
M.
Re: 'records delimited by' in external tables [message #249974 is a reply to message #249967] Fri, 06 July 2007 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want or are unable to ask your customer to send you correct file, it's up to you.

Regards
Michel
Re: 'records delimited by' in external tables [message #250280 is a reply to message #249974] Mon, 09 July 2007 07:56 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
So the question remains: Is it technically feasible to do it in oracle?

Regards,
M.
Re: 'records delimited by' in external tables [message #250287 is a reply to message #250280] Mon, 09 July 2007 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is end of line if end of line may not be end of line (but also may be)?
In a clear way, this is the question you ask.

Regards
Michel
Re: 'records delimited by' in external tables [message #250289 is a reply to message #242820] Mon, 09 July 2007 08:50 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Excellent point.

What may be a newline character coming from one environment, may be actual data and need to be processed when coming from another environment.

When should it be treated as a newline and when should it be treated as data ?

You either need a consistent file format or a different external table for each environment.

As Michel has said, to try and "fix" this during processing is not the proper approach.

[Updated on: Mon, 09 July 2007 08:51]

Report message to a moderator

Re: 'records delimited by' in external tables [message #250299 is a reply to message #250289] Mon, 09 July 2007 10:25 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Hi,

we are reading plain text files from three different environments (unix, windows, mac). So the question if a hex code '0A' or '0D' needs to be treated as data is fortunately not an issue. I just would like to know if it is somehow feasible using standard oracle functionality. Maybe it's possible and if the the question can be answered in 5 lines of code, why not?

Best regards,
M.
Re: 'records delimited by' in external tables [message #251797 is a reply to message #250299] Mon, 16 July 2007 12:34 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:23325542868374#25691282045922
Re: 'records delimited by' in external tables [message #252078 is a reply to message #251797] Tue, 17 July 2007 15:00 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Hi Andrew,

thanks a lot for your hint!! I will give it a try.

Best regards,
M.
Re: 'records delimited by' in external tables [message #252249 is a reply to message #252078] Wed, 18 July 2007 06:08 Go to previous messageGo to next message
moschen
Messages: 25
Registered: April 2007
Junior Member
Andrew, your tip was excellent!

So the only thing that needed to be changed in the initial code example is 'r' to 'rb'. Afterwards it works like a charm.

Example:

declare
  lv_filehandle UTL_FILE.file_type;
  lv_buffer  RAW(32767) := '';
  lv_instr_index number; 
begin
  --lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_mac.txt', 'rb', 32767);
  --lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_unix.txt', 'rb', 32767);
  lv_filehandle := UTL_FILE.fopen ('FILE_DIR', 'test_windows.txt', 'rb', 32767);

  UTL_FILE.GET_RAW(lv_filehandle, lv_buffer, 32767);
  dbms_output.put_line('row in HEX: '|| lv_buffer);

  if instr(lv_buffer,'0D0A') > 0 then
    dbms_output.put_line('WIN: '|| instr(lv_buffer,'0D0A'));
  elsif instr(lv_buffer,'0A') > 0 then
    dbms_output.put_line('UNX: '|| instr(lv_buffer,'0A'));
  elsif instr(lv_buffer,'0D') > 0 then
    dbms_output.put_line('MAC: '|| instr(lv_buffer,'0D'));
  end if;
  UTL_FILE.FCLOSE(lv_filehandle);
end;


Restriction is of course, that the line break has to be within the first 32767 bytes.

Best regards,
Andre

By the way, with 'wb' you will also be able to prevent oracle from putting a line break behind the last line when closing a file.

[Updated on: Wed, 18 July 2007 07:12]

Report message to a moderator

Re: 'records delimited by' in external tables [message #252383 is a reply to message #252249] Wed, 18 July 2007 13:28 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
thanks for the feedback - I wan't aware of rb.
Previous Topic: Insert or/and update
Next Topic: sorting and query tuning
Goto Forum:
  


Current Time: Thu Dec 08 14:43:14 CST 2016

Total time taken to generate the page: 0.15210 seconds