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
| SQL> drop table t_contact;
Table supprimée.
SQL>
SQL> create table t_contact(
c1 number, c2 varchar2(10), c3 varchar2(10), c4 varchar2(10), c5 clob);
Table créée.
SQL>
SQL> CREATE OR REPLACE PACKAGE SQL_CLOB AS
2 PROCEDURE add_clob(p_id NUMBER, p_name VARCHAR2);
3 END SQL_CLOB;
4 /
Package créé.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY SQL_CLOB AS
2
3 PROCEDURE add_clob(p_id NUMBER, p_name VARCHAR2) AS
4 v_clob CLOB;
5 v_bfile BFILE;
6
7 BEGIN
8 INSERT INTO t_contact
9 VALUES
10 (p_id,'','','', empty_clob()) RETURNING c5 INTO v_clob;
11 v_bfile := bfilename(directory => 'BLOBDIR', filename => p_name);
12 dbms_lob.fileopen(v_bfile);
13 dbms_lob.loadfromfile(v_clob, v_bfile, dbms_lob.getlength(v_bfile));
14 dbms_lob.fileclose(v_bfile);
15 END;
16 END SQL_CLOB;
17 /
Corps de package créé.
SQL>
SQL>
SQL> exec sql_clob.add_clob(1, 'TEST.TXT');
Procédure PL/SQL terminée avec succès.
SQL>
SQL> select dbms_lob.substr(c5,2,1) from t_contact;
DBMS_LOB.SUBSTR(C5,2,1)
--------------------------------------------------------------------------------
ok
SQL> |
Partager