We help IT Professionals succeed at work.

how to preserve a line break in Sql Plus ?

I have a query whose output look like this:
Description output
When I run this in a command window, it doesn't preserve the line break
Description in command window.
How can I preserve the line break in Sql Plus ?
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
I assume you are talking about the blank line between 'major.' and 'A department'?

Looks like the line break is there since the text did break.  It just didn't create the 'blank' line.

You need to find out what line break character is in the text and adjust it accordingly (possibly with a simple replace).

See what this does in sqlplus:
select 'hello' || chr(10) || chr(10) || chr(10) || 'World' from dual;
Most Valuable Expert 2011
Top Expert 2012
Commented:
is your output from dbms_output?

if so, removing blank lines is a "feature"
Gadsden ConsultingIT Specialist

Author

Commented:
Sql Plus command window doesn't keep the blank lines,  a Sql Window in Pl Sql Developer does (right side of image).
Hello World
the data is in Oracle Designer,
Designer Description
Gadsden ConsultingIT Specialist

Author

Commented:
sdstuber,

it's just from a straight query run in command line.

so it's probably not possible.

but if it does have CHR(10) or CHR(13) embedded, couldn't I find that ?
Most Valuable Expert 2011
Top Expert 2012

Commented:
select dump(your_result_column) from your_table


you'll get a numeric representation of each character in the string
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>Sql Plus command window doesn't keep the blank lines,

It does for me but I'm on 11g sqlplus which is command line...
Looks like you are using sqlplusw, the windows version that was removed after 10g.

Just for grins, run sqlplus from a CMD prompt and try my and then your query.
Gadsden ConsultingIT Specialist

Author

Commented:
Typ=1 Len=12: 68,101,115,99,114,105,112,116,105,111,110,58
Typ=1 Len=350: 65,115,115,105,103,110,32,97,32,102,97,99,117,108,116,121,32,109,101,109,98,101,114,32,116,111,32,98,101,32,97,110,32,97,99,97,100,101,109,105,99,32,97,100,118,105,115,101,114,32,102,111,114,32,97,32,109,97,106,111,114,46,13,10,13,10,65,32,100,101,112,97,114,116,109,101,110,116,32,99,104,97,105,114,32,99,97,110,32,111,110,108,121,32,97,115,115,105,103,110,32,97,100,118,105,115,101,114,115,32,102,111,114,32,109,97,106,111,114,115,32,105,110,32,116,104,101,105,114,32,100,101,112,97,114,116,109,101,110,116,46,32,32,32,69,97,99,104,32,109,97,106,111,114,32,99,97,110,32,104,97,118,101,32,111,110,101,32,111,114,32,116,119,111,32,97,115,115,111,99,105,97,116,101,100,32,100,101,112,97,114,116,109,101,110,116,115,32,40,101,46,103,46,44,32,83,81,69,32,105,115,32,97,100,118,105,115,101,100,32,98,121,32,98,111,116,104,32,77,97,116,104,32,68,101,112,116,32,102,97,99,117,108,116,121,32,97,110,100,32,98,121,32,69,99,111,110,111,109,105,99,115,32,68,101,112,116,32,102,97,99,117,108,116,121,41,46,32,84,104,101,32,82,101,103,105,115,116,97,114,32,104,97,115,32,116,104,101,32,97,117,116,104,111,114,105,116,121,32,116,111,32,97,115,115,105,103,110,32,97,100,118,105,115,101,114,115,32,102,114,111,109,32,97,110,121,32,100,101,112,97,114,116,109,101,110,116,46,13,10
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>but if it does have CHR(10) or CHR(13) embedded, couldn't I find that ?

It is a cr/lf in the text.

Notice the 13,10 combinations.
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv - looks good ! :-)
Sql Plus output
Gadsden ConsultingIT Specialist

Author

Commented:
a. Now I have to find where this spools to.
b. The Sql Plus Window isn't Windows, so I can't copy and paste
c. I'm trying to spool and see what I get

how can I increase the width ? col descr a2400 didn't work.
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>b. The Sql Plus Window isn't Windows,
The last one you posted was the Windows version of sqlplus.  Look at the executable and it will be SQLPLUSW.exe.

>> so I can't copy and paste

Sure you can.  I do it all the time.

>>Now I have to find where this spools to.

Nowhere unless you tell it to spool.  If you don't specify the path in the spool command (spool c:\myoutput.txt), it is in the folder where sqlplus was launched from.  Typically %ORACLE_HOME%/bin

>> how can I increase the width ? col descr a2400 didn't work.

sqlplus command:
set lines 32000

By default it is 80.
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv, ok, checking now, thanks.
Gadsden ConsultingIT Specialist

Author

Commented:
Ok, it looks like it will work, but I'm having to fiddle with the output and such. Spooling isn't working right

For my query:
SET HEADING OFF;
SET LINES 32000;
SPOOL C:\Users\peck\TEST.OUT

-- Line 3 (Description, from Designer)
SELECT descr FROM (
SELECT ct.TXT_TEXT as Descr, 'b'
FROM sdd_mod gmd  
     INNER JOIN cdi_text ct ON ct.parent_ivid = gmd.ivid
WHERE gmd.SHORT_NAME = '&short_name'
   AND jr_version.is_latest_version@devel(gmd.ivid) = 1
   AND ct.TXT_TYPE = 'CDIDSC'
ORDER BY 2,1
)   ;  
spool off;

I get this output, so it's missing the 2nd paragraph.
test output
I basically got my answer, so I don't need to belabor this. If I have formatting issues, I'll create another post.

Thanks !
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>I get this output, so it's missing the 2nd paragraph.

On the screen, sure.  The linesize is 32000 characters.  No wrapping.

I bet the text is in the output...
Gadsden ConsultingIT Specialist

Author

Commented:
I posted the output (see the file name on the screen shot).

Heading out, will pick this up tomorrow, but I'm basically good, thanks !
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I posted the output (see the file name on the screen shot).

Yes, I see that now.  I thought that was the sqlplus window.
Gadsden ConsultingIT Specialist

Author

Commented:
Oh, it's there in the output,  but the linesize 32000 caused the first line to be gigantic.
johnsoneSenior Oracle DBA
Commented:
You need to add:

SET TRIMSPOOL ON

That will remove the excess blanks at the end of the file.  Make sure you do that before your SPOOL command.
Gadsden ConsultingIT Specialist

Author

Commented:
Nice! Thanks again, I'll give it a try tomorrow.
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
For tomorrows test, here is a simple one I ran from home that will hopefully help a little:
SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 varchar2(30));

Table created.

SQL>
SQL> insert into tab1 values('Hello' || chr(13) || chr(10) || 'World');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select col1 from tab1;

COL1
------------------------------
Hello
World


SQL> select replace(col1,chr(13),chr(10)) from tab1;

REPLACE(COL1,CHR(13),CHR(10))
------------------------------
Hello

World


SQL>

Open in new window

Gadsden ConsultingIT Specialist

Author

Commented:
nice ! I ran it at home and it works on Sql Plus but not in Pl/Sql Developer. I'll try it first thing tomorrow.

However, I'll probably be able to get what I want in Sql Plus with the SET TRIMSPOOL ON.

Thanks :-)
CHR(13) replacement.
Gadsden ConsultingIT Specialist

Author

Commented:
Ok, everything is looking good !

but how do I suppress the "OLD VALUE:", "NEW_VALUE" stuff?
Sql output
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>> but how do I suppress the "OLD VALUE:", "NEW_VALUE" stuff?

set verify off
Gadsden ConsultingIT Specialist

Author

Commented:
ok, good, but I still got "Enter value for short_name: XYZ123" in the output.
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>but I still got "Enter value for short_name: XYZ123" in the output.

That wasn't part of the OLD NEW values...  ;)

You'll need to ACCEPT:

undefine short_name
accept short_name char prompt 'Enter Short Name: '

set verify off
set ...


spool somefile
select ...

Open in new window

Gadsden ConsultingIT Specialist

Author

Commented:
Bingo !

thanks, and it's a wrap :-)