/* $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_sql              varchar2(32767);
  e_tbl_exists       EXCEPTION;
  PRAGMA exception_init(e_tbl_exists, -00955);
  e_col_exists       EXCEPTION;
  PRAGMA exception_init(e_col_exists, -01430);

BEGIN

  l_sql :=
    'CREATE TABLE XXAS_COM_PRODUCTS
      (prod_key       varchar2(30) NOT NULL
      ,version        varchar2(30) NOT NULL
      ,install_date   date         NOT NULL
      ,serial_number  raw(255)     NOT NULL
      ,status         raw(255)     NOT NULL
      ,allocations    XMLType
      ,signature      raw(255))';
  EXECUTE IMMEDIATE l_sql;
  
  l_sql := 'GRANT ALL ON XXAS_COM_PRODUCTS TO &1 WITH GRANT OPTION';
  EXECUTE IMMEDIATE l_sql;
  

EXCEPTION
  WHEN e_tbl_exists THEN
    BEGIN
      l_sql :=
        'ALTER TABLE XXAS_COM_PRODUCTS
               ADD  (allocations XMLType
                    ,signature   raw(255))';
      EXECUTE IMMEDIATE l_sql;
    EXCEPTION
      WHEN e_col_exists THEN
        NULL;
    END;

END;
/

COMMIT;
EXIT;
