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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| create or replace
PROCEDURE ADDHAS AS
ligne_fic VARCHAR2(32767);
contenu_fic UTL_FILE.file_type;
separateur VARCHAR2(1) := ';';
i PLS_INTEGER := 0 ;
numLine NUMBER;
IDHOUSEHOLD NUMBER;
INHOUSEHOLDTYPE VARCHAR2(3);
INHOUSEHOLDNAME VARCHAR2(50);
INHOUSEHOLDSERVICE VARCHAR2(32);
INHOUSEHOLDAD2 VARCHAR2(50);
INHOUSEHOLDAD3 VARCHAR2(50);
INHOUSEHOLDAD4 VARCHAR2(50);
INHOUSEHOLDAD5 VARCHAR2(50);
INHOUSEHOLDPOSTALCODE VARCHAR2(10);
INHOUSEHOLDCITY VARCHAR2(50);
INHOUSEHOLDCOUNTRY VARCHAR2(50);
INHOUSEHOLDINSEECODE VARCHAR2(10);
INHOUSEHOLDIRISCODE VARCHAR2(10);
INHOUSEHOLDRNVP NUMBER(1);
INHOUSEHOLDRNVPSTATUS VARCHAR(3);
INHOUSEHOLDREMOVE NUMBER(1);
INHOUSEHOLDNPAI NUMBER(1);
BEGIN
BEGIN
contenu_fic := UTL_FILE.FOPEN('TMP', 'out.csv', 'r', 32767);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
LOOP
BEGIN
UTL_FILE.GET_LINE (contenu_fic, ligne_fic);
numLine := to_number(substr(ligne_fic, 0, INSTR( ligne_fic, separateur, 1, 1 )-1));
INHOUSEHOLDTYPE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 1 )+1, (INSTR( ligne_fic, separateur, 1, 2 )- INSTR( ligne_fic, separateur, 1, 1 ))-1);
INHOUSEHOLDNAME := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 2 )+1, (INSTR( ligne_fic, separateur, 1, 3 )- INSTR( ligne_fic, separateur, 1, 2 ))-1);
INHOUSEHOLDSERVICE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 3 )+1, (INSTR( ligne_fic, separateur, 1, 4 )- INSTR( ligne_fic, separateur, 1, 3 ))-1);
INHOUSEHOLDAD2 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 4 )+1, (INSTR( ligne_fic, separateur, 1, 5 )- INSTR( ligne_fic, separateur, 1, 4 ))-1);
INHOUSEHOLDAD3 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 5 )+1, (INSTR( ligne_fic, separateur, 1, 6 )- INSTR( ligne_fic, separateur, 1, 5 ))-1);
INHOUSEHOLDAD4 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 6 )+1, (INSTR( ligne_fic, separateur, 1, 7 )- INSTR( ligne_fic, separateur, 1, 6 ))-1);
INHOUSEHOLDAD5 := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 7 )+1, (INSTR( ligne_fic, separateur, 1, 8 )- INSTR( ligne_fic, separateur, 1, 7 ))-1);
INHOUSEHOLDPOSTALCODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 8 )+1, (INSTR( ligne_fic, separateur, 1, 9 )- INSTR( ligne_fic, separateur, 1, 8 ))-1);
INHOUSEHOLDCITY := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 9 )+1, (INSTR( ligne_fic, separateur, 1, 10 )- INSTR( ligne_fic, separateur, 1, 9 ))-1);
INHOUSEHOLDCOUNTRY := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 10 )+1, (INSTR( ligne_fic, separateur, 1, 11 )- INSTR( ligne_fic, separateur, 1, 10 ))-1);
INHOUSEHOLDINSEECODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 11 )+1, (INSTR( ligne_fic, separateur, 1, 12 )- INSTR( ligne_fic, separateur, 1, 11 ))-1);
INHOUSEHOLDIRISCODE := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 12 )+1, (INSTR( ligne_fic, separateur, 1, 13 )- INSTR( ligne_fic, separateur, 1, 12 ))-1);
INHOUSEHOLDRNVP := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 13 )+1, (INSTR( ligne_fic, separateur, 1, 14 )- INSTR( ligne_fic, separateur, 1, 13 ))-1));
INHOUSEHOLDRNVPSTATUS := substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 14 )+1, (INSTR( ligne_fic, separateur, 1, 15 )- INSTR( ligne_fic, separateur, 1, 14 ))-1);
INHOUSEHOLDREMOVE := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 15 )+1, (INSTR( ligne_fic, separateur, 1, 16 )- INSTR( ligne_fic, separateur, 1, 15 ))-1));
INHOUSEHOLDNPAI := to_number(substr(ligne_fic, INSTR( ligne_fic, separateur, 1, 16 )+1));
INSERT INTO HOUSEHOLD (HOUSEHOLDTYPE, HOUSEHOLDNAME, HOUSEHOLDSERVICE, HOUSEHOLDAD2, HOUSEHOLDAD3, HOUSEHOLDAD4, HOUSEHOLDAD5, HOUSEHOLDDEPT, HOUSEHOLDPOSTALCODE, HOUSEHOLDCITY, HOUSEHOLDCOUNTRY, HOUSEHOLDINSEECODE, HOUSEHOLDIRISCODE, HOUSEHOLDRNVP, HOUSEHOLDRNVPSTATUS, HOUSEHOLDREMOVE, HOUSEHOLDNPAI)
VALUES (INHOUSEHOLDTYPE, INHOUSEHOLDNAME, INHOUSEHOLDSERVICE, INHOUSEHOLDAD2, INHOUSEHOLDAD3, INHOUSEHOLDAD4, INHOUSEHOLDAD5, SUBSTR(INHOUSEHOLDPOSTALCODE,0,2), INHOUSEHOLDPOSTALCODE, INHOUSEHOLDCITY, INHOUSEHOLDCOUNTRY, INHOUSEHOLDINSEECODE, INHOUSEHOLDIRISCODE, INHOUSEHOLDRNVP, INHOUSEHOLDRNVPSTATUS, INHOUSEHOLDREMOVE, INHOUSEHOLDNPAI )
RETURNING HOUSEHOLDID INTO IDHOUSEHOLD;
UPDATE MATCHING SET HOUSEHOLDID = IDHOUSEHOLD WHERE LINEID = numLine;
i := i + 1;
IF i = 10000 THEN
commit;
i := 0;
end if;
EXCEPTION WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(contenu_fic);
EXIT;
END;
END LOOP;
COMMIT;
END ADDHAS; |
Partager