SQL Script for select data from EBS and make a CSV file to FTP

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 =&gt ; 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