-
Question about UTl_file.FOPEN
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?
-
HELP!!!
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.
-
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.
The problem could be any number of things, including a bug in your program.
How would someone debug a program that they have never seen?
-
-
How do you suggest that I post the program. I'm not allowed to post it because it exceeds the limit of this forum.
-
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.
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.
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|