Mega Code Archive
Retrieve the LOB locator created by the previous INSERT statement
<![CDATA[
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> DECLARE
2 directions CLOB;
3 amount BINARY_INTEGER;
4 offset INTEGER;
5 first_direction VARCHAR2(100);
6 more_directions VARCHAR2(500);
7 BEGIN
8 DELETE
9 FROM facebook
10 WHERE name='Falls';
11
12 INSERT INTO facebook (name,directions) VALUES ('Falls',EMPTY_CLOB( ));
13
14
15 SELECT directions
16 INTO directions
17 FROM facebook
18 WHERE name='Falls';
19
20 DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
21
22 first_direction := 'the Bridge.';
23 amount := LENGTH(first_direction);
24
25 offset := 1;
26 DBMS_LOB.WRITE(directions, amount, offset, first_direction);
27
28
29 more_directions := ' more.';
30 DBMS_LOB.WRITEAPPEND(directions,LENGTH(more_directions), more_directions);
31
32 more_directions := ' more.';
33 DBMS_LOB.WRITEAPPEND(directions,LENGTH(more_directions), more_directions);
34
35 DBMS_LOB.CLOSE(directions);
36 END;
37
38 DECLARE
39 directions CLOB;
40 directions_1 VARCHAR2(300);
41 directions_2 VARCHAR2(300);
42 chars_read_1 BINARY_INTEGER;
43 chars_read_2 BINARY_INTEGER;
44 offset INTEGER;
45 BEGIN
46
47 SELECT directions INTO directions FROM facebook WHERE name='Falls';
48
49 offset := 1;
50
51 chars_read_1 := 9;
52
53 DBMS_LOB.READ(directions, chars_read_1, offset, directions_1);
54
55 IF chars_read_1 = 9 THEN
56 offset := offset + chars_read_1;
57 chars_read_2 := 5;
58 DBMS_LOB.READ(directions, chars_read_2, offset, directions_2);
59 ELSE
60 chars_read_2 := 0;
61 directions_2 := '';
62 END IF;
63
64 DBMS_OUTPUT.PUT_LINE('Characters read = ' ||TO_CHAR(chars_read_1+chars_read_2));
65
66 DBMS_OUTPUT.PUT_LINE(directions_1);
67 DBMS_OUTPUT.PUT_LINE(directions_2);
68 END;
69 /
DECLARE
*
ERROR at line 38:
ORA-06550: line 38, column 1:
PLS-00103: Encountered the symbol "DECLARE"
SQL>
SQL> drop table facebook;
Table dropped.
SQL> drop directory bfile_data;
Directory dropped.
]]>