-- Must do this to run script in SQL*Plus, to avoid interpretation
-- of & as substitution character:

CREATE OR REPLACE DIRECTORY admin_dir AS '&&lobdir'
/

CREATE OR REPLACE PROCEDURE picture 
  (prod_id VARCHAR2, fname VARCHAR2) AS
  fileref                       BFILE;
  lob_locator                   BLOB;
  dst_offset                    INTEGER := 1;
  src_offset                    INTEGER := 1;
BEGIN
  SELECT product_image INTO lob_locator FROM product
   WHERE product_id = prod_id FOR UPDATE;
  IF lob_locator IS NULL THEN
    UPDATE product SET product_image = EMPTY_BLOB()
     WHERE product_id = prod_id
     RETURNING product_image INTO lob_locator;
  END IF;
  fileref := BFILENAME('ADMIN_DIR', fname);
  DBMS_LOB.OPEN(fileref);
  DBMS_LOB.LOADBLOBFROMFILE(lob_locator, fileref, DBMS_LOB.LOBMAXSIZE,
                            dst_offset, src_offset);
  DBMS_LOB.CLOSE(fileref);
END;
/

DECLARE
  CURSOR pic (num NUMBER) IS
  SELECT product_id
    FROM product
   WHERE product_image IS NULL
     AND substr(description, 2, 1) = 'e'
     AND product_id like '%' || num;
BEGIN
  FOR rec IN pic(1) LOOP
    picture(rec.product_id, 'product1.jpg');
  END LOOP;
  FOR rec IN pic(2) LOOP
    picture(rec.product_id, 'product2.jpg');
  END LOOP;
  FOR rec IN pic(3) LOOP
    picture(rec.product_id, 'product3.jpg');
  END LOOP;
  FOR rec IN pic(4) LOOP
    picture(rec.product_id, 'product4.jpg');
  END LOOP;
END;
/

DROP PROCEDURE picture
/

CREATE OR REPLACE PROCEDURE 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('ADMIN_DIR', 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;
/

DECLARE
  CURSOR war (id VARCHAR2) IS
  SELECT product_id
    FROM product
   WHERE warranty_text IS NULL
     AND substr(description, 3, 1) = 't'
     AND vendor_id < id;
BEGIN
  FOR rec IN war ('BAAA') LOOP
    warranty(rec.product_id, 'warranty1.txt');
  END LOOP;
  FOR rec IN war ('DAAA') LOOP
    warranty(rec.product_id, 'warranty2.txt');
  END LOOP;
  FOR rec IN war ('MAAA') LOOP
    warranty(rec.product_id, 'warranty3.txt');
  END LOOP;
  FOR rec IN war ('SAAA') LOOP
    warranty(rec.product_id, 'warranty4.txt');
  END LOOP;
END;
/

DROP PROCEDURE warranty
/

DROP DIRECTORY admin_dir
/

EXIT;
