csv文件上传数据库.doc

上传界面：

选择按钮：
declare
access_id number;
l_server_url varchar2(100);
l_parameters varchar2(100);
button_choice integer;
l_file_id varchar2(100);
l_gfm_id integer;
BEGIN

access_id := fnd_gfm.authorize(null);

fnd_profile.get('APPS_WEB_AGENT', l_server_url);

l_parameters :='access_id='||access_id||' l_server_url='||l_server_url;

fnd_function.execute(FUNCTION_NAME=>'FND_FNDFLUPL',
OPEN_FLAG => 'Y',
SESSION_FLAG => 'Y',
OTHER_PARAMS =>l_parameters);

-- Display a modal message for user to indicate file upload
-- is completed.
FND_MESSAGE.set_name('FND','ATCHMT-FILE-UPLOAD-COMPLETE');

button_choice := FND_MESSAGE.question(
button1=> 'YES',
button2=> null,
button3=> 'NO',
default_btn => 1,
cancel_btn =>3,
icon=> 'question');

IF ( button_choice = 3 ) THEN
null;
ELSIF ( button_choice = 1 ) THEN
l_file_id := '';

l_gfm_id := fnd_gfm.get_file_id(access_id);

:MAIN.GFM_ID := fnd_gfm.get_file_id(access_id);

IF l_gfm_id IS NOT NULL THEN

select decode(instr(file_name,'/'),0,file_name,
substr(file_name,instr(file_name,'/')+1))
into l_file_id
from fnd_lobs
where file_id = l_gfm_id;

if l_file_id is not null then
:main.filename := l_file_id;

select CUX_CE_STMT_S.nextval
into :main.working_id
from dual;

end if;
END IF;

END IF;

END;

上传按钮：
DECLARE
REQID NUMBER := 0;
l_exit BOOLEAN;
BEGIN

IF :MAIN.FILENAME IS NULL THEN
FND_MESSAGE.SET_STRING('请选择要装入的数据文件。' );
FND_MESSAGE.SHOW;
ELSE
--set cursor busy---------------------------------------------------
Set_Application_Property(CURSOR_STYLE, 'BUSY') ;
SAVEPOINT sp_match_one_day ;

REQID := FND_REQUEST.submit_request('CE', 'CUX_STMT_UPLOAD', NULL, NULL, FALSE,
:MAIN.statement_number,
:MAIN.bank_account_num,
--TO_CHAR(:MAIN.statement_date, 'YYYY-MM-DD'),
:MAIN.statement_date,
:MAIN.bank_name,
:MAIN.bank_branch_name,
:MAIN.CURRENCY_CODE,
'CUX_STMT_UPLOAD',:MAIN.GFM_ID, :main.file_cs, :main.DELIMITER, :main.working_id,
CHR(0),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
);

IF REQID >0 THEN

l_exit := APP_FORM.quietcommit();
FND_MESSAGE.SET_STRING('Request ID:' || REQID || ' 请求已提交，请查看并发程序。 ');
FND_MESSAGE.SHOW;
ELSE
ROLLBACK TO sp_match_one_day;
--
FND_MESSAGE.SET_STRING('提交请求失败！');
FND_MESSAGE.ERROR;
END IF;
:MAIN.FILENAME := NULL;
--
FND_FUNCTION.EXECUTE( FUNCTION_NAME=>'FND_FNDRSRUN',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'CONCURRENT_PROGRAM_NAME="'||'CUX_STMT_UPLOAD'||'" PROGRAM_APPL_SHORT_NAME="'||'CE'||'" DODT_REQ_ID="'||
TO_CHAR(REQID)||'"'
);
--
Set_Application_Property(CURSOR_STYLE, 'DEFAULT');

END IF;

END;

其中：CUX_STMT_UPLOAD，调用的是cux_ce_stmt_upload.main
CREATE OR REPLACE PACKAGE cux_ce_stmt_upload IS

-- Author : Siman he
-- Created : 2006-3-16
-- Purpose : Import HR Absence Attendance Data Into ERP Environment

gp_working_id NUMBER;
gp_org_id NUMBER;
gp_user_id NUMBER;

gp_statement_number VARCHAR2(100);
gp_bank_account_num VARCHAR2(100);
--gp_statement_date VARCHAR2(100);
gp_statement_date DATE;
gp_bank_name VARCHAR2(100);
gp_bank_branch_name VARCHAR2(100);
gp_currency_code VARCHAR2(100);

PROCEDURE log(i_chr_message IN VARCHAR2);

PROCEDURE output(i_chr_message IN VARCHAR2);

FUNCTION convertblob(inblob IN BLOB,
sourcecs IN VARCHAR2,
destcs IN VARCHAR2) RETURN BLOB AS
LANGUAGE JAVA NAME 'ConvertLOBCS.convertBlob( oracle.sql.BLOB, java.lang.String, java.lang.String) return oracle.sql.BLOB';

FUNCTION convertclob(inblob IN BLOB, sourcecs IN VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'ConvertLOBCS.convertClob( oracle.sql.BLOB, java.lang.String ) return oracle.sql.CLOB';

FUNCTION convertstr(inblob IN BLOB, sourcecs IN VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'ConvertLOBCS.convertStr( oracle.sql.BLOB, java.lang.String ) return java.lang.String';

PROCEDURE extract_blob(p_file_id IN NUMBER,
p_src_cs IN VARCHAR2,
p_delimiter IN VARCHAR2,
p_working_id IN NUMBER);

PROCEDURE ins_interface;

PROCEDURE main(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_statement_number IN VARCHAR2,
p_bank_account_num IN VARCHAR2,
p_statement_date IN VARCHAR2,
--p_statement_date IN DATE,
p_bank_name IN VARCHAR2,
p_bank_branch_name IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_area IN VARCHAR2,
p_gfm_id IN NUMBER,
p_file_cs IN VARCHAR2,
p_delimiter IN NUMBER,
p_working_id IN NUMBER);

END cux_ce_stmt_upload;
/
CREATE OR REPLACE PACKAGE BODY cux_ce_stmt_upload IS
/*=========================================================================
Copyright (C) Hand Business Consulting Services
AllRights Reserved

$ Header Huawei , Wanglin
* Version 1.0
* Purpose :
* Batch Import Nonrecurring and Recurring data to Oracle ERP by
* Fixed length or Excel
* Package : cux_ce_stmt_upload
* Procedure : 1.main Manual batch Import Employee payroll
element information to Oracle ERP
* Create By : Wanglin
* Create Date: 2006-3-21 12:50:23
* Purpose :
1. Supply Import API for user to Import Employee payroll element information to Oracle ERP ,
Application for Notes system

2. Manual Batch Import Employee payroll element information to Oracle ERP
Application for Manual Excel Data
* Updated By : Wanglin
* Updated Date: 2006-5-14
* Purpose :
1. Add Batch Update Recurring and Nonrecurring Pay Value

=============================================================================*/
cn_max_field CONSTANT INTEGER := 40;
cv_processing_type VARCHAR2(3);
cv_dest_cs CONSTANT VARCHAR2(30) := 'UTF8';
cv_commit_number CONSTANT NUMBER := 5000;
TYPE t_matrix_cell IS TABLE OF VARCHAR2(500);

PROCEDURE log(i_chr_message IN VARCHAR2) IS
BEGIN
fnd_file.put_line(fnd_file.log, i_chr_message);
NULL;
END;

PROCEDURE output(i_chr_message IN VARCHAR2) AS
BEGIN
fnd_file.put_line(fnd_file.output, i_chr_message);
END;

PROCEDURE alter_nls_date(p_date_string IN VARCHAR2) IS
l_cursor_id INTEGER;
l_dummy INTEGER;
l_string VARCHAR2(128);
BEGIN
l_string := 'ALTER session set NLS_DATE_LANGUAGE=' || p_date_string;
l_cursor_id := dbms_sql.open_cursor;

dbms_sql.parse(l_cursor_id, l_string, dbms_sql.native);

l_dummy := dbms_sql.EXECUTE(l_cursor_id);
END alter_nls_date;
--
-- Check effective date whether is validation
--
PROCEDURE check_date(p_effective_date IN VARCHAR2,
o_errcode OUT NUMBER,
o_errmsg OUT VARCHAR2) IS
v_date DATE;
BEGIN
o_errcode := 0;
SELECT to_date(p_effective_date, 'YYYY-MM-DD') INTO v_date FROM dual;
EXCEPTION
WHEN OTHERS THEN
o_errcode := -20001;
o_errmsg := '时间格式出错,导入失败';
END;

PROCEDURE extract_cell_data(p_line IN VARCHAR2,
p_delimiter IN VARCHAR2,
p_working_id IN NUMBER) IS

v_line VARCHAR2(4000);
v_field VARCHAR2(4000);
r_cells t_matrix_cell;
n_field_cnt INTEGER;
n_delimiter_pos INTEGER;
BEGIN
r_cells := t_matrix_cell();
r_cells.EXTEND(cn_max_field);
n_field_cnt := 1;

IF p_line IS NOT NULL THEN
-- extract values from field
v_line := p_line;
LOOP
v_field := NULL;
n_delimiter_pos := instr(v_line, p_delimiter);
IF n_delimiter_pos > 0 THEN
v_field := ltrim(rtrim(substr(v_line, 1, n_delimiter_pos - 1)));
v_line := substr(v_line, n_delimiter_pos + 1);
ELSIF ltrim(rtrim(v_line)) IS NOT NULL THEN
v_field := ltrim(rtrim(v_line));
v_line := NULL;
END IF;

--output(to_char(n_field_cnt) || '-' || v_field);
IF v_field IS NOT NULL THEN
r_cells(n_field_cnt) := v_field;
END IF;
n_field_cnt := n_field_cnt + 1;
-- exit when finish extract last field
IF (v_line IS NULL) OR (n_field_cnt = cn_max_field) THEN
EXIT;
END IF;
END LOOP;
--output(r_cells.COUNT);

-- Insert the different column values from the file to different columns of the cux_HR_MATRIX_CELLS TABLE
/*
INSERT INTO cux_ce_stmt_temp
(working_id,
line_number,
statement_number,
bank_account_num,
statement_date,
bank_name,
bank_branch_name,
trx_date,
trx_code,
bank_trx_number,
effective_date,
trx_text,
amount,
currency_code,
process_flag)
VALUES
(p_working_id,
r_cells(1),
r_cells(2),
r_cells(3),
TO_DATE(r_cells(4),'YYYY-MM-DD HH24:MI:SS'),
r_cells(5),
r_cells(6),
TO_DATE(r_cells(7),'YYYY-MM-DD HH24:MI:SS'),
r_cells(8),
r_cells(9),
TO_DATE(r_cells(10),'YYYY-MM-DD HH24:MI:SS'),
r_cells(11),
r_cells(12),
r_cells(13),
'N');
*/
INSERT INTO cux_ce_stmt_temp
(working_id,
statement_number,
bank_account_num,
statement_date,
bank_name,
bank_branch_name,
currency_code,
line_number,
trx_date,
trx_code,
bank_trx_number,
effective_date,
trx_text,
amount,
process_flag)
VALUES
(p_working_id,
gp_statement_number,
gp_bank_account_num,
--to_date(gp_statement_date, 'DD-MON-RRRR'),
gp_statement_date,
gp_bank_name,
gp_bank_branch_name,
gp_currency_code,
r_cells(1),
to_date(r_cells(2), 'YYYY-MM-DD HH24:MI:SS'),
r_cells(3),
r_cells(4),
to_date(r_cells(5), 'YYYY-MM-DD HH24:MI:SS'),
r_cells(6),
r_cells(7),
'N');

END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END extract_cell_data;

PROCEDURE extract_blob(p_file_id IN NUMBER,
p_src_cs IN VARCHAR2,
p_delimiter IN VARCHAR2,
p_working_id IN NUMBER) IS

l_data BLOB := NULL;

c_data CLOB := NULL;
n_pos INTEGER;
n_offset INTEGER;
n_clob_size INTEGER;
n_src_offset INTEGER := 1;
n_dest_offset INTEGER := 1;
v_buf VARCHAR2(4000);
v_warn VARCHAR2(4000);

n_lang_ctx INTEGER := dbms_lob.default_lang_ctx;
BEGIN
dbms_lob.createtemporary(c_data, FALSE, dbms_lob.session);

-- 获取大字段文件,存放导入的文件(L_DATA)
IF p_file_id IS NOT NULL THEN
SELECT fl.file_data
INTO l_data
FROM fnd_lobs fl
WHERE fl.file_id = p_file_id
FOR UPDATE OF file_data;

dbms_output.put_line(dbms_lob.getlength(l_data));
-- 判定当前字符集类型是否UTF8
IF p_src_cs <> cv_dest_cs THEN
l_data := convertblob(l_data, p_src_cs, cv_dest_cs);
END IF;

-- Convert the BLOB format to CLOB format

dbms_lob.converttoclob(dest_lob => c_data,
src_blob => l_data,
amount => dbms_lob.lobmaxsize,
dest_offset => n_dest_offset,
src_offset => n_src_offset,
blob_csid => nls_charset_id(cv_dest_cs),
lang_context => n_lang_ctx,
warning => v_warn);

dbms_output.put_line(dbms_lob.getlength(c_data));

n_offset := 1;
n_clob_size := dbms_lob.getlength(c_data);
LOOP
n_pos := dbms_lob.instr(lob_loc => c_data,
pattern => chr(10),
offset => n_offset,
nth => 1);
--output(to_char(n_pos));
IF nvl(n_pos, 0) = 0 THEN
n_pos := n_clob_size + 1;
END IF;

v_buf := dbms_lob.substr(lob_loc => c_data,
amount => n_pos - n_offset, -- N_NEXT_POS - N_POS,
offset => n_offset); --N_POS+1);
n_offset := n_pos + 1;

-- break down the fields into different columns by the Tab Delimiter