DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: Question about UTl_file.FOPEN

  1. #11
    Join Date
    Aug 2003
    Posts
    100
    The long shot unfortunately didn't work. I have the DBA looking into the alert logs.

  2. #12
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by ssmith001
    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.

  3. #13
    Join Date
    Aug 2003
    Posts
    100
    I tried that and it never printed. Looks like it never gets to the bottom.

  4. #14
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by ssmith001
    I tried that and it never printed. Looks like it never gets to the bottom.
    Hence my comment about unhandled exception.
    So is there a bug in Oracle or your program?
    Start by making sure that your query returns the correct rows.

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

  6. #16
    Join Date
    Aug 2003
    Posts
    100
    Bingo, I had a WHEN OTHER exception that was closing the cursor...CRAP!!! Thanks for all your help and patience!

  7. #17
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by ssmith001
    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.

  8. #18
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    It's a long shot, but try running utl_file.fflush before closing the files.
    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.

    P.S: too late

  9. #19
    Join Date
    Aug 2003
    Posts
    100
    Can you explain your stmt about making the cursors explicit?

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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!
    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