xx_report_bi_pck
CREATE OR REPLACE PACKAGE xx_report_bi_pck IS
TYPE t_params is table of varchar2(100);
TYPE t_values is table of varchar2(250);
FUNCTION base64decode(p_clob CLOB) RETURN BLOB;
procedure tofile(p_blob BLOB, p_directory varchar2, p_file_name varchar2);
procedure submit(p_reportAbsolutePath varchar2,
p_attributeFormat varchar2,
p_params xx_report_bi_pck.t_params,
p_values xx_report_bi_pck.t_values,
p_file_name varchar2,
p_rrl_id varchar2,
p_submiter varchar2,
p_result out varchar2,
p_description out varchar2);
procedure CallBiReport(p_report_id varchar2,
p_param_list xx_report_bi_pck.t_params,
p_value_list xx_report_bi_pck.t_values,
p_format varchar2,
p_submiter varchar2,
p_result out varchar2,
p_description out varchar2);
procedure DownloadBiReport(p_report_id varchar2,
p_param_list xx_report_bi_pck.t_params,
p_value_list xx_report_bi_pck.t_values,
p_format varchar2,
p_submiter varchar2,
p_rrl_id out number);
procedure testCall;
END;
/
CREATE OR REPLACE PACKAGE BODY xx_report_bi_pck IS
-- ham nay ngon
FUNCTION base64decode(p_clob CLOB) RETURN BLOB
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/base64decode.sql
-- Author : Tim Hall
-- Description : Decodes a Base64 CLOB into a BLOB
-- Last Modified: 09/11/2011
-- -----------------------------------------------------------------------------------
IS
l_blob BLOB;
l_raw RAW(32767);
l_amt NUMBER := 7700;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
BEGIN
BEGIN
DBMS_LOB.createtemporary(l_blob, FALSE, DBMS_LOB.CALL);
LOOP
DBMS_LOB.read(p_clob, l_amt, l_offset, l_temp);
l_offset := l_offset + l_amt;
l_raw := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(l_temp));
DBMS_LOB.append(l_blob, TO_BLOB(l_raw));
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
RETURN l_blob;
END;
-- ham nay ngon lam (write to file)
procedure tofile(p_blob BLOB, p_directory varchar2, p_file_name varchar2) is
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
l_blob_len := DBMS_LOB.getlength(p_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(p_directory, p_file_name, 'wb', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
-- Thuc hien submit bi report va thuc hien
procedure submit(p_reportAbsolutePath varchar2,
p_attributeFormat varchar2,
p_params xx_report_bi_pck.t_params,
p_values xx_report_bi_pck.t_values,
p_file_name varchar2,
p_rrl_id varchar2,
p_submiter varchar2,
p_result out varchar2,
p_description out varchar2) is
v_request xx_report_soap_api.t_request;
v_response xx_report_soap_api.t_response;
--Thong tin ORS
v_url varchar2(200);
v_namespace varchar2(100);
v_method varchar2(30) := 'runReport';
v_soap_action varchar2(20) := '';
v_ors_user varchar2(15);
v_ors_pass varchar2(20);
v_byte_tag varchar2(100) := 'reportBytes';
v_xml_param varchar2(32767);
v_result_base64_encode clob;
v_listOfParamNameValues varchar2(32767);
v_length number;
begin
--1. Lay thong tin tu thiet lap
v_url := xx_common_pck.get_conf('BI_SERVICE_URL', sysdate);
v_namespace := 'xmlns="http://xmlns.oracle.com/oxp/service/v2"';
v_ors_user := xx_common_pck.get_conf('BI_USERNAME', sysdate);
v_ors_pass := xx_common_pck.get_conf('BI_PASSWORD', sysdate);
--2.
v_length := p_params.COUNT;
if v_length > 0 then
for i in 1 .. p_params.COUNT loop
v_listOfParamNameValues := v_listOfParamNameValues ||
' <v2:item>
<v2:UIType></v2:UIType>
<v2:dataType></v2:dataType>
<v2:dateFormatString></v2:dateFormatString>
<v2:dateFrom></v2:dateFrom>
<v2:dateTo></v2:dateTo>
<v2:defaultValue></v2:defaultValue>
<v2:fieldSize></v2:fieldSize>
<v2:label></v2:label>
<v2:lovLabels>
<!--Zero or more repetitions:-->
<v2:item></v2:item>
</v2:lovLabels>
<v2:multiValuesAllowed>true</v2:multiValuesAllowed>
<v2:name>' ||
p_params(i) ||
'</v2:name>
<v2:refreshParamOnChange>true</v2:refreshParamOnChange>
<v2:selectAll>true</v2:selectAll>
<v2:templateParam>true</v2:templateParam>
<v2:useNullForAll>true</v2:useNullForAll>
<v2:values>
<!--Zero or more repetitions:-->
<v2:item>' ||
p_values(i) ||
'</v2:item>
</v2:values>
</v2:item>';
end loop;
end if;
v_xml_param := '<v2:reportRequest>
<v2:XDOPropertyList>
<v2:metaDataList>
<!--Zero or more repetitions:-->
<v2:item>
<v2:metaDataName></v2:metaDataName>
<v2:metaDataValue></v2:metaDataValue>
</v2:item>
</v2:metaDataList>
</v2:XDOPropertyList>
<v2:attributeCalendar></v2:attributeCalendar>
<v2:attributeFormat>' || p_attributeFormat ||
'</v2:attributeFormat>
<v2:attributeLocale>en-US</v2:attributeLocale>
<v2:attributeTemplate></v2:attributeTemplate>
<v2:attributeTimezone></v2:attributeTimezone>
<v2:byPassCache>true</v2:byPassCache>
<v2:dynamicDataSource>
<v2:JDBCDataSource>
<v2:JDBCDriverClass></v2:JDBCDriverClass>
<v2:JDBCDriverType></v2:JDBCDriverType>
<v2:JDBCPassword></v2:JDBCPassword>
<v2:JDBCURL></v2:JDBCURL>
<v2:JDBCUserName></v2:JDBCUserName>
<v2:dataSourceName></v2:dataSourceName>
</v2:JDBCDataSource>
<v2:fileDataSource>
<v2:dynamicDataSourcePath></v2:dynamicDataSourcePath>
<v2:temporaryDataSource>true</v2:temporaryDataSource>
</v2:fileDataSource>
</v2:dynamicDataSource>
<v2:flattenXML>true</v2:flattenXML>
<v2:parameterNameValues>
<v2:listOfParamNameValues>' ||
v_listOfParamNameValues ||
'</v2:listOfParamNameValues>
</v2:parameterNameValues>
<v2:reportAbsolutePath>' ||
p_reportabsolutepath || '</v2:reportAbsolutePath>
<v2:reportData></v2:reportData>
<v2:reportOutputPath></v2:reportOutputPath>
<v2:reportRawData></v2:reportRawData>
<v2:sizeOfDataChunkDownload>-1</v2:sizeOfDataChunkDownload>
</v2:reportRequest>
<v2:userID>' || v_ors_user ||
'</v2:userID>
<v2:password>' || v_ors_pass || '</v2:password>';
v_request := xx_report_soap_api.new_request(p_method => v_method,
p_namespace => v_namespace);
-- DBMS_OUTPUT.ENABLE(9999999999999999);
xx_report_soap_api.add_complex_parameter(p_request => v_request,
p_xml => v_xml_param);
v_response := xx_report_soap_api.invoke(p_request => v_request,
p_url => v_url,
p_action => v_soap_action);
-- dbms_output.put_line('invoke ok');
-- DBMS_OUTPUT.ENABLE(9999999999999999);
v_result_base64_encode := xx_report_soap_api.get_return_value(p_response => v_response,
p_name => v_byte_tag,
p_namespace => 'xmlns="http://xmlns.oracle.com/oxp/service/v2"');
-- dbms_output.put_line('lay request ok');
-- DBMS_OUTPUT.ENABLE(1000000000);
insert into xx_report_out_files
(rof_id,
rrl_id,
filename,
file_mimetype,
file_charset,
file_blob,
creation_date,
created_by)
values
(rof_seq.nextval,
p_rrl_id,
p_file_name,
'application/octet-stream',
null,
base64decode(v_result_base64_encode),
sysdate,
p_submiter);
if sql%rowcount > 0 then
commit;
end if;
-- tofile(base64decode(v_result_base64_encode), p_directory, p_file_name);
p_result := 'C';
p_description := 'Thanh cong';
-- dbms_output.put_line('DONE');
exception
when others then
p_description := SQLERRM;
p_result := 'E';
dbms_output.put_line('lay request ko ok: ' || p_description);
RAISE;
end;
-- Thu tuc lam 2 nhiem vu:
-- 1. Ghi log vao bang xx_REPORT_RUN_LOGS
-- 2. submit len BI report submit (luu file ve: xx_report_out_files)
-- 3. Neu hoan thanh logs = 'C'
-- 4. Neu loi logs = 'E'
procedure CallBiReport(p_report_id varchar2,
p_param_list xx_report_bi_pck.t_params,
p_value_list xx_report_bi_pck.t_values,
p_format varchar2,
p_submiter varchar2,
p_result out varchar2,
p_description out varchar2) as
v_params varchar2(4000);
i integer;
v_rrl_id number;
v_reportAbsolutePath varchar2(250);
v_format varchar2(20);
begin
--1 ghep mang tham so, gia tri tham so
v_params := '';
for i in 1 .. p_param_list.COUNT loop
v_params := v_params || '#' || p_param_list(i) || ':' ||
nvl(p_value_list(i), '');
end loop;
--2 insert log
select rrl_seq.nextval into v_rrl_id from dual;
insert into xx_REPORT_RUN_LOGS
(RRL_ID,
REPORT_ID,
PARAMETERS,
START_DATE,
SUBMITER,
FLAG_STATUS,
CREATION_DATE,
CREATED_BY)
values
(v_rrl_id,
p_report_id,
v_params,
sysdate,
p_submiter,
'I',
sysdate,
p_submiter);
--3 lay cac thuoc tinh da khai bao cua bao cao
begin
select a.report_attr_value
into v_reportAbsolutePath
from xx_report_attributes a
where a.report_attr_code = 'ABSOLUTEPATH'
and a.report_id = p_report_id
and sysdate between nvl(a.from_date, sysdate) and
nvl(a.to_date, sysdate);
exception
when others then
-- chua co khai bao bao cao
raise;
end;
--4 format neu tham so khong truyen thi lay theo mac dinh
if p_format is null then
begin
select a.report_attr_value
into v_format
from xx_report_attributes a
where a.report_attr_code = 'FORMAT'
and a.report_id = p_report_id
and sysdate between nvl(a.from_date, sysdate) and
nvl(a.to_date, sysdate);
exception
when others then
-- chua co khai bao bao cao
raise;
end;
else
v_format := p_format;
end if;
-- update log sang RUN truoc khi submit
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'R'
where l.rrl_id = v_rrl_id;
-- submit BI
submit(v_reportAbsolutePath,
v_Format,
p_param_list,
p_value_list,
p_report_id || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS') || '_' ||
to_char(v_rrl_id) || '.' || lower(v_format), -- file_name = report_id gan + log_id + thoi diem chay
v_rrl_id,
p_submiter,
p_result,
p_description);
if p_result = 'C' then
-- cap nhat hoan thanh
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'C',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
else
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'E',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
end if;
exception
when others then
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'E',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
commit;
raise;
end;
procedure DownloadBiReport(p_report_id varchar2,
p_param_list xx_report_bi_pck.t_params,
p_value_list xx_report_bi_pck.t_values,
p_format varchar2,
p_submiter varchar2,
p_rrl_id out number) as
v_params varchar2(4000);
i integer;
v_rrl_id number;
v_reportAbsolutePath varchar2(250);
v_format varchar2(20);
v_Result varchar2(5);
v_description varchar2(2000);
begin
--1 ghep mang tham so, gia tri tham so
v_params := '';
for i in 1 .. p_param_list.COUNT loop
v_params := v_params || '#' || p_param_list(i) || ':' ||
nvl(p_value_list(i), '');
end loop;
--2 insert log
select rrl_seq.nextval into v_rrl_id from dual;
insert into xx_REPORT_RUN_LOGS
(RRL_ID,
REPORT_ID,
PARAMETERS,
START_DATE,
SUBMITER,
FLAG_STATUS,
CREATION_DATE,
CREATED_BY)
values
(v_rrl_id,
p_report_id,
v_params,
sysdate,
p_submiter,
'I',
sysdate,
p_submiter);
--3 lay cac thuoc tinh da khai bao cua bao cao
begin
select a.report_attr_value
into v_reportAbsolutePath
from xx_report_attributes a
where a.report_attr_code = 'ABSOLUTEPATH'
and a.report_id = p_report_id
and sysdate between nvl(a.from_date, sysdate) and
nvl(a.to_date, sysdate);
exception
when others then
-- chua co khai bao bao cao
raise;
end;
--4 format neu tham so khong truyen thi lay theo mac dinh
if p_format is null then
begin
select a.report_attr_value
into v_format
from xx_report_attributes a
where a.report_attr_code = 'FORMAT'
and a.report_id = p_report_id
and sysdate between nvl(a.from_date, sysdate) and
nvl(a.to_date, sysdate);
exception
when others then
-- chua co khai bao bao cao
raise;
end;
else
v_format := p_format;
end if;
-- update log sang RUN truoc khi submit
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'R'
where l.rrl_id = v_rrl_id;
-- submit BI
submit(v_reportAbsolutePath,
v_Format,
p_param_list,
p_value_list,
p_report_id || '_' || to_char(sysdate, 'YYYYMMDDHH24MISS') || '_' ||
to_char(v_rrl_id) || '.' || lower(v_format), -- file_name = report_id gan + log_id + thoi diem chay
v_rrl_id,
p_submiter,
v_result,
v_description);
if v_result = 'C' then
-- cap nhat hoan thanh
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'C',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
else
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'E',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
end if;
p_rrl_id:= v_rrl_id;
exception
when others then
update xx_REPORT_RUN_LOGS l
set l.flag_status = 'E',
l.last_update_date = sysdate,
l.last_updated_by = p_submiter
where l.rrl_id = v_rrl_id;
commit;
raise;
end;
procedure testCall is
v_params xx_report_bi_pck.t_params := t_params();
v_values xx_report_bi_pck.t_values := t_values();
-- v_reportABSOLUTEPATH varchar2(100) := '/Báo cáo /REP_001.xdo';
v_attributeFORMAT varchar2(5) := 'xlsx';
v_result varchar2(1);
v_description varchar2(4000);
v_report_id varchar2(20);
begin
v_params.extend;
v_params(1) := 'PERIOD_CODE';
v_params.extend;
v_params(2) := 'PERIOD_TYPE';
v_values.extend;
v_values(1) := '201810';
v_values.extend;
v_values(2) := 'MTH';
v_report_id := 'REP_PR_000';
CallBiReport(v_report_id,
v_params,
v_values,
v_attributeFormat,
'LOILM',
v_result,
v_description);
dbms_output.put_line(v_result);
dbms_output.put_line(v_description);
end;
END;
/