Oracle PL/SQL/System Packages/dbms lob — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 09:55, 26 мая 2010

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> --