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 ?
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
 
slightwv (䄆 Netminder) 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

0
 
slightwv (䄆 Netminder) 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;
0
 
sdstuberCommented:
is your output from dbms_output?

if so, removing blank lines is a "feature"
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Gadsden ConsultingIT SpecialistAuthor 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
0
 
Gadsden ConsultingIT SpecialistAuthor 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 ?
0
 
sdstuberCommented:
select dump(your_result_column) from your_table


you'll get a numeric representation of each character in the string
0
 
slightwv (䄆 Netminder) 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.
0
 
Gadsden ConsultingIT SpecialistAuthor 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
0
 
slightwv (䄆 Netminder) 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.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv - looks good ! :-)
Sql Plus output
0
 
Gadsden ConsultingIT SpecialistAuthor 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.
0
 
slightwv (䄆 Netminder) 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.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv, ok, checking now, thanks.
0
 
Gadsden ConsultingIT SpecialistAuthor 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 !
0
 
slightwv (䄆 Netminder) 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...
0
 
Gadsden ConsultingIT SpecialistAuthor 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 !
0
 
slightwv (䄆 Netminder) 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.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
Oh, it's there in the output,  but the linesize 32000 caused the first line to be gigantic.
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
Nice! Thanks again, I'll give it a try tomorrow.
0
 
slightwv (䄆 Netminder) 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

0
 
Gadsden ConsultingIT SpecialistAuthor 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.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
Ok, everything is looking good !

but how do I suppress the "OLD VALUE:", "NEW_VALUE" stuff?
Sql output
0
 
slightwv (䄆 Netminder) Commented:
>> but how do I suppress the "OLD VALUE:", "NEW_VALUE" stuff?

set verify off
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, good, but I still got "Enter value for short_name: XYZ123" in the output.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
Bingo !

thanks, and it's a wrap :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.