LINESIZE - File Output 3

[pnad]

This is my final query:

SET FEEDBACK off
SET HEADING off
SET TERM off
SET VERIFY off
SET ECHO OFF
SET SPACE 0

set linesize 140

col col3 format a140 truncate
col col2 format a140 truncate
col col1 format a140 truncate

SPOOL C:\FTPtoBank\text.txt

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
rpad(' ',15) || 'V' ||
rpad(' ',80) as col3
from table a, table b
where a.tran_id = b.tran_id
UNION
select '01' || '987' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',96) as col2,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date
UNION
select '01' || '987' || '9999999999' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',96) as col1,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date;

SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit



The file that is outputted as a result of the query does not have a linesize of 140. In the first SELECT, it has more than 15 spaces before the 'V' and more than 80 spaces after. How do I set the linesize so each line is only 140 characters.

Please advise.

Thanks.

 

[Thargy]

SET TRIMSPOOL ON

will get rid of a load of white spaces, which may be needlessly lengthening lines.

Try that first, and let us know what happens.

T

Grinding away at things Oracular

 

[pnad]

The file size has come down from 3Kb to 1 kb but there are still a lot of white spaces remaining. It does like TRIMSPOOL helped a bit but the length of each line is still more than 140.

 

[Thargy]

pnad,

let's divide and conquer here.

Run the first select, i.e.

select '01' || '987' || '1234512345' ||
       rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
       rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
       rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
       rpad(' ',15) || 'V' ||
       rpad(' ',80) as col3
  from table a, table b
  where a.tran_id = b.tran_id

and see what that produces. Since there is only a col3 selected, the sizes of 1 and 2 are immaterial.
In the select, you RPAD col3 with 80 spaces, therefore I would be surprised if you DID NOT get 80 trailing spaces.

Spool the file with trimspool on, and just the first select.
Then set the RPAD down to 40, spool again and compare the two files. Make sure to move the initial spool file, before rerunning, or sql plus will gaily overwrite it for you.

T

Grinding away at things Oracular

 

[pnad]

This is the query that I ran:

SET FEEDBACK off
SET HEADING off
SET TERM off
SET VERIFY off
SET ECHO OFF
SET SPACE 0
SET TRIMSPOOL ON

set linesize 140

col col3 format a140 truncate


SPOOL C:\FTPtoBank\test1.txt

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
rpad(' ',15) || 'V' ||
rpad(' ',80) as col3
from table a, table b
where a.tran_id = b.tran_id;

SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit

This is the o/p produced:



01987123451234500000106282008013000000263750
V

01987123451234500000106332008013000000015000
V

01987123451234500000106342008013000000712800
V

01987123451234500000106322008013000000040100
V

01987123451234500000106292008013000000132031

V

01987123451234500000106302008013000000127949
V

01987123451234500000106352008013000000001500
V

01987123451234500000106312008013000000005000
V

And then I ran this:

SET FEEDBACK off
SET HEADING off
SET TERM off
SET VERIFY off
SET ECHO OFF
SET SPACE 0
SET TRIMSPOOL ON

set linesize 140

col col3 format a140 truncate


SPOOL C:\FTPtoBank\test1.txt

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
rpad(' ',15) || 'V' ||
rpad(' ',40) as col3
from table a, table b
where a.tran_id = b.tran_id;

SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit
And the o/p is:


01987123451234500000106282008013000000263750
V

01987123451234500000106332008013000000015000
V

01987123451234500000106342008013000000712800
V

01987123451234500000106322008013000000040100
V

01987123451234500000106292008013000000132031

V

01987123451234500000106302008013000000127949
V

01987123451234500000106352008013000000001500
V

01987123451234500000106312008013000000005000
V


Its the same o/p. It does not add the trailing spaces. The length of each line in the file should be 140 - thats why I tried adding the spaces at the end.

 

[Thargy]

PNAD,

I have come across situations where I was setting attributes in a file, but sql plus was ignoring them.

I quit the session I was in, restarted, and ran the query, and all the SET commands took effect. Could you try that please. I know it sounds suspiciously like windoze support asking you to reboot, but I can't think of anything else right now.

T

Grinding away at things Oracular

 

[pnad]

I tried that but unfortunately there was no difference in output.

I tried putting all this in a bat file and then calling SQL Plus client from there but that does not seem to work either.

 

[SantaMufasa]

PNAD,

Are you running this from a Windows (GUI) SQL*Plus client? If so, then you must also confirm that your Windows client is accommodating at least 140 positions of output. To confirm the setting, in your SQL*Plus client session, navigate with the top menu to:

Options -> Environment -> "Buffer Width" (at least 140, preferrably 200+)

Once you have done this, re-run your query and advise us of your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Hi SantaMufasa,

I am running this from a Windows (GUI) SQL*Plus client.Buffer Width is set to 241.

The o/p still looks as follows:

01987123451234500000106282008013000000263750
V

01987123451234500000106332008013000000015000
V

01987123451234500000106342008013000000712800
V

01987123451234500000106322008013000000040100
V

01987123451234500000106292008013000000132031

V

01987123451234500000106302008013000000127949
V

01987123451234500000106352008013000000001500
V

01987123451234500000106312008013000000005000
V

 

[SantaMufasa]

Could you please post the results of a SQL*Plus "show all" command? Thanks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep ""
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback OFF
flagger OFF
flush ON
heading OFF
headsep "|" (hex 7c)
instance "local"
linesize 140
lno 12
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 2
recsep WRAP
recsepchar " " (hex 20)
release 902000700
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool ON
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout OFF
timing OFF
trimout ON
trimspool ON
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "EASPROC"
verify OFF
wrap : lines will be wrapped

 

[SantaMufasa]

PNAD,

Thanks for your posting. I see no "smoking gun" that accounts for your output issue. I invite you to post appropriate "CREATE TABLE..." and a couple/few "INSERT INTO..." SQL statements that would allow us to simulate and troubleshoot your situation.

Otherwise, if I were in your place and troubleshooting alone, then I would "cut back" on the SELECT statement's display expressions until the output appears correct. Then I would add back in display expressions until the problem recurs. With the recurrence, we could then identify the problem component.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

This is what I tried now:

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11)||
rpad(' ',15) || 'V' ||
' '||'E' as col3
from table a, table b
where a.tran_id = b.tran_id
UNION
select '02' || '987' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
' '||' '||'E' as col2,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date
UNION
select '03' || '987' || '9999999999' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
' '||' '||'E' as col2,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date;

You will notice that I concatenated an 'E' at the end of each line at the 140th position implying End-Of-Line. It works separately for the three SELECTs but it wont work for the combined query -

select '01' || '987' || '1234512345' ||
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

 

[SantaMufasa]

PNAD,

When you do a UNION (or UNION ALL, or INTERSECT, or MINUS), each SELECT must have the same number of columns. In your case, above, the first SELECT has only one column (named COL3), while the other two SELECTs have two columns (i.e., COL2 and COL3).

The correction seems to be that you rename the first SELECT's COL3 to COL2, then add a "NULL COL3" to the first SELECT.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Thanks for your patience, SantaMufasa.

I am able to get it to work now.

 

[SantaMufasa]

So, PNAD, what was the problem that, once fixed, generated correct output for you?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Well, its a work-around kinda solution.

The original requirement was to get a fixed length flat of 140.

The '01's were the detail records where position 61-140 was spaces. That was messing up the output. The RPAD did not seem to put the spaces at the end of the record. So I put spaces from 61-139 and put a 'E' character at 140.

For the summary lines (02 and 03), I did the same thing. I put spaces from 45-139 and an 'E' character at 140.

This is final query:

select '01' || '987' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8)||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11)||
rpad(' ',95) || 'V' as col3, null as col2
from table a, table b
where a.tran_id = b.tran_id
Union
select '02' || '987' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',95)||'E' as col2,
null as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date
Union
select '03' || '987' || '9999999999' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 11) ||
rpad(' ',95)||'E' as col2,
null as col3
from table a, table b
where group by a.check_date;

 

[Thargy]

Santa,

thanks for helping out PNAD.

Home internet is flakey right now, so I had to abandon after I quit work. Sorry for not staying with you PNAD.

T

Grinding away at things Oracular

 

[maxmouses]

I've been trying to find a way to set linesize and pagesize permanently so I don't have to do it everytime I login into Oracle.

Is there a way to do that?

Thanks.

 

[Thargy]

max,

yes, you can either include a SET LINESIZE in the file of interest, or, edit the glogin.sql file.

This is run every time you invoke sqlplusw and configures your environment. The file is to be found in the ORACLE_HOME/sqlplus/admin folder.

Regards

T

Grinding away at things Oracular