PPTX Creator

Stored Procedure (Called by Application Process)

CREATE OR REPLACE PROCEDURE GET_MULTI_PPTX ( p_empnos IN VARCHAR2 )
  AUTHID CURRENT_USER
AS
  l_values pptx_creator_pkg.t_vc_value_tab;
  l_patterns pptx_creator_pkg.t_vc_value_row;
  l_file BLOB;
  l_new_file BLOB;
  i NUMBER := 0;
BEGIN
  SELECT file_content
    INTO l_file
    FROM pptx_template
   WHERE name = 'Employees';

l_patterns := pptx_creator_pkg.t_vc_value_row( '#EMPNO#', '#ENAME#', '#JOB#'
                                               , '#MGR#', '#HIREDATE#', '#SAL#'
                                               , '#COMM#', '#DEPTNO#'
                                               );
  FOR rec IN (SELECT to_char(EMPNO) empno
                   , ENAME
                   , JOB
                   , (SELECT ename FROM emp par WHERE par.empno = ch.mgr) MGR
                   , to_char(HIREDATE, 'dd.mm.yyyy') hiredate
                   , to_char(SAL) sal
                   , to_char(COMM) comm
                   , (SELECT dname FROM dept WHERE dept.deptno = ch.deptno) DEPTNO
                FROM EMP ch
              WHERE INSTR(p_empnos, to_char(empno)) > 0)
  LOOP
    i := i + 1;
    l_values(i) := pptx_creator_pkg.t_vc_value_row( rec.empno
                                                  , rec.ename
                                                  , rec.job
                                                  , rec.mgr
                                                  , rec.hiredate
                                                  , rec.sal
                                                  , rec.comm
                                                  , rec.deptno
                                                  );
  END LOOP;
  l_new_file := pptx_creator_pkg.convert_template(l_file, l_patterns, l_values);
  IF l_new_file IS NOT NULL THEN
    OWA_UTIL.mime_header ('application/octet', FALSE);
    HTP.p ('Content-length: ' || dbms_lob.getlength(l_new_file));
    HTP.p ( 'Content-Disposition: attachment; filename="Employees_' || to_char(sysdate, 'YYYYMMDD') || '.pptx"');
    OWA_UTIL.http_header_close;
    WPG_DOCLOAD.download_file (l_new_file);
  END IF;
  
END GET_MULTI_PPTX;

How to use it