Oracle PL/SQL/System Packages/dbms lob — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 12:55, 26 мая 2010
Содержание
- 1 Call dbms_lob.write to write value to clob type value
- 2 DBMS_LOB.CREATETEMPORARY
- 3 DBMS_LOB.ERASE
- 4 DBMS_LOB.FREETEMPORARY
- 5 DBMS_LOB.ISTEMPORARY
- 6 DBMS_LOB.READ
- 7 DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
- 8 DBMS_LOB.WRITE
- 9 This block demonstrates DBMS_LOB.GETLENGTH.
- 10 This block demonstrates the use of DBMS_LOB.COPY.
- 11 This script tests the DBMS_LOB.LOADFROMFILE procedure
- 12 Use dbms_lob.compare to compare
- 13 Use dbms_lob.compare to compare clob type value with offset
- 14 Use dbms_lob for load clob data from file
- 15 Use dbms_lob.getchunksize to get the clob column size
- 16 Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
- 17 Use dbms_lob.writeappend to append value to clob type value
Call dbms_lob.write to write value to clob type value
<source lang="sql"> 2
SQL> declare
2 l_clob clob := "12345"; 3 begin 4 dbms_lob.write( l_clob, 2, 3, "AB" ); 5 dbms_output.put_line( l_clob ); 6 dbms_lob.write( l_clob, 2, 9, "CD" ); 7 dbms_output.put_line( l_clob ); 8 end; 9 /
12AB5 12AB5 CD PL/SQL procedure successfully completed. SQL> SQL> --
</source>
DBMS_LOB.CREATETEMPORARY
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE temporary_lob_example IS
2 clobVariable CLOB; 3 amount_var INTEGER := 19; 4 offsetPos INTEGER := 1; 5 charVariable VARCHAR2(19) := "AAAA"; 6 BEGIN 7 DBMS_LOB.CREATETEMPORARY(clobVariable, TRUE); 8 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 9 10 IF (DBMS_LOB.ISTEMPORARY(clobVariable) = 1) THEN 11 DBMS_OUTPUT.PUT_LINE("clobVariable is temporary"); 12 END IF; 13 14 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 15 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 16 17 DBMS_LOB.FREETEMPORARY(clobVariable); 18 END temporary_lob_example; 19 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
DBMS_LOB.ERASE
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clobVariable CLOB; 3 charVariable VARCHAR2(50); 4 offsetPos INTEGER := 1; 5 amount_var INTEGER := 50; 6 BEGIN 7 initClob(clobVariable, id_par); 8 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 9 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var); 11 END readClob; 12 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE erase_example IS
2 clobVariable CLOB; 3 offsetPos INTEGER := 2; 4 amount_var INTEGER := 5; 5 BEGIN 6 SELECT clobData INTO clobVariable FROM myTable WHERE id = 1 FOR UPDATE; 7 8 readClob(1); 9 DBMS_LOB.ERASE(clobVariable, amount_var, offsetPos); 10 readClob(1); 11 12 END erase_example; 13 /
Procedure created. SQL> SQL> drop table myTable; Table dropped.
</source>
DBMS_LOB.FREETEMPORARY
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE temporary_lob_example IS
2 clobVariable CLOB; 3 amount_var INTEGER := 19; 4 offsetPos INTEGER := 1; 5 charVariable VARCHAR2(19) := "AAAA"; 6 BEGIN 7 DBMS_LOB.CREATETEMPORARY(clobVariable, TRUE); 8 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 9 10 IF (DBMS_LOB.ISTEMPORARY(clobVariable) = 1) THEN 11 DBMS_OUTPUT.PUT_LINE("clobVariable is temporary"); 12 END IF; 13 14 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 15 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 16 17 DBMS_LOB.FREETEMPORARY(clobVariable); 18 END temporary_lob_example; 19 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
DBMS_LOB.ISTEMPORARY
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE temporary_lob_example IS
2 clobVariable CLOB; 3 amount_var INTEGER := 19; 4 offsetPos INTEGER := 1; 5 charVariable VARCHAR2(19) := "AAAA"; 6 BEGIN 7 DBMS_LOB.CREATETEMPORARY(clobVariable, TRUE); 8 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 9 10 IF (DBMS_LOB.ISTEMPORARY(clobVariable) = 1) THEN 11 DBMS_OUTPUT.PUT_LINE("clobVariable is temporary"); 12 END IF; 13 14 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 15 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 16 17 DBMS_LOB.FREETEMPORARY(clobVariable); 18 END temporary_lob_example; 19 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
DBMS_LOB.READ
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE temporary_lob_example IS
2 clobVariable CLOB; 3 amount_var INTEGER := 19; 4 offsetPos INTEGER := 1; 5 charVariable VARCHAR2(19) := "AAAA"; 6 BEGIN 7 DBMS_LOB.CREATETEMPORARY(clobVariable, TRUE); 8 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 9 10 IF (DBMS_LOB.ISTEMPORARY(clobVariable) = 1) THEN 11 DBMS_OUTPUT.PUT_LINE("clobVariable is temporary"); 12 END IF; 13 14 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 15 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 16 17 DBMS_LOB.FREETEMPORARY(clobVariable); 18 END temporary_lob_example; 19 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 clob_col CLOB, 4 blob_col BLOB);
Table created. SQL> SQL> SQL> column CLOB_Data format a60 SQL> column BLOB_Data format a60 SQL> SELECT key,
2 DBMS_LOB.SUBSTR(clob_col, 50) CLOB_Data, 3 DBMS_LOB.SUBSTR(blob_col, 25) BLOB_Data 4 FROM lobdemo 5 WHERE key IN (0, 100, 101, 102, 103, 1000, 1001, 1002) 6 ORDER BY key;
no rows selected SQL> SQL> drop table lobdemo; Table dropped. SQL>
</source>
DBMS_LOB.WRITE
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE temporary_lob_example IS
2 clobVariable CLOB; 3 amount_var INTEGER := 19; 4 offsetPos INTEGER := 1; 5 charVariable VARCHAR2(19) := "AAAA"; 6 BEGIN 7 DBMS_LOB.CREATETEMPORARY(clobVariable, TRUE); 8 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 9 10 IF (DBMS_LOB.ISTEMPORARY(clobVariable) = 1) THEN 11 DBMS_OUTPUT.PUT_LINE("clobVariable is temporary"); 12 END IF; 13 14 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 15 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 16 17 DBMS_LOB.FREETEMPORARY(clobVariable); 18 END temporary_lob_example; 19 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
This block demonstrates DBMS_LOB.GETLENGTH.
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 clob_col CLOB, 4 blob_col BLOB);
Table created. SQL> SQL> SQL> set serveroutput on format wrapped SQL> DECLARE
2 CURSOR c_LOBQuery IS 3 SELECT key, clob_col, blob_col 4 FROM lobdemo 5 WHERE key IN (0, 100, 101, 102, 103, 1000, 1001, 1002) 6 ORDER BY key; 7 BEGIN 8 FOR v_Rec IN c_LOBQuery LOOP 9 DBMS_OUTPUT.PUT_LINE("Key: " || v_Rec.key); 10 DBMS_OUTPUT.PUT_LINE(" Length of CLOB: " || 11 DBMS_LOB.GETLENGTH(v_Rec.clob_col)); 12 DBMS_OUTPUT.PUT_LINE(" Length of BLOB: " || 13 DBMS_LOB.GETLENGTH(v_Rec.blob_col)); 14 END LOOP; 15 END; 16 /
PL/SQL procedure successfully completed. SQL> SQL> drop table lobdemo; Table dropped. SQL> SQL>
</source>
This block demonstrates the use of DBMS_LOB.COPY.
<source lang="sql">
SQL> SQL> set serveroutput on SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 clob_col CLOB, 4 blob_col BLOB);
Table created. SQL> SQL> SQL> DECLARE
2 v_Row100 lobdemo%ROWTYPE; 3 v_Row101 lobdemo%ROWTYPE; 4 BEGIN 5 SELECT * 6 INTO v_Row100 7 FROM lobdemo 8 WHERE key = 100; 9 SELECT * 10 INTO v_Row101 11 FROM lobdemo 12 WHERE key = 101 13 FOR UPDATE; 14 15 DBMS_OUTPUT.PUT_LINE("Initial values of row 101:"); 16 LOBUtils.Print(v_Row101.clob_col); 17 LOBUtils.Print(v_Row101.blob_col); 18 19 DBMS_LOB.COPY(v_Row101.clob_col, v_Row100.clob_col, 26, 50, 1); 20 21 DBMS_LOB.COPY(v_Row101.blob_col, v_Row100.blob_col, 5, 2, 1); 22 23 DBMS_OUTPUT.PUT_LINE("After copy, values of row 101:"); 24 LOBUtils.Print(v_Row101.clob_col); 25 LOBUtils.Print(v_Row101.blob_col); 26 END; 27 /
DECLARE
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 5
SQL> SQL> drop table lobdemo; Table dropped. SQL>
</source>
This script tests the DBMS_LOB.LOADFROMFILE procedure
<source lang="sql">
SQL> SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY, 3 isbn CHAR(10 CHAR), 4 description CLOB, 5 nls_description NCLOB, 6 misc BLOB, 7 chapter_title VARCHAR2(30 CHAR), 8 bfile_description BFILE 9 );
Table created. SQL> SQL> SQL> INSERT INTO book (id,isbn,description,nls_description,misc,bfile_description)VALUES (1,"3", EMPTY_CLOB(),EMPTY_CLOB(),EMPTY_BLOB(),BFILENAME("book_LOC", "b.pdf")); 1 row created. SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 v_dest_blob BLOB; 3 v_dest_clob CLOB; 4 v_source_locator1 BFILE := BFILENAME("book_LOC", "bfile_example.pdf"); 5 v_source_locator2 BFILE := BFILENAME("book_LOC", "bfile_example.txt"); 6 7 BEGIN 8 9 UPDATE book SET description = EMPTY_CLOB(),misc = EMPTY_BLOB(); 10 11 SELECT description, misc INTO v_dest_clob, v_dest_blob FROM book WHERE id = 1 FOR UPDATE; 12 13 14 DBMS_LOB.OPEN(v_source_locator1, DBMS_LOB.LOB_READONLY); 15 DBMS_LOB.OPEN(v_source_locator2, DBMS_LOB.LOB_READONLY); 16 DBMS_LOB.OPEN(v_dest_blob, DBMS_LOB.LOB_READWRITE); 17 DBMS_LOB.OPEN(v_dest_clob, DBMS_LOB.LOB_READWRITE); 18 19 DBMS_OUTPUT.PUT_LINE("Length of the BLOB file is: "||DBMS_LOB.GETLENGTH(v_source_locator1)); 20 DBMS_OUTPUT.PUT_LINE("Length of the CLOB file is: "||DBMS_LOB.GETLENGTH(v_source_locator2)); 21 DBMS_OUTPUT.PUT_LINE("Size of BLOB pre-load: "||DBMS_LOB.GETLENGTH(v_dest_blob)); 22 DBMS_OUTPUT.PUT_LINE("Size of CLOB pre-load: "||DBMS_LOB.GETLENGTH(v_dest_clob)); 23 24 25 26 DBMS_LOB.CLOSE(v_source_locator1); 27 DBMS_LOB.CLOSE(v_source_locator2); 28 DBMS_LOB.CLOSE(v_dest_blob); 29 DBMS_LOB.CLOSE(v_dest_clob); 30 31 EXCEPTION 32 WHEN OTHERS 33 THEN 34 DBMS_OUTPUT.PUT_LINE(SQLERRM); 35 36 DBMS_LOB.CLOSE(v_source_locator1); 37 DBMS_LOB.CLOSE(v_source_locator2); 38 DBMS_LOB.CLOSE(v_dest_blob); 39 DBMS_LOB.CLOSE(v_dest_clob); 40 41 END; 42 /
SQL> SQL> PROMPT ** SELECT of the description column SQL> SQL> SET LONG 64000 SQL> SELECT description
2 FROM book 3 WHERE id = 1;
DESCRIPTION
1 row selected. SQL> SQL> SQL> drop table book; Table dropped. SQL> SQL>
</source>
Use dbms_lob.compare to compare
<source lang="sql">
SQL> SQL> declare
2 c1 clob := "chris"; 3 c2 clob := "sean"; 4 begin 5 dbms_output.put_line(dbms_lob.rupare( c1, c2 ) ); 6 end; 7 /
-1 PL/SQL procedure successfully completed. SQL> --
</source>
Use dbms_lob.compare to compare clob type value with offset
<source lang="sql">
SQL> SQL> declare
2 c1 clob := "chris"; 3 c2 clob := "sean"; 4 begin 5 dbms_output.put_line(dbms_lob.rupare( c1, c2, 1, 5, 1 ) ); 6 end; 7 /
0 PL/SQL procedure successfully completed. SQL> --
</source>
Use dbms_lob for load clob data from file
<source lang="sql">
SQL> SQL> SQL> create or replace directory dir1 as "c:\temp\"; Directory created. SQL> SQL> create or replace directory "dir2" as "c:\temp\"; Directory created. SQL> SQL> create table demo
2 ( id int primary key, 3 theClob clob 4 ) 5 /
Table created. SQL> SQL> declare
2 l_clob clob; 3 l_bfile bfile; 4 begin 5 insert into demo values ( 1, empty_clob() ) 6 returning theclob into l_clob; 7 8 l_bfile := bfilename( "DIR1", "test.txt" ); 9 dbms_lob.fileopen( l_bfile ); 10 11 dbms_lob.loadfromfile( l_clob, l_bfile,dbms_lob.getlength( l_bfile ) ); 12 13 dbms_lob.fileclose( l_bfile ); 14 end; 15 /
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 9
SQL> SQL> select dbms_lob.getlength(theClob), theClob from demo
2 /
no rows selected SQL> SQL> SQL> drop table demo; Table dropped. SQL> SQL> SQL> SQL> --
</source>
Use dbms_lob.getchunksize to get the clob column size
<source lang="sql">
SQL> SQL> SQL> create table my_lob(
2 id number, 3 c clob ) 4 /
Table created. SQL> insert into my_lob values ( 1, empty_clob() ); 1 row created. SQL> SQL> SQL> select dbms_lob.getchunksize( c ) from my_lob; DBMS_LOB.GETCHUNKSIZE(C)
8132
1 row selected. SQL> SQL> drop table my_lob; Table dropped. SQL> --
</source>
Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
<source lang="sql">
SQL> CREATE TABLE nmyTable (
2 id INTEGER PRIMARY KEY, 3 nclobData NCLOB 4 );
Table created. SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB 4 );
Table created. SQL> SQL> CREATE OR REPLACE TRIGGER before_myTable_update
2 BEFORE UPDATE 3 ON myTable 4 FOR EACH ROW 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE("myTable changed"); 7 DBMS_OUTPUT.PUT_LINE("Length = " || DBMS_LOB.GETLENGTH(:new.clobData)); 8 END before_myTable_update; 9 /
Trigger created. SQL> SQL> drop table myTable; Table dropped. SQL> drop table nmyTable; Table dropped.
</source>
Use dbms_lob.writeappend to append value to clob type value
<source lang="sql">
SQL> SQL> declare
2 l_clob clob := "12345"; 3 begin 4 dbms_lob.writeappend( l_clob, 5, "ABCDEFGHIJK" ); 5 dbms_output.put_line( l_clob ); 6 end; 7 /
12345ABCDE PL/SQL procedure successfully completed. SQL> --
</source>