DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Question about UTl_file.FOPEN

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    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?

  2. #2
    Join Date
    Aug 2003
    Posts
    100

    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.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Perhaps you could post your program.

  4. #4
    Join Date
    Aug 2003
    Posts
    100
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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?

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Have you looked at this?

  7. #7
    Join Date
    Aug 2003
    Posts
    100
    How do you suggest that I post the program. I'm not allowed to post it because it exceeds the limit of this forum.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  9. #9
    Join Date
    Aug 2003
    Posts
    100
    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;

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width