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 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
XXAE_REPAY.REPAY_DATE = in_date 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:\xpxpxpxp\';
vFilename := 'WREN.txt';
vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767); FOR rec_file IN cur_file_loop LOOP 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; 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);
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
retcode := 2;
errbuf := SQLERRM;
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 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
XXAE_REPAY.REPAY_DATE = in_date 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:\xpxpxpxp\';
vFilename := 'WREN.txt';
vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767); FOR rec_file IN cur_file_loop LOOP 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; 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);
EXCEPTION
WHEN OTHERS THEN
--ROLLBACK;
retcode := 2;
errbuf := SQLERRM;
xxae_common_pkg.put_log_footer(iv_retcode => retcode,
iv_errbuf => errbuf);
dbms_output.put_line('ERROR:'||SQLERRM);
END main;END XXAE03103;
/
是不是你数据长度太大,超过上限了
SQL> create or replace PROCEDURE main(errbuf IN OUT VARCHAR2
2 ,retcode IN OUT VARCHAR2
3 ,in_occur_type IN VARCHAR2
4 ,in_date IN DATE)
5
6 IS
7 vHandle UTL_FILE.FILE_TYPE;
8 vDirname VARCHAR2(250);
9 vFilename VARCHAR2(250);
10 lv_file_line VARCHAR(32767);
11 BEGIN
12 vDirname :='C:\backup';
13 vFilename := 'WREN.txt';
14 vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767);
15
16 for rec_file IN (SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID FROM hr.employees)
17 LOOP
18 lv_file_line := rec_file.EMPLOYEE_ID || ','
19 || rec_file.FIRST_NAME || ','
20 || rec_file.LAST_NAME || ','
21 || rec_file.EMAIL || ','
22 || rec_file.PHONE_NUMBER || ','
23 || rec_file.HIRE_DATE || ','
24 || rec_file.JOB_ID || ','
25 || rec_file.SALARY || ','
26 || rec_file.MANAGER_ID || ','
27 || rec_file.DEPARTMENT_ID;
28
29 UTL_FILE.PUT_LINE(vHandle,lv_file_line);
30 END LOOP;
31 UTL_FILE.FCLOSE(vHandle);
32 EXCEPTION
33 WHEN OTHERS THEN
34 --ROLLBACK;
35 retcode := 2;
36 errbuf := SQLERRM;
37 dbms_output.put_line('ERROR:'||SQLERRM);
38 END main;
39 /Procedure createdSQL>
SQL> declare
2 a nvarchar2(30);
3 b nvarchar2(30);
4 begin
5 --a := '';
6 main(a,b,'c',sysdate);
7 DBMS_OUTPUT.put_line(a);
8 end;
9 /PL/SQL procedure successfully completedSQL>
vHandle := UTL_FILE.FOPEN(vDirname,vFilename,'w', 32767);
dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!23333333333333333333');
就红字的地方错了,我不知道为什么??
create or replace directory UTL_TEST1
as 'C:\xpxpxpxp\';
vHandle := UTL_FILE.FOPEN('UTL_TEST1',vFilename,'w', 32767);
------------------------------------ ----------- ---------------------
utl_file_dir string c:\backupSQL> alter system set utl_file_dir='C:\xpxpxpxp' scope=spfile;System altered.
as 'C:\xpxpxpxp\'; 这句话 加在程序的那部分阿??
谢谢了
'C:\xpxpxpxp\'是DB服务器路径