Oracle PL/SQL/System Packages/dbms lob
Содержание
- 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
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> --
DBMS_LOB.CREATETEMPORARY
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.
DBMS_LOB.ERASE
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.
DBMS_LOB.FREETEMPORARY
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.
DBMS_LOB.ISTEMPORARY
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.
DBMS_LOB.READ
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.
DBMS_LOB.SUBSTR: Select the first 50 characters of clob_col, and the first 25 bytes of blob_col, for each row.
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>
DBMS_LOB.WRITE
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.
This block demonstrates DBMS_LOB.GETLENGTH.
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>
This block demonstrates the use of DBMS_LOB.COPY.
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>
This script tests the DBMS_LOB.LOADFROMFILE procedure
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>
Use dbms_lob.compare to compare
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> --
Use dbms_lob.compare to compare clob type value with offset
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> --
Use dbms_lob for load clob data from file
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> --
Use dbms_lob.getchunksize to get the clob column size
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> --
Use DBMS_LOB.GETLENGTH to get the length of a clob type variable
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.
Use dbms_lob.writeappend to append value to clob type value
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> --