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> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.