1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need assistance to manage concurrent request if file not found

Discussion in 'Oracle Apps Technical' started by RG Hegde, Jul 23, 2015.

  1. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello ,

    I am loading the data from text file to staging table using UTL_FILE .

    My requirement is if the file is not exist at server location the the request should wait/hold .

    Can anyone please assist how could I manage this in the package
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi RG,

    Try with the below code. This will complete only the file found in the specified path otherwise it will keep on running. Hope it will help you.

    Code (SQL):
    --- CREATE OR REPLACE DIRECTORY XX_DIR as '<file path>';
    --
    DECLARE
      p_DirName  varchar2(30) := 'XX_DIR';
      p_FileName varchar2(40) := 'Test.txt';
      l_fexists BOOLEAN;
      l_flen   NUMBER;
      l_bsize  NUMBER;
      l_res    NUMBER(1);
      ln NUMBER;
    BEGIN
      l_res := 0;
      ln := 0;
      LOOP
       
      utl_file.fgetattr(UPPER(p_DirName), p_FileName, l_fexists, l_flen, l_bsize);
        ln := ln+1;
      IF l_fexists
      THEN
        l_res := 1;
      END IF;
      dbms_output.put_line(ln||' - l_res : '||l_res);
        EXIT WHEN l_fexists;
        dbms_lock.sleep(10);
    END LOOP;
    dbms_output.put_line(' l_res '||l_res);
    dbms_output.put_line(' l_flen '||l_flen);
    dbms_output.put_line(' l_bsize '||l_bsize);
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('EXCEPTION - '||SQLERRM);
    END;
    /

     
    Regards
    Sambasiva Reddy.K
     
    RG Hegde likes this.