Hosting Service

03 Jun 2018 | MAK Azad

How to convert | export oracle matrix report into M.S Excel file, oracle 11g

Category : Oracle | Tag : M.S Excel | Oracle Matrix Reports |

Create package specification

CREATE OR REPLACE PACKAGE create_salary_xl_file
AUTHID CURRENT_USER AS v_fh UTL_FILE.file_type;
v_dir VARCHAR2 (30) := 'HRDB_DATAPUMP_DIR';

v_file VARCHAR2 (300) := ' Salary for the Month of ' || TO_CHAR (SYSDATE, 'fmMonth rrrr') || '.xls';
--Author Azad --Date 20 July, 2017
PROCEDURE create_excel_salary_table (p_company_code VARCHAR2, p_emp_code VARCHAR2, p_branch_code VARCHAR2); PROCEDURE run_query (p_sql IN VARCHAR2);
PROCEDURE start_workbook;
PROCEDURE end_workbook;
PROCEDURE start_worksheet (p_sheetname IN VARCHAR2);
PROCEDURE end_worksheet;
PROCEDURE set_date_style;
END;

 

Then create package body as bellow


CREATE OR REPLACE PACKAGE BODY create_salary_xl_file AS
PROCEDURE CREATE_EXCEL_SALARY_TABLE (p_company_code VARCHAR2, p_emp_code VARCHAR2, p_branch_code VARCHAR2) AS V_ERDDCD CLOB;
V_MAIN_QUERY CLOB;
V_TABLE NUMBER;
V_DROP VARCHAR2 (500);


BEGIN
SELECT LISTAGG ('''' || CODE || '''' || 'AS "' || CODE || '"', ',') WITHIN GROUP (ORDER BY PYHDRCDE DESC, PYSOFCDE)
INTO V_ERDDCD
FROM (
SELECT CODE, PYHDRCDE, PYSOFCDE FROM (
SELECT DISTINCT SUBSTR (REPLACE (REPLACE (DPR_CODE_DESC ('999', B.PYTYPCDE), ' ', '_'), '/', ''), 1, 30) CODE,
DECODE (PYHDRCDE || PYSOFCDE, 'ER999', 'DD', PYHDRCDE) PYHDRCDE, PYSOFCDE
FROM PYCODMAS A, PYSUMATN B
WHERE B.PYTYPCDE = A.PYHDRCDE || A.PYSOFCDE
AND PYHDRCDE IN ('ER', 'DD')));
BEGIN
SELECT COUNT (TABLE_NAME)
INTO V_TABLE
FROM ALL_ALL_TABLES
WHERE UPPER (TABLE_NAME) = UPPER ('excel_salary')
AND UPPER (OWNER) <> 'SYS';
IF V_TABLE > 0 THEN
V_DROP := 'DROP TABLE excel_salary';
EXECUTE IMMEDIATE V_DROP;
END IF;
END;
V_MAIN_QUERY := ' CREATE TABLE excel_salary AS SELECT * FROM (
SELECT pyempcde, pyempnam, designation,monyr,pyacctno,SUM (pyamount) pyamount,pycoddes FROM (
SELECT a.pyempcde pyempcde, INITCAP (a.pyempnam || '' '' || a.lastname) pyempnam, a.pycatcde, SUBSTR (REPLACE (REPLACE (dpr_code_desc (''999'', b.pytypcde), '' '', ''_''), ''/'', ''''), 1, 30) pycoddes,
DECODE (SUBSTR (b.pytypcde, 1, 2), ''ER'', b.pyamount, (-1 * b.pyamount)) pyamount,
pytypcde , dpr_code_desc (''999'', a.pydescde) designation,
d.pyamount designation_sl, UPPER (TO_CHAR (TO_DATE (pyprcmth, ''MM''), ''fmMonth'') || '', '' || pyprcyer) monyr,
a.pyacctno
FROM pyempmas a, pysumatn b,pycodmas d,syjobmas f
WHERE a.pycomcde = ''' || P_COMPANY_CODE || '''
AND a.pyempcde = DECODE (UPPER ( ''' || p_emp_code || '''), ''ALL'', a.pyempcde, ''' || p_emp_code || ''')
AND a.pycomcde = b.pycomcde
AND a.pyempcde = b.pyempcde
AND d.pycomcde = ''999''
AND d.pyhdrcde = ''DG''
AND SUBSTR (a.pydescde, 3) = d.pysofcde
AND a.pycomcde = f.compcode
AND a.pydepcde = f.costcode
AND f.handover = DECODE (UPPER ( ''' || p_branch_code || '''), ''ALL'', f.handover, ''' || p_branch_code || ''')
AND SUBSTR (b.pytypcde, 1, 2) IN (''ER'', ''DD'')
AND b.pytypcde NOT IN (''ER994'', ''DD994'')
order by designation_sl, d.pysofcde, pyempcde )
GROUP BY
pyempcde, pyempnam, pycoddes, pycatcde, designation, monyr, pyacctno )
PIVOT ( SUM(pyamount) FOR pycoddes IN (' || V_ERDDCD || ' ) ) ';
--dbms_output.put_line(V_MAIN_QUERY);
EXECUTE IMMEDIATE V_MAIN_QUERY;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, SQLERRM);
END;


PROCEDURE run_query (p_sql IN VARCHAR2) IS
v_v_val VARCHAR2 (4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.desc_tab;
col_num NUMBER;


BEGIN
c := DBMS_SQL.open_cursor;
-- parse the SQL statement
DBMS_SQL.parse (c, p_sql, DBMS_SQL.native);
-- start execution of the SQL statement
d := DBMS_SQL.EXECUTE (c);
-- get a description of the returned columns
DBMS_SQL.describe_columns (c, col_cnt, rec_tab);
-- bind variables to columns
FOR j IN 1 .. col_cnt
LOOP
CASE rec_tab (j).col_type
WHEN 1 THEN DBMS_SQL.define_column (c, j, v_v_val, 4000);
WHEN 2 THEN DBMS_SQL.define_column (c, j, v_n_val);
WHEN 12 THEN DBMS_SQL.define_column (c, j, v_d_val);
ELSE
DBMS_SQL.define_column (c, j, v_v_val, 4000);
END CASE;
END LOOP;
-- Output the column headers
UTL_FILE.put_line (v_fh, '');
FOR
j IN 1 .. col_cnt
LOOP
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line (v_fh, '' || rec_tab (j).col_name || '');
UTL_FILE.put_line (v_fh, '');
END LOOP;
UTL_FILE.put_line (v_fh, '');
-- Output the data
LOOP
v_ret := DBMS_SQL.fetch_rows (c);
EXIT WHEN v_ret = 0;
UTL_FILE.put_line (v_fh, '');
FOR j IN 1 .. col_cnt LOOP
CASE rec_tab (j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line (v_fh, '' || v_v_val || '');
UTL_FILE.put_line (v_fh, '');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_n_val);
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line (v_fh, '' || TO_CHAR (v_n_val) || '');
UTL_FILE.put_line (v_fh, '');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE (c, j, v_d_val);
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line ( v_fh, '' || TO_CHAR (v_d_val, 'YYYY-MM-DD"T"HH24:MI:SS') || '');
UTL_FILE.put_line (v_fh, '');
ELSE
DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line (v_fh, '' || v_v_val || '');
UTL_FILE.put_line (v_fh, '');
END CASE;
END LOOP;
UTL_FILE.put_line (v_fh, '');
END LOOP;
DBMS_SQL.close_cursor (c);
END;


PROCEDURE start_workbook IS
BEGIN
UTL_FILE.put_line (v_fh, '');
UTL_FILE.put_line (v_fh, '');
END;
PROCEDURE end_workbook IS
BEGIN
UTL_FILE.put_line (v_fh, '');
END;


-- PROCEDURE start_worksheet (p_sheetname IN VARCHAR2) IS BEGIN UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); END;

PROCEDURE end_worksheet IS BEGIN UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); END;

-- PROCEDURE set_date_style IS BEGIN UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); UTL_FILE.put_line (v_fh, ''); END; END;

Then create a procedure to call the packaged procedure as bellow

CREATE OR REPLACE PROCEDURE call_generate_salary_excel
AUTHID CURRENT_USER IS

BEGIN
     create_salary_xl_file.create_excel_salary_table ('100', 'ALL', 'ALL');
     create_salary_xl_file.v_fh := UTL_FILE.fopen (UPPER (create_salary_xl_file.v_dir), create_salary_xl_file.v_file, 'w', 32767 );
     create_salary_xl_file.start_workbook;
     create_salary_xl_file.set_date_style;
     create_salary_xl_file.start_worksheet ('SALARY');
     create_salary_xl_file.run_query ('select * from excel_salary');
     create_salary_xl_file.end_worksheet;
     create_salary_xl_file.end_workbook;
     UTL_FILE.fclose (create_salary_xl_file.v_fh);
END;

Finally call the procedure as bellow

Begin
    call_generate_salary_excel;
End;

 

All post under category Oracle


1. How to convert number into word/text with PL/SQL
2. An Overview and Clear Understanding of Oracle REF CURSOR
3. How to query/fetch data of MS Access DB from an Oracle DB?
4. How to install Apex5 with Oracle REST Data Services in windows
5. How to Restore Deleted Rows in Oracle | Data Recovery
6. How to run an oracle job from windows batch file
7. How to convert | export oracle matrix report into M.S Excel file, oracle 11g