/*
 * clob_query.sql
 * Appendix I, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script demonstrates how to query a CLOB column value.
 */

-- Unremark for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000

DECLARE

  -- Define a local associative array collection.
  TYPE presidential_biography IS TABLE OF VARCHAR2(600)
    INDEX BY BINARY_INTEGER;
    
  -- Control Variables
  chunk             NUMBER := 80;
  clob_size         NUMBER;
  counter           NUMBER := 1;
  position          NUMBER := 1;
  
  -- Define a CLOB and maximum sized variable string.
  descriptor        CLOB;
  biography_line    VARCHAR2(4000);
  
BEGIN
  -- A FOR UPDATE makes this a DML transaction.
  SELECT    biography
  INTO      descriptor
  FROM      president
  WHERE     president_id = &primary_key
  FOR UPDATE; 
  
  -- Check that descriptor is not null and not empty.
  IF (descriptor IS NOT NULL) AND (dbms_lob.getlength(descriptor) > 0) THEN
  
    -- Set the CLOB size.
    clob_size := dbms_lob.getlength(descriptor);
    
    -- Only enter when current position is less than size.
    WHILE (position < clob_size) LOOP
    
      -- Get the position of the next HTML tag.
      chunk := dbms_lob.instr(descriptor,'<p />',position,1) - position + 5;
      
      -- Read the chunk including the HTML tag.
      dbms_lob.read(descriptor,chunk,position,biography_line);
      
      -- Format lines per return value.
      IF counter < 10 THEN
        dbms_output.put_line('[ '||counter||']['||biography_line||']');
      ELSE
        dbms_output.put_line('['||counter||']['||biography_line||']');   
      END IF;
      
      -- Set next position and increment counter.
      position := position + chunk;
      counter := counter + 1;
      
    END LOOP;
    
  END IF;

END;
/