-- warranty_search.sql

CREATE OR REPLACE FUNCTION warranty_search (pid VARCHAR2,
                                        searchfor VARCHAR2 )
  RETURN VARCHAR2
IS
  retval       VARCHAR2(80) := '';
  clobvalue    CLOB;
  foundtext    VARCHAR2(80) := '';
  len          NUMBER;
  ppos         NUMBER;
BEGIN
  -- Get the LOB locator for a warranty
  SELECT warranty_text INTO clobvalue FROM product
   WHERE product_id = pid;
  IF clobvalue IS NULL THEN
    retval := 'Not Found: Null warranty for product ID ' || pid ;
  ELSE
    len := length(clobvalue);
    IF len = 0 THEN
      retval := 'Not Found: Warranty empty for product ID ' || pid ;
    ELSE
      foundtext := REGEXP_SUBSTR(clobvalue, searchfor);
      IF (LENGTH(foundtext) > 0) THEN
        retval := 'Found: ' || SUBSTR(clobvalue,
                  REGEXP_INSTR(clobvalue, searchfor) - 10,
                  80 - LENGTH(foundtext) - 10 );
      ELSE
        retval := 'Not Found: "' || searchfor || '" in product '
                  || pid || ' warranty.';
      END IF;
    END IF;
  END IF;
  RETURN retval;
END;
/
SET SERVEROUTPUT ON
DECLARE
  found VARCHAR2(80);
BEGIN
  dbms_output.put_line('Searching for: ' || 'EXPRESS');
  found := warranty_search('CSCO0000002','EXPRESS');
  dbms_output.put_line(found);
END;
/
