Oracle PL/SQL/Data Type/Clob
Содержание
- 1 Append data to clob
- 2 Appending and Writing to LOBs
- 3 Assign string value to clob type variable
- 4 clob data type
- 5 Clob type column
- 6 clob type data as string
- 7 Compare clob data
- 8 Compare two clobs
- 9 Copy clob data
- 10 Copying Internal LOBs
- 11 Creating an Internal LOB Table
- 12 DBMS_LOB package.
- 13 erase clob data
- 14 In() function with clob data
- 15 Initialize clob
- 16 Print out the length of the CLOBs previously INSERTed
- 17 Read clob type data, DBMS_LOB.READ
- 18 Use between ... and with clob data
- 19 Use clob to store xml data
- 20 Use clob type in PL/SQL
- 21 Use dbms_lob.erase to remove value from clob type value
- 22 Use dbms_lob.fileopen to open bfile
- 23 use like operator with clob type data
Append data to clob
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>
SQL> CREATE OR REPLACE PROCEDURE append_example IS
2 clobSrc CLOB;
3 clobDest CLOB;
4 BEGIN
5 SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
6 SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
7
8 readClob(1);
9 DBMS_LOB.APPEND(clobDest, clobSrc);
10 readClob(1);
11
12 END append_example;
13 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
Appending and Writing to LOBs
SQL>
SQL>
SQL> CREATE TABLE LOBS(
2 lob_index INTEGER,
3 CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"Teach Yourself Oracle8i in 21 Days");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"Oracle Data Warehousing Unleashed");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL> DECLARE
2 Source_Lob CLOB;
3 Dest_Lob CLOB;
4 Write_Amount INTEGER := 10;
5 Writing_Position INTEGER ;
6 Buffer VARCHAR2(10) := "Added Text";
7 BEGIN
8 SELECT CLOB_LOCATOR into Dest_LOB
9 FROM LOBS
10 WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update
11 SELECT CLOB_LOCATOR into Source_LOB
12 FROM LOBS
13 WHERE LOB_INDEX = 1;
14 DBMS_LOB.APPEND(Dest_LOB, Source_LOB);
15 COMMIT;
16 SELECT CLOB_LOCATOR into Source_LOB
17 FROM LOBS
18 WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update
19
20 Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1;
21 DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer);
22 COMMIT;
23 END;
24 /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL> --
Assign string value to clob type variable
SQL>
SQL>
SQL> declare
2 v1 clob := "abc";
3 begin
4 If v1 = "abc" then null; end if;
5
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
clob data type
SQL> create table MyTest(
2 ch clob
3 );
Table created.
SQL>
SQL> insert into myTest values("A simple line of text");
1 row created.
SQL> insert into myTest values("Line 1; \n line 2");
1 row created.
SQL>
SQL> select * from mytest;
CH
--------------------------------------------------------------------------------
A simple line of text
Line 1; \n line 2
SQL>
SQL>
SQL> drop table myTest;
Table dropped.
Clob type column
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
clob type data as string
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 PROCEDURE nclob_example
2 AS
3 clobVariable CLOB := "It is the east and Juliet is the sun";
4 nclobVariable NCLOB;
5 BEGIN
6 INSERT INTO nmyTable (id, nclobData) VALUES (1, clobVariable);
7
8 SELECT nclobData INTO clobVariable FROM nmyTable WHERE id = 1;
9
10 DBMS_OUTPUT.PUT_LINE("clobVariable = " || clobVariable);
11 END nclob_example;
12 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table nmyTable;
Table dropped.
Compare clob data
SQL>
SQL> declare
2 v1 clob := "abc";
3 begin
4
5 If v1 >= "abc" then null; end if;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
Compare two clobs
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clobData CLOB NOT NULL
4 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE compare_example IS
2 clobVariable1 CLOB;
3 clobVariable2 CLOB;
4 return_var INTEGER;
5 BEGIN
6 SELECT clobData INTO clobVariable1 FROM myTable WHERE id = 1;
7 SELECT clobData INTO clobVariable2 FROM myTable WHERE id = 2;
8
9 return_var := DBMS_LOB.ruPARE(clobVariable1, clobVariable2);
10 DBMS_OUTPUT.PUT_LINE("return_var = " || return_var);
11
12 return_var := DBMS_LOB.ruPARE(clobVariable1, clobVariable1);
13 DBMS_OUTPUT.PUT_LINE("return_var = " || return_var);
14 END compare_example;
15 /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
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.
Copying Internal LOBs
SQL>
SQL> CREATE TABLE LOBS(
2 lob_index INTEGER,
3 CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"Teach Yourself Oracle8i in 21 Days");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"Oracle Data Warehousing Unleashed");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL> DECLARE
2 Source_Lob CLOB;
3 Dest_Lob CLOB;
4 Copy_Amount INTEGER;
5 BEGIN
6 SELECT CLOB_LOCATOR into Dest_LOB
7 FROM LOBS
8 WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW
9 SELECT CLOB_LOCATOR into Source_LOB
10 FROM LOBS
11 WHERE LOB_INDEX = 1;
12 Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
13 DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
14 COMMIT;
15 SELECT CLOB_LOCATOR into Dest_LOB
16 FROM LOBS
17 WHERE LOB_INDEX = 6 FOR UPDATE;
18 SELECT CLOB_LOCATOR into Source_LOB
19 FROM LOBS
20 WHERE LOB_INDEX = 2;
21 Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
22 DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
23 COMMIT;
24 END;
25 /
PL/SQL procedure successfully completed.
SQL> drop table lobs;
Table dropped.
SQL>
SQL> --
Creating an Internal LOB Table
SQL> --
SQL>
SQL>
SQL> CREATE TABLE LOBS(
2 lob_index INTEGER,
3 CLOB_Locator CLOB);
Table created.
SQL>
SQL>
SQL> INSERT INTO LOBS VALUES(1,"T");
1 row created.
SQL> INSERT INTO LOBS VALUES(2,"O");
1 row created.
SQL> INSERT INTO LOBS VALUES(3,"");
1 row created.
SQL> INSERT INTO LOBS VALUES(4,"Oracle Unleashed 2E");
1 row created.
SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());
1 row created.
SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());
1 row created.
SQL>
SQL>
SQL> drop table lobs;
Table dropped.
SQL>
SQL> --
DBMS_LOB package.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE ReverseLOB(
2 p_InputLocator IN CLOB,
3 p_OutputLocator IN OUT CLOB,
4 p_ChunkSize IN NUMBER) AS
5
6 v_InputOffset BINARY_INTEGER;
7 v_OutputOffset BINARY_INTEGER;
8 v_LOBLength BINARY_INTEGER;
9 v_CurrentChunkSize BINARY_INTEGER;
10 e_TrimLength EXCEPTION;
11 PRAGMA EXCEPTION_INIT(e_TrimLength, -22926);
12
13 BEGIN
14 v_LOBLength := DBMS_LOB.GETLENGTH(p_InputLocator);
15
16 BEGIN
17 DBMS_LOB.TRIM(p_OutputLocator, v_LOBLength);
18 EXCEPTION
19 WHEN e_TrimLength THEN
20 NULL;
21 END;
22
23 v_InputOffset := 1;
24 v_OutputOffset := v_LOBLength + 1;
25
26 LOOP
27 EXIT WHEN v_InputOffset > v_LOBLength;
28
29 IF (v_LOBLength - v_InputOffset + 1) > p_ChunkSize THEN
30 v_CurrentChunkSize := p_ChunkSize;
31 ELSE
32 v_CurrentChunkSize := v_LOBLength - v_InputOffset + 1;
33 END IF;
34
35 v_OutputOffset := v_OutputOffset - v_CurrentChunkSize;
36
37 DBMS_LOB.COPY(p_OutputLocator,
38 p_InputLocator,
39 v_CurrentChunkSize,
40 v_OutputOffset,
41 v_InputOffset);
42
43 v_InputOffset := v_InputOffset + v_CurrentChunkSize;
44 END LOOP;
45 END ReverseLOB;
46 /
Procedure created.
SQL>
SQL>
SQL> CREATE TABLE lobdemo (
2 key NUMBER,
3 clob_col CLOB,
4 blob_col BLOB);
Table created.
SQL>
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (1, "abcdefghijklmnopqrstuvwxyz");
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (2, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (3, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (4, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (5, EMPTY_CLOB());
1 row created.
SQL> INSERT INTO lobdemo (key, clob_col)
2 VALUES (6, EMPTY_CLOB());
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT key, clob_col
2 FROM lobdemo
3 WHERE key BETWEEN 1 AND 6
4 ORDER BY key;
KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
1
abcdefghijklmnopqrstuvwxyz
2
3
KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
4
5
6
6 rows selected.
SQL>
SQL> DECLARE
2 v_Source CLOB;
3 v_Destination CLOB;
4 v_Key NUMBER;
5 CURSOR c_Destinations IS
6 SELECT key, clob_col
7 FROM lobdemo
8 WHERE key BETWEEN 2 and 6
9 FOR UPDATE;
10 BEGIN
11 SELECT clob_col
12 INTO v_Source
13 FROM lobdemo
14 WHERE key = 1;
15
16 OPEN c_Destinations;
17 LOOP
18 FETCH c_Destinations INTO v_Key, v_Destination;
19 EXIT WHEN c_Destinations%NOTFOUND;
20
21 IF (v_Key = 2) THEN
22 ReverseLOB(v_Source, v_Destination, 4);
23 ELSIF (v_Key = 3) THEN
24 ReverseLOB(v_Source, v_Destination, 2);
25 ELSIF (v_Key = 4) THEN
26 ReverseLOB(v_Source, v_Destination, 1);
27 ELSIF (v_Key = 5) THEN
28 ReverseLOB(v_Source, v_Destination, 10);
29 ELSIF (v_Key = 6) THEN
30 ReverseLOB(v_Source, v_Destination, 30);
31 END IF;
32 END LOOP;
33 CLOSE c_Destinations;
34 COMMIT;
35 END;
36 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT key, clob_col
2 FROM lobdemo
3 WHERE key BETWEEN 1 AND 6
4 ORDER BY key;
KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
1
abcdefghijklmnopqrstuvwxyz
2
yzuvwxqrstmnopijklefghabcd
3
yzwxuvstqropmnklijghefcdab
KEY
----------
CLOB_COL
--------------------------------------------------------------------------------
4
zyxwvutsrqponmlkjihgfedcba
5
uvwxyzklmnopqrstabcdefghij
6
abcdefghijklmnopqrstuvwxyz
6 rows selected.
SQL>
SQL> DROP TABLE lobdemo;
Table dropped.
SQL>
SQL>
erase 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 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.
In() function with clob data
SQL> declare
2 v1 clob := "abc";
3 begin
4
5 If "abc" IN (v1) then null; end if;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
Initialize clob
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL> -- create the PL/SQL procedures and methods
SQL> CREATE OR REPLACE PROCEDURE myProcedure(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 myProcedure;
5 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Print out the length of the CLOBs previously INSERTed
SQL>
SQL> CREATE TABLE lobdemo (
2 key NUMBER,
3 clob_col CLOB,
4 blob_col BLOB);
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_CLOB CLOB;
3 v_Key lobdemo.key%TYPE;
4 CURSOR c_SomeLOBs IS
5 SELECT key, clob_col
6 FROM lobdemo
7 WHERE key BETWEEN 50 and 61;
8 BEGIN
9 OPEN c_SomeLOBs;
10 LOOP
11 FETCH c_SomeLOBs INTO v_Key, v_CLOB;
12 EXIT WHEN c_SomeLOBs%NOTFOUND;
13
14 DBMS_OUTPUT.PUT_LINE(
15 "Length of CLOB at key " || v_Key || " is: " ||
16 DBMS_LOB.GETLENGTH(v_CLOB));
17 END LOOP;
18 CLOSE c_SomeLOBs;
19 END;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lobdemo;
Table dropped.
SQL>
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.
Use between ... and with clob data
SQL> declare
2 v1 clob := "abc";
3 begin
4
5 If v1 between "a" and "z" then null; end if;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
Use clob to store xml data
SQL> CREATE TABLE myTable
2 (myID NUMBER PRIMARY KEY,
3 myValue XMLTYPE )
4 XMLTYPE myValue STORE AS CLOB
5 /
Table created.
SQL>
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Use clob type in PL/SQL
SQL>
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> --
Use dbms_lob.erase to remove value from clob type value
SQL>
SQL>
SQL> declare
2 l_c clob := "Hello World!";
3 l_a number := 9;
4 begin
5 dbms_lob.erase( l_c, l_a, 6 );
6 dbms_output.put_line( "The clob now = *" || l_c || "*" );
7 dbms_output.put_line( "The amount that was erased was: " || l_a );
8 end;
9 /
The clob now = *Hello *
The amount that was erased was: 7
PL/SQL procedure successfully completed.
SQL> --
Use dbms_lob.fileopen to open bfile
SQL>
SQL> create table bfile_table(
2 name varchar2(255),
3 the_file bfile );
Table created.
SQL> insert into bfile_table values ( "doc 1", bfilename( "WROX_DIR", "my_doc.pdf" ) );
1 row created.
SQL>
SQL> declare
2 l_bfile bfile;
3 l_dir_alias varchar2(2000);
4 l_filename varchar2(2000);
5 begin
6 select the_file
7 into l_bfile
8 from bfile_table
9 where name = "doc 1";
10 dbms_lob.fileopen( l_bfile, dbms_lob.file_readonly );
11 if dbms_lob.fileexists( l_bfile ) = 1 then
12 dbms_lob.filegetname( l_bfile, l_dir_alias, l_filename );
13 dbms_output.put_line( "Directory alias: " || l_dir_alias );
14 dbms_output.put_line( "Filename: " || l_filename );
15 end if;
16 if dbms_lob.fileisopen( l_bfile ) = 1 then
17 dbms_lob.fileclose( l_bfile );
18 end if;
19 end;
20 /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at line 10
SQL>
SQL> drop table bfile_table;
Table dropped.
SQL>
SQL>
SQL> --
use like operator with clob type data
SQL> declare
2 v1 clob := "abc";
3 begin
4
5 If v1 like "a%" then null; end if;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>