generates document pl/sql

Question

This code make a automation document that let me generate data in word document based on the template and the employee name.

but i need to generate more than one document by using for loop if i have more than one employee

DECLARE

  l_new_file Blob:= empty_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;
 

CURSOR c_template IS

     SELECT ATTACH_FILENAME 
          , ATTACH_DATA
       FROM TBL_ATTACH_FILE
      WHERE ATTACH_id =:P9_NEW;

  r_template c_template%ROWTYPE;

 CURSOR c_employee IS

  select "الموظف"."رقم" as "رقم",
    "الموظف"."الموظف" as "الموظف" 
 from "الموظف" "الموظف" 
   WHERE "الموظف"."تعرفه" =:P7_NE;

   r_employee c_employee%ROWTYPE;
  

  

  FUNCTION replace_special_chars ( l_text VARCHAR2 )  RETURN VARCHAR2 IS
     l_return  VARCHAR2(4000);

 begin

     l_return := l_text;

     l_return := SUBSTR(REPLACE ( l_return, '&', '&'), 1, 4000);

     l_return := SUBSTR(REPLACE ( l_return, '<', '&lt;'), 1, 4000);

     l_return := SUBSTR(REPLACE ( l_return, '>', '&gt;'), 1, 4000);

     l_return := SUBSTR(REPLACE ( l_return, chr(10), '<w:br/>'), 1, 4000);

     RETURN l_return;

  END;

BEGIN

   OPEN c_template;

   FETCH c_template INTO r_template;
 
   CLOSE c_template;
 
   SELECT "SQ_ATTACH_FILE".nextval
   INTO l_doc_id
   FROM dual;
  
   l_teller := 1;

   OPEN c_employee;
   FETCH c_employee into r_employee; 
 if c_employee%FOUND
   THEN 

      l_names.EXTEND(2);

      l_values.EXTEND(2);
      
      l_names(1) := '#الموظف#';

      l_values(1) := r_employee."الموظف";

        l_names(2) := '#رقم#';

      l_values(2) := r_employee."رقم";

      l_new_file := ooxml_util_pkg.get_file_from_template ( r_template.ATTACH_DATA, l_names, l_values);

       l_blob_len := DBMS_LOB.getlength(l_new_file);
  
  l_file := UTL_FILE.fopen('MY_DOCS',:NAMES||'.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;  

    CLOSE c_employee;
  
END;

Answer 1

Here is one possibility. Convert the anonymous block you have posted to a stored procedure with 2 parameters.

CREATE OR REPLACE PROCEDURE generate_mail
(p_ne IN NUMBER,
 p_attach_id IN TBL_ATTACH_FILE.ATTACH_ID%TYPE
)
AS
  l_new_file Blob:= empty_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;
 
CURSOR c_template IS
     SELECT ATTACH_FILENAME 
          , ATTACH_DATA
       FROM TBL_ATTACH_FILE
      WHERE ATTACH_id =p_attach_id;
  r_template c_template%ROWTYPE;
 CURSOR c_employee IS
  select "الموظف"."رقم" as "رقم",
    "الموظف"."الموظف" as "الموظف" 
 from "الموظف" "الموظف" 
   WHERE "الموظف"."تعرفه" =p_ne;
   r_employee c_employee%ROWTYPE;
  
  
  FUNCTION replace_special_chars ( l_text VARCHAR2 )  RETURN VARCHAR2 IS
     l_return  VARCHAR2(4000);
 begin
     l_return := l_text;
     l_return := SUBSTR(REPLACE ( l_return, '&', '&'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '<', '&lt;'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, '>', '&gt;'), 1, 4000);
     l_return := SUBSTR(REPLACE ( l_return, chr(10), '<w:br/>'), 1, 4000);
     RETURN l_return;
  END;
BEGIN
   OPEN c_template;
   FETCH c_template INTO r_template;
 
   CLOSE c_template;
 
   SELECT "SQ_ATTACH_FILE".nextval
   INTO l_doc_id
   FROM dual;
  
   l_teller := 1;
   OPEN c_employee;
   FETCH c_employee into r_employee; 
 if c_employee%FOUND
   THEN 
      l_names.EXTEND(2);
      l_values.EXTEND(2);
      
      l_names(1) := '#الموظف#';
      l_values(1) := r_employee."الموظف";
        l_names(2) := '#رقم#';
      l_values(2) := r_employee."رقم";
      l_new_file := ooxml_util_pkg.get_file_from_template ( r_template.ATTACH_DATA, l_names, l_values);
       l_blob_len := DBMS_LOB.getlength(l_new_file);
  
  l_file := UTL_FILE.fopen('MY_DOCS',:NAMES||'.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;  
    CLOSE c_employee;
  
END generate_mail;
/

Then call the procedure in a FOR LOOP. Replace the SELECT statement in the example below with the relevant statement for your business case :

FOR r IN (SELECT col1, col2 FROM my_table) LOOP
  generate_mail 
   (p_ne => r.col1,
    p_attach_id => r.col2
   );
END LOOP;
/

Note

• I assumed "الموظف"."تعرفه" is numeric - if not, change the declaration of p_ne in the procedure header to the appropriate data type.
• I assumed the :P7_NE and :P9_NEW are the arguments for your procedure.