Why does my export file wrap-around?

[weberm]

I have a stored procedure which is invoked by a UNIX shell script which writes a report file. The UNIX shell script then emails the file as part of a report.

I modified the program to include more columns in the report but noticed that the file seems to be wrapping around onto a new line around the 70th characer, like below:

Processor    PID    Transaction Auth	  Transaction	   Cardholder
Total Ticket
Source			  Date		    Number	     Name
Amount
---------   ------  -------------------   -----------	   ---------
------------
AALF        985417  07/25/2006 18:00:36   15967613          PRIMERICA
120.95
AALF	    985418  07/25/2006 18:04:39   15967653	      PRIMERICA
120.95
AALF	    985421  07/25/2006 18:08:50   15967691	      PRIMERICA
120.95
AALF	    985424  07/25/2006 18:12:29   15967716	      DEBORAH A NELSON
115.95
AALF	    985426  07/25/2006 18:13:04   15967722	      PRIMERICA
120.95
AALF	    985427  07/25/2006 18:18:05   15967757	      US BANCORP
57.95
AALF	    985428  07/25/2006 18:17:22   15967752	      ALISA CASELLA
57.95
AALF	    985429  07/25/2006 18:19:27   15967766	      PRIMERICA
120.95
AALF	    985430  07/25/2006 18:22:45   15967783	      ANA C MANZANO
58.95
AALF	    985431  07/25/2006 18:24:01   15967799	      PRIMERICA
120.95
AALF	    985432  07/25/2006 18:27:57   15967826	      MIRACLE
FUNDRAISING INC 			57.95

Is this some sort of side-effect of invoking sqlplus in UNIX or is more information needed?

 

[SantaMufasa]

WebERM,

I don't believe we have enough information yet to be able to resolve your issue. We need to know more about the stored procedure...things such as:[ul][li]What code does the stored procedure use to write this output file?[/li][li]How is the Unix script invoking the stored procedure?[/li][/ul]If you can post background relating to the above issues, I'm sure we can offer more help.

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

 

[weberm]

No worries! I hate to write a novela if it's obvious off the bat.

The stored procedure uses an implicit cursor and DBMS_OUTPUT statements as follows:

     FOR v_aalf IN aalf_cur LOOP
         DBMS_OUTPUT.put('AALF' || c_four_spaces);
	     DBMS_OUTPUT.put(TO_CHAR(v_aalf.pmt_id,'999999999') || c_two_spaces);
-- 		 DBMS_OUTPUT.put(v_aalf.pmt_tran_auth_dt || c_three_spaces); 
		 DBMS_OUTPUT.put(TO_CHAR(v_aalf.pmt_tran_auth_dt,'MM/DD/YYYY HH24:MI:SS' ) || c_three_spaces); 
-- 		 DBMS_OUTPUT.put(RPAD(v_aalf.pmt_ccv_conf_num_an,9) || c_ten_spaces);
		 DBMS_OUTPUT.put(RPAD(v_aalf.PMT_TRAN_NO,10) || c_ten_spaces);
		 DBMS_OUTPUT.put(RPAD(v_aalf.PMT_CARD_HOLDER_NAME_AN,40) || c_two_spaces);
		 DBMS_OUTPUT.put_line(TO_CHAR(v_aalf.pmt_total_tkt_am,'9999999.99'));
     END LOOP;

The UNIX shell script invokes the stored procedure like this:

HOME/bin/qs "exec ecommerce_reconciliation_rpt"

bin/qs is use to invoke ORACLE:

echo "$*" |sqlplus -s rcpi/[i]password[/i]

The output from the shell script is piped to a log file which is emailed.

 

[SantaMufasa]

WebERM,

Outputting via DBMS_OUTPUT.PUT_LINE is subject to the settings of your SQL*Plus session. Therefore, issue this command from your SQL*Plus prompt:

show linesize

You will see that the current value of "linesize" is where your output is wrapping. So, increase your linesize with this command:

set linesize <some larger number>

The maximum value for LINESIZE is 32767. You will probably want to set these SQL*Plus directives, as well:

set echo off
set trimspool on

Let us know if this resolves your problem.

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

 

[weberm]

Ah, I wondered if this was related to a sqlplus setting!

If I open a sqlplus session in UNIX will it retain the new settings? Time to experiment...

 

[SantaMufasa]

Nope...sorry.

I recommend that you invoke a script from Unix, which sets the proper SQL*Plus values then invokes the stored procedure.

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

 

[SantaMufasa]

After re-reading my previous post, I believe I should clarify my suggestion:

Invoke a SQL*Plus script from Unix, which (first) sets the proper SQL*Plus values then invokes the stored procedure (from the same SQL*Plus script).

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

 

[weberm]

No worries! I have UNIX SQL*PLUS scripts which invoke the SET SERVEROUTPUT ON command in TEST so my DBMS_OUTPUT statements show up (but they show up on their own in PROD), but that's for another thread. Adding SET LINESPACE is trivial.

 

[SantaMufasa]

Adding SET LINESPACE is trivial.

It may be trivial, but it will be wrong <grin>. Again, it is spelled, "SET LINESIZE <some large number>".

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

 

[weberm]

Oops, my mistake! I had a brainfart while typing "LINESIZE".

I've since discovered that putting SQL*PLUS commands in login.sql fixes the problem without having to modify my UNIX shell scripts. The next step for me is to get glogin.sql modified so I won't need the login.sql file...