extract data with a script

[whip]

Let's see if I can explain this. What I do nowadays is generate data using PL/SQL and then process it with external programs. Therefore I need a method to extract my generated data (which is text only) to a file. My idea is to use a Command window which would start the generator program and then do the extraction.

The generator creates strings which usually contain a single line of text but this string can also be empty which needs to be output as an empty line. The string can also contain multiple lines (ie. LF characters).

What I tried is insert these strings in the generator program into a table then spool the table data to a file in a Command window. This method has problems with both empty lines and multi-line strings. Empty lines do not appear in the output and multi-line strings have an extra LF character appended.

Does anyone have an idea on how to solve these problems?

Tom

 

[Marco Kalter]

How exactly does this script look that generates these extrac LF characters and omits empty lines?

 

[whip]

A simplified script is the following:

create table test(
ID NUMBER,
TEXT VARCHAR2(4000)
);

insert into TEST (ID, TEXT)
values (1, 'before multi');
insert into TEST (ID, TEXT)
values (2, 'multi 1' || chr(10) || 'multi 2');
insert into TEST (ID, TEXT)
values (3, 'after multi');
insert into TEST (ID, TEXT)
values (4, 'before empty line');
insert into TEST (ID, TEXT)
values (5, null);
insert into TEST (ID, TEXT)
values (6, 'after empty line');
commit;
prompt selecting text only
select text from test order by id;

prompt selecting >text<
select '>' || text || '

 

[Marco Kalter]

Thanks, I was able to reproduce this issue with this script. We'll fix it.