Retrieve the LOB locator created by the previous INSERT statement : 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> 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>
SQL> drop table facebook;

Table dropped.

SQL> drop directory bfile_data;

Directory dropped.