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
84
85
| create or replace
PROCEDURE INSDB
(
PC$Filepath IN VARCHAR2
)
IS
LN$Curseur NUMBER := 1;
LN$CurrentPos NUMBER := 1;
LN$LastPos NUMBER := 1;
LN$Id NUMBER := 0;
LN$BCode VARCHAR2(255);
LN$Date VARCHAR2(255);
LN$Source VARCHAR2(255);
LN$IsGoodText VARCHAR2(255);
LN$IsGood NUMBER := 0;
LN$Bfile_Csid VARCHAR2(255) := NLS_CHARSET_ID('UTF8');
LN$Lang NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
LN$Warn NUMBER := 0;
L$Blob CLOB;
L$Bfile BFILE ;
LN$Len NUMBER := dbms_lob.lobmaxsize;
LN$src_off PLS_INTEGER := 1 ;
LN$dst_off PLS_INTEGER := 1 ;
BEGIN
IF PC$Filepath IS NOT NULL THEN
FOR LN$Curseur IN 1..4
LOOP
LN$CurrentPos := INSTR(PC$Filepath, '_', LN$LastPos) - LN$LastPos;
CASE LN$Curseur
WHEN 1 THEN LN$BCode := SUBSTR(PC$Filepath, LN$LastPos, LN$CurrentPos) ;
WHEN 2 THEN LN$Date := SUBSTR(PC$Filepath, LN$LastPos, LN$CurrentPos) ;
WHEN 3 THEN LN$Source := SUBSTR(PC$Filepath, LN$LastPos, LN$CurrentPos) ;
WHEN 4 THEN LN$IsGoodText := SUBSTR(PC$Filepath, LN$LastPos, LN$CurrentPos) ;
ELSE
EXIT;
END CASE;
LN$LastPos := LN$LastPos + LN$CurrentPos + 1;
END LOOP;
CASE UPPER(LN$IsGoodText)
WHEN 'D' THEN LN$IsGood := 0;
WHEN 'G' THEN LN$IsGood := 1;
END CASE;
INSERT INTO MONSCHEMA.uf (I, B, ID, IG, S, D, IP) VALUES (NULL, LN$BCode, LN$Date, LN$IsGood, LN$Source, empty_clob(), 0);
SELECT upseq.currval INTO LN$Id FROM dual;
SELECT datas INTO L$Blob FROM MONSCHEMA.T1 WHERE id = LN$Id FOR UPDATE;
L$Bfile := BFILENAME( 'DDIR', PC$Filepath );
dbms_lob.fileopen(L$Bfile, dbms_lob.file_readonly);
dbms_lob.loadclobfromfile(
L$Blob, -- CLOB de destination
L$Bfile, -- Pointeur de fichier en entrée
LN$Len, -- Nombre d'octets à lire
LN$src_off, -- Position source de départ
LN$dst_off,
LN$Bfile_Csid,
LN$Lang,
LN$Warn
);
dbms_lob.fileclose(L$Bfile);
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END; |
