Oracle PL/SQL Tutorial/Large Objects/bfilename
bfilename function
<source lang="sql">
SQL> SQL> create or replace type vcArray as table of varchar2(4000)
2 /
Type created. SQL> SQL> create or replace
2 function trace_file_contents( p_filename in varchar2 ) 3 return vcArray 4 pipelined 5 as 6 l_bfile bfile := bfilename("UDUMP_DIR",p_filename); 7 l_last number := 1; 8 l_current number; 9 begin 10 select rownum into l_current 11 from user_avail_trace_files 12 where filename = p_filename; 13 14 dbms_lob.fileopen( l_bfile ); 15 loop 16 l_current := dbms_lob.instr( l_bfile, "0A", l_last, 1 ); 17 exit when (nvl(l_current,0) = 0); 18 pipe row(utl_raw.cast_to_varchar2(dbms_lob.substr( l_bfile, l_current-l_last+1,l_last ) )); 19 l_last := l_current+1; 20 end loop; 21 dbms_lob.fileclose(l_bfile); 22 return; 23 end; 24 /
Function created. SQL> SQL> SQL></source>
Stored procedure to read the data from the BFILE into the clob clob and display the new clob contents.
<source lang="sql">
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></source>
Use the file named b_file.txt and attach it to the myTable table in the col_bfile column.
<source lang="sql">
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> SQL> SQL> SQL> UPDATE myTable
2 SET col_bfile = BFILENAME ("INFILE", "b_file.txt") 3 WHERE key = 1;
1 row updated. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>