This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 30, 2013 5:28 AM by RaviR RSS

Create a Multiple Files using PL-SQL

RaviR Newbie
Currently Being Moderated

Hi Experts,

I have a table which contains 360 records and want to create multiple files with each file containing 12 records.

 

I tried to use the below code, however was unsuccessfull..

Please help!!!!

 

 

DECLARE

   num      NUMBER             := 1;

   f_file   UTL_FILE.file_type;

   row_sm   NUMBER             := 0.00;

 

CURSOR c_cur

   IS

      SELECT   customer_key, all_columns_concat

          FROM demo;

c_rec    c_cur%ROWTYPE;

BEGIN

   OPEN c_cur;

   LOOP

      FETCH c_cur

       INTO c_rec;

 

     EXIT WHEN c_cur%NOTFOUND;

      WHILE num = c_rec.file_num

      LOOP

         f_file := UTL_FILE.fopen ('DM_DIR', 'FILE_' || num, 'w', 3267);

         num := num + 1;

      END LOOP;


      UTL_FILE.put_line (f_file, c_rec.all_columns_concat);

      row_sm := c_rec.sum_row + row_sm;

   END LOOP;


   UTL_FILE.fclose (f_file);

  CLOSE c_cur;

END;

 

Regards,

RaviR.

  • 1. Re: Create a Multiple Files using PL-SQL
    SomeoneElse Guru
    Currently Being Moderated

    > I tried to use the below code, however was unsuccessfull..

     

    Always tell us what went wrong or what error you got.  "unsuccessful" doesn't tell us anything.

  • 2. Re: Create a Multiple Files using PL-SQL
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    here is working code :

     

    -- Created on 29.10.2013 by RAMIN.H

    declare

      cursor cur_select is  SELECT level f FROM dual connect by level <= 100;


      rec_cur_select cur_select%ROWTYPE;

      n number;

      f utl_file.file_type; 

    begin

        n := 12;

     

        open cur_select;

        loop

          fetch cur_select

            into rec_cur_select;

     

            exit when cur_select%NOTFOUND;

         

            if  mod(n,12) = 0 then

                f := utl_file.fopen('GL_OUT','file'||(n/12),'W',3267);          

                utl_file.put_line(f,rec_cur_select.f);

            end if;

            n := n+1;

         

            utl_file.put_line(f,rec_cur_select.f);

         

            if  mod(n,12) = 0 then

                utl_file.fclose(f);

            end if;      

        end loop;

        close cur_select;

     

        utl_file.fclose(f);

    end;

     

    ----

    Ramin Hashimzade

  • 3. Re: Create a Multiple Files using PL-SQL
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    What was unsuccessful?  If you get an error, post the full error message.  If you get wrong results, explain the problem and post the expected results from some sample data (CREATE TABLE and INSERT statments) that you post.

     

    This part of the code looks very suspicious:

     

      WHILE num = c_rec.file_num

          LOOP

             f_file := UTL_FILE.fopen ('DM_DIR', 'FILE_' || num, 'w', 3267);

             num := num + 1;

          END LOOP;

    Why are you opening several files, one immediately after the other?  Since you're using the same variable (f_file) for all of them, you'll only be able to srite to the last one.

     

    If you want to write 12 lines to each file, maybe you should open one file at the beginning, and keep count of how many lines you write to it.  Immediately before writing, check if you're about the write the 13th line.  If so, close that file, open a new one (you can use the same f_file) and re-set the line counter.  Remember to close the last file after writing the last row.

  • 4. Re: Create a Multiple Files using PL-SQL
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi,

     

    As said SomeoneElse , we can not see errors.

    Please check : http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html

    I think it will be helpful for you.

     

    Regards

    Mahir M. Quluzade

  • 5. Re: Create a Multiple Files using PL-SQL
    Suri Pro
    Currently Being Moderated

    Hi Ravi,

     

    Did you see below line. What is file_num ? Where you have declared ? How you are fetching this value to c_rec ? Did you observe ?

     

    WHILE num = c_rec.file_num 

     

    Please post the code with actual error message. Then quickly we can figure out where the problem is.

     

    Cheers,

    Suri

  • 6. Re: Create a Multiple Files using PL-SQL
    zen1983 Newbie
    Currently Being Moderated

    Yes, I second to Suri. you should include file_num in your select query of the cursor.

  • 7. Re: Create a Multiple Files using PL-SQL
    RaviR Newbie
    Currently Being Moderated

    Thanks Ramin,

     

    Your Code really helped. I am now not getting no errors and files are also being created. However, if I replace the UTL_FILE with DBMS_OUTPUT, I get below output. In your code i replaced 12 with 3. Hence its dividing after 3 rows. (with 1st row duplicated)

     

    Output

    1

    1

    2

    3

    ========================================

    4

    4

    5

    6

    ========================================

    7

    7

    8

    9

    ========================================

    10

    10

    11

    12

    ========================================

    13

    13

    14

    15

    ========================================

    16

    16

    ...........


    Now my concern is that i my table I have a flag which determines the seperation...

    Column1File_Num
    11
    21
    31
    42
    52
    62
    73
    83
    93
    103
    113


    Hence if the file_Num is 1 it will go to File1, file_num=2 will be written to File2 and so on.

     

    Please help me to get this logic.

     

    Thanks in advance.

     

    Regards,

    RaviR

  • 8. Re: Create a Multiple Files using PL-SQL
    RaviR Newbie
    Currently Being Moderated

    Sorry guys for confusing all and not posting my query properly. Will take a note of it.

  • 9. Re: Create a Multiple Files using PL-SQL
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi, Ravi!

     

    Please check my script please:

     

    create table data as

    select 1 col, 1 file_id from dual union all

    select 2, 1 file_id from dual union all

    select 3, 1 file_id from dual union all

    select 4, 2 file_id from dual union all

    select 5, 2 file_id from dual union all

    select 6, 2 file_id from dual union all

    select 7, 3 file_id from dual union all

    select 8, 3 file_id from dual union all

    select 9, 3 file_id from dual union all

    select 10, 3 file_id from dual union all

    select 11, 3 file_id from dual;

     

     

    select *  from  data;

    /

    declare

    v_file_id number := 1;

    v_file utl_file.file_type;

    begin

     

      v_file := utl_file.fopen('DATA_PUMP_DIR','file'||v_file_id||'.txt','W',3267);         

      for t in (select *  from data order by col,file_id)

       loop

         if (v_file_id = t.file_id) then

          utl_file.put_line(v_file,t.col);

         else

           v_file_id := t.file_id;

           utl_file.fclose(v_file);

           v_file := utl_file.fopen('DATA_PUMP_DIR','file'||v_file_id||'.txt','W',3267); 

           utl_file.put_line(v_file,t.col);

         end if;  

       end loop;

       utl_file.fclose(v_file);

    end;

    /

     

    I used DATA_PUMP_DIR as directory.

     

    Regards

    Mahir M. Quluzade

  • 10. Re: Create a Multiple Files using PL-SQL
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    -- Created on 29.10.2013 by RAMIN.H

    declare

      cursor cur_select is 

             select f, f_num, lag(f_num) over (order by f) prev_f_num  ---!pay attention

             from(SELECT level f, ntile(9) over (order by level) f_num FROM dual connect by level <= 100);

     

     

      rec_cur_select cur_select%ROWTYPE;

      n number;

      f utl_file.file_type;

    begin

        n := 12;

     

        open cur_select;

        loop

          fetch cur_select

            into rec_cur_select;

     

            exit when cur_select%NOTFOUND;

      

            if (rec_cur_select.prev_f_num is not null and rec_cur_select.prev_f_num != rec_cur_select.f_num) then

                utl_file.fclose(f);

            end if;

         

            if  (rec_cur_select.prev_f_num is null or rec_cur_select.prev_f_num != rec_cur_select.f_num) then

                f := utl_file.fopen('GL_OUT','file'||rec_cur_select.f_num,'W',3267);         

            end if;

      

            utl_file.put_line(f,rec_cur_select.f);

           

        end loop;

        close cur_select;

     

        utl_file.fclose(f);

    end;

     

    ----

    Ramin Hashimzade

  • 11. Re: Create a Multiple Files using PL-SQL
    RaviR Newbie
    Currently Being Moderated

    Thanks Mahir for your help. It is very close to my solution.

     

    Ramin OMG, you are a genius. You have solved my problem totally. One last concern I have another column which is numeric. So for the group 1 all the rows in that column should be added and the output to be displayed as a Trailer.

     

    Is this possible?? Please help me!!

  • 12. Re: Create a Multiple Files using PL-SQL
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Hi,

    I am not genius... i gave you simple code ))))

    About last problem... show in example what you want to do

     

    ----

    Ramin Hashimzade

  • 13. Re: Create a Multiple Files using PL-SQL
    RaviR Newbie
    Currently Being Moderated

    Hi Ramin,

     

    So my table goes like this below mentioned. I have to create a multiple files based on file_num. Each file will have a trailer whic will be as follows:

    File1 will have the trailer (21 + 3 + 41 + 0) --> 65

    File2 --> (2 + 4 + 32 + 98) --> 134

    File3....and so on..

    In short, every file will have trailer that will sum - up all the rows of the Numeric_Column which belongs to its own group / File_num

     

    Column_1Numeric_ClumnFile_NUM
    1211
    231
    3411
    401
    522
    642
    7322
    8982
    983
    1053
    11434
    12454
    13214

     

    Hope I have explained you my concern properly.

     

    Thanks in Advance.

  • 14. Re: Create a Multiple Files using PL-SQL
    MahirM.Quluzade Guru
    Currently Being Moderated

    Hi all.


    I'm sure Ramin Hashimzadeh will change script soon for you.

    I changed my script, and I want share with you.

     

    drop table data purge;

    create table data as

    select 1 col,21 col_num, 1 file_id from dual union all

    select 2,3, 1 file_id from dual union all

    select 3,41, 1 file_id from dual union all

    select 4,0, 1 file_id from dual union all

    select 5,2, 2 file_id from dual union all

    select 6,4, 2 file_id from dual union all

    select 7,32, 2 file_id from dual union all

    select 8,98, 2 file_id from dual union all

     

     

    select 9,8, 3 file_id from dual union all

    select 10,5, 3 file_id from dual union all

    select 11,43, 4 file_id from dual union all

    select 12,45, 4 file_id from dual union all

    select 13,21, 4 file_id from dual;

     

     

    select *  from  data;

    /

    declare

    v_file_id number := 1;

    v_file utl_file.file_type;

    v_sum number := 0;

    v_str VARCHAR2(100):='';

    begin

     

      v_file := utl_file.fopen('DATA_PUMP_DIR','file'||v_file_id||'.txt','W',3267);        

      v_str:='(';

      for t in (select *  from data order by col,file_id)

       loop

         if (v_file_id = t.file_id) then

          utl_file.put_line(v_file,t.col);

          v_str := v_str ||to_char(t.col_num)||'+';

          v_sum := v_sum + t.col_num;

         else

           v_file_id := t.file_id;

           utl_file.put_line(v_file,substr(v_str,1,length(v_str)-1)||')='||to_char(v_sum));

           v_sum:=0;

           v_str:='(';

           utl_file.fclose(v_file);

           v_file := utl_file.fopen('DATA_PUMP_DIR','file'||v_file_id||'.txt','W',3267);

           utl_file.put_line(v_file,t.col);

           v_str := v_str||to_char(t.col_num)||'+';

           v_sum := v_sum + t.col_num;

         end if; 

        

       end loop;

       utl_file.put_line(v_file,substr(v_str,1,length(v_str)-1)||')='||to_char(v_sum));

       utl_file.fclose(v_file);

    end;

    /

     

    Thanks to  all!

     

    Regards

    Mahir M. Quluzade

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points