csv文件上传数据库[1]

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 extract_cell_data(REPLACE(v_buf, chr(13)), p_delimiter, p_working_id); EXIT WHEN n_pos > n_clob_size; END LOOP; COMMIT; IF dbms_lob.istemporary(l_data) > 0 THEN dbms_lob.freetemporary(l_data); END IF; IF dbms_lob.istemporary(c_data) > 0 THEN dbms_lob.freetemporary(c_data); END IF; END IF; EXCEPTION WHEN OTHERS THEN RAISE; END extract_blob; -------------------------------------------------------------------------------------- -- Name : import -- Description : This script populates the Standard PO Vendor tables -- taking the data from temp table cdc_ap_vendor_intrim -- where data file for data conversion is loaded -- -- The Script populates data into 4 Tables : -- CUX_CE_BANK_STATEMENT_TEMP -- CE_STATEMENT_HEADERS_INT_ALL -- CE_STATEMENT_LINES_INTERFACE -- -- Modification Log: -- Author Date Version Changes -- ---------------- --------- ------- ----------------------------------- -- Wanglin 20060414 1.0 Created -- -------------------------------------------------------------------------------------- PROCEDURE ins_interface IS v_insert_or_update VARCHAR2(10); -- 处理标志: i - 插入 u - 修改 p_error_flag VARCHAR2(1); p_error_info VARCHAR2(200); v_line_all NUMBER; v_line_suc NUMBER; v_line_fal NUMBER; v_state_cnt NUMBER := 0; l_line_cnt NUMBER := 0; l_int_cnt NUMBER := 0; exc_stmt EXCEPTION; CURSOR cur_stmt_header IS SELECT DISTINCT t.working_id, t.statement_number, t.bank_account_num, t.bank_name, t.bank_branch_name, t.statement_date, t.currency_code FROM cux_ce_stmt_temp t WHERE (process_flag != \'S\' OR process_flag IS NULL) AND t.working_id = gp_working_id ORDER BY t.statement_number; r_stmt_header cur_stmt_header%ROWTYPE; CURSOR cur_stmt_line(p_statement_number IN VARCHAR2, p_bank_account_num IN VARCHAR2, p_bank_name IN VARCHAR2, p_bank_branch_name IN VARCHAR2, p_statement_date IN DATE, p_currency_code IN VARCHAR2) IS SELECT * FROM cux_ce_stmt_temp t WHERE (process_flag != \'S\' OR process_flag IS NULL) AND t.working_id = gp_working_id AND t.statement_number = p_statement_number AND t.bank_account_num = p_bank_account_num AND t.bank_name = p_bank_name AND t.bank_branch_name = p_bank_branch_name AND t.statement_date = p_statement_date AND t.currency_code = p_currency_code ORDER BY line_number; r_stmt_line cur_stmt_line%ROWTYPE; BEGIN output(\'银行对帐单数据导入开始于:\' || to_char(SYSDATE, \'YYYY/MM/DD HH24:MI:SS\')); output(\' \'); v_line_all := 0; v_line_suc := 0; v_line_fal := 0; v_insert_or_update := \'U\'; -- 记录本次处理的总数 BEGIN SELECT COUNT(*) INTO v_line_all FROM cux_ce_stmt_temp WHERE (process_flag != \'S\' OR process_flag IS NULL); END; OPEN cur_stmt_header; LOOP FETCH cur_stmt_header INTO r_stmt_header; EXIT WHEN cur_stmt_header%NOTFOUND; BEGIN SELECT COUNT(statement_number) INTO v_state_cnt FROM ce_statement_headers_int_all WHERE statement_number = r_stmt_header.statement_number AND bank_account_num = r_stmt_header.bank_account_num AND org_id = gp_org_id ; IF v_state_cnt > 0 THEN v_insert_or_update := \'U\'; ELSE v_insert_or_update := \'I\'; END IF; END; IF v_insert_or_update = \'I\' THEN BEGIN -- insert ce_statement_headers_int_all INSERT INTO ce_statement_headers_int_all (statement_number, bank_account_num, bank_name, bank_branch_name, statement_date, currency_code, record_status_flag, intra_day_flag, org_id, created_by, creation_date, last_updated_by, last_update_date) VALUES (r_stmt_header.statement_number, r_stmt_header.bank_account_num, r_stmt_header.bank_name, r_stmt_header.bank_branch_name, r_stmt_header.statement_date, r_stmt_header.currency_code, \'N\', \'N\', gp_org_id, gp_user_id, SYSDATE, gp_user_id, SYSDATE); EXCEPTION

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库csv文件上传数据库[1]在线全文阅读。