в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:59Z

1 версия

Новая страница

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

<p>CLOBs are very useful structures. You can store lots of text information in a CLOB.</p>



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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Large Objects/CLOB - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия