-- assign_warranty.sql

CREATE OR REPLACE PROCEDURE assign_warranty
  (prod_id VARCHAR2, fname VARCHAR2) AS
  fileref                       BFILE;
  lob_locator                   CLOB;
  dst_offset                    INTEGER := 1;
  src_offset                    INTEGER := 1;
  src_charset                   NUMBER := 0;
  lang_ctxt                     NUMBER := 0;
  warnings                      INTEGER := 0;
BEGIN
  SELECT warranty_text INTO lob_locator FROM product
   WHERE product_id = prod_id
  FOR UPDATE;
  IF lob_locator IS NULL THEN
    UPDATE product SET warranty_text = EMPTY_CLOB()
     WHERE product_id = prod_id
     RETURNING warranty_text INTO lob_locator;
  END IF;
  fileref := BFILENAME('EXAMPLE_LOBDIR', fname);
  DBMS_LOB.OPEN(fileref);
  DBMS_LOB.LOADCLOBFROMFILE(lob_locator, fileref, DBMS_LOB.LOBMAXSIZE,
							dst_offset, src_offset, src_charset,
							lang_ctxt, warnings);
  DBMS_LOB.CLOSE(fileref);
END;
/

BEGIN
  assign_warranty('SYBS0000006', 'warranty3.txt');
END;
/
