I'm using UTL_FILE and it appears that it creates the file OK for a while and then it stops mid-stream. Is there something I need to override some sort of default max file size?
Printable View
I'm using UTL_FILE and it appears that it creates the file OK for a while and then it stops mid-stream. Is there something I need to override some sort of default max file size?
Can someone please help me with my question? I need to find out why my files seem to be getting chopped off in the middle of the file. I know there should be more records than are actually getting written to the file.
Perhaps you could post your program.
It's too big to post. What would you be looking for? The should be 1839 records in the 1st file and only 575 are getting written to the file.
The problem could be any number of things, including a bug in your program.Quote:
Originally posted by ssmith001
It's too big to post. What would you be looking for? The should be 1839 records in the 1st file and only 575 are getting written to the file.
How would someone debug a program that they have never seen? :rolleyes:
Have you looked at this?
How do you suggest that I post the program. I'm not allowed to post it because it exceeds the limit of this forum.
If you post something that is too long, people won't read th whole thing anyway. Just post the relevant parts. Also make sure that you aren't obscuring potential error messages.Quote:
Originally posted by ssmith001
How do you suggest that I post the program. I'm not allowed to post it because it exceeds the limit of this forum.
OK. I've chopped out a lot of the junk and left the good parts.
DECLARE
-------------------------------
-- Variables for BOL record --
-------------------------------
l_bol_cont_mv_disc VARCHAR2(11);
l_bol_pallet_chg VARCHAR2(11);
l_bol_misc_chg VARCHAR2(11);
l_bol_cancel_truck VARCHAR2(11);
l_bol_sfi_out_route_mile VARCHAR2(11);
l_bol_return VARCHAR2(11);
l_bol_sfi_detent VARCHAR2(11);
l_bol_fuel_sur VARCHAR2(11);
l_bol_redeliv VARCHAR2(11);
l_bol_reconsign VARCHAR2(11);
l_bol_cust_out_route_mile VARCHAR2(11);
l_bol_cust_detent VARCHAR2(11);
l_bol_lmpr_chg VARCHAR2(11);
l_bol_unload VARCHAR2(11);
l_bol_vouch_amt VARCHAR2(11);
l_bol_tot_wgt VARCHAR2(11);
l_bol_line_haul NUMBER(13,2);
----------------------------------
-- Variables for BOLORD record --
----------------------------------
l_bolord_invc_prd VARCHAR2(2);
l_bolord_invc_year VARCHAR2(2);
l_bolord_invc_cntry VARCHAR2(2);
l_bolord_ship_prd VARCHAR2(2);
l_bolord_ship_year VARCHAR2(2);
l_bolord_ship_cntry VARCHAR2(2);
l_bolord_sfi_detent VARCHAR2(11);
l_bolord_cont_mv_disc VARCHAR2(11);
l_bolord_pallet_chg VARCHAR2(11);
l_bolord_misc_chg VARCHAR2(11);
l_bolord_cancel_truck VARCHAR2(11);
l_bolord_sfi_out_route_mile VARCHAR2(11);
l_bolord_misc_chrg VARCHAR2(11);
l_bolord_return VARCHAR2(11);
l_bolord_fuel_surchg VARCHAR2(11);
l_bolord_fuel_test VARCHAR2(11);
l_bolord_redlvry VARCHAR2(11);
l_bolord_reconsign VARCHAR2(11);
l_bolord_cust_out_route_mile VARCHAR2(11);
l_bolord_cust_detent VARCHAR2(11);
l_bolord_lmpr_chg VARCHAR2(11);
l_bolord_unload_chrg VARCHAR2(11);
l_bolord_accrd_wgt VARCHAR2(12);
l_dir VARCHAR2(60);
l_bol_file VARCHAR2(60);
l_bolord_file VARCHAR2(60);
l_output1 utl_file.file_type;
l_output2 utl_file.file_type;
---------------------------------------------------------------
-- Select BOL information into cursor
---------------------------------------------------------------
CURSOR bol_cursor IS
SELECT ltrim(to_char(ord_key_seq.nextval@plogist,'00000009')) AS BOL_KEY -- Use ORD_KEY_SEQ in DLOGIST database
, LPAD(SUBSTR(v.vendor_nbr, 1, DECODE(INSTR(v.vendor_nbr,'-')-1,
-1, length(v.vendor_nbr),
INSTR(v.vendor_nbr,'-')-1)),4,0) AS VEND_NBR
, '000' AS INVC_DATE_CNTRY
, to_char(i.inv_date,'YYDDD') AS INV_DATE
, h.fscl_prd AS FSCL_PRD
, substr(h.fscl_year,3,2) AS FSCL_YEAR
, to_char(i.inv_ship_date,'MMDDYY') AS SHIP_DATE
, substr(i.inv_bol,6,6) AS INV_BOL_FOR_FILE --needed because the inv_bol is used later on
--as the key for looking for accessorials
, i.inv_bol AS INV_BOL
, lpad(i.inv_distance,5,0) AS MILES
, rpad(replace(i.origin_postal_code,'-',null),9) AS SHIP_PT_ZIP
, i.inv_carrier_id AS CARRIER_CODE
, ' ' AS FRT_BILL_NBR
, '000' AS RCV_DATE_CEN
, to_char(l.trip_end_date,'YYDDD') AS RCV_DATE
, rpad(c.notes,35) AS CARRIER_NAME
FROM fp_vendor_info v
, fp_invoice i
, cyc_date_history@prod h
, load l
, carrier c
WHERE trunc(i.inv_ship_date) = h.cal_day_greg
AND i.inv_carrier_id = v.carrier_id
AND i.match_load_id = l.bill
AND i.inv_carrier_id = c.id
AND i.status_code = 70 -- only grab vouchered invoices
AND i.inv_bol NOT IN (SELECT trim(BOL_NBR) FROM SFITRAN.SFI_TMS_CUST_SPECIFIC_FREIGHT);
bol_rec bol_cursor%ROWTYPE;
---------------------------------------------------------------
-- Select BOLORD information into cursor
---------------------------------------------------------------
CURSOR bolord_cursor(inv_bol VARCHAR2) IS
SELECT rpad(so.ord_id,29) AS ORD_NBR
, ltrim(to_char(sol.ord_line_weight,'099999999999')) AS GROSS_WGT
, ltrim(to_char(NVL(sc.total,0),'0999999999.99')) AS ACCRD_AMT
, to_char(round(sc.haul_cost,2),'09999999.99') AS ORD_LINE_HAUL
, ltrim(to_char(round(sc.stopoff,2),'09999999.99')) AS STOP_CHRG
, rpad(replace(i.origin_postal_code,'-',null),9) AS SHIPPER_ZIP
, rpad(replace(i.dest_postal_code,'-',null),9) AS SHIPTO_ZIP
, sh.ship_to_loc_id AS CUST_NBR
, '00000000.00' AS EQUIP_DISC
, lpad(sh.dest_trip_stop_nbr,2,'0') AS STOP_SEQ
FROM fp_shipment_order so
, fp_shipment_ord_ln sol
, fp_shipment_header sh
, fp_so_cost sc
, fp_invoice i
WHERE so.load_id = bol_rec.inv_bol -- BOL # of the load from the bol cursor
AND so.load_id = sol.load_id
AND so.shipment_bol = sol.shipment_bol
AND so.ord_id = sol.ord_id
AND so.cost_key = sc.cost_key
AND sc.cost_type = 'I' --(I)nvoiced vs. (R)ated
AND i.match_load_id = so.load_id
AND so.load_id = sh.load_id
AND sh.shipment_bol = sol.shipment_bol;
bolord_rec bolord_cursor%ROWTYPE;
BEGIN
------------------------------------------
-- Format the file names so that they have
-- an extension of 'YYYYMMDD'
------------------------------------------
l_dir := '/app/manuv711/pnetwork/transserv/in';
select 'boldly.tms.'||to_char(sysdate, 'YYYYMMDD') into l_bol_file from dual;
select 'orddly.tms.'||to_char(sysdate, 'YYYYMMDD') into l_bolord_file from dual;
------------------------------------------
-- check to see if either file is open
------------------------------------------
IF utl_file.is_open(l_output1) THEN
utl_file.fclose(l_output1);
END IF;
IF utl_file.is_open(l_output2) THEN
utl_file.fclose(l_output2);
END IF;
------------------------------------------
-- open both bol.dat and bolord.dat files
------------------------------------------
l_output1 := UTl_file.FOPEN( l_dir, l_bol_file, 'w') ;
l_output2 := UTl_file.FOPEN( l_dir, l_bolord_file, 'w') ;
OPEN bol_cursor;
LOOP
FETCH bol_cursor into bol_rec;
EXIT WHEN bol_cursor%NOTFOUND;
-- Grab the continuous move discount accessorial charges if they exist
SELECT ltrim(to_char(NVL(SUM(m.charge),0),'09999999.99')) into l_bol_cont_mv_disc
FROM fp_acc_master m
WHERE m.accessorial_id = 'CMD'
AND m.load_id = bol_rec.inv_bol;
-- Grab the Pallet accessorial charges if they exist
SELECT ltrim(to_char(NVL(SUM(s.charge),0),'09999999.99')) into l_bol_pallet_chg
FROM fp_acc_shipment s
WHERE s.accessorial_id = 'PEC'
AND s.load_id = bol_rec.inv_bol;
-------------------------------
-- Write data to BOL flat file
-------------------------------
utl_file.put_line(l_output1,bol_rec.bol_key||
bol_rec.vend_nbr||
bol_rec.invc_date_cntry||
bol_rec.inv_date||
bol_rec.fscl_prd||
bol_rec.fscl_year||
bol_rec.ship_date||
bol_rec.inv_bol_for_file);
INSERT INTO SFITRAN.SFI_TMS_CUST_SPECIFIC_FREIGHT(bol_key, bol_nbr, process_date)
values (ord_key_seq.CURRVAL@plogist, bol_rec.inv_bol, SYSDATE);
COMMIT;
------------------------------------------------------------------
-- For each BOL record written above, now run out and get all the
-- order information for the BOL number held in bol_rec.inv_bol
------------------------------------------------------------------
FOR bol_ord_rec IN bolord_cursor(bol_rec.inv_bol)
LOOP
-- select period, year, and century of the invoice date
SELECT h.fscl_prd AS INVC_PRD
, substr(h.fscl_year,3,2) AS INVC_YEAR
, '00' AS INVC_CNTRY
INTO l_bolord_invc_prd
, l_bolord_invc_year
, l_bolord_invc_cntry
FROM cyc_date_history@prod h
, fp_invoice i
WHERE trunc(i.inv_ship_date) = h.cal_day_greg
AND i.match_load_id = bol_rec.inv_bol;
----------------------------------
-- Write data to BOLORD flat file
----------------------------------
utl_file.put_line(l_output2,bol_rec.bol_key||
l_bolord_ship_prd||
l_bolord_ship_year||
l_bolord_ship_cntry);
END LOOP;
END LOOP;
IF bolord_cursor%ISOPEN
THEN
CLOSE bolord_cursor;
END IF;
--------------------------
-- close both files
--------------------------
utl_file.fclose( l_output1 );
utl_file.fclose( l_output2 );
END;
It's a long shot, but try running utl_file.fflush before closing the files.
What did you do to validate that not all the data is being written? Have you checked the database alert log for any suspicious error messages?
The long shot unfortunately didn't work. I have the DBA looking into the alert logs.
Try writing one last line before you close the files, just to make sure that the program finishing normally.Quote:
Originally posted by ssmith001
The long shot unfortunately didn't work. I have the DBA looking into the alert logs.
I tried that and it never printed. Looks like it never gets to the bottom.
Hence my comment about unhandled exception.Quote:
Originally posted by ssmith001
I tried that and it never printed. Looks like it never gets to the bottom.
So is there a bug in Oracle or your program?
Start by making sure that your query returns the correct rows.
You may want to add these SELECT statements as a part of your main query.
When a SELECT INTO dosn't find data it throws an error. So One missing
record will cause your program to error out.
Code:-- Grab the continuous move discount accessorial charges if they exist
SELECT ltrim(to_char(NVL(SUM(m.charge),0),'09999999.99')) into l_bol_cont_mv_disc
FROM fp_acc_master m
WHERE m.accessorial_id = 'CMD'
AND m.load_id = bol_rec.inv_bol;
-- Grab the Pallet accessorial charges if they exist
SELECT ltrim(to_char(NVL(SUM(s.charge),0),'09999999.99')) into l_bol_pallet_chg
FROM fp_acc_shipment s
WHERE s.accessorial_id = 'PEC'
AND s.load_id = bol_rec.inv_bol;
Bingo, I had a WHEN OTHER exception that was closing the cursor...CRAP!!! Thanks for all your help and patience!
You should make both cursors implicit as well.Quote:
Originally posted by ssmith001
Bingo, I had a WHEN OTHER exception that was closing the cursor...CRAP!!! Thanks for all your help and patience!
Just for debugging, try putting this after every utl_file.put_line. If you're crashing out before the end, there may be lines in OS buffers that you aren't seeing.Quote:
Originally posted by slimdave
It's a long shot, but try running utl_file.fflush before closing the files.
P.S: too late :)
Can you explain your stmt about making the cursors explicit?
Whee! I'm a genius!Quote:
Originally posted by ssmith001
Bingo, I had a WHEN OTHER exception that was closing the cursor...CRAP!!! Thanks for all your help and patience!
Sorry who mentioned error handling??? ;)Quote:
Originally posted by slimdave
*Originally posted by ssmith001
Bingo, I had a WHEN OTHER exception that was closing the cursor...CRAP!!! Thanks for all your help and patience!*
Whee! I'm a genius!
That's like Agatha Christie bringing in a new character in the last 10 pages of a book. Dirty Pool by my standards. ;)Quote:
How did you know it was posted on Oracle.com?
On the other hand you post was today at 1:07 PM. I told him to look at the error handler without benefit of seeing code yesterday. Now its my turn.Quote:
Originally posted by gandolf989
If you post something that is too long, people won't read th whole thing anyway. Just post the relevant parts. Also make sure that you aren't obscuring potential error messages.
Whee! I'm a genius! ;)
I'll tell you what -- you can have the popular vote, and I'll have the electoral college. How's that for a compromise?Quote:
Originally posted by gandolf989
On the other hand you post was today at 1:07 PM. I told him to look at the error handler without benefit of seeing code yesterday. Now its my turn.
Whee! I'm a genius! ;)
If you really want, you can have all of the credit. ;)Quote:
Originally posted by slimdave
I'll tell you what -- you can have the popular vote, and I'll have the electoral college. How's that for a compromise?