| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL output substitutions
I have this PL/SQL stored procedure which produces a columnar report.
When I run it in SQL*PLUS, the columns are all aligned, however, when
I run it via an UNIX shell script which pipes the report to a file, I
discovered some of the white space gets converted to tabs! Has anyone
ever experienced this before? I'm at my wit's end trying to figure out
what's going on. I include a sample of the PL/SQL code and output that
was emailed below, in case it helps.
Thanks in advance,
MRW
CREATE OR REPLACE PROCEDURE ecommerce_reconciliation_rpt
/*
|| PROGRAM: eCommerce Reconciliation Report
|| AUTHOR: Michael R. Weber
|| FILE:
|| CREATED: April 5, 2004
|| PURPOSE: This procedure genrates the weekly eCommerce Reconcilaition reports
|| and displays approved transactions that are older than two days but
|| have not been reconciled yet.
|| NB: I used <ALT>0160 as first char in c_header2.
|| REVISED:
*/
IS
/* variables */
v_print_line VARCHAR2(80); v_proc_source CHAR(4); /* constants */ c_crlf VARCHAR2(2) := CHR(13) || CHR(10); c_header1 VARCHAR2(80) := 'Processor PID Trans Auth Confirmation Total Ticket'; c_header2 VARCHAR2(80) := ' Source Dates Number Amount '; c_header3 VARCHAR2(80) := '--------- ------ ---------- ------------ ------------'; c_two_spaces CONSTANT CHAR(2) := ' '; c_three_spaces CONSTANT CHAR(3) := ' '; c_four_spaces CONSTANT CHAR(4) := ' '; c_five_spaces CONSTANT CHAR(5) := ' '; c_ten_spaces CONSTANT CHAR(10) := ' ';
/* cursor declaration */
CURSOR aalf_cur
IS
SELECT DISTINCT app_id,
pmt_tran_auth_dt,
pmt_ccv_conf_num_an,
pmt_total_tkt_am
FROM aalf.applications,
aalf.applicants,
aalf.payments,
aalf.fees
WHERE APP_APL_ID = APL_ID (+)
AND app_id = pmt_app_id(+)
AND app_id = fee_app_id(+)
AND app_status_cd IN('A', 'E')
AND app_accepted_dt < SYSDATE - 2
AND pmt_type_cd != 'C'
AND pmt_doi_rct_no IS NULL;
CURSOR alis_cur
IS
SELECT DISTINCT ALIS.payment_invoice_id,
ALIS.trans_auth_date_time,
ALIS.confirmation_number,
ALIS.total_ticket_amt
FROM ialis.viw_receipt ALIS
WHERE alis.payment_status IN('A', 'E')
AND alis.trans_auth_date_time < SYSDATE - 2
AND alis.dfs_receipt_number IS NULL;
CURSOR dice_cur
IS
SELECT dice_fee.id_invoice_pid,
dice_fee.dt_tran_auth,
dice_fee.ds_cip_conf_num,
dice_fee.am_total_tkt
FROM dice.fee dice_fee
WHERE dice_fee.cd_invoice_status IN('A', 'E')
AND dice_fee.dt_added < SYSDATE - 2
AND dice_fee.cd_payment_type != 'C'
AND dice_fee.cd_fee_type != 'C'
AND dice_fee.no_doi_rct IS NULL;
CURSOR fcce_cur
IS
SELECT fcce_fee.id_invoice_pid,
fcce_fee.dt_tran_auth,
fcce_fee.ds_cip_conf_num,
fcce_fee.am_total_tkt
FROM fcdice.fee fcce_fee
WHERE fcce_fee.cd_invoice_status IN('A', 'E')
AND fcce_fee.dt_added < SYSDATE - 2
AND fcce_fee.cd_payment_type IN ('E', 'N', 'V')
AND fcce_fee.no_doi_rct IS NULL;
BEGIN
DBMS_OUTPUT.enable(100000);
DBMS_OUTPUT.put_line(c_header1);
DBMS_OUTPUT.put_line(c_header2);
DBMS_OUTPUT.put_line(c_header3);
FOR v_aalf IN aalf_cur LOOP
DBMS_OUTPUT.put('AALF' || c_four_spaces);
DBMS_OUTPUT.put(TO_CHAR(v_aalf.app_id,'999999999') || c_two_spaces);
DBMS_OUTPUT.put(v_aalf.pmt_tran_auth_dt || c_three_spaces);
DBMS_OUTPUT.put(RPAD(v_aalf.pmt_ccv_conf_num_an,9) || c_ten_spaces);
DBMS_OUTPUT.put_line(TO_CHAR(v_aalf.pmt_total_tkt_am,'9999999.99'));
END LOOP;
FOR v_alis IN alis_cur LOOP
DBMS_OUTPUT.put('ALIS' || c_four_spaces);
DBMS_OUTPUT.put(TO_CHAR(v_alis.payment_invoice_id,'999999999') || c_two_spaces);
DBMS_OUTPUT.put(v_alis.trans_auth_date_time || c_three_spaces);
DBMS_OUTPUT.put(RPAD(v_alis.confirmation_number,9) || c_ten_spaces);
DBMS_OUTPUT.put_line(TO_CHAR(v_alis.total_ticket_amt,'9999999.99'));
END LOOP;
FOR v_dice IN dice_cur LOOP
DBMS_OUTPUT.put('DICE' || c_four_spaces);
DBMS_OUTPUT.put(TO_CHAR(v_dice.id_invoice_pid,'999999999') || c_two_spaces);
DBMS_OUTPUT.put(v_dice.dt_tran_auth || c_three_spaces);
DBMS_OUTPUT.put(RPAD(v_dice.ds_cip_conf_num,9) || c_ten_spaces);
DBMS_OUTPUT.put_line(TO_CHAR(v_dice.am_total_tkt,'9999999.99'));
END LOOP;
FOR v_fcce IN fcce_cur LOOP
DBMS_OUTPUT.put('FCCE' || c_four_spaces);
DBMS_OUTPUT.put(TO_CHAR(v_fcce.id_invoice_pid,'999999999') || c_two_spaces);
DBMS_OUTPUT.put(v_fcce.dt_tran_auth || c_three_spaces);
DBMS_OUTPUT.put(RPAD(v_fcce.ds_cip_conf_num,9) || c_ten_spaces);
DBMS_OUTPUT.put_line(TO_CHAR(v_fcce.am_total_tkt,'9999999.99'));
END LOOP;
Processor PID Trans Auth Confirmation Total Ticket Source Dates Number Amount --------- ------ ---------- ------------ ------------ AALF 154879 04/18/2003 01094242 149.95 AALF 181835 06/27/2003 01427781 57.95 AALF 300042 04/09/2004 03004610 115.95 AALF 300043 04/09/2004 03004554 115.95 AALF 300044 04/09/2004 03004558 58.95 AALF 300052 04/09/2004 03004926 58.95 AALF 300058 04/09/2004 03005064 115.95 AALF 300059 04/09/2004 03005091 58.95 AALF 300061 04/10/2004 03005794 57.95 AALF 300063 04/10/2004 03007252 58.95 AALF 300066 04/10/2004 03007329 58.95 AALF 300073 04/10/2004 03007588 57.95 AALF 300074 04/10/2004 03007611 58.95 AALF 300077 04/10/2004 03007737 58.95 AALF 300078 04/10/2004 03007786 57.95 AALF 300081 04/10/2004 03008077 58.95 AALF 300083 04/10/2004 03008194 130.95 AALF 300088 04/10/2004 03008321 57.95 AALF 300090 04/10/2004 03008371 57.95 ALIS 100 12/07/2003 2209491 62.10 ALIS 101 12/07/2003 2209506 62.10 ALIS 102 12/07/2003 2209497 62.10 ALIS 507659 12/07/2003 2209572 62.10 ALIS 507660 12/07/2003 2209574 62.10 ALIS 507662 12/07/2003 2209617 124.20 ALIS 507664 12/07/2003 2209631 124.20 ALIS 507665 12/07/2003 2209659 186.30 ALIS 507666 12/07/2003 2209661 186.30 ALIS 507667 12/07/2003 2209666 186.30 ALIS 507910 12/11/2003 02226662 62.10 ALIS 508229 12/15/2003 02240143 62.10 ALIS 508311 12/16/2003 02244474 62.10 ALIS 509049 12/22/2003 02265897 372.60 ALIS 509572 12/24/2003 02273670 124.20 ALIS 509674 12/26/2003 02277180 62.10 ALIS 509776 12/29/2003 02282452 62.10 ALIS 510048 12/30/2003 02288028 62.10 ALIS 510089 12/30/2003 02288851 62.10 ALIS 510632 01/06/2004 02330293 62.10 ALIS 511130 01/07/2004 02337328 62.10 ALIS 517609 01/29/2004 2496569 62.10 ALIS 533424 03/25/2004 2874025 434.70 ALIS 535556 04/09/2004 3001598 434.70 ALIS 536243 04/09/2004 3000517 124.20 ALIS 536395 04/09/2004 2999592 62.10 ALIS 536942 04/09/2004 3000574 75.30 ALIS 537221 04/08/2004 2994799 248.40 ALIS 537224 04/08/2004 2994686 62.10 ALIS 537225 04/08/2004 2994692 62.10 ALIS 537226 04/08/2004 2994718 62.10 ALIS 537227 04/08/2004 2994723 62.10 ALIS 537228 04/08/2004 2994736 62.10 ALIS 537229 04/08/2004 2994743 62.10 ALIS 537230 04/08/2004 2994752 62.10 ALIS 537231 04/08/2004 2994765 62.10 ALIS 537232 04/08/2004 2994772 62.10 ALIS 537233 04/08/2004 2994781 62.10 ALIS 537234 04/08/2004 2994789 62.10 ALIS 537235 04/08/2004 2994798 62.10 ALIS 537236 04/08/2004 2994801 62.10 ALIS 537237 04/08/2004 2994881 62.10 ALIS 537243 04/08/2004 2995467 62.10 ALIS 537247 04/09/2004 2996538 62.10 ALIS 537248 04/09/2004 2998286 62.10 ALIS 537249 04/09/2004 2998292 62.10 ALIS 537250 04/09/2004 2998298 62.10 ALIS 537251 04/09/2004 2998303 62.10 ALIS 537252 04/09/2004 2998417 62.10 ALIS 537254 04/09/2004 2998513 2918.70 ALIS 537255 04/09/2004 2998492 62.10 ALIS 537256 04/09/2004 2998525 62.10 ALIS 537257 04/09/2004 2998561 62.10 ALIS 537258 04/09/2004 2998539 62.10 ALIS 537260 04/09/2004 2998735 124.20 ALIS 537261 04/09/2004 2998693 124.20 ALIS 537262 04/09/2004 2998725 62.10 ALIS 537263 04/09/2004 2998722 124.20 ALIS 537264 04/09/2004 2998729 62.10 ALIS 537265 04/09/2004 2998757 62.10 ALIS 537266 04/09/2004 2998755 2111.40 ALIS 537267 04/09/2004 2998750 62.10 ALIS 537268 04/09/2004 2998760 124.20 ALIS 537269 04/09/2004 2998801 124.20 ALIS 537270 04/09/2004 2998788 248.40 ALIS 537271 04/09/2004 2998822 124.20 ALIS 537272 04/09/2004 2998848 124.20 ALIS 537273 04/09/2004 2998865 372.60 ALIS 537274 04/09/2004 2998866 62.10 ALIS 537275 04/09/2004 2998872 62.10 ALIS 537276 04/09/2004 2998902 62.10 ALIS 537277 04/09/2004 2998937 62.10 ALIS 537278 04/09/2004 2998944 62.10 ALIS 537279 04/09/2004 2998951 13358.10 ALIS 537280 04/09/2004 2998967 62.10 ALIS 537281 04/09/2004 2998968 62.10 ALIS 537282 04/09/2004 2998984 124.20 ALIS 537283 04/09/2004 2999020 9377.10 ALIS 537284 04/09/2004 2999075 62.10 ALIS 537285 04/09/2004 2999091 124.20 ALIS 537287 04/09/2004 2999137 62.10 ALIS 537288 04/09/2004 2999173 124.20 ALIS 537289 04/09/2004 2999179 6.60 ALIS 537290 04/09/2004 2999199 62.10 ALIS 537291 04/09/2004 2999237 62.10 ALIS 537292 04/09/2004 2999279 62.10 ALIS 537293 04/09/2004 2999282 62.10 ALIS 537294 04/09/2004 2999284 62.10 ALIS 537296 04/09/2004 2999398 124.20 ALIS 537297 04/09/2004 2999416 62.10 ALIS 537298 04/09/2004 2999441 248.40 ALIS 537299 04/09/2004 2999417 62.10 ALIS 537300 04/09/2004 2999431 62.10 ALIS 537301 04/09/2004 2999538 504.30 ALIS 537304 04/09/2004 2999515 62.10 ALIS 537307 04/09/2004 2999570 124.20 ALIS 537308 04/09/2004 2999630 62.10 ALIS 537309 04/09/2004 2999593 4595.40 ALIS 537310 04/09/2004 2999622 62.10 ALIS 537311 04/09/2004 2999633 62.10 ALIS 537312 04/09/2004 2999645 2925.30 ALIS 537314 04/09/2004 2999662 62.10 ALIS 537315 04/09/2004 2999661 62.10 ALIS 537317 04/09/2004 2999689 62.10 ALIS 537319 04/09/2004 2999733 62.10 ALIS 537324 04/09/2004 2999830 62.10 ALIS 537325 04/09/2004 2999839 161.10 ALIS 537326 04/09/2004 2999879 62.10 ALIS 537328 04/09/2004 2999834 62.10 ALIS 537329 04/09/2004 2999847 745.20 ALIS 537331 04/09/2004 2999887 62.10 ALIS 537332 04/09/2004 2999872 62.10 ALIS 537339 04/09/2004 2999937 62.10 ALIS 537344 04/09/2004 2999971 62.10 ALIS 537346 04/09/2004 3000010 310.50 ALIS 537347 04/09/2004 3000050 68.70 ALIS 537348 04/09/2004 3000012 62.10 ALIS 537350 04/09/2004 3000027 62.10 ALIS 537351 04/09/2004 3000039 310.50 ALIS 537352 04/09/2004 3000053 62.10 ALIS 537353 04/09/2004 3000087 62.10 ALIS 537354 04/09/2004 3000070 1566.60 ALIS 537355 04/09/2004 3000106 62.10 ALIS 537356 04/09/2004 3000171 62.10 ALIS 537357 04/09/2004 3000135 62.10 ALIS 537358 04/09/2004 3000160 124.20 ALIS 537359 04/09/2004 3000165 62.10 ALIS 537360 04/09/2004 3000197 124.20 ALIS 537361 04/09/2004 3000239 62.10 ALIS 537362 04/09/2004 3000260 434.70 ALIS 537363 04/09/2004 3000242 62.10 ALIS 537364 04/09/2004 3000262 62.10 ALIS 537365 04/09/2004 3000275 62.10 ALIS 537366 04/09/2004 3000268 62.10 ALIS 537368 04/09/2004 3000373 434.70 ALIS 537369 04/09/2004 3000413 248.40 ALIS 537370 04/09/2004 3000443 62.10 ALIS 537371 04/09/2004 3000489 155.25 ALIS 537372 04/09/2004 3000465 232.50 ALIS 537374 04/09/2004 3000563 150.60 ALIS 537375 04/09/2004 3000599 62.10 ALIS 537376 04/09/2004 3000649 62.10 ALIS 537377 04/09/2004 3000664 62.10 ALIS 537378 04/09/2004 3000701 62.10 ALIS 537379 04/09/2004 3000706 683.10 ALIS 537382 04/09/2004 3000774 310.50 ALIS 537383 04/09/2004 3000769 248.40 ALIS 537384 04/09/2004 3000806 454.50 ALIS 537385 04/09/2004 3000873 186.30 ALIS 537386 04/09/2004 3000895 186.30 ALIS 537387 04/09/2004 3001086 155.25 ALIS 537389 04/09/2004 3000930 496.80 ALIS 537390 04/09/2004 3000956 186.30 ALIS 537392 04/09/2004 3000968 310.50 ALIS 537394 04/09/2004 3001034 310.50 ALIS 537395 04/09/2004 3001065 124.20 ALIS 537397 04/09/2004 3001111 124.20 ALIS 537398 04/09/2004 3001186 62.10 ALIS 537399 04/09/2004 3001173 558.90 ALIS 537403 04/09/2004 3001265 310.50 ALIS 537406 04/09/2004 3001295 62.10 ALIS 537407 04/09/2004 3001305 683.10 ALIS 537409 04/09/2004 3001327 186.30 ALIS 537412 04/09/2004 3001382 454.20 ALIS 537415 04/09/2004 3001440 62.10 ALIS 537416 04/09/2004 3001492 62.10 ALIS 537418 04/09/2004 3001538 62.10 ALIS 537419 04/09/2004 3001715 155.25 ALIS 537420 04/09/2004 3001713 434.70 ALIS 537421 04/09/2004 3001760 124.20 ALIS 537423 04/09/2004 3002055 2931.90 ALIS 537424 04/09/2004 3001841 62.10 ALIS 537425 04/09/2004 3001854 124.20 ALIS 537426 04/09/2004 3001862 248.40 ALIS 537427 04/09/2004 3001870 62.10 ALIS 537429 04/09/2004 3002115 62.10 ALIS 537430 04/09/2004 3002098 186.30 ALIS 537431 04/09/2004 3002111 1157.40 ALIS 537432 04/09/2004 3002126 62.10 ALIS 537433 04/09/2004 3002227 124.20 ALIS 537434 04/09/2004 3002271 186.30 ALIS 537436 04/09/2004 3002295 441.30 ALIS 537437 04/09/2004 3002320 62.10 ALIS 537438 04/09/2004 3002394 62.10 ALIS 537439 04/09/2004 3002341 62.10 ALIS 537440 04/09/2004 3002369 62.10 ALIS 537442 04/09/2004 3002466 124.20 ALIS 537443 04/09/2004 3002425 62.10 ALIS 537444 04/09/2004 3002423 62.10 ALIS 537445 04/09/2004 3002491 2091.00 ALIS 537446 04/09/2004 3002437 62.10 ALIS 537448 04/09/2004 3002444 62.10 ALIS 537449 04/09/2004 3002471 62.10 ALIS 537450 04/09/2004 3002490 310.50 ALIS 537451 04/09/2004 3002492 124.20 ALIS 537452 04/09/2004 3002518 186.30 ALIS 537453 04/09/2004 3002534 248.40 ALIS 537454 04/09/2004 3002565 62.10 ALIS 537455 04/09/2004 3002588 807.30 ALIS 537456 04/09/2004 3002561 372.60 ALIS 537457 04/09/2004 3002593 310.50 ALIS 537460 04/09/2004 3002607 281.40 ALIS 537462 04/09/2004 3002615 186.30 ALIS 537463 04/09/2004 3002630 503.40 ALIS 537464 04/09/2004 3002651 434.70 ALIS 537465 04/09/2004 3002642 144.00 ALIS 537466 04/09/2004 3002660 186.30 ALIS 537469 04/09/2004 3002704 124.20 ALIS 537470 04/09/2004 3002702 248.40 ALIS 537471 04/09/2004 3002712 62.10 ALIS 537472 04/09/2004 3002734 496.80 ALIS 537474 04/09/2004 3002763 496.80 ALIS 537475 04/09/2004 3002755 68.70 ALIS 537476 04/09/2004 3002771 261.60 ALIS 537477 04/09/2004 3002787 62.10 ALIS 537478 04/09/2004 3002797 186.30 ALIS 537479 04/09/2004 3002805 62.10 ALIS 537480 04/09/2004 3002832 504.30 ALIS 537481 04/09/2004 3002807 124.20 ALIS 537482 04/09/2004 3002820 310.50 ALIS 537483 04/09/2004 3003010 504.30 ALIS 537485 04/09/2004 3002853 62.10 ALIS 537486 04/09/2004 3002847 124.20 ALIS 537487 04/09/2004 3002867 503.40 ALIS 537488 04/09/2004 3002914 62.10 ALIS 537489 04/09/2004 3002885 869.40 ALIS 537490 04/09/2004 3002912 192.90 ALIS 537491 04/09/2004 3002896 186.30 ALIS 537492 04/09/2004 3002904 62.10 ALIS 537493 04/09/2004 3002910 93.15 ALIS 537494 04/09/2004 3002919 255.00 ALIS 537496 04/09/2004 3002930 186.30 ALIS 537497 04/09/2004 3002933 683.10 ALIS 537498 04/09/2004 3003225 186.30 ALIS 537499 04/09/2004 3002972 372.60 ALIS 537500 04/09/2004 3002978 186.30 ALIS 537502 04/09/2004 3002990 62.10 ALIS 537504 04/09/2004 3003022 1403.10 ALIS 537506 04/09/2004 3003015 186.30 ALIS 537509 04/09/2004 3003033 248.40 ALIS 537510 04/09/2004 3003040 192.90 ALIS 537512 04/09/2004 3003077 1117.80 ALIS 537514 04/09/2004 3003106 310.50 ALIS 537515 04/09/2004 3003141 434.70 ALIS 537516 04/09/2004 3003128 62.10 ALIS 537517 04/09/2004 3003138 248.40 ALIS 537526 04/09/2004 3003243 62.10 ALIS 537527 04/09/2004 3003247 186.30 ALIS 537528 04/09/2004 3003299 186.30 ALIS 537536 04/09/2004 3003393 62.10 ALIS 537542 04/09/2004 3003446 62.10 ALIS 537543 04/09/2004 3003479 124.20 ALIS 537544 04/09/2004 3003552 62.10 ALIS 537550 04/09/2004 3003599 62.10 ALIS 537554 04/09/2004 3003631 62.10 ALIS 537557 04/09/2004 3003705 379.20 ALIS 537560 04/09/2004 3003736 62.10 ALIS 537569 04/09/2004 3004123 186.30 ALIS 537570 04/09/2004 3004229 137.40 ALIS 537571 04/09/2004 3004249 124.20 ALIS 537572 04/09/2004 3004267 504.30 ALIS 537573 04/09/2004 3004290 876.90 ALIS 537574 04/09/2004 3004351 124.20 ALIS 537575 04/09/2004 3004350 503.40 ALIS 537576 04/09/2004 3004361 62.10 ALIS 537577 04/09/2004 3004388 248.40 ALIS 537578 04/09/2004 3004393 572.10 ALIS 537579 04/09/2004 3004402 62.10 ALIS 537580 04/09/2004 3004417 496.80 ALIS 537581 04/09/2004 3004446 186.30 ALIS 537582 04/09/2004 3004494 124.20 ALIS 537583 04/09/2004 3004503 124.20 ALIS 537584 04/09/2004 3004514 62.10 ALIS 537585 04/09/2004 3004524 434.70 ALIS 537586 04/09/2004 3004539 248.40 ALIS 537587 04/09/2004 3004546 310.50 ALIS 537588 04/09/2004 3004557 434.70 ALIS 537589 04/09/2004 3004564 62.10 ALIS 537590 04/09/2004 3004570 516.60 ALIS 537591 04/09/2004 3004607 621.00 ALIS 537592 04/09/2004 3004620 62.10 ALIS 537593 04/09/2004 3004643 807.30 ALIS 537594 04/09/2004 3004656 434.70 ALIS 537595 04/09/2004 3004674 385.80 ALIS 537596 04/09/2004 3004687 248.40 ALIS 537597 04/09/2004 3004724 124.20 ALIS 537598 04/09/2004 3004736 62.10 ALIS 537599 04/09/2004 3004742 62.10 ALIS 537600 04/09/2004 3004753 62.10 ALIS 537601 04/09/2004 3004766 186.30 ALIS 537602 04/09/2004 3004772 130.80 ALIS 537603 04/09/2004 3004783 124.20 ALIS 537604 04/09/2004 3004796 124.20 ALIS 537605 04/09/2004 3004802 62.10 ALIS 537606 04/09/2004 3004807 62.10 ALIS 537607 04/09/2004 3004818 192.90 ALIS 537609 04/10/2004 3008149 62.10 ALIS 537610 04/10/2004 3008158 62.10 ALIS 537611 04/10/2004 3008173 62.10 ALIS 537612 04/10/2004 3008201 62.10 DICE 537568 04/09/2004 100005428 102.50 FCCE 537608 04/10/2004 101001840 75.00Received on Tue Apr 13 2004 - 10:39:14 CDT
 |
 |