SQL Script for select data from ebs and make a csv file to FTP，ebscsv

SQL Script for select data from ebs and make a csv file to FTP，ebscsv

DECLARE  CURSOR cur_lcy_test IS    SELECT rcta.customer_trx_id, rcta.trx_number, rcta.trx_date      FROM ra_customer_trx_all 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; -- ファイル名  w_utl_file_type utl_file.file_type;  w_flag          VARCHAR2(20) DEFAULT NULL;  w_rec_count     NUMBER DEFAULT 0;  w_loop_num      NUMBER DEFAULT 0;  w_file_path     VARCHAR2(2000) DEFAULT NULL;  w_filename      utl_file.file_type;  --  c_open_mode_read  CONSTANT VARCHAR2(2000) := 'r';  c_open_mode_write CONSTANT VARCHAR2(2000) := 'w';  c_true            CONSTANT VARCHAR2(2000) := 'TRUE';  c_false            CONSTANT VARCHAR2(2000) := '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_mode_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,                                    max_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')); --修改  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(13),  --                                      'JA16SJIS',  --                                      'UTF8'));  --utl_file.fflush(w_filename);  --utl_file.fclose(w_filename);               --  CLOSE cur_lcy_test;END;

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。