Oracle APEX IR报表下载CSV文件的方法
目录
0. 准备工作
1. 下载--自定义SQL
2. 下载--检索结果
0. 准备工作
-- 建表
CREATE TABLE T_DL_EMP(EMPNO NUMBER(4) NOT NULL -- 雇员编号，由四个数字组成。, ENAME VARCHAR2(10) -- 雇员姓名，由10个字符组成。, JOB VARCHAR2(9) -- 雇员的职位，由9个字符组成。, MGR NUMBER(4) -- 雇员的经理编号，经理也是雇员之一。, HIREDATE DATE -- 雇员的雇佣日期。, SAL NUMBER(7, 2) -- 雇员的基本工资，由5位整数和2位小数组成，共7位。, COMM NUMBER(7, 2) -- 雇员的奖金。, DEPTNO NUMBER(2) -- 雇员所在部门的编号。
);
-- 插入数据
INSERT INTO T_DL_EMP VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, NULL, 20);
INSERT INTO T_DL_EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30);
INSERT INTO T_DL_EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30);
INSERT INTO T_DL_EMP VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO T_DL_EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('1981-09-28', 'YYYY-MM-DD'), 1250, 1400, 30);
INSERT INTO T_DL_EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO T_DL_EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO T_DL_EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('1982-12-09', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO T_DL_EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('1981-11-07', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO T_DL_EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO T_DL_EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('1983-01-12', 'YYYY-MM-DD'), 1100, NULL, 20);
INSERT INTO T_DL_EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('1981-12-03', 'YYYY-MM-DD'), 950, NULL, 30);
INSERT INTO T_DL_EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO T_DL_EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('1982-01-23', 'YYYY-MM-DD'), 1300, NULL, 10);1. 下载--自定义SQL
在页的Process中应用如下代码：
DECLAREPROCEDURE PROC_DL_CSV_M1ISlv_file_name VARCHAR2(255); -- CSV文件名称lt_context apex_exec.t_context;lt_export apex_data_export.t_export;lv_sql_txt VARCHAR2(2000);BEGIN-- 出力ファイル名を設定lv_file_name := '员工表_' || TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS');lv_sql_txt := Q'!!';lv_sql_txt := lv_sql_txt || ' SELECT';lv_sql_txt := lv_sql_txt || ' EMPNO AS "员工编号"';lv_sql_txt := lv_sql_txt || ' , ENAME AS "员工姓名"';lv_sql_txt := lv_sql_txt || ' , JOB AS "职位"';lv_sql_txt := lv_sql_txt || ' , MGR AS "经理编号"';lv_sql_txt := lv_sql_txt || ' , TO_CHAR(HIREDATE,''YYYY/MM/DD'') AS "入职日期"';lv_sql_txt := lv_sql_txt || ' , TO_CHAR(SAL,''FM999,999,990'') AS "基本工资"';lv_sql_txt := lv_sql_txt || ' , TO_CHAR(COMM,''FM999,999,990'') AS "奖金"';lv_sql_txt := lv_sql_txt || ' , DEPTNO AS "所在部门编号"';lv_sql_txt := lv_sql_txt || ' FROM T_DL_EMP';lt_context := apex_exec.open_query_context(p_location => apex_exec.c_location_local_db, p_sql_query => lv_sql_txt);lt_export := apex_data_export.export (p_context => lt_context, p_format => apex_data_export.c_format_csv, p_as_clob => TRUE, p_file_name => lv_file_name);apex_exec.close(lt_context);apex_data_export.download(p_export => lt_export);EXCEPTIONWHEN OTHERS THENapex_exec.close(lt_context);RAISE;END PROC_DL_CSV_M1;
BEGINPROC_DL_CSV_M1;
END;2. 下载--检索结果
通过IR报表的检索结果下载，先将IR报表的静态ID命名为“myReport"。
beginselect region_idinto ln_region_idfrom apex_application_page_regionswhere application_id = :APP_IDand page_id = :APP_PAGE_IDand static_id = 'myReport';lv_file_name := '员工表_' || TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS') || '.csv';lt_context := apex_region.open_query_context(p_page_id => :APP_PAGE_ID, p_region_id => ln_region_id);apex_data_export.add_column(p_columns => lt_columns, p_name => 'EMPNO', p_heading => '员工编号');apex_data_export.add_column(p_columns => lt_columns, p_name => 'ENAME', p_heading => '员工姓名');apex_data_export.add_column(p_columns => lt_columns, p_name => 'JOB', p_heading => '职位');apex_data_export.add_column(p_columns => lt_columns, p_name => 'MGR', p_heading => '经理编号');apex_data_export.add_column(p_columns => lt_columns, p_name => 'HIREDATE', p_heading => '入职日期');apex_data_export.add_column(p_columns => lt_columns, p_name => 'SAL', p_heading => '基本工资');apex_data_export.add_column(p_columns => lt_columns, p_name => 'COMM', p_heading => '奖金');apex_data_export.add_column(p_columns => lt_columns, p_name => 'DEPTNO', p_heading => '所在部门编号');lt_export := apex_data_export.export (p_context => lt_context, p_format => apex_data_export.c_format_csv, p_columns => lt_columns, p_as_clob => true, p_file_name => lv_file_name);apex_exec.close(lt_context);lcl_csv := lt_export.content_clob;-- MIME型を設定owa_util.mime_header('text/csv; charset=UTF-8', FALSE);-- 出力ファイル名を設定htp.p('Content-Disposition: attachment; ' || encode_filename(lv_file_name));-- HTTPヘッダをクローズowa_util.http_header_close;htp.prn(replace(lcl_csv, chr(10), chr(13)||chr(10)));-- APEXエンジンに終了htmldb_application.g_unrecoverable_error := true;
exceptionwhen othersthenapex_exec.close(lt_context);apex_error.add_error(p_message => SQLERRM, p_display_location => apex_error.c_inline_in_notification);end;
