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');
--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); //lv_file_line是有数据的
UTL_FILE.PUT_LINE(vHandle,lv_file_line); //难道是这句话错了?? dbms_output.put_line('!!!!!!!!!!!UTL_FILE.PUT_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;
/最主要的就是这俩句话,
dbms_output.put_line(lv_file_line); //lv_file_line是有数据的
UTL_FILE.PUT_LINE(vHandle,lv_file_line); //难道是这句话错了??但也没什么错误阿,我是在sql*plus中调用这个包的,为什么呢,大家帮帮忙啊!!
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');
--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); //lv_file_line是有数据的
UTL_FILE.PUT_LINE(vHandle,lv_file_line); //难道是这句话错了?? dbms_output.put_line('!!!!!!!!!!!UTL_FILE.PUT_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;
/最主要的就是这俩句话,
dbms_output.put_line(lv_file_line); //lv_file_line是有数据的
UTL_FILE.PUT_LINE(vHandle,lv_file_line); //难道是这句话错了??但也没什么错误阿,我是在sql*plus中调用这个包的,为什么呢,大家帮帮忙啊!!
UTL_FILE.PUT_LINE(vHandle,lv_file_line,true); 试试
看看下面的是否可以
--sys
CREATE DIRECTORY ddd AS 'c:\';
GRANT READ,WRITE ON DIRECTORY ddd to SCOTT;--scott
DECLARE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE := UTL_FILE.FOPEN('DDD', 'xx.txt', 'w');
UTL_FILE.put_line(FILE_HANDLE, 'abcd\n');
UTL_FILE.FCLOSE(FILE_HANDLE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
/
权限赋予成功
程序正常完了
但c盘根目录下也没创建xx.txt这个文件阿,和我的程序一样哦。
帮帮忙!!