|
SQL>
SQL> create table myTable
2 (key NUMBER PRIMARY KEY
3 ,col_blob BLOB
4 ,col_clob CLOB);
Table created.
SQL>
SQL> INSERT INTO myTable (key, col_blob, col_clob)
2 VALUES (1, HEXTORAW('101F'), 'ZYXW');
1 row created.
SQL>
SQL>
SQL> ALTER TABLE myTable add (col_bfile BFILE);
Table altered.
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_bfile_read
2 IS
3 v_fileloc BFILE;
4 v_col_clob CLOB;
5 v_filename VARCHAR2(2000);
6 v_amount NUMBER := 80;
7 v_offset NUMBER := 1;
8 v_buffer VARCHAR2(80);
9
10 v_dir VARCHAR2(80) := 'INFILE';
11
12 BEGIN
13 v_fileloc := BFILENAME (v_dir, 'b_file.txt');
14
15 DBMS_LOB.FILEOPEN (v_fileloc, DBMS_LOB.FILE_READONLY);
16
17 SELECT col_clob
18 INTO v_col_clob
19 FROM myTable
20 WHERE key = 2
21 FOR UPDATE;
22
23 DBMS_LOB.LOADFROMFILE (v_col_clob, v_fileloc, DBMS_LOB.GETLENGTH (v_fileloc) );
24 DBMS_LOB.READ (v_col_clob, v_amount, v_offset, v_buffer);
25
26 DBMS_OUTPUT.PUT_LINE (v_buffer);
27
28 DBMS_LOB.FILECLOSE (v_fileloc);
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 DBMS_LOB.FILECLOSEALL;
32 RAISE_APPLICATION_ERROR(-20001, 'No data found!');
33
34 WHEN VALUE_ERROR THEN
35 DBMS_LOB.FILECLOSEALL;
36 RAISE_APPLICATION_ERROR(-20001, 'Value Error!');
37
38 WHEN DBMS_LOB.INVALID_ARGVAL THEN
39 DBMS_LOB.FILECLOSEALL;
40 RAISE_APPLICATION_ERROR(-20001, 'Read only mode not specified');
41
42 WHEN OTHERS THEN
43 DBMS_LOB.FILECLOSEALL;
44 RAISE_APPLICATION_ERROR(-20009, 'Other exception raised: ' || SQLCODE);
45 END;
46 /
Procedure created.
SQL>
SQL> exec sp_bfile_read
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
|