Skip to Main Content
  • Questions
  • The output file alignment must be adjusted.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, RUTHENDAR.

Asked: October 17, 2016 - 7:20 am UTC

Last updated: October 18, 2016 - 3:05 am UTC

Version: 11.0.2.0

Viewed 1000+ times

You Asked

Hi Team,

I executed shell script file. The shell script file has the below sql information in it.

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

Output:-
10000009|Prashad|Mahavir||||MAHAVIR.PRASHAD@XXX.COM|XXX Root||8511||10
025305|01-JUN-85

10000010|Drumm|Patrick||||PATRICK.DRUMM@XXX.COM|XXX Root||8459||100368
93|31-OCT-83

10000016|Kahler|Kristijan||||KRISTIJAN.KAHLER@XXX.COM|XXX Root||8756||
10001780|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||1002

5305|31-MAR-08

10000152|Gibson|Eric||||ERIC.GIBSON@XXX.COM|XXX Root||8758||10025305|2
9-MAR-10

10000155|Illis|William||||WILLIAM.ILLIS@XXX.COM|XXX Root||8753||100253
05|05-FEB-90

10000157|Clark|Sarah||||SARAH.CLARK@XXX.COM|XXX Root||8609||10025305|1
0-FEB-03

10000163|Rinaldi|Paula||||PAULA.RINALDI@XXX.COM|XXX Root||8800||100253
05|04-JUN-01

But the actual output must be like this:-

Actual Output:-
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

I had submitted this question before and your team had advised with setchunksizeoption in sql and
select(dump) parsed_data from tablename where (cost)unit not like 'U%' or cost)unit is null);

So I modified the sql file as per your team's advise.
The Sql
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 :-

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:

Please help me to achieve the actual output:-

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





and Connor said...

Asking the same question twice...does not lead to a faster answer.

Please stick with the first one - that's where we are posting updates.

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