Blanc lines in dbms_output.put_line

[Gerrit van den Ham]

The following is a part of a SQL script i'm executing.
I don't understand why i get several blanc lines between some dbms_output lines in the output tab.

for r_select in c_select
loop

for r_groepen in c_groepen(r_select.ask_id)
loop
delete from aos_ask_groepen
where id = r_groepen.grp_id;
dbms_output.put_line ('delete from aos_ask_groepen where id ='|| r_groepen.grp_id);
end loop;

delete from aos_ask_groep_afspraken
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_ask_groep_afspraken where ask_id ='|| r_select.ask_id);

delete from aos_cmr_groep_afspraak_bevest
where bsd_id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_cmr_groep_afspraak_bevest where bsd_id ='|| r_select.bsd_id);

for r_documenten in c_documenten(r_select.ask_id)
loop
delete from aos_dct_documenten
where id = r_documenten.id;
dbms_output.put_line ('delete from aos_dct_documenten where id ='|| r_documenten.id);
end loop;

delete from aos_cmr_afspraak_brieven
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_cmr_afspraak_brieven where ask_id ='|| r_select.ask_id);

delete from aos_ask_afspraak_notities
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_ask_afspraak_notities where ask_id ='|| r_select.ask_id);

delete from aos_cmr_bevestiging_brieven
where bsd_id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_cmr_bevestiging_brieven where bsd_id = '||r_select.bsd_id);

delete from aos_cmr_afspraak_relaties
where ask_id = r_select.ask_id;
dbms_output.put_line('delete from aos_cmr_afspraak_relaties where ask_id ='|| r_select.ask_id);

end loop;

 

[Marco Kalter]

Can you let me know your exact PL/SQL Developer version?

 

[Gerrit van den Ham]

Marco,

Version 7.1.5.1397

 

[Marco Kalter]

I cannot reproduce this. Can you send me a test script that produces this issue for you>

 

[Gerrit van den Ham]

Marcel,

Here is my script.

-- PACTA SCHONING
-- Opruimen van Afspraken in Pacta, met bijbehorende referenties
-- tabellen in in aos_abr_jobs (functioneel het takenoverzicht) worden niet opgeruimd
-- aangezien deze tabellen aan de gebruiker_id hangen.

DECLARE

CURSOR C_SELECT
IS
select bsg.ask_id, bsg.bsd_id
from
aos_rle_beschikbaarheid bsd,
aos_rle_bedrijven bdf,
aos_cmr_afspraak_bevestigingen bsg
where bdf.id = bsd.bdf_id
and bsg.ask_id = 9477
and bsg.bsd_id = bsd.id
and bsd.bte_id = 3
and bdf.code in('AMSD2', 'ALKW0');

CURSOR C_GROEPEN
(b_ask_id number)
IS
select *
from aos_ask_groep_afspraken
where ask_id = b_ask_id;

CURSOR C_DOCUMENTEN
(b_ask_id number)
IS
select *
from aos_cmr_afspraak_brieven
where id = b_ask_id;

CURSOR C_ASK_STATUS
(b_ask_id number)
IS
select *
from aos_ask_status_changes
where ask_id = b_ask_id;

BEGIN
for r_select in c_select
loop

for r_groepen in c_groepen(r_select.ask_id)
loop
delete from aos_ask_groepen
where id = r_groepen.grp_id;
dbms_output.put_line ('delete from aos_ask_groepen where id ='|| r_groepen.grp_id);
end loop;

delete from aos_ask_groep_afspraken
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_ask_groep_afspraken where ask_id ='|| r_select.ask_id);

delete from aos_cmr_groep_afspraak_bevest
where bsd_id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_cmr_groep_afspraak_bevest where bsd_id ='|| r_select.bsd_id);

for r_documenten in c_documenten(r_select.ask_id)
loop
delete from aos_dct_documenten
where id = r_documenten.id;
dbms_output.put_line ('delete from aos_dct_documenten where id ='|| r_documenten.id);
end loop;

delete from aos_cmr_afspraak_brieven
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_cmr_afspraak_brieven where ask_id ='|| r_select.ask_id);

delete from aos_ask_afspraak_notities
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_ask_afspraak_notities where ask_id ='|| r_select.ask_id);

delete from aos_cmr_bevestiging_brieven
where bsd_id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_cmr_bevestiging_brieven where bsd_id = '||r_select.bsd_id);

delete from aos_cmr_afspraak_relaties
where ask_id = r_select.ask_id;
dbms_output.put_line('delete from aos_cmr_afspraak_relaties where ask_id ='|| r_select.ask_id);

delete from aos_cmr_afspraak_locaties_uwv
where ask_id = r_select.ask_id;
dbms_output.put_line('delete from aos_cmr_afspraak_locaties_uwv where ask_id ='|| r_select.ask_id);

delete from aos_cmr_afspraak_bevestigingen
where ask_id = r_select.ask_id;
dbms_output.put_line ('delete from aos_cmr_afspraak_bevestigingen where ask_id ='|| r_select.ask_id);

delete from aos_ask_brieven
where bsd_id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_ask_brieven where bsd_id = '||r_select.bsd_id);

delete from aos_rle_beschikbaarheid
where id = r_select.bsd_id;
dbms_output.put_line ('delete from aos_rle_beschikbaarheid where id = '||r_select.bsd_id);

delete from aos_ask_afspraken
where id = r_select.ask_id;
dbms_output.put_line ('delete from aos_ask_afspraken where id = '||r_select.ask_id);

for r_ask_status in c_ask_status(r_select.ask_id)
loop
delete from aos_ask_status_changes
where id = r_ask_status.id;
dbms_output.put_line('delete from aos_ask_status_changes where id = ' || r_ask_status.id);
end loop;

commit;

end loop;

EXCEPTION
WHEN OTHERS THEN
rollback;
DBMS_OUTPUT.PUT_LINE('ERROR : '||SQLERRM);
END;

 

[Marco Kalter]

I don't have the necessary tables/views/data to run this. Can you send a complete example?

 

[Gerrit van den Ham]

Marco,

It's too much work for me to do this.
In SQL*Plus it runs fine.
Let's stop this topic and thanks.

 

[cassiusdrow]

Sorry to bump an old thread but I found a simple example that shows extra lines in the dbms_output in PL/SQL Developer v7.0.3.1123. It looks like adding a tab causes the issue for me.

BEGIN
   dbms_output.put_line( REPLACE( 'Test with tabs', ' ', CHR(9)));
   dbms_output.put_line( 'Test with spaces');
   dbms_output.put_line( REPLACE( 'Test with tabs', ' ', CHR(9)));
   dbms_output.put_line( 'Test with spaces');
   dbms_output.put_line( REPLACE( 'Test with tabs', ' ', CHR(9)));
   dbms_output.put_line( 'Test with spaces');
END;
/

This produces this output:

Test	with	tabs

Test with spaces
Test	with	tabs

Test with spaces
Test	with	tabs

Test with spaces

When sqlplus produces this output:

Test    with    tabs
Test with spaces
Test    with    tabs
Test with spaces
Test    with    tabs
Test with spaces

 

[Marco Kalter]

This is fixed in the current 7.1.5 release.