/*
 * alter_world_leaders.sql
 * Chapter 15, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script modifies database PL/SQL components for the PHP example
 * by adding a BIOGRAPHY column using a CLOB data type, and a PHOTOGRAPH
 * column using a BFILE data type.
 */

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

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT   utc.column_name
            ,        ut.table_name
            FROM     user_tables ut
            ,        user_tab_columns utc
            WHERE    ut.table_name = 'PRESIDENT'
            AND      ut.table_name = utc.table_name
            AND      utc.column_name = 'BIOGRAPHY' ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE PRESIDENT DROP (biography)';
  END LOOP;
  FOR i IN (SELECT   utc.column_name
            ,        ut.table_name
            FROM     user_tables ut
            ,        user_tab_columns utc
            WHERE    ut.table_name = 'PRESIDENT'
            AND      ut.table_name = utc.table_name
            AND      utc.column_name = 'PHOTOGRAPH' ) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE PRESIDENT DROP (photograph)';
  END LOOP;
END;
/

-- Add a CLOB column to the PRESIDENT table.
ALTER TABLE PRESIDENT ADD (biography CLOB);

-- Add a BFILE column to the PRESIDENT table.
ALTER TABLE PRESIDENT ADD (photograph BFILE);

CREATE OR REPLACE TYPE biography_table AS TABLE OF VARCHAR2(4000);
/

-- Define a package to support read/write to LOB data types.
CREATE OR REPLACE PACKAGE biography IS

  PROCEDURE read_biography 
  ( president_id_in IN     NUMBER
  , descriptor      IN OUT CLOB );
  
  PROCEDURE update_biography 
  ( president_id_in IN     NUMBER
  , descriptor      IN OUT CLOB );
  
  PROCEDURE view_photograph 
  ( president_id_in IN     NUMBER
  , alias           IN OUT VARCHAR2
  , file_name       IN OUT VARCHAR2 );
  
END biography;
/

SHOW ERRORS

-- Implement the package body to read/write to LOB data types.
CREATE OR REPLACE PACKAGE BODY biography IS

  -- Read biorgraphy based on primary key value.
  PROCEDURE read_biography 
  ( president_id_in IN     NUMBER
  , descriptor      IN OUT CLOB ) IS

  BEGIN

    -- A FOR UPDATE makes this a DML transaction.
    SELECT    biography
    INTO      descriptor
    FROM      president
    WHERE     president_id = president_id_in
    FOR UPDATE;
       
  END read_biography;

  -- Update biorgraphy based on primary key value.
  PROCEDURE update_biography 
  ( president_id_in IN     NUMBER
  , descriptor      IN OUT CLOB ) IS

  BEGIN

    -- A FOR UPDATE makes this a DML transaction.
    UPDATE    president
    SET       biography = empty_clob()
    WHERE     president_id = president_id_in
    RETURNING biography INTO descriptor;
       
  END update_biography;

  -- Read biorgraphy based on primary key value.
  PROCEDURE view_photograph 
  ( president_id_in IN     NUMBER
  , alias           IN OUT VARCHAR2
  , file_name       IN OUT VARCHAR2 ) IS

    descriptor BFILE;

    directory_num EXCEPTION;
    PRAGMA EXCEPTION_INIT(directory_num,-22285);  

  BEGIN

    -- A FOR UPDATE makes this a DML transaction.
    SELECT    photograph
    INTO      descriptor
    FROM      president
    WHERE     president_id = president_id_in
    FOR UPDATE; 

    IF descriptor IS NOT NULL THEN     
      dbms_lob.filegetname(descriptor,alias,file_name);
    END IF;

  EXCEPTION
    WHEN directory_num THEN
    RETURN;
  END view_photograph;
  
END biography;
/

SHOW ERRORS

-- Procedure to add biographies by using PL/SQL
CREATE OR REPLACE PROCEDURE add_biography 
( president_id_in IN     NUMBER
, biography       IN     BIOGRAPHY_TABLE ) IS

  -- Define a local CLOB variable.
  descriptor  CLOB;

BEGIN

  -- Update row with empty CLOB, and return value into local variable.
  SELECT    biography
  INTO      descriptor
  FROM      president
  WHERE     president_id = president_id_in
  FOR UPDATE;

  -- Open the CLOB variable for bidirectional I/O.    
  dbms_lob.open(descriptor,dbms_lob.lob_readwrite);

  -- Append the nested table elements to CLOB.
  FOR i IN 1..biography.COUNT LOOP
    dbms_lob.writeappend(descriptor,LENGTH(biography(i)),biography(i));
  END LOOP;
    
  -- Close the CLOB.
  dbms_lob.close(descriptor);
    
  -- Commit the change.
  COMMIT;
  
END add_biography;
/

SHOW ERRORS

-- Update row with empty CLOB, and return value into local variable.
UPDATE    president
SET       biography = empty_clob()
WHERE     president_id = 1;

COMMIT;

DECLARE

  -- Define a local nested table collection.
  TYPE presidential_biography IS TABLE OF VARCHAR2(600);

  -- Define and initialize a NESTED TABLE.
  biography_in      BIOGRAPHY_TABLE := biography_table();
  biography_out     CLOB;
 
BEGIN

  -- Enable space.
  biography_in.EXTEND(10);

  -- Add biography.  
  biography_in(1)  := 'On April 30, 1789, George Washington, ...<p />';
  biography_in(2)  := 'Born in 1732 into a Virginia planter ...<p />';
  biography_in(3)  := 'He pursued two intertwined interests: ...<p />';
  biography_in(4)  := 'From 1759 to the outbreak of the American ...<p />';
  biography_in(5)  := 'When the Second Continental Congress ...<p />';
  biography_in(6)  := 'He realized early that the best strategy ...<p />';
  biography_in(7)  := 'Washington longed to retire to his ...<p />';
  biography_in(8)  := 'He did not infringe upon the policy making ...<p />';
  biography_in(9)  := 'To his disappointment, two parties were ...<p />';
  biography_in(10) := 'Washington enjoyed less than three years ...<p />';

  -- Add biography for one president.
  add_biography(1,biography_in);  

END;
/

DECLARE

  descriptor    CLOB;
  amount        NUMBER;
  offset        NUMBER := 1;
  buffer        VARCHAR2(4000);
  
BEGIN

  -- A FOR UPDATE makes this a DML transaction.
  SELECT    biography
  INTO      descriptor
  FROM      president
  WHERE     president_id = 1
  FOR UPDATE;

  amount := dbms_lob.getlength(descriptor);

  dbms_lob.read(descriptor,amount,offset,buffer);
  
  dbms_output.put_line('Length is ['||amount||']');
       
END;
/

-- Update PRESIDENT table.
UPDATE president
SET photograph = BFILENAME('MY_DIRECTORY','Washington.gif')
WHERE president_id = 1;

DECLARE
  input        NUMBER := 1;
  directory    VARCHAR2(255);
  file         VARCHAR2(255);
BEGIN
  -- Call the stored procedure and verify file name.
  biography.view_photograph(input,directory,file);
  dbms_output.put_line('File name ['||file||']');
END;
/

-- Test BFILE process.
DECLARE
  myvar BFILE;
  directory_num EXCEPTION;
  PRAGMA EXCEPTION_INIT(directory_num,-22285);  
BEGIN
  SELECT   photograph
  INTO     myvar
  FROM     president
  WHERE    president_id = &id;
  IF (myvar is null) THEN
    dbms_output.put_line('Null value');
  ELSE
    dbms_output.put_line(TO_CHAR(dbms_lob.getlength(myvar)));
  END IF;
EXCEPTION
  WHEN directory_num THEN
    dbms_output.put_line('Null value');
    myvar := NULL;
    RETURN;
END;
/

COMMIT;