Oracle PL/SQL Tutorial/Large Objects/CLOB
Содержание
- 1 Adding Content to a CLOB
- 2 close Clob data pointer
- 3 Compare date value after to_char() and trim()
- 4 Convert string to clob
- 5 Copy clob data
- 6 Creating Tables Containing CLOB Objects
- 7 Initialize CLOB column
- 8 Insert into clob column
- 9 Loading data to the CLOB by using BFILE
- 10 Obtain Clob data pointer
- 11 Open the CLOB
- 12 Performing basic string operations on CLOBs
- 13 Read clob data to varchar2 type variable
- 14 Read clob type data, DBMS_LOB.READ
- 15 Reading and Writing to a CLOB
- 16 Update clob data
Adding Content to a CLOB
SQL>
SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB());
1 row created.
SQL>
SQL>
SQL> UPDATE myTable
2 SET clob_column = "AAAAA"
3 WHERE id = 1;
1 row updated.
SQL>
SQL> select * from myTable;
ID CLOB_COLUMN
---------- --------------------------------------------------------------------------------
1 AAAAA
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>
close Clob data pointer
SQL>
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
2
3 clob_pointer CLOB;
4
5 v_buf VARCHAR2(1000);
6
7 Amount BINARY_INTEGER :=1000;
8
9 Position INTEGER :=1;
10 BEGIN
11 v_buf :=rpad("A",1000,"A");
12
13 insert into myClob values (1,EMPTY_CLOB());
14
15 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE;
16
17 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
18
19 FOR i IN 1..500 LOOP
20
21 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
22
23 Position :=Position +Amount;
24
25 END LOOP;
26
27 DBMS_LOB.CLOSE (clob_pointer);
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.
Compare date value after to_char() and trim()
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL>
SQL> select gift_id, register_date
2 from gift
3 where trim(to_char(register_date, "Month")) = "February";
GIFT_ID REGISTER_
---------- ---------
1 14-FEB-99
2 14-FEB-99
3 14-FEB-99
4 14-FEB-99
4 rows selected.
SQL>
SQL>
SQL> drop table gift;
Table dropped.
Convert string to clob
SQL>
SQL> declare
2
3 c1 clob := to_clob("abc");
4 c2 clob;
5
6 begin
7 case c1
8 when to_clob("abc") then dbms_output.put_line("abc");
9 when to_clob("def") then dbms_output.put_line("def");
10 end case;
11
12 c2 := case c1
13 when to_clob("abc") then "abc"
14 when to_clob("def") then "def"
15 end;
16
17 dbms_output.put_line(c2);
18
19 end;
20 /
abc
abc
PL/SQL procedure successfully completed.
SQL>
Copy clob data
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clobData CLOB NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN
3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par;
4 END initClob;
5 /
Procedure 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> CREATE OR REPLACE PROCEDURE copy_example IS
2 clobSrc CLOB;
3 clobDest CLOB;
4 src_offsetPos INTEGER := 1;
5 dest_offsetPos INTEGER := 7;
6 amount_var INTEGER := 5;
7 BEGIN
8 SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
9 SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
10
11 readClob(1);
12 DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos);
13 readClob(1);
14
15 ROLLBACK;
16 END copy_example;
17 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Creating Tables Containing CLOB Objects
SQL>
SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL> desc myTable;
Name Null? Type
---------------
ID NOT NULL NUMBER(38)
CLOB_COLUMN NOT NULL CLOB
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Initialize CLOB column
SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB());
1 row created.
SQL>
SQL> select * from mytable;
ID CLOB_COLUMN
---------- -----------
1
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Insert into clob column
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO myClob VALUES (102,RPAD("AAAAAAAAAAAAAAAAAA",40000,"A"));
1 row created.
SQL>
SQL>
SQL>
SQL> drop table myClob;
Table dropped.
SQL>
Loading data to the CLOB by using BFILE
CLOBs are very useful structures. You can store lots of text information in a CLOB.
SQL>
SQL> create table catalog
2 (id number,
3 name VARCHAR2(2000),
4 manual_cl CLOB,
5 firstpage_bl BLOB,
6 mastertxt_bf BFILE
7 );
Table created.
SQL>
SQL>
SQL> create directory IO as "C:\IO";
SQL> --grant read, write on directory IO to public;
SQL>
SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm"));
1 row created.
SQL>
SQL>
SQL> declare
2 v_file_bf BFILE;
3 v_manual_cl CLOB;
4 lang_ctx NUMBER := DBMS_LOB.default_lang_ctx;
5 charset_id NUMBER := 0;
6 src_offset NUMBER := 1;
7 dst_offset NUMBER := 1;
8 warning NUMBER;
9 begin
10 update catalog set manual_cl = EMPTY_CLOB() where id = 1;
11
12 select mastertxt_bf, manual_cl into v_file_bf, v_manual_cl from catalog where id = 1;
13
14 DBMS_LOB.fileopen(v_file_bf, DBMS_LOB.file_readonly);
15 DBMS_LOB.loadclobfromfile (v_manual_cl, v_file_bf,DBMS_LOB.getlength (v_file_bf),
16 src_offset, dst_offset,charset_id, lang_ctx,warning);
17 DBMS_LOB.fileclose (v_file_bf);
18 end;
19 /
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 14
SQL>
SQL> drop directory IO;
Directory dropped.
SQL>
SQL> drop table catalog;
Table dropped.
Obtain Clob data pointer
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
2 clob_pointer CLOB;
3 v_buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;
5 Position INTEGER :=1;
6 BEGIN
7 v_buf :=rpad("A",1000,"A");
8
9 insert into myClob values (5,EMPTY_CLOB());
10
11 SELECT clob_data INTO clob_pointer FROM myClob WHERE id =5 FOR UPDATE;
12
13 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
14
15 FOR i IN 1..500 LOOP
16
17 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
18
19 Position :=Position +Amount;
20
21 END LOOP;
22
23 DBMS_LOB.CLOSE (clob_pointer);
24 END;
25 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.
Open the CLOB
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> DECLARE
2 clob_pointer CLOB;
3 v_Buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;
5 Position INTEGER :=1;
6 fp UTL_FILE.FILE_TYPE;
7 BEGIN
8
9 insert into myClob values (1,EMPTY_CLOB());
10
11 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1;
12
13 if (DBMS_LOB.ISOPEN(clob_pointer)!=1) then
14
15 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
16
17 end if;
18
19 fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
20
21 LOOP
22 BEGIN
23
24 DBMS_LOB.READ (clob_pointer, Amount, Position, v_Buf);
25
26 UTL_FILE.PUT_LINE(fp, v_Buf, TRUE);
27
28 Position :=Position + Amount;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 EXIT;
32 END;
33 END LOOP;
34
35 UTL_FILE.FCLOSE(fp);
36
37 DBMS_LOB.CLOSE (clob_pointer);
38 END;
39 /
SQL>
SQL> drop table myClob;
Table dropped.
Performing basic string operations on CLOBs
SQL>
SQL> create table catalog
2 (
3 id number,
4 name VARCHAR2(2000),
5 manual_cl CLOB,
6 firstpage_bl BLOB,
7 mastertxt_bf BFILE
8 );
Table created.
SQL>
SQL>
SQL> create directory IO as "C:\IO";
Directory created.
SQL> --grant read, write on directory IO to public;
SQL>
SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm"));
1 row created.
SQL>
SQL>
SQL> declare
2 v_manual_cl CLOB;
3 v_nr NUMBER;
4 v_tx VARCHAR2 (2000);
5 v_add_tx VARCHAR2 (2000):="Loaded: "||TO_CHAR(SYSDATE,"mm/dd/yyyy hh24:mi");
6 begin
7 select manual_cl into v_manual_cl from catalog where id = 1
8 for update;
9
10 DBMS_LOB.writeappend (v_manual_cl,LENGTH (v_add_tx), v_add_tx);
11
12 v_nr := INSTR (v_manual_cl, "Loaded:", -1);
13 v_tx := SUBSTR (v_manual_cl, v_nr);
14 DBMS_OUTPUT.put_line (v_tx);
15 end;
16
17 drop table catalog;
18
19 drop directory IO;
20
21 --Keep in mind that LOB pointers are transaction dependent. This means that if you have a COMMIT command in your code, the LOB pointer could become invalid (not pointing to anything) and you may not be able to perform some operations by using that locator.
22
Read clob data to varchar2 type variable
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clobData CLOB NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN
3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par;
4 END initClob;
5 /
Procedure 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 write_example(id_par IN INTEGER) IS
2 clobVariable CLOB;
3 charVariable VARCHAR2(10) := "pretty";
4 offsetPos INTEGER := 7;
5 amount_var INTEGER := 6;
6 BEGIN
7 SELECT clobData INTO clobVariable FROM myTable WHERE id = id_par FOR UPDATE;
8
9 readClob(1);
10 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable);
11 readClob(1);
12
13 END write_example;
14 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Read clob type data, DBMS_LOB.READ
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN
3 SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
4 END initClob;
5 /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clob_var CLOB;
3 char_buffer_var VARCHAR2(50);
4 offset INTEGER := 1;
5 amount_var INTEGER := 50;
6 BEGIN
7 initClob(clob_var, id_par);
8 DBMS_LOB.READ(clob_var, amount_var, offset, char_buffer_var);
9 DBMS_OUTPUT.PUT_LINE("char_buffer_var = " || char_buffer_var);
10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
11 END readClob;
12 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
Reading and Writing to a CLOB
SQL>
SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY,
3 clob_data CLOB);
Table created.
SQL>
SQL> INSERT INTO myClob VALUES (101,null);
1 row created.
SQL>
SQL> declare
2 clob_pointer CLOB;
3 v_buf VARCHAR2(1000);
4 Amount BINARY_INTEGER :=1000;
5 Position INTEGER :=1;
6 BEGIN
7 v_buf :=rpad("A",1000,"A");
8
9 insert into myClob values (1 ,EMPTY_CLOB());
10
11 commit;
12
13 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE;
14 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE);
15
16 FOR i IN 1..500 LOOP
17
18 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf);
19
20 Position :=Position +Amount;
21
22 END LOOP;
23
24 DBMS_LOB.CLOSE (clob_pointer);
25
26 END;
27 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myClob;
Table dropped.
SQL>
SQL>
Update clob data
SQL>
SQL> CREATE TABLE myClobTable (id NUMBER,clob_data CLOB);
Table created.
SQL>
SQL> CREATE TABLE myLongTable (id NUMBER,long_data LONG);
Table created.
SQL>
SQL> INSERT INTO myLongTable VALUES (100,RPAD("A",200000,"A"));
1 row created.
SQL>
SQL>
SQL> update myClobTable set clob_data =(select to_lob(long_data)from
2 myLongTable where id =100)
3
SQL> drop table myLongTable;
Table dropped.
SQL>
SQL>
SQL> drop table myClobTable;
Table dropped.