Skip to Main Content
  • Questions

  • How to acheive the output in the aligned format ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 16, 2016 - 9:57 am UTC

Last updated: October 18, 2016 - 1:47 am UTC

Version: 11.2.0.2.0

Viewed 1000+ times

You Asked

The following .sql file is called by the .sh shell script.
connect username/password@sidname
SET SERVEROUTPUT ON;
SET LINESIZE 4600;
#SET TRIMSPOOL ON;
SET WRAP OFF;
SET HEADING OFF;
#SET TRIMOUT ON;
SET TIMING ON;
SET FEEDBACK ON;
SET SPOOL ON;
SPOOL /local/home/test/subscription.txt
SELECT PARSED_DATA FROM TABLENAME where ( cost_unit not like 'U%' or cost_unit is null);
SPOOL OFF;

The actual output of subscription.txt is below:- Here 6 rows in the file subscription.txt are zig-zag.


10000009|Prashad|Mahavir||||MAHAVIR.PRASHAD@XXX.COM|XXX Root||8511||10025305|01-JUN-8510000010|Drumm|Patrick||||PATRICK
.DRUMM@XXX.COM|XXX Root||8459||10036893|31-OCT-
8310000016|Kahler|Kristijan||||KRISTIJAN.KAHLER@XXX.COM|XXX Root||8756||10001780|08-JAN-0110000096|Matchaba|Patrice||||
PATRICE.MATCHABA@XXX.COM|XXX Root||8765||
10025305|01-DEC-0210000151|Banerji|Donald||||DONALD.BANERJI@XXX.COM|XXX Root||8821||10025305|31-MAR-08
10000152|Gibson|Eric||||ERIC.GIBSON@XXX.COM|XXX Root||8758||
10025305|29-MAR-106rows selected.Elapsed: 00:00:01.01



How to achieve the output of subscription.txt in the below format ?

EMPLOYEE ID|LAST NAME|FIRST NAME|MIDDLE NAME |SUFFIX|TITLE|EMAIL ADDRESS|DEPARTMENT|DEPARTMENT NAME|COST UNIT|COST UNIT DESC|ECM GROUP|START DATE
10000009|Prashad|Mahavir||||MAHAVIR.PRASHAD@XXX.COM|XXX Root||8511||10025305|01-JUN-85
10000010|Drumm|Patrick||||PATRICK.DRUMM@XXX.COM|XXX Root||8459||10006819|31-OCT-83
10000016|Kahler|Kristijan||||KRISTIJAN.KAHLER@XXX.COM|XXX Root||8756||10006819|08-JAN-01
10000096|Matchaba|Patrice||||PATRICE.MATCHABA@XXX.COM|XXX Root||8765||10025305|01-DEC-02
10000151|Banerji|Donald||||DONALD.BANERJI@XXX.COM|XXX Root||8821||10025305|31-MAR-08
10000152|Gibson|Eric||||ERIC.GIBSON@XXX.COM|XXX Root||8758||10025305|29-MAR-10
6rows selected.Elapsed: 00:00:01.01



and Connor said...

Your sqlplus settings look ok. What do you get when you do:

SELECT dump(PARSED_DATA) FROM TABLENAME where ( cost_unit not like 'U%' or cost_unit is null);

are there any embedded line feeds or carriage returns ?

=============

Addenda:

Dont forget to set longchunksize as well

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 17, 2016 - 7:04 am UTC

Hi Team,
I modified the script and executed it.

connect username/password@sid
SET SERVEROUTPUT ON;
SET LINESIZE 46000;
#SET TRIMSPOOL ON;
SET LONGCHUNKSIZE;
SET WRAP OFF;
SET HEADING OFF;
#SET TRIMOUT ON;
SET TIMING ON;
SET FEEDBACK ON;
SET SPOOL ON;
SPOOL /local/home/lmsn01/ds/fmv/test/subscription.txt
SELECT DUMP(PARSED_DATA) FROM TABLENAME where (cost_unit not like 'U%' or cost_unit is null);
SPOOL OFF;

The output is obtained as below:-

Typ=1 Len=96: 49,48,48,48,48,48,48,57,124,80,114,97,115,104,97,100,124,77,97,104
Typ=1 Len=92: 49,48,48,48,48,48,49,48,124,68,114,117,109,109,124,80,97,116,114,1
Typ=1 Len=98: 49,48,48,48,48,48,49,54,124,75,97,104,108,101,114,124,75,114,105,1
Typ=1 Len=98: 49,48,48,48,48,48,57,54,124,77,97,116,99,104,97,98,97,124,80,97,11
Typ=1 Len=94: 49,48,48,48,48,49,53,49,124,66,97,110,101,114,106,105,124,68,111,1
Typ=1 Len=88: 49,48,48,48,48,49,53,50,124,71,105,98,115,111,110,124,69,114,105,9
Typ=1 Len=92: 49,48,48,48,48,49,53,53,124,73,108,108,105,115,124,87,105,108,108,
Typ=1 Len=88: 49,48,48,48,48,49,53,55,124,67,108,97,114,107,124,83,97,114,97,104
Typ=1 Len=92: 49,48,48,48,48,49,54,51,124,82,105,110,97,108,100,105,124,80,97,11
Typ=1 Len=90: 49,48,48,48,48,49,55,57,124,67,111,117,116,117,114,101,124,69,114,
Typ=1 Len=92: 49,48,48,48,48,49,56,48,124,83,116,114,97,117,98,124,83,104,101,10
Typ=1 Len=94: 49,48,48,48,48,49,56,55,124,71,105,108,101,115,124,80,101,110,101,
Typ=1 Len=97: 49,48,48,48,48,49,57,55,124,67,104,105,97,110,103,124,89,97,110,11

Typ=1 Len=88: 49,48,48,48,48,50,48,48,124,80,108,97,122,97,124,68,105,97,110,97,
Typ=1 Len=95: 49,48,48,48,48,50,49,49,124,83,116,114,105,103,107,97,115,124,73,1
Typ=1 Len=86: 49,48,48,48,48,50,52,48,124,76,101,97,108,124,74,101,115,117,115,1
Typ=1 Len=90: 49,48,48,48,48,50,53,57,124,83,104,97,119,124,67,104,114,105,115,1
Typ=1 Len=106: 49,48,48,48,48,50,54,51,124,65,98,97,110,103,124,65,110,116,104,1
Typ=1 Len=84:6 rows selected.

Elapsed: 00:00:01.07

Please advise me. This is not the correct output. The actual output must be like below:-

EMPLOYEE ID|LAST NAME|FIRST NAME|MIDDLE NAME |SUFFIX|TITLE|EMAIL ADDRESS|DEPARTMENT|DEPARTMENT NAME|COST UNIT|COST UNIT DESC|ECM GROUP|START DATE
10000009|Prashad|Mahavir||||MAHAVIR.PRASHAD@XXX.COM|XXX Root||8511||10025305|01-JUN-85
10000010|Drumm|Patrick||||PATRICK.DRUMM@XXX.COM|XXX Root||8459||10006819|31-OCT-83
10000016|Kahler|Kristijan||||KRISTIJAN.KAHLER@XXX.COM|XXX Root||8756||10006819|08-JAN-01
10000096|Matchaba|Patrice||||PATRICE.MATCHABA@XXX.COM|XXX Root||8765||10025305|01-DEC-02
10000151|Banerji|Donald||||DONALD.BANERJI@XXX.COM|XXX Root||8821||10025305|31-MAR-08
10000152|Gibson|Eric||||ERIC.GIBSON@XXX.COM|XXX Root||8758||10025305|29-MAR-10
6rows selected.Elapsed: 00:00:01.01
Connor McDonald
October 18, 2016 - 1:47 am UTC

I wasn't saying use DUMP as *solution*. I was saying use DUMP to examine the content - look for carriage returns and/or line feeds which might be messing up your output.

So you

set long 50000
set longchunksize 50000
select dump(...)

and looks for "10" and "13"...if they are present, you could need to remove them as you query, for example:

select replace(replace(parsed_data,chr(10)),chr(13)) from ...

window = CR/LF, unix is only LF

A reader, October 18, 2016 - 12:38 am UTC

I think you are trying to open a unix formatted file in windows with notepad or something. UNIX formatted files only have a line feed (LF) at the end of a line, while the end of line in Windows has two "characters" a carraige return and a line feed (or CRLF) and when opened with standard windows tools like notepad they show as one long continuous line of text.

If this is the case, try opening the file in Wordpad.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.