в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:57Z

1 версия

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

== DBMS_LOB Package==



<source lang="sql">
SQL>
SQL>
SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY,
3 blob_data BLOB);
Table created.
SQL>
SQL>
SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 15

SQL>
SQL> drop table myBlob;
Table dropped.
SQL></source>

== DBMS_LOB.READ==



<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>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(1, HEXTORAW("101F"), "ZYXW");
1 row created.
SQL>
SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(2, HEXTORAW("111101F"), "ABCD");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_inmyTable2
2 IS
3
4 v_key1 myTable.key%TYPE;
5 blobValue1 myTable.col_blob%TYPE;
6 clobValue1 myTable.col_clob%TYPE;
7 v_key2 myTable.key%TYPE;
8 blobValue2 myTable.col_blob%TYPE;
9 clobValue2 myTable.col_clob%TYPE;
10 v_buffer VARCHAR2(1000);
11 v_offset NUMBER;
12 v_amount NUMBER;
13 v_dest_offset NUMBER := 1;
14 v_src_offset NUMBER := 1;
15
16
17 BEGIN
18
19 SELECT key, col_blob, col_clob
20 INTO v_key1, blobValue1, clobValue1
21 FROM myTable
22 WHERE key = 1
23 FOR UPDATE;
24
25 SELECT key, col_blob, col_clob
26 INTO v_key2, blobValue2, clobValue2
27 FROM myTable
28 WHERE key = 2
29 FOR UPDATE;
30
31 v_amount := 80;
32 v_offset := 1;
33 DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
34 DBMS_OUTPUT.PUT_LINE ("Clob1 Contents => " || v_buffer);
35
36 v_amount := 80;
37 v_offset := 1;
38 DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
39 DBMS_OUTPUT.PUT_LINE ("Clob2 Contents => " || v_buffer);
40
41 v_amount := 80;
42 v_offset := 1;
43 DBMS_LOB.COPY (clobValue1, clobValue2, v_amount, v_dest_offset, v_src_offset);
44
45 v_amount := 80;
46 v_offset := 1;
47 DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer);
48 DBMS_OUTPUT.PUT_LINE ("Clob1 Contents => " || v_buffer);
49
50 v_amount := 80;
51 v_offset := 1;
52 DBMS_LOB.READ (clobValue2, v_amount, v_offset, v_buffer);
53 DBMS_OUTPUT.PUT_LINE ("Clob2 Contents => " || v_buffer);
54
55 END;
56 /
Procedure created.
SQL> exec sp_inmyTable2
Clob1 Contents => ZYXW
Clob2 Contents => ABCD
Clob1 Contents => ABCD
Clob2 Contents => ABCD
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL></source>

== Load a file==



<source lang="sql">
SQL>
SQL> set echo on
SQL>
SQL> create table demo
2 ( id int primary key,
3 theBlob blob
4 )
5 /
Table created.
SQL>
SQL> create or replace directory my_files as "c:\temp\";
Directory created.
SQL>
SQL> create sequence blob_seq;
Sequence created.
SQL>
SQL>
SQL> create or replace procedure load_a_file( p_dir_name in varchar2,p_file_name in varchar2) as
2 l_blob blob;
3 l_bfile bfile;
4 begin
5 insert into demo values ( blob_seq.nextval, empty_blob() )
6 returning theBlob into l_Blob;
7
8 l_bfile := bfilename( p_dir_name, p_file_name );
9 dbms_lob.fileopen( l_bfile );
10
11 dbms_lob.loadfromfile( l_blob, l_bfile,dbms_lob.getlength( l_bfile ) );
12 dbms_lob.fileclose( l_bfile );
13 end;
14 /
Procedure created.
SQL>
SQL> exec load_a_file( "MY_FILES", "clean.sql" );
BEGIN load_a_file( "MY_FILES", "clean.sql" ); END;
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "sqle.LOAD_A_FILE", line 9
ORA-06512: at line 1

SQL>
SQL> drop table demo;
Table dropped.
SQL> drop sequence blob_seq;
Sequence dropped.
SQL></source>

== Print clob data out==



<source lang="sql">
SQL>
SQL> create or replace procedure print_clob( p_clob in clob )
2 as
3 l_offset number default 1;
4 begin
5 loop
6 exit when l_offset > dbms_lob.getlength(p_clob);
7 dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
8 l_offset := l_offset + 255;
9 end loop;
10 end;
11 /
Procedure created.
SQL>
SQL>
SQL></source>

== Show Java source file==



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_java_source (
2 name IN VARCHAR2,
3 schema IN VARCHAR2 := NULL
4 )
5 IS
6 b CLOB;
7 v VARCHAR2 (2000);
8 i INTEGER;
9 BEGIN
10 DBMS_LOB.createtemporary (b, FALSE);
11 dbms_java.export_source (name, NVL (schema, USER), b);
12 i := 1000;
13 DBMS_LOB.read (b, i, 1, v);
14 DBMS_OUTPUT.put_line (v);
15 END;
16 /
Warning: Procedure created with compilation errors.
SQL>
SQL>
SQL></source>

== Use dbms_lob.getlength and dbms_lob.substr with blob type column==



<source lang="sql">
SQL>
SQL> create table demo
2 ( id int primary key,
3 theBlob blob
4 )
5 /
Table created.
SQL>
SQL> select id, dbms_lob.getlength(theBlob) len, clean(theBlob) piece, dbms_lob.substr(theBlob,40,1) raw_data
2 from demo
3 where id =1
4 /
no rows selected
SQL> drop table demo;
Table dropped.</source>

== Use dbms_lob.getlength to get the length of a clob data==



<source lang="sql">
SQL>
SQL> create or replace procedure print_clob( p_clob in clob )
2 as
3 l_offset number default 1;
4 begin
5 loop
6 exit when l_offset > dbms_lob.getlength(p_clob);
7 dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
8 l_offset := l_offset + 255;
9 end loop;
10 end;
11 /
Procedure created.
SQL>
SQL></source>

== Use dbms_lob package to deal with clob data==



<source lang="sql">
SQL>
SQL> create global temporary table myTable
2 ( id int primary key,
3 c_lob clob,
4 b_lob blob
5 )
6 /
Table created.
SQL>
SQL> create sequence myTable_seq;
Sequence created.
SQL>
SQL> create or replace function to_clob( p_cname in varchar2,p_tname in varchar2,p_rowid in rowid ) return clob
2 as
3 l_clob clob;
4 l_id int;
5 begin
6 select myTable_seq.nextval into l_id from dual;
7 execute immediate "insert into myTable (id,c_lob) select :id, to_lob( " || p_cname || " )
8 from " || p_tname || " where rowid = :rid " using IN l_id, IN p_rowid;
9
10 select c_lob into l_clob from myTable where id = l_id ;
11
12 return l_clob;
13 end;
14 /
Function created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop sequence myTable_seq;
Sequence dropped.</source>

== Use dbms_lob.substr to get part of clob data==



<source lang="sql">
SQL>
SQL>
SQL> set echo on
SQL>
SQL>
SQL> create table t ( str varchar2(10), lob clob );
Table created.
SQL>
SQL> insert into t values ( "hello", "hello" );
1 row created.
SQL>
SQL> select substr( str, 3, 2 ), dbms_lob.substr( lob, 3, 2) lob
2 from t
3 /
SU
--
LOB
--------------------------------------------------------------------------------
ll
ell

SQL>
SQL> drop table t;
Table dropped.</source>

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

Oracle PL/SQL Tutorial/System Packages/DBMS LOB - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия