CREATE OR REPLACE PACKAGE BODY APPS.XXAE03103
IS PROCEDURE main(errbuf IN OUT VARCHAR2
,retcode IN OUT VARCHAR2
,in_occur_type IN VARCHAR2
,in_date IN DATE)
IS
--create or replace directory vDirname as 'C:\xpxpxpxp\';
CURSOR cur_file_loop IS
SELECT
XXAE_REPAY.TPP_CODE,
XXAE_REPAY.TPP_DIVIDE_CODE,
XXAE_REPAY.CONTR_CORP_CODE,
XXAE_CONTR_CORP.CONTR_CORP_NAME,
XXAE_REPAY.CONTR_FORM_TYPE,
XXAE_REPAY.CONTR_SUB_ID,
XXAE_REPAY.SCHEME_TYPE,
XXAE_REPAY.DOMESTIC_BRAND_CODE,
XXAE_REPAY.DOMESTIC_BRAND_CODE2,
XXAE_REPAY.INTER_BRAND_CODE,
XXAE_REPAY.CARD_NUM,
XXAE_REPAY.USER_KANA_NAME,
XXAE_REPAY.CLEARING_PROMISE_DATE,
XXAE_REPAY.REPAY_STATUS_TYPE,
XXAE_REPAY.REPAY_OPERATION_TYPE,
XXAE_REPAY.REPAY_AMT,
XXAE_REPAY.REPAY_DATE,
XXAE_REPAY.REPAY_OCCUR_TYPE,
XXAE_REPAY.CASH_SHIFT_TYPE,
XXAE_REPAY.INDIVIDUAL_PAYMENT_REGIST_DATE,
XXAE_REPAY.CASH_SHIFT_TO_DEPT_CODE,
XXAE_REPAY.BILL_TRUST_TYPE,
XXAE_REPAY.REPAY_TRUST_TYPE,
XXAE_REPAY.ISS_TPP_8_CODE,
XXAE_REPAY.CR_COOP_COLLECTION_DATE,
XXAE_REPAY.CR_COOP_PAYMENT_DATE
FROM XXAE_REPAY,XXAE_CONTR_CORP
WHERE XXAE_REPAY.REPAY_OCCUR_TYPE = in_occur_type AND
--trunc(XXAE_REPAY.REPAY_DATE) = to_date(in_date,'yyyy/mm/dd') AND
XXAE_REPAY.CONTR_CORP_CODE = XXAE_CONTR_CORP.CONTR_CORP_CODE
ORDER BY XXAE_REPAY.TPP_CODE;
vHandle UTL_FILE.FILE_TYPE;
vDirname VARCHAR2(250); --パス名
vFilename VARCHAR2(250); --ファイル名
-- vOutput VARCHAR2(32767);
lv_file_line VARCHAR(32767);
BEGIN
xxae_common_pkg.put_log_header();
vDirname :='C:\TEMP';
vFilename := 'WREN.txt'; vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767);
--dbms_output.put_line(in_occur_type||in_date);
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!23333333333333333333');
FOR rec_file IN cur_file_loop LOOP
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!23333333333333333333');
lv_file_line := rec_file.TPP_CODE || ','
|| rec_file.TPP_DIVIDE_CODE || ','
|| rec_file.CONTR_CORP_CODE || ','
|| rec_file.CONTR_CORP_NAME || ','
|| rec_file.CONTR_FORM_TYPE || ','
|| rec_file.CONTR_SUB_ID || ','
|| rec_file.SCHEME_TYPE || ','
|| rec_file.DOMESTIC_BRAND_CODE || ','
|| rec_file.DOMESTIC_BRAND_CODE2 || ','
|| rec_file.INTER_BRAND_CODE || ','
|| rec_file.CARD_NUM || ','
|| rec_file.USER_KANA_NAME || ','
|| rec_file.CLEARING_PROMISE_DATE || ','
|| rec_file.REPAY_STATUS_TYPE || ','
|| rec_file.REPAY_OPERATION_TYPE || ','
|| rec_file.REPAY_AMT || ','
|| rec_file.REPAY_DATE || ','
|| rec_file.REPAY_OCCUR_TYPE || ','
|| rec_file.CASH_SHIFT_TYPE || ','
|| rec_file.INDIVIDUAL_PAYMENT_REGIST_DATE || ','
|| rec_file.CASH_SHIFT_TO_DEPT_CODE || ','
|| rec_file.BILL_TRUST_TYPE || ','
|| rec_file.REPAY_TRUST_TYPE || ','
|| rec_file.ISS_TPP_8_CODE || ','
|| rec_file.CR_COOP_COLLECTION_DATE || ','
|| rec_file.CR_COOP_PAYMENT_DATE;
dbms_output.put_line(lv_file_line);
UTL_FILE.PUT_LINE(vHandle,lv_file_line);
--vOutput := '初期化パラメータ UTL_FILE_DIR 経由でのファイル出力です';
END LOOP;
UTL_FILE.FCLOSE(vHandle);
xxae_common_pkg.put_log_footer(iv_retcode => retcode,iv_errbuf => errbuf);
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!244444444444444444444');
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
retcode := 2;
errbuf := 'CUO LE ';
xxae_common_pkg.put_log_footer(iv_retcode => retcode,
iv_errbuf => errbuf);
-- dbms_output.put_line('ERROR:'||SQLERRM);
END main;END XXAE03103;
/大家帮帮忙哦
IS PROCEDURE main(errbuf IN OUT VARCHAR2
,retcode IN OUT VARCHAR2
,in_occur_type IN VARCHAR2
,in_date IN DATE)
IS
--create or replace directory vDirname as 'C:\xpxpxpxp\';
CURSOR cur_file_loop IS
SELECT
XXAE_REPAY.TPP_CODE,
XXAE_REPAY.TPP_DIVIDE_CODE,
XXAE_REPAY.CONTR_CORP_CODE,
XXAE_CONTR_CORP.CONTR_CORP_NAME,
XXAE_REPAY.CONTR_FORM_TYPE,
XXAE_REPAY.CONTR_SUB_ID,
XXAE_REPAY.SCHEME_TYPE,
XXAE_REPAY.DOMESTIC_BRAND_CODE,
XXAE_REPAY.DOMESTIC_BRAND_CODE2,
XXAE_REPAY.INTER_BRAND_CODE,
XXAE_REPAY.CARD_NUM,
XXAE_REPAY.USER_KANA_NAME,
XXAE_REPAY.CLEARING_PROMISE_DATE,
XXAE_REPAY.REPAY_STATUS_TYPE,
XXAE_REPAY.REPAY_OPERATION_TYPE,
XXAE_REPAY.REPAY_AMT,
XXAE_REPAY.REPAY_DATE,
XXAE_REPAY.REPAY_OCCUR_TYPE,
XXAE_REPAY.CASH_SHIFT_TYPE,
XXAE_REPAY.INDIVIDUAL_PAYMENT_REGIST_DATE,
XXAE_REPAY.CASH_SHIFT_TO_DEPT_CODE,
XXAE_REPAY.BILL_TRUST_TYPE,
XXAE_REPAY.REPAY_TRUST_TYPE,
XXAE_REPAY.ISS_TPP_8_CODE,
XXAE_REPAY.CR_COOP_COLLECTION_DATE,
XXAE_REPAY.CR_COOP_PAYMENT_DATE
FROM XXAE_REPAY,XXAE_CONTR_CORP
WHERE XXAE_REPAY.REPAY_OCCUR_TYPE = in_occur_type AND
--trunc(XXAE_REPAY.REPAY_DATE) = to_date(in_date,'yyyy/mm/dd') AND
XXAE_REPAY.CONTR_CORP_CODE = XXAE_CONTR_CORP.CONTR_CORP_CODE
ORDER BY XXAE_REPAY.TPP_CODE;
vHandle UTL_FILE.FILE_TYPE;
vDirname VARCHAR2(250); --パス名
vFilename VARCHAR2(250); --ファイル名
-- vOutput VARCHAR2(32767);
lv_file_line VARCHAR(32767);
BEGIN
xxae_common_pkg.put_log_header();
vDirname :='C:\TEMP';
vFilename := 'WREN.txt'; vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767);
--dbms_output.put_line(in_occur_type||in_date);
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!23333333333333333333');
FOR rec_file IN cur_file_loop LOOP
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!23333333333333333333');
lv_file_line := rec_file.TPP_CODE || ','
|| rec_file.TPP_DIVIDE_CODE || ','
|| rec_file.CONTR_CORP_CODE || ','
|| rec_file.CONTR_CORP_NAME || ','
|| rec_file.CONTR_FORM_TYPE || ','
|| rec_file.CONTR_SUB_ID || ','
|| rec_file.SCHEME_TYPE || ','
|| rec_file.DOMESTIC_BRAND_CODE || ','
|| rec_file.DOMESTIC_BRAND_CODE2 || ','
|| rec_file.INTER_BRAND_CODE || ','
|| rec_file.CARD_NUM || ','
|| rec_file.USER_KANA_NAME || ','
|| rec_file.CLEARING_PROMISE_DATE || ','
|| rec_file.REPAY_STATUS_TYPE || ','
|| rec_file.REPAY_OPERATION_TYPE || ','
|| rec_file.REPAY_AMT || ','
|| rec_file.REPAY_DATE || ','
|| rec_file.REPAY_OCCUR_TYPE || ','
|| rec_file.CASH_SHIFT_TYPE || ','
|| rec_file.INDIVIDUAL_PAYMENT_REGIST_DATE || ','
|| rec_file.CASH_SHIFT_TO_DEPT_CODE || ','
|| rec_file.BILL_TRUST_TYPE || ','
|| rec_file.REPAY_TRUST_TYPE || ','
|| rec_file.ISS_TPP_8_CODE || ','
|| rec_file.CR_COOP_COLLECTION_DATE || ','
|| rec_file.CR_COOP_PAYMENT_DATE;
dbms_output.put_line(lv_file_line);
UTL_FILE.PUT_LINE(vHandle,lv_file_line);
--vOutput := '初期化パラメータ UTL_FILE_DIR 経由でのファイル出力です';
END LOOP;
UTL_FILE.FCLOSE(vHandle);
xxae_common_pkg.put_log_footer(iv_retcode => retcode,iv_errbuf => errbuf);
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!244444444444444444444');
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
retcode := 2;
errbuf := 'CUO LE ';
xxae_common_pkg.put_log_footer(iv_retcode => retcode,
iv_errbuf => errbuf);
-- dbms_output.put_line('ERROR:'||SQLERRM);
END main;END XXAE03103;
/大家帮帮忙哦
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货