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; -- Comment NUMBER: = 0; TYPE comment is table of VARCHAR2 (4000) index by BINARY_INTEGER; w_rec_data comment; w_separator VARCHAR2 (1 ): = ','; w_file_name character % TYPE; -- invalid variable character name 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; -- invalid 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; <strong> loop fetch cur_lcy_test INTO rec_lcy_test; exit when cur_lcy_test % NOTFOUND; -- token: = w_csv_line_num + 1; w_rec_data (w_csv_line_num): = xxcmpz11.func _ make_qt2_str (to_char (token )) | w_separator | xxcmpz11.func _ make_qt2_str (to_char (bytes) | w_separator | prop _ make_qt2_str (to_date (delimiter, 'yyyy/mm/dd ')); -- end loop completion; -- w_file_name: = 'LCY_WRITE_FILE_TEST.csv '; w_file_path: = 'xxif _ output'; -- 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 ('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 => convert, buffer => convert (w_rec_data (w_loop_num), 'ja16sjis ', 'utf8'); -- modify end loop user_file_line_loop; -- commandid before using them 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;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.