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
<source lang="sql">
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></source>
close Clob data pointer
<source lang="sql">
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.</source>
Compare date value after to_char() and trim()
<source lang="sql">
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.</source>
Convert string to clob
<source lang="sql">
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></source>
Copy clob data
<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 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.</source>
Creating Tables Containing CLOB Objects
<source lang="sql">
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></source>
Initialize CLOB column
<source lang="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> select * from mytable;
ID CLOB_COLUMN
-----------
1
SQL> SQL> drop table myTable; Table dropped. SQL></source>
Insert into clob column
<source lang="sql">
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></source>
Loading data to the CLOB by using BFILE
CLOBs are very useful structures. You can store lots of text information in a CLOB.
<source lang="sql">
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.</source>
Obtain Clob data pointer
<source lang="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 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.</source>
Open the CLOB
<source lang="sql">
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.</source>
Performing basic string operations on CLOBs
<source lang="sql">
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</source>
Read clob data to varchar2 type variable
<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 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.</source>
Read clob type data, DBMS_LOB.READ
<source lang="sql">
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.</source>
Reading and Writing to a CLOB
<source lang="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 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></source>
Update clob data
<source lang="sql">
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.</source>
