в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:57Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Large Objects/bfilename - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия