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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:
Bingo !

thanks, and it's a wrap :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.