0

I'm trying to write some data to a file from oracle using pl/SQL. I seams like the script can only handle very small line sizes. when the linesize gets too big line breakes are added to the file, and I can't figure out why.

Please take a look.

This is my script code:

DECLARE
  fHandle  UTL_FILE.FILE_TYPE;
  filename VARCHAR2(32000) := 'fil';
BEGIN
  fHandle := UTL_FILE.FOPEN('dir', filename, 'w');
  FOR i IN (SELECT colA, colB, substr(colC,1,20) as colC, colD FROM table) 
--FOR i IN (SELECT colA, colB, colC, colD FROM table) 
  LOOP
    utl_file.put_line(fHandle, i.colA||';'||i.ColB||';'||i.colC||';'||i.colD);
  END LOOP;
  UTL_FILE.FCLOSE(fHandle);
END;

This is the data table:

G100000 1   "AXE DE GUIDAGE D.20h7 LG 700 - POUR DERIVEUR COURSE 500"                  Obsolete
G200000 2   "AXE DE GUIDAGE D.20h7 LG 700 - POUR DERIVEUR COURSE 500"                  Released
G300000 1   "*** IDEM D620203-09 *** AIR COMPRESSOR - 1089057554 - PRESS.TRANSDUCER"   Obsolete 
G400000 1   "*** IDEM D620203-08 *** AIR COMPRESSOR - 1089057470 - TEMPERATURE SENSOR" Obsolete

This is the file output:

G100000;1;AXE DE GUIDAGE D.20h;Obsolete
G200000;2;AXE DE GUIDAGE D.20h;Released
S300000;1;*** IDEM D620203-08 ;Obsolete
S400000;1;*** IDEM D620203-09 ;Obsolete

But if I exchange line 6 with line 7 I get this outcome:

G100000;1;*** IDEM D620203-08 ***
AIR COMPRESSOR - 1089057470 - TEMPERATURE SENSOR;Obsolete
G200000;2;AXE DE GUIDAGE D.20h7 LG 700 
- POUR DERIVEUR COURSE 500;Released
G300000;1;*** IDEM D620203-09 ***
AIR COMPRESSOR - 1089057554 - PRESS.TRANSDUCER;Obsolete
G400000;1;AXE DE GUIDAGE D.20h7 LG 700 
- POUR DERIVEUR COURSE 500;Obsolete

Any help is very much appreciated.

BR Kresten

Kresten
  • 810
  • 13
  • 36
  • 1
    Are you sure your table data doesn't just contain line break characters? If you use the `dump()` function to examine the data you can look for control characters, e.g. between `***` and `AIR`. – Alex Poole Mar 18 '19 at 12:19

2 Answers2

1
  1. Do you really get linebreaks or your text editor automatically breaks lines by length limit?
  2. Check "colC" column. The text in there might already have line breaks. Make sure your tool (the one which you used to dump the "table data" above) actually shows you line breaks. For some, you have to double-click on the table cell in the UI to open a "detail" viewer which can display new lines.
  3. If you have at least one CLOB column in your concatenation you may exceed max varchar2 size.
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
Amir Kadyrov
  • 1,253
  • 4
  • 9
1

From the output, it looks like the data in the table already contains line breaks / new line characters.

Try to replace them with spaces using

replace(replace(colC,chr(10),' '), chr(13),'')

Note: The first replacement works on Unix line breaks (LF), the second one handles Windows line breaks (CR+LF)

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • None of the above seems to help. This is a DUMP of one of the colC collumns. Is there any line breakes in this:Typ=1 Len=73: 42,42,42,32,73,68,69,77,32,68,54,50,48,50,48,51,45,48,56,32,42,42,42,13,10,65,73,82,32,67,79,77,80,82,69,83,83,79,82,32,45,32,49,48,56,57,48,53,55,52,55,48,32,45,32,84,69,77,80,69,82,65,84,85,82,69,32,83,69,78,83,79,82 – Kresten Mar 18 '19 at 13:16
  • @Kresten - yes, the `13,10` part between the run `42,42,42` (which is three asterisks) and `65,73,82` which is 'AIR'. This answer isn't quite right though, the calls to `ascii()` aren't needed - it should just use `chr(10)` and `chr(13)`. – Alex Poole Mar 18 '19 at 16:32
  • @AlexPoole Thanks for the edit. I was wondering about the `ascii()` function but got the example from dba-oracle.com and didn't dare to change it (I don't have an Oracle instance ATM). Not sure why it was in there in the first place: http://www.dba-oracle.com/t_removing_line_break_characters_column_data.htm – Aaron Digulla Mar 19 '19 at 14:35
  • Hmm, lots of mistakes on that page.Not a site I trust generally anyway, but that's a particularly poor example... *8-) – Alex Poole Mar 19 '19 at 14:49