DECLARE CURSOR Cur_lcy_test is SELECT rcta.customer_trx_id, Rcta.trx_number, rcta.trx_date from Ra_customer_trx_a ll rcta WHERE rcta.customer_trx_id = 11993; --Rec_lcy_test Cur_lcy_test%rowtype; --w_csv_line_num Number: = 0; TYPE Tbl_varchar2 is TABLE of VARCHAR2 (4000) INDEX by Binary_integer; W_rec_data tbl_varchar2; W_separator VARCHAR2 (1): = ', '; W_file_name Xxifc_if_file_status.if_file_name%type; --ファイル name W_utl_file_type Utl_file.file_type; W_flag VARCHAR2 () DEFAULT NULL; W_rec_count number DEFAULT 0; W_loop_num number DEFAULT 0; W_file_path VARCHAR2 (+) DEFAULT NULL; W_filename Utl_file.file_type; --C_open_mode_read CONSTANT VARCHAR2 (+): = ' R '; C_open_mode_write CONSTANT VARCHAR2 (a): = ' W '; C_true CONSTANT VARCHAR2 (+): = ' true '; C_false CONSTANT VARCHAR2 (+): = ' false '; --begin OPEN cur_lcy_test; <<get_lcy_test_loop>> Loop FETCH cur_lcy_test into Rec_lcy_test; EXIT when Cur_lcy_test%notfound; --W_csv_line_num: = W_csv_line_num + 1; W_rec_data (w_csv_line_num): = Xxcmpz11.func_make_qt2_str (To_char (rec_lcy_test.customer_trx_id)) | | W_separator | | Xxcmpz11.func_make_qt2_str (To_char (rec_lcy_test.trx_number)) | | W_separator | | Xxcmpz11.func_make_qt2_str (To_date (Rec_lcy_test.trx_date, ' Yyyy/mm/dd ')); --END LOOP get_lcy_test_loop; --W_file_name: = ' lcy_write_file_test.csv '; W_file_path: = ' xxif_output '; --all_directories.directory_name BEGIN W_utl_file_type: = Utl_file.fopen (location = W_file_path, filename = w_file_name, Open_mode = C_open_mod E_read, max_linesize = 32767);W_flag: = C_true; Utl_file.fclose (file = W_utl_file_type); EXCEPTION when OTHERS then w_flag: = C_false; END; IF (W_flag = c_true) then Dbms_output.put_line (' W_flag '); END IF; --W_rec_count: = W_rec_data.count; --W_utl_file_type: = Utl_file.fopen (location = w_file_path, filename => ; W_file_name, Open_mode = C_open_mode_write, M Ax_linesize = 32767); --<<user_file_line_loop>> for W_loop_num in 1.. W_rec_count LOOP utl_file.put_line (file = w_utl_file_type, buffer = = CONVERT (W_rec_data (w _loop_num), ' Ja16sjis ', ' UTF8 '); --Modify END LOOP user_file_line_loop; --ファイルのクローズif (utl_file.is_open (file = w_utl_file_type)) then utl_file.fclose (file = W_utl_file_type); END IF; -- --w_filename: = Utl_file.fopen (c_location, C_bat_name, ' a '); --utl_file.put_line (W_filename,--convert (' ren ' | | w_file_name | | ' ' || Replace (W_file_name, ",") | | CHR,--' ja16sjis ',--' UTF8 '); --utl_file.fflush (W_filename); --utl_file.fclose (W_filename); --CLOSE cur_lcy_test; END;
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL Script for select data from EBS and make a CSV file to FTP