Demonstrations of SQL functions being applied to CLOB values : CLOB « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CLOB

SQL>
SQL> CREATE TABLE facebook (
  2     name VARCHAR2(80),
  3     photo BLOB,
  4     directions CLOB,
  5     description NCLOB,
  6     web_page BFILE);

Table created.

SQL>
SQL> CREATE DIRECTORY bfile_data AS 'c:\xxx';

Directory created.

SQL>
SQL> DECLARE
  2     name CLOB;
  3     name_upper CLOB;
  4     directions CLOB;
  5     blank_space VARCHAR2(1) := ' ';
  6  BEGIN
  7     SELECT name, SUBSTR(directions,1,500)
  8     INTO name, directions
  9     FROM facebook
 10     WHERE name = 'Falls';
 11
 12     name_upper := UPPER(name);
 13
 14     IF name = name_upper THEN
 15        DBMS_OUTPUT.PUT_LINE('We did not need to uppercase the name.');
 16     END IF;
 17
 18     IF INSTR(directions,'Bridge') <> 0 THEN
 19        DBMS_OUTPUT.PUT_LINE('To get to ' || name_upper || blank_space);
 20     END IF;
 21  END;
 22  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7


SQL>
SQL>
SQL> drop table facebook;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.