Oracle PL/SQL/System Packages/dbms lob

Материал из SQL эксперт
Версия от 12:55, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Packages/dbms_lob&oldid=1249»