oracle nested for loop

Question

I need to generate on oracle apex 4 documents from the same template and every document have 1 row and when i run this code it generate 4 document and the first one have the data but others dont have anything and want to know what is the wrong.

If there any solution for that.

declare
  l_new_file Blob  ;
  l_doc_id NUMBER;
  l_names  T_STR_ARRAY := T_STR_ARRAY();
  l_values T_STR_ARRAY := T_STR_ARRAY();
  l_teller NUMBER;
  l_record_nr NUMBER;
  v_mime  VARCHAR2(100) := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
  v_length  NUMBER;
  l_new_file_name VARCHAR2(200);
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
  A_B  TBL_ATTACH_FILE.ATTACH_FILENAME%type;
  A_D   TBL_ATTACH_FILE.ATTACH_DATA%type;
 
   
  
 CURSOR c_employee IS
  select "الموظف"."رقم" as "رقم",
    "الموظف"."الموظف" as "الموظف" 
 from "الموظف" "الموظف" 
  ;
   r_employee c_employee%ROWTYPE;

BEGIN

  <<outer_loop>>
for rec in  c_employee 
loop 
 
   SELECT ATTACH_FILENAME 
          , ATTACH_DATA into A_B ,A_D
       FROM TBL_ATTACH_FILE
      WHERE ATTACH_id =:P8_NEW;
 
  
 if c_employee%FOUND

  THEN 
      l_names.EXTEND(2);
      l_values.EXTEND(2);
      
      l_names(1) := '#الموظف#';
      l_values(1) := rec."الموظف";--r_employee."الموظف";
        l_names(2) := '#رقم#';
      l_values(2) := rec."رقم";--r_employee."رقم";
      
      l_new_file := ooxml_util_pkg.get_file_from_template ( A_D, l_names, l_values);
       l_blob_len := DBMS_LOB.getlength(l_new_file);
  
  l_file := UTL_FILE.fopen('MY_DOCS',l_values(1) ||'.docx','wb', 32767);
  
  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.read(l_new_file, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  
  UTL_FILE.fclose(l_file);
  
   END IF;  
   
   end loop outer_loop ; 
END;

Koen Lostrie · Accepted Answer · 2022-05-29 08:53:47Z

1

The value of l_pos is not reset after the first iteration of the loop. As a result, the LOOP statement below will only read data from the BLOB value in the first iteration of the loop

  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.read(l_new_file, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;

Solution: reset the value of l_pos in every iteration of outer loop.

...
  l_new_file := ooxml_util_pkg.get_file_from_template ( A_D, l_names, l_values);
  l_blob_len := DBMS_LOB.getlength(l_new_file);
  l_file := UTL_FILE.fopen('MY_DOCS',l_values(1) ||'.docx','wb', 32767);
  -- reset l_pos
  l_pos := 1;
...

answered May 29, 2022 at 8:53

Koen Lostrie

19.4k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user14983948 Over a year ago

its now make an infinite data in one document

Koen Lostrie Over a year ago

impossible to comment on that without seeing your code...

Collectives™ on Stack Overflow

oracle nested for loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related