/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

l_create_flag varchar2(1);
l_alter_flag  varchar2(1);

BEGIN --
  BEGIN -- Create
    SELECT 'Y'
     INTO l_create_flag
     FROM dual
     WHERE EXISTS
              (SELECT 'true'
                FROM all_tab_columns
                WHERE table_name = 'XXAS_PER_THUMBNAILS' );
                
    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                     WHERE table_name = 'XXAS_PER_THUMBNAILS'
                     AND column_name = 'BLOB_THUMB_SMALL');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_PER_THUMBNAILS
          ADD (
           blob_thumb_small  BLOB
          ,blob_thumb_med    BLOB
          ,mime_type         VARCHAR2(256)
              )';
    END; -- Alter
    
  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_PER_THUMBNAILS
						  (person_id         number NOT NULL PRIMARY KEY
						  ,thumb_small       ORDSYS.ORDImage
						  ,thumb_med         ORDSYS.ORDImage
						  ,blob_11i          BLOB
						  ,image_source      varchar2(30)
						  ,creation_date     date
						  ,created_by        number
						  ,last_update_date  date
						  ,last_updated_by   number
						  ,last_update_login number
						  ,blob_thumb_small  BLOB
						  ,blob_thumb_med    BLOB
						  ,mime_type         varchar2(256))';
            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_PER_THUMBNAILS TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;