Data Type Error 1

[cwsstins]

I've having a problem where I keep getting this error:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 128

...when I run my .sql through a UNIX .sh script.

charcreatedate and charmoddate are both varchar2(10). If I use charcreatedate in the WHERE clause, the script runs fine. But if I run it with charmoddate, it produces the error. Any ideas as to the problem?

declare 
   cursor c1 is 
	select 
        merchantname,
        charcreatedate,
        charmoddate
        from tbl1
        where charmoddate = TO_CHAR(SYSDATE,'YYYY-MM-DD');

F1 tbl1.merchantname%TYPE;
F2 tbl1.charcreatedate%TYPE;
F3 tbl1.charmoddate%TYPE;


BEGIN UTL_FILE portion...

 

[SantaMufasa]

Stinsman,

Do you get the same error when you run the block from SQL*Plus? If so, when you issue the "L" (List) command, following the error, could you please copy and paste the contents of the few lines before and after Line 128?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cwsstins]

OK, I ran it in PL/SQL and yes, received the same error. I ran the script on my test box, which is essentially the same db structure, and it ran fine.

The weird thing is, line 128 is actually blank:

F1 tbl1.merchantname%TYPE;
F2 tbl1.charcreatedate%TYPE;
F3 tbl1.charmoddate%TYPE;
[b]    <this is line 128, a blank line>[/b]
  l_file_hdl utl_file.file_type;
BEGIN
  l_file_hdl:= utl_file.fopen('/apps/htcs/data','ccs45.txt','W',32767);

 

[SantaMufasa]

...issue the "L" (List) command, following the error, (then) please copy and paste the contents of the few lines before and after Line 128...

Stinsman, it's not that I don't trust you, but I want/need to see the literal screen contents of your SQL*Plus "LIST" command.


Not that I don't trust you, but to resolve your issue, I need to see the line numbers that SQL*Plus prints. For example:

128    F1 tbl1.merchantname%TYPE;
126    F2 tbl1.charcreatedate%TYPE;
127    F3 tbl1.charmoddate%TYPE;
128        <this is line 128, a blank line>
129      l_file_hdl utl_file.file_type;
130    BEGIN
131      l_file_hdl:= utl_file.fopen
132    ('/apps/htcs/data','ccs45.txt','W',32767);

...but I want SQL*Plus to show me the line numbers; I don't want you to do the numbering by hand.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cwsstins]

I see what you mean...OK, I didn't want to post the whole code because it's long and the snippets I've provided have been examples. Here's the whole code, and after running it in SQL*Plus, the offending line is in bold:

set feedback off;
set long 99000;
set serveroutput on size 1000000;

set escape '\'

declare 
   cursor c1 is 
	select 
	rtrim(STATIONNO),
	substr(replace(ACCTNO,'-',''),1,12), 
        substr(replace(MERCHANTNM,'&','\&'),1,40), 
        DECODE (ATTENTION, '', 'N/A', substr(replace(replace(replace(ATTENTION,'<','\&lt;'),'>','\&gt;'),'&','\&amp;'),1,27)),
	substr(replace(STREETTXT,'&','\&amp;'),1,30), 
        substr(replace(CITYNM,'&','\&amp;'),1,25),    
	STATECD,
	substr(ZIPCD,1,5), 
	rtrim(CNTCTPHNNO),
	rtrim(SLSPERSNCODE),
	rtrim(STATUSCD),
	rtrim(CANCELCD),
	rtrim(SUSPENDDT),
	rtrim(SLSREGION),
	rtrim(AMFIRSTNAME),
	rtrim(AMLASTNAME),
	rtrim(AMPHONE),
	rtrim(AMPHNEXT),
	rtrim(AMEMAIL),
	rtrim(MERCHTYPE),
	rtrim(SERVDESC),
	rtrim(REST1),
	rtrim(REST2),
	rtrim(REST3),
	rtrim(REST4),
	rtrim(REST5),
	rtrim(POSTYPE),
	rtrim(HLPDSKPROVIDER),
	rtrim(STARTDT),
	rtrim(SERVICECD1),
	rtrim(SERVICECD2),
	rtrim(SERVICECD3),
	rtrim(SERVICECD4),
	rtrim(SERVICECD5),
	rtrim(TERRITORY),
      	rtrim(HEATGENDATE),
       	rtrim(HEATMODDATE),
	rtrim(MFDELDT)
	from profile 
	where HEATMODDATE= TO_CHAR(SYSDATE,'YYYY-MM-DD') and datasource = 'Mainframe';
	
tStationNo Profile.StationNo%TYPE;
tAcctNo Profile.AcctNo%TYPE;
tMerchantNm Profile.MerchantNm%TYPE;
tAttention Profile.Attention%TYPE;
tStreetTxt Profile.StreetTxt%TYPE;
tCityNm Profile.CityNm%TYPE;
tStateCD Profile.StateCD%TYPE;
tZipCD Profile.ZipCD%TYPE;
tCntctPhnNo Profile.CntctPhnNo%TYPE;
TSlsPersnCode Profile.SlsPersnCode%TYPE;
tStatusCd Profile.StatusCd%TYPE;
tCancelCd Profile.CancelCd%TYPE;
tSuspendDt Profile.SuspendDt%TYPE;
tSlsRegion Profile.SlsRegion%TYPE;
tAMFirstName Profile.AMFirstName%TYPE;
tAMLastName Profile.AMLastName%TYPE;
tAMPhone Profile.AMPhone%TYPE;
tAMPhnExt Profile.AMPhnExt%TYPE;
tAMEmail Profile.AMEmail%TYPE;
tMerchType Profile.MerchType%TYPE;
tServDesc Profile.ServDesc%TYPE;
tRest1 Profile.Rest1%TYPE;
tRest2 Profile.Rest2%TYPE;
tRest3 Profile.Rest3%TYPE;
tRest4 Profile.Rest4%TYPE;
tRest5 Profile.Rest5%TYPE;
tPOSType Profile.POSType%TYPE;
tHlpDskProvider Profile.HlpDskProvider%TYPE;
tStartDt Profile.StartDt%TYPE;
tServiceCD1 Profile.ServiceCD1%TYPE;
tServiceCD2 Profile.ServiceCD2%TYPE;
tServiceCD3 Profile.ServiceCD3%TYPE;
tServiceCD4 Profile.ServiceCD4%TYPE;
tServiceCD5 Profile.ServiceCD5%TYPE;
tTerritory Profile.Territory%TYPE;
tHeatGenDate Profile.HeatGenDate%TYPE;
tHeatModDate Profile.HeatModDate%TYPE;
tMFDelDt Profile.MFDelDt%TYPE;
action VARCHAR2(1);

  l_file_hdl utl_file.file_type;
BEGIN
  l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45.txt','W',32767);


utl_file.put_line(l_file_hdl,'<?xml version="1.0" encoding="UTF-8"?>');
utl_file.put_line(l_file_hdl,'<merchant_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.test.com/import_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]

open c1;
loop
 [b]  fetch c1 into [/b]
	tStationNo,
	tAcctNo,
	tMerchantNm,
	tAttention,
	tStreetTxt,
	tCityNm,
	tStateCD,
	tZipCD,
	tCntctPhnNo,
	tSlsPersnCode,
	tStatusCd,
	tCancelCd,
	tSuspendDt,
	tSlsRegion,
	tAMFirstName,
	tAMLastName,
	tAMPhone,
	tAMPhnExt,
	tAMEmail,
	tMerchType,
	tServDesc,
	tRest1,
	tRest2,
	tRest3,
	tRest4,
	tRest5,
	tPOSType,
	tHlpDskProvider,
	tStartDt,
	tServiceCD1,
	tServiceCD2,
	tServiceCD3,
	tServiceCD4,
	tServiceCD5,
	tTerritory,
	tHeatGenDate,
	tHeatModDate,
	tMFDelDt;

if tHeatGenDate = TO_CHAR(SYSDATE,'YYYY-MM-DD')
	then action := 'A';

else

if (tHeatGenDate <> TO_CHAR(SYSDATE,'YYYY-MM-DD') or
    tHeatGenDate is null)
	then action := 'C';

	End if;
   End if;

   exit when c1%notfound;

   utl_file.put_line(l_file_hdl, '<merchant>');
	utl_file.put_line(l_file_hdl, '	<action>' || action || '</action>');
	utl_file.put_line(l_file_hdl, '	<alternate_id>' || '9999999' || '</alternate_id>');
	utl_file.put_line(l_file_hdl, '	<customer_id>' || 'CCS45' || '</customer_id>');
	utl_file.put_line(l_file_hdl, '	<merchant_id>' || tAcctNo || '</merchant_id>');
	utl_file.put_line(l_file_hdl, '	<merchant_name>' || tMerchantNm || '</merchant_name>');
	utl_file.put_line(l_file_hdl, '	<merchant_address1>' || tStreetTxt || '</merchant_address1>');
	utl_file.put_line(l_file_hdl, '	<merchant_address2>' || '' || '</merchant_address2>');
	utl_file.put_line(l_file_hdl, '	<merchant_city>' || tCityNm || '</merchant_city>');
	utl_file.put_line(l_file_hdl, '	<merchant_state>' || tStateCD || '</merchant_state>');
	utl_file.put_line(l_file_hdl, '	<merchant_zip>' || tZipCD || '</merchant_zip>');
	utl_file.put_line(l_file_hdl, '	<merchant_country>' || 'US' || '</merchant_country>');
	utl_file.put_line(l_file_hdl, '	<merchant_phone>' || tCntctPhnNo || '</merchant_phone>');
	utl_file.put_line(l_file_hdl, '	<merchant_contact>' || tAttention || '</merchant_contact>');
	utl_file.put_line(l_file_hdl, '	<merchant_email_address>' || '' || '</merchant_email_address>');
	utl_file.put_line(l_file_hdl, '	<email_address_required>' || '0' || '</email_address_required>');
	utl_file.put_line(l_file_hdl, '	<rep_id>' || tSlsPersnCode || '</rep_id>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Sales Rep Name' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || 'N/A' || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Station Number' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tStationNo || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Status' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tStatusCd || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Suspend Reason' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tCancelCd || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Suspend Date' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tSuspendDt || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Sales Region' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tSlsRegion || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Manager First Name' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tAMFirstName || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Manager Last Name' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tAMLastName || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Manager Phone Number' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tAMPhone || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Manager Phone Ext' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tAMPhnExt || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Manager E-mail' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tAMEmail || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Account Type' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tMerchType || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Description' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServDesc || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Restriction 1' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tRest1 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Restriction 2' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tRest2 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Restriction 3' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tRest3 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Restriction 4' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tRest4 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Restriction 5' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tRest5 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'POS Type' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tPOSType || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Help Desk Provider' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tHlpDskProvider || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Start Date' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tStartDt || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Code 1' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServiceCD1 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Code 2' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServiceCD2 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Code 3' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServiceCD3 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Code 4' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServiceCD4 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'Service Code 5' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tServiceCD5 || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<customer_spec_info>');
	utl_file.put_line(l_file_hdl, '			<field_name>' || 'POS Transaction Type' || '</field_name>');
	utl_file.put_line(l_file_hdl, '			<field_value>' || tTerritory || '</field_value>');
	utl_file.put_line(l_file_hdl, '	</customer_spec_info>');
	utl_file.put_line(l_file_hdl, '	<ship>');
	utl_file.put_line(l_file_hdl, '			<ship_name>' || tMerchantNm || '</ship_name>');
	utl_file.put_line(l_file_hdl, '			<ship_address1>' || tStreetTxt  || '</ship_address1>');
	utl_file.put_line(l_file_hdl, '			<ship_city>' || tCityNm  || '</ship_city>');
	utl_file.put_line(l_file_hdl, '			<ship_state>' || tStateCD  || '</ship_state>');
	utl_file.put_line(l_file_hdl, '			<ship_zip>' || tZipCD  || '</ship_zip>');
	utl_file.put_line(l_file_hdl, '			<ship_phone>' || tCntctPhnNo  || '</ship_phone>');
	utl_file.put_line(l_file_hdl, '			<ship_contact>' || tAttention  || '</ship_contact>');
	utl_file.put_line(l_file_hdl, '			<ship_country>' || 'US' || '</ship_country>');
	utl_file.put_line(l_file_hdl, '	</ship>');
   utl_file.put_line(l_file_hdl, '</merchant>');

end loop;

utl_file.put_line(l_file_hdl, '</merchant_import>');

utl_file.fflush(l_file_hdl);

close c1;

utl_file.fclose(l_file_hdl);

end;

/
exit;

 

[SantaMufasa]

But guess what, Stinsman, YOU STILL HAVE NOT POSTED A COPY AND PASTE OF A CODE EXCERPT THAT INCLUDES THE SQL*PLUS-GENERATED LINE NUMBERS FOLLOWING THE EXECUTION OF YOUR CODE. Notice in my last post, that there are line numbers on the left edge of the code excerpt. I need to see a copy-and-paste of the few lines before and after line 128.

How do you know that "fetch c1 into..." is Line 128? Did you count lines, or did you see the number "128" on the screen in front of that line? I need to see the copy-and-paste from SQL*Plus that LITERALLY numbers the lines that appear in your SQL*Plus SQL buffer.

If you have questions or troubles with my specific request, you are welcome to phone me for specific instructions at the phone number that appears on my Dasages web page which URL appears in my signature.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cwsstins]

Sorry for the confusion...yes, the "fetch c1 into" line that I indicated in the complete code is the offending line in a LIST output (line 121 now because I removed some comments from the code prior to running it last):

109 action VARCHAR2(1);
110
111 l_file_hdl utl_file.file_type;
112 BEGIN
113 l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45.txt','W',32767);
114
115
116 utl_file.put_line(l_file_hdl,'<?xml version="1.0" encoding="UTF-8"?>');
117 utl_file.put_line(l_file_hdl,'<import xsi:noNamespaceSchemaLocation="

http://xml.test.c

118
119 open c1;
120 loop
121 fetch c1 into
122 tStationNo,
123 tAcctNo,
124 tMerchantNm,
125 tAttention,
126 tStreetTxt,
127 tCityNm,
128 tStateCD,
129 tZipCD,
130 tCntctPhnNo,
131 tSlsPersnCode,
132 tStatusCd,
133 tCancelCd,
134 tSuspendDt,
135 tSlsRegion,

 

[SantaMufasa]

Stinsman,

Yes, you can call me "incurably retentive", and most people would call me an idiot for doing what I have done, but I wanted you to benefit (long-term) as much as possible from your enquiry. So, I took the opportunity to do some "code-tightening" for you, which cut your code nearly 50% (309 lines down to 175 lines), yet it produces the same results.

In performing this code tightening, we may have also resolved the error you were encountering.

Here is the revised code (using an "implicit cursor" with a CURSOR 'FOR LOOP', which was largely responsible for the code reductions):

set feedback off;
set long 99000;
set serveroutput on size 1000000;
set escape '\'
Declare
    action VARCHAR2(1);
    l_file_hdl utl_file.file_type;
    procedure prt (str_in varchar2) is
    begin
        utl_file.put_line(l_file_hdl,str_in);
    end;
    procedure fmt (label_in varchar2,content varchar2) is
    begin
        prt('    <'||label_in||'>'||content||'</'||label_in||'>');
    end;
BEGIN
    l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45.txt','W',32767);
    prt('<?xml version="1.0" encoding="UTF-8"?>');
    prt('<merchant_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.test.com/import_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]
    for t in (select * from profile 
               where HEATMODDATE=TO_CHAR(SYSDATE,'YYYY-MM-DD')
                 and datasource = 'Mainframe') loop
    if t.HeatGenDate = TO_CHAR(SYSDATE,'YYYY-MM-DD')
        then action := 'A';
    elsif (tHeatGenDate <> TO_CHAR(SYSDATE,'YYYY-MM-DD')
           or t.HeatGenDate is null)
        then action := 'C';
    End if;
    prt('<merchant>');
    fmt('action',action);
    fmt('alternate_id','9999999');
    fmt('customer_id','CCS45');
    fmt('merchant_id',substr(replace(t.ACCTNO,'-',''),1,12); 
    fmt('merchant_name',substr(replace(t.MERCHANTNM,'&','\&amp;'),1,40), );
    fmt('merchant_address1',substr(replace(t.STREETTXT,'&','\&amp;'),1,30); 
    fmt('merchant_address2','');
    fmt('merchant_city',substr(replace(t.CITYNM,'&','\&amp;'),1,25));
    fmt('merchant_state',t.StateCD);
    fmt('merchant_zip',substr(t.ZIPCD,1,5));
    fmt('merchant_country','US');
    fmt('merchant_phone',rtrim(t.CntctPhnNo));
    fmt('merchant_contact',
         DECODE(t.ATTENTION
               ,'', 'N/A'
               ,substr(replace(replace(replace(ATTENTION,'<','\&lt;'),'>','\&gt;'),'&','\&amp;'),1,27)));
    fmt('merchant_email_address','');
    fmt('email_address_required','0');
    fmt('rep_id',rtrim(t.SlsPersnCode));
    prt('customer_spec_info>');
    fmt('field_name','Sales Rep Name');
    fmt('field_value','N/A');
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Station Number');
    fmt('field_value',rtrim(t.StationNo));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Status');
    fmt('field_value',rtrim(t.StatusCd));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Suspend Reason');
    fmt('field_value',rtrim(t.CancelCd));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Suspend Date');
    fmt('field_value',rtrim(t.SuspendDt));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Sales Region');
    fmt('field_value',rtrim(t.SlsRegion));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Manager First Name');
    fmt('field_value',rtrim(t.AMFirstName));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Manager Last Name');
    fmt('field_value',rtrim(t.AMLastName));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Manager Phone Number');
    fmt('field_value',rtrim(t.AMPhone));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Manager Phone Ext');
    fmt('field_value',rtrim(t.AMPhnExt));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Manager E-mail');
    fmt('field_value',rtrim(t.AMEmail));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Account Type');
    fmt('field_value',rtrim(t.MerchType));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Description');
    fmt('field_value',rtrim(t.ServDesc));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Restriction 1');
    fmt('field_value',rtrim(t.Rest1));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Restriction 2');
    fmt('field_value',rtrim(t.Rest2));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Restriction 3');
    fmt('field_value',rtrim(t.Rest3));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Restriction 4');
    fmt('field_value',rtrim(t.Rest4));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Restriction 5');
    fmt('field_value',rtrim(t.Rest5));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','POS Type');
    fmt('field_value',rtrim(t.POSType));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Help Desk Provider');
    fmt('field_value',rtrim(t.HlpDskProvider));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Start Date');
    fmt('field_value',rtrim(t.StartDt));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Code 1');
    fmt('field_value',rtrim(t.ServiceCD1));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Code 2');
    fmt('field_value',rtrim(t.ServiceCD2));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Code 3');
    fmt('field_value',rtrim(t.ServiceCD3));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Code 4');
    fmt('field_value',rtrim(t.ServiceCD4));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','Service Code 5');
    fmt('field_value',rtrim(t.ServiceCD5));
    prt( '    </customer_spec_info>');
    prt( '    <customer_spec_info>');
    fmt('field_name','POS Transaction Type');
    fmt('field_value',rtrim(t.Territory));
    prt( '    </customer_spec_info>');
    prt( '    <ship>');
    fmt('ship_name',substr(replace(t.MERCHANTNM,'&','\&amp;'),1,40));
    fmt('ship_address1',substr(replace(t.STREETTXT,'&','\&amp;'),1,30);
    fmt('ship_city',substr(replace(t.CITYNM,'&','\&amp;'),1,25));
    fmt('ship_state',t.StateCD);
    fmt('ship_zip',substr(t.ZIPCD,1,5));
    fmt('ship_phone',rtrim(t.CntctPhnNo));
    fmt('ship_contact'
               ,DECODE(t.ATTENTION
               ,'', 'N/A'
               ,substr(replace(replace(replace(ATTENTION,'<','\&lt;'),'>','\&gt;'),'&','\&amp;'),1,27)));
    fmt('ship_country','US');
    prt( '    </ship>');
    prt( '</merchant>');
end loop;
prt( '</merchant_import>');
utl_file.fclose(l_file_hdl);
end;
/

Now, since I do not have your table definitions and sample data, I could not test execute this code. Resultingly, if there are any syntax errors, I propose that you simply work through any errors, and certainly don't give up on the code since it is much, much tighter.

If you have any questions about any constructs or techniques that I have used, please re-post. Also, please advise us if the "ORA-06502: PL/SQL: numeric or value error" persists. But if the problem persists in the revised code, we should be able to isolate the problem more quickly.

Let us know your thoughts and findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cwsstins]

Wow...what can I say? That's awesome. I've got about 3 dozen more scripts that I'm going to send your way for some "tightening up"

Seriously, it took a little tweaking, but it works perfectly now. Thanks a lot!

 

[SantaMufasa]

Thanks, Stinsman, for your acknowledgement.

I'm delighted to do things like code tightening, script writing, or anything else having to do with SQL and/or Database Administration.

The above tightening exercise, as you can imagine, took over an hour. It was very satisfying to do that for you...(For me, it's nearly recreational.)

For future activities like the above (that turn out to be more service/project oriented versus a "simple question", which is what Tek-Tips is really set up to do), may I suggest your directing the request via the link in my signature, below. I have several very satisfied customers that found me via our Tek-Tips friendships and activities. It is a very affordable option and the quality of service via that channel is far higher (and quicker) in cases like yours, than we can provide via Tek-Tips.

Best regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

BTW, a couple of observations that I should have made earlier:

1) If you "SET DEFINE OFF", it disables the default behaviour of the "&" character. That may save you all of the REPLACE-ing that you did.

2) Why did you need to "RTRIM" everything? Unless you are storing blank trailing spaces or using CHAR instead of VARCHAR, the "RTRIM" is extraneous (wasting execution cycles).

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Thargy]

Santa,

above and beyond, way, way, way above and beyond.

T

Grinding away at things Oracular

 

[cwsstins]

1 - thanks for the info on that...I had been experimenting with the "\" escape character quite a bit

2 - there are trailing blank spaces in many of the fields

Thanks.

 

[SantaMufasa]

Stinsman,

1) What is causing the trailing blank spaces?

2) If the trailing blanks are "unavoidable", then why not strip them off with a BEFORE INSERT OR UPDATE trigger?

The Rule of Thumb is, "Bad data should live as short a life as possible."...And trainling blanks are "bad data". By getting rid of the trailing blanks immediately, you avoid having to deal with them ever again.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cwsstins]

The data are being imported from another system (that I don't manage) and that is where the data originate.

The trigger makes sense...I will give that a try. Thanks again Mufasa, for everything.