Read file with multiple empty lines from ORACLE DB with BASH

Question

Possible Duplicate:
why empty line is not getting stored in variable

I try to read and output a textfile from my database. It works but empty lines will be deleted.

EXAMPLE:

Hello Mr. X

Text from Mailboddy

Greetins
Mr. Y

OUTPUT:

Hello Mr. X
Text from Mailboddy
Greetins
Mr. Y

CODE:

sqlplus -s $DBLOGIN <<ENDE_SQL > file
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
DECLARE
  l_text CLOB;
BEGIN
  SELECT text
    INTO l_text
    FROM table;  
  while dbms_lob.substr(l_text, 1, l_pos) is not null LOOP
    if dbms_lob.substr(l_text, 2, l_pos) = CHR(13) || CHR(10) then
      DBMS_OUTPUT.NEW_LINE;
      l_pos:=l_pos + 1;
    else
      DBMS_OUTPUT.put(dbms_lob.substr(l_text, 1, l_pos));
    end if;
    l_pos:=l_pos + 1;
  END LOOP;
END;
/
ENDE_SQL

The PL/SQL Code works in SQLDeveloper with the empty lines. But in the file all empty lines are removed.

The question is similar to this: http://stackoverflow.com/questions/9931974/why-empty-line-is-not-getting-stored-in-variable — user1482309, Oct 08 '12 at 14:17
u need to remove empty lines so store the output then cat file |sed '/^$/d' for final result — V H, Oct 08 '12 at 16:38
This isn't really a duplicate; the other question is about shell behaviour, this is is about how Oracle's `dbms_output` works, and nothing to do with the shell. — Alex Poole, Oct 10 '12 at 09:18

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Assuming the data is loaded into the CLOB with the line breaks as CHR(13)||CHR(10), and you can see it in the expected format if you just select directly from the table, then the problem is with how SQL*Plus is interacting with DBMS_OUTPUT.

By default, SET SERVEROUTPUT ON sets the FORMAT to WORD_WRAPPED. The documentation says 'SQL*Plus left justifies each line, skipping all leading whitespace', but doesn't note that this also skips all blank lines.

If you set SERVEROUTPUT ON FORMAT WRAPPED or ... TRUNCATED then your blank lines will reappear. But you need to make sure your linesize is wide enough for the longest possible line you want to print, particularly if you go with TRUNCATED.

(Also, your code is not declaring l_pos NUMBER := 1, and is missing a final DBMS_OUTPUT.NEW_LINE so you'll lose the final line from the CLOB).

To demonstrate, if I create a dummy table with just a CLOB column, and populate it with a value that has the carriage return/linefeed you're looking for:

create table t42(text clob);

insert into t42 values ('Hello Mr. X' || CHR(13) || CHR(10)
    || CHR(13) || CHR(10)
    || 'Text from Mailboddy' || CHR(13) || CHR(10)
    || CHR(13) || CHR(10)
    || 'Greetins' || CHR(13) || CHR(10)
    || 'Mr. Y');

select * from t42;

I get:

TEXT
--------------------------------------------------------------------------------
Hello Mr. X

Text from Mailboddy

Greetins
Mr. Y

Using your procedure (very slightly modified so it will run):

sqlplus -s $DBLOGIN <<ENDE_SQL > file
SET FEEDBACK OFF;
SET SERVEROUTPUT ON FORMAT WORD_WRAPPED; -- setting this explicitly for effect
DECLARE
  l_text CLOB;
  l_pos number := 1; -- added this
BEGIN
  SELECT text
    INTO l_text
    FROM t42;
  while dbms_lob.substr(l_text, 1, l_pos) is not null LOOP
    if dbms_lob.substr(l_text, 2, l_pos) = CHR(13) || CHR(10) then
      DBMS_OUTPUT.NEW_LINE;
      l_pos:=l_pos + 1;
    else
      DBMS_OUTPUT.put(dbms_lob.substr(l_text, 1, l_pos));
    end if;
    l_pos:=l_pos + 1;
  END LOOP;
  dbms_output.new_line; -- added this
END;
/

ENDE_SQL

file contains:

Hello Mr. X
Text from Mailboddy
Greetins
Mr. Y

If I only change one line in your code, to:

SET SERVEROUTPUT ON FORMAT WRAPPED;

then file now contains:

Hello Mr. X

Text from Mailboddy

Greetins
Mr. Y

You might want to consider UTL_FILE for this, rather than DBMS_OUTPUT, depending on your configuration. Something like this might give you some pointers.

Read file with multiple empty lines from ORACLE DB with BASH

1 Answers1