1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
CURSOR C0 IS
SELECT lpad(AK.PAC_NO,10,' ')||
TO_CHAR(AZ.EFT_NO,'S999999999')||
TO_CHAR(AZ.APB_NO,'S999999999')||
lpad(AK.PAC_CAL_PAR_NO,10,' ')||
lpad(AK.PAC_CAL_SIT_NO,10,' ')||
lpad(AK.PAC_CAL_GRA_NO,10,' ')||
lpad(to_char(AK.MOB_NO),10,' ')||
TO_CHAR(AK.TTE_NO,'S999999999')||
TO_CHAR(AK.LCF_NO,'S999999999')||
TO_CHAR(AK.MTE_NO,'S999999999')||
LPAD(AK.PAC_CODE,8,' ')||
nvl(AK.PAC_ND,' ')||
nvl(to_char(AK.PAC_SERVICE_UNIVERSEL),' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_MS_SOUHAITEE,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_MS_CONTRACTUELLE,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_MS_TECHNIQUE,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_MS_COMMERCIALE,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_PREMIERE_FACTURATION,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_RESILIATION,'YYYYMMDDhh24miss'),' '),14,' ')||
lpad(nvl(BC.LCF_CODE,' '),30,' ')||
TO_CHAR(BD.TTC_MT_CFF,'S9999.99')||
lpad(nvl(TO_CHAR(AK.PAC_DATE_RESILIATION_SVG,'YYYYMMDDhh24miss'), ' '),14,' ')||
AK.PAC_SU_ELIGIBLE
FROM
POINT_ACCES AK, PARTENAIRE_ALIMENTATION AZ,
LIBELLE_CFF BC, TYPE_TERMINAL_CFF BD
WHERE
(AK.PAC_CAL_PAR_NO = AZ.PAR_NO)
AND (AK.LCF_NO = BD.LCF_NO(+))
AND (AK.TTE_NO = BD.TTE_NO(+))
AND (AK.LCF_NO = BC.LCF_NO(+))
;
TYPE T_LINE IS TABLE OF VARCHAR2(258);
V_LINE T_LINE;
V_NLINE VARCHAR2(32767);
V_ANNEE VARCHAR2(4);
F1 UTL_FILE.FILE_TYPE;
N_LIG NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('DATE DEB : '||TO_CHAR(SYSDATE,'DD/MM/YY HH:MI:SS'));
F1 := UTL_FILE.FOPEN('/home/oracle/test_pl/','rppac05.txt','w');
N_LIG := 0;
V_NLINE:='';
OPEN C0;
LOOP
--V_LINE := '';
FETCH C0 BULK COLLECT INTO V_LINE LIMIT 127;
EXIT WHEN C0%NOTFOUND;
FOR I IN V_LINE.FIRST .. V_LINE.LAST LOOP
V_NLINE := V_NLINE||V_LINE(i)||'\n';
END LOOP;
UTL_FILE.PUTF(F1, V_NLINE);
V_NLINE:='';
END LOOP;
CLOSE C0;
UTL_FILE.FCLOSE(F1);
DBMS_OUTPUT.PUT_LINE('DATE FIN : '||TO_CHAR(SYSDATE,'DD/MM/YY HH:MI:SS'));
END;
/ |
Partager