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
| begin
f := utl_file.fopen(rep,'ventes.txt','R');
utl_file.get_line(f,s);
date:=substr(s,7,8);
nbr:=sUbstr(s,15);
dbms_output.put_line('FICHIER EXTRAIT LE');
dbms_output.put_line(date);
dbms_output.put_line('NOMBRE DE LIGNE:');
dbms_output.put_line(nbr);
loop
utl_file.get_line(f,s);
c1:=substr(s,1,2);
c2:=substr(s,3,3);
c3:=substr(s,6,6);
c4:=substr(s,12,1);
c5:=substr(s,13,1);
c6:=substr(s,14,1);
c7:=substr(s,15,4);
c8:=substr(s,19,7);
c91:=substr(s,26,9);
c92:=substr(s,35,2);
c9:=c91||'.'||c92;
c101:=substr(s,37,9);
c102:=substr(s,46,6);
c10:=c101||'.'||c102;
c11:=substr(s,52,7);
c12:=substr(s,59,11);
c13:=substr(s,70,15);
insert into dwh_roy_ventes values(c1,to_number(c2/1000),
c3,to_number(c4),to_number(c5),to_number(c7),
c8,to_number(c9/1000),to_number(c10/1000),c11,to_number(c12),to_number(c13));
dbms_output.put_line(c9);
end loop;
dbms_output.put_line(to_number(c10,'999999999.999999'));
utl_file.fclose(f);
EXCEPTION
when NO_DATA_FOUND
then
dbms_output.put_line('extraction termine');
WHEN others
THEN
dbms_output.put_line(SQLERRM);
UTL_FILE.FCLOSE_ALL;
end; |