Oracle PL/SQL Tutorial/System Packages/DBMS LOB
Содержание
- 1 DBMS_LOB Package
- 2 DBMS_LOB.READ
- 3 Load a file
- 4 Print clob data out
- 5 Show Java source file
- 6 Use dbms_lob.getlength and dbms_lob.substr with blob type column
- 7 Use dbms_lob.getlength to get the length of a clob data
- 8 Use dbms_lob package to deal with clob data
- 9 Use dbms_lob.substr to get part of clob data
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>