using utl_file to write records to a txt file

[sweetleaf]

hi,

i have a stored procedure which parses thru a comma delimmited file and performs updates, inserts etc based upon the record status' of the data.
if a record is an "unacceptable" status i would like to write the record to an exception file.so, if there were 15
"unacceptable" records i would want an exception file containing the 15 entries.

so far every method i use allows me to write only 1 record(the last record in the txt file).

Any help would be greatly appreciated!
Mark

 

[karluk]

There most likely is a logic flaw in your stored procedure. The utl_file package is certainly capable of writing multiple records to file.

If it's not too long, please post the source code of your procedure. Maybe someone can spot the error.

 

[sweetleaf]

Thats a great idea karluk,

here's the code. i don;t know why this won't work:


PROCEDURE SP_1 IS
/*************************************************************************
* AUTHOR : Mark
* RETURNS : N/A
* NOTES : N/A
* MODIFIED : (MM/DD/YY)
* mark : 03/12/2002 - Creation
*************************************************************************/
v_errorCode NUMBER;
v_errorMessage VARCHAR2(1000);
delim CHAR(1) := ',';
v_numchars NUMBER(3);
v_currloc NUMBER(3) := 1;
v_delimloc NUMBER(3) := 0;
loc VARCHAR2(50) := '/export/home/transporter/db';
file_in VARCHAR2(50) := 'File_1.txt';
fid_in UTL_FILE.FILE_TYPE;
fid_out UTL_FILE.FILE_TYPE;
line_in VARCHAR2(500);
line_out VARCHAR2(1000);
RMA_num VARCHAR2(16);
rooNum VARCHAR2(16);
changeDt DATE;
v_statuscheck VARCHAR2(5);
fileLen NUMBER(5);

efid_out UTL_FILE.FILE_TYPE;
eline_out VARCHAR2(1000);
loc_out varchar2(50) :='/export/home/transporter/db';
file_out varchar2(50) :='exception_jonic_sent_to_nesa' || TO_CHAR(sysdate,'YYYYMMDD') || '.TXT';


BEGIN
efid_out := UTL_FILE.FOPEN (loc_out, file_out, 'W');
fid_in := UTL_FILE.FOPEN(loc, file_in,'R');

UTL_FILE.GET_line (fid_in, line_in);
LOOP
begin
UTL_FILE.GET_line (fid_in, line_in);
fileLen :=length(line_in);
exception
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(efid_out); --no more data, but ok
UTL_FILE.FCLOSE (fid_in);
exit;
end;

/* Parse the line_in */
/* COL 1 - RMA# */
v_currloc := v_delimloc + 1;
v_delimloc := INSTR (line_in, delim, v_currloc);
v_numchars := v_delimloc - v_currloc;

IF LENGTH(translate(SUBSTR (line_in, v_currloc, v_numchars),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ()-.#$%^&*@!_=+`~[]{};<>,/?\|','1234567890')) > 0 THEN
RMA_num := SUBSTR (line_in, v_currloc, v_numchars);
dbms_output.put_line('RMA#: ' || RMA_num);
END IF;


/* COL 2 - ROO NUMBER */
v_currloc := v_delimloc + 1;
v_delimloc := INSTR (line_in, delim, v_currloc);
v_numchars := v_delimloc - v_currloc;

IF LENGTH(translate(SUBSTR (line_in, v_currloc, v_numchars),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ()-.#$%^&*@!_=+`~[]{};<>,/?\|','0123456789')) > 1 THEN
rooNum := SUBSTR (line_in, v_currloc, v_numchars);
dbms_output.put_line('ird#: ' || rooNum);
END IF;
--v_currloc := v_delimloc + 1;
/* the actual sql statement */


/* COL 3 - CHANGE DT*/
v_currloc := v_delimloc + 1;
v_delimloc := fileLen; --INSTR (line_in, delim, v_currloc);
v_numchars := v_delimloc - v_currloc;

--IF LENGTH(translate(SUBSTR (line_in, v_currloc, v_numchars),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ()-.#$%^&*@!_=+`~[]{};<>,/?\|','0123456789')) > 1 THEN
changeDt := to_date(SUBSTR (line_in, v_currloc, v_numchars),'mm/dd/yy hh:mi:ss');
dbms_output.put_line('change date: ' || changeDt);
--END IF;
--v_currloc := v_delimloc + 1;




/* get ird status for the last record for this ird */
SELECT table_1
INTO v_statuscheck
FROM table_1
WHERE ird_no = rooNum AND
newest_status_ind = 1;



/* if current status is 13 set it to 6 ('REPAIR') and update with RMA# else write to exception log */

if changedt is not null then
/* make new record */
insert into table_1
(ird_no,status,change_date,newest_status_ind,rec_create_date,rma_no)
values(rooNum,'28',changedt,3,sysdate,rma_num);


/* provide temp newest_status 3 as where clause so new record can be inserted then updated based on 3
then reset via another update to newest_status 0 */
UPDATE table_1
SET newest_status_ind = 2
WHERE ird_no = rooNum AND
newest_status_ind = 1;


/* update the newest record with the values of the former record in newest_status 3 */
UPDATE table_1
SET STATUS_CHANGE_REASON = (select STATUS_CHANGE_REASON from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
RETAILER_NO = (select RETAILER_NO from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
RETAILER_STORE_NO = (select RETAILER_STORE_NO from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
REC_SOURCE = (select REC_SOURCE from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
ACCT_NO = (select ACCT_NO from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
CONVERTER_MAKE_CD = (select CONVERTER_MAKE_CD from mdu_ird_status where ird_no=roonum and newest_status_ind = 2),
NEWEST_STATUS_IND = 1
WHERE ird_no = rooNum AND
newest_status_ind = 3;

/* update former record with newest status from 3 to 0 */
UPDATE table_1
SET newest_status_ind = 0
WHERE ird_no = rooNum AND
newest_status_ind = 2;


/***********************************************THIS IS WHERE THE PROBLEM IS: **************************************

if v_statuscheck <> 13 then


/* write to exception file */

eline_out:=rtrim('WARNING: ' || rooNum || ' was not at REPACKAGE status');
UTL_FILE.PUT_LINE(efid_out,eline_out);



end if;

/*******************************************************************************************************************

end if;

COMMIT;

v_numchars := NULL;
v_currloc := 1;
v_delimloc := 0;
--IF v_errors IS NOT NULL THEN
-- line_out := line_in || ' - ' || v_errors;
--UTL_FILE.PUT_LINE(fid_out,line_out);
-- v_errors := NULL;
--END IF;
line_in := null;
line_out := null;
eline_out := null;

END LOOP;
UTL_FILE.FCLOSE (fid_in);
UTL_FILE.FCLOSE(efid_out);


EXCEPTION
/*
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE (fid_in);
line_out := SQLCODE || SUBSTR(SQLERRM, 1, 100) || LINE_IN ||'NO DATA FOUND';
return_c:= -1;
-- UTL_FILE.FCLOSE (fid_out);
*/

WHEN UTL_FILE.INVALID_MODE THEN

v_errorCode :=SQLCODE;
v_errorMessage :=SUBSTR(SQLERRM, 1, 100);

line_out := v_errorCode || v_errorMessage || LINE_IN ||'INVALID MODE';
dbms_output.put_line(line_out);
-- return_c:= -1;


WHEN UTL_FILE.INVALID_PATH THEN

v_errorCode :=SQLCODE;
v_errorMessage :=SUBSTR(SQLERRM, 1, 100);

line_out := v_errorCode || v_errorMessage || LINE_IN ||'INVALID PATH';
dbms_output.put_line(line_out);
-- return_c:= -1;


WHEN UTL_FILE.INVALID_OPERATION THEN

v_errorCode :=SQLCODE;
v_errorMessage :=SUBSTR(SQLERRM, 1, 100);

line_out := v_errorCode || v_errorMessage || LINE_IN ||'INVALID OP';
dbms_output.put_line(line_out);
-- return_c:= -1;

WHEN OTHERS THEN

v_errorCode :=SQLCODE;
v_errorMessage :=SUBSTR(SQLERRM, 1, 100);

line_out := v_errorCode || v_errorMessage || LINE_IN ||'2: THE IMPORT DATA FILE HAS ERRORS AND DATA COULD NOT BE IMPORTED';
dbms_output.put_line(line_out);


-- UTL_FILE.PUT_LINE(fid_out,line_out);
UTL_FILE.FCLOSE (fid_in);
UTL_FILE.FCLOSE (fid_out);
-- return_c:= -1;

END SP_1;