Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Unanswered: opening files dynamically

    Hi,
    I am writing a plsql script that will read values from a table and write into a file and when the count exceeds 500,,it will create a new file and start writing to it. So each file should have max 500 records...
    Can someone tell me how do i do it? What do i put in declare part?

    Thanks,
    kc132

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Create something like this
    Code:
    declare
    
    cursor my_cur is
    select object_owner,object_name
    from all_objects
    order by object_owner,object_name;
    
    cnt number;
    total number;
    fhandle UTL_FILE.FILE_TYPE;
    
    begin
    
    cnt := null;
    total := 1;
    
    for rec in my_cur loop
      if cnt iis null then
        fhandle := UTL_FILE.FOPEN ('/usr/dump','dump'||to_char(total)||'.txt','w');
         cnt := 1;
       end if;
    
        if cnt = 500 then
          cnt := 0;
           UTL_FILE.FCLOSE (fhandle);
           total := total + 1;
           fhandle := UTL_FILE.FOPEN ('/usr/dump','dump'||to_char(total)||'.txt','w');
         end if;
      UTL_FILE.PUT_LINE (fhandle,rec.object_owner||','||rec.object_name);
       cnt := cnt + 1;
    end loop;
    
    if cnt is not null then
      UTL_FILE.FCLOSE (fhandle);
    end if;
    
    end;
    This code assumes that the database is configured and has the directory rights to write to the /usr/dump directory.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I would agree with this method, but would make a few modifications.

    Instead of storing the count, use the built in cursor attributes.

    I also like to use the MOD function to determine when to reset filename.

    Code:
    DECLARE
    
    Cursor c_name Is
       SELECT
       FROM
       WHERE;
    
    lv_file_name    VARCHAR2(100);
    lv_file_dir       VARCHAR2(100);
    lv_file_hand    UTL_FILE.FILE_TYPE;
    
    BEGIN 
    
       lv_file_dir  := '';
       lv_file_name := 'name_';
    
       FOR cur_rec in c_name LOOP      
    
          -- if modulus function returns 0, then we have processed 500 records.
          IF MOD (cur_rec%ROWCOUNT,500) = 0 THEN
    
    
             -- if file is open, close it so we can open new file
             IF UTL_FILE.IS_OPEN(lv_file_hand) THEN
          
                UTL_FILE.FCLOSE(lv_file_hand);
    
             END IF;
    
             -- change file name
             lv_file_name := SUBSTR(lv_file_name,1,5)||cur_rec%ROW_COUNT;
    
             -- Open new file
             LV_FH := UTL_FILE.FOPEN(lv_file_dir, lv_file_name, 'w');
    
          END IF;
    
          -- Write Record to file
          UTL_FILE.PUT_LINE(lv_fh,cur_rec.field);
    
       END LOOP;
    
    EXCEPTIONS
       -- Add exception handling
    END;
    HIH

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I like it, much cleaner! There is only one thing I would change. Make sure to close the file when you finish the loop.

    Code:
    DECLARE
    
    Cursor c_name Is
       SELECT
       FROM
       WHERE;
    
    lv_file_name    VARCHAR2(100);
    lv_file_dir       VARCHAR2(100);
    lv_file_hand    UTL_FILE.FILE_TYPE;
    
    BEGIN 
    
       lv_file_dir  := '';
       lv_file_name := 'name_';
    
       FOR cur_rec in c_name LOOP      
    
          -- if modulus function returns 0, then we have processed 500 records.
          IF MOD (cur_rec%ROWCOUNT,500) = 0 THEN
    
    
             -- if file is open, close it so we can open new file
             IF UTL_FILE.IS_OPEN(lv_file_hand) THEN
          
                UTL_FILE.FCLOSE(lv_file_hand);
    
             END IF;
    
             -- change file name
             lv_file_name := SUBSTR(lv_file_name,1,5)||cur_rec%ROW_COUNT;
    
             -- Open new file
             LV_FH := UTL_FILE.FOPEN(lv_file_dir, lv_file_name, 'w');
    
          END IF;
    
          -- Write Record to file
          UTL_FILE.PUT_LINE(lv_fh,cur_rec.field);
    
       END LOOP;
    
       IF UTL_FILE.IS_OPEN(lv_file_hand) THEN
          
          UTL_FILE.FCLOSE(lv_file_hand);
    
       END IF;
    
    
    EXCEPTIONS
       -- Add exception handling
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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