1 2 Previous Next 17 Replies Latest reply: Oct 30, 2013 7:30 AM by Mahir M. Quluzade RSS

    Create a Multiple Files using PL-SQL

    RaviR

      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

          > 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

            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

              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
                Mahir M. Quluzade

                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

                  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

                    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

                      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

                        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
                          Mahir M. Quluzade

                          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

                            -- 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

                              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

                                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

                                  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
                                    Mahir M. Quluzade

                                    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