-- Create sql in-place upgrade patch for some data types
-- inside SeedData.xml. The sql will be shown (as much as 
-- possible) in-line with associated segment of SeedData.xml.
-- The xml will be set off from the sql by commenting each
-- line with "--SeedData.xml->".
--
-- Note: PLEASE connect as OWBSYS before execute the file.
--
--  Bug 8437739 - MISSING INDEX AND PARTITION VALUE IN TABLE CONFIGURATION PANEL 
-- 

SET SERVEROUTPUT ON
SET LINESIZE 500
-- ##########################################################################
DECLARE -- Outer block for utility functions/variables BUGBUG: Should these be packagized (uprop_util)
-- ##########################################################################

  SCRIPTID CONSTANT VARCHAR2(40) := 'bug 8437739'; -- For output logging
  EOL      CONSTANT VARCHAR2(01) := CHR(10); -- new line
  TRACEON  CONSTANT BOOLEAN      := TRUE; -- Provide function level trace output
  OUTINITED         BOOLEAN      := FALSE; -- Flag indicating output has been enabled


  -- Record definitions for the various views
  -- Handy sql to make record definitions (some hand editing required (in sqlworksheet anyway)

  
  
-- **************************************************************************
-- **************************************************************************
--                      Useful functions/procedures
-- **************************************************************************
-- **************************************************************************

-- ==========================================================================
-- put message to output (uses DBMS_OUTPUT)
  PROCEDURE put(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF NOT OUTINITED THEN
      OUTINITED := TRUE;
      DBMS_OUTPUT.ENABLE(200000);
      put('DBMS_OUTPUT Enabled.');
    END IF;
    
    IF (LENGTH(msg) + LENGTH(SCRIPTID) + 2) > 255 THEN
      -- 10.1 database can't handle messages with length > 255
      --DBMS_OUTPUT.put_line('Message length > 255');
      --DBMS_OUTPUT.put_line(SUBSTR(msg, 0, 255));
      -- Break the string up into lines or 255-byte chunks
      DECLARE
        l_msg_line VARCHAR2(255);
        l_eol_index NUMBER;
        l_curr_index NUMBER;
        l_msg_len NUMBER := LENGTH(msg);
        l_loop_index NUMBER := 0;
      BEGIN
        DBMS_OUTPUT.PUT_LINE(SCRIPTID || ':');
        l_eol_index := 1;
        l_curr_index := 1;
        l_loop_index := 0;
        WHILE (l_curr_index < l_msg_len AND l_loop_index < 10) LOOP
          l_eol_index := INSTR(msg, EOL, l_curr_index);
          --DBMS_OUTPUT.put_line('l_eol_index = ' || TO_CHAR(l_eol_index));
          IF (l_eol_index <= 0) OR (l_eol_index - l_curr_index) > 255 THEN
            l_msg_line := SUBSTR(msg, l_curr_index, 255);
            l_curr_index := l_curr_index + 255;
          ELSE 
            l_msg_line := SUBSTR(msg, l_curr_index, l_eol_index - l_curr_index);
            l_curr_index := l_eol_index + 1;
          END IF;
          DBMS_OUTPUT.PUT_LINE(l_msg_line);        
          l_loop_index := l_loop_index + 1;
        END LOOP;
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;
    ELSE 
      DBMS_OUTPUT.put_line(SCRIPTID || ': ' || msg);
    END IF;  

  END;

-- ==========================================================================
-- put message to output controlled by TRACEON variable (uses put)
  PROCEDURE putTrace(msg VARCHAR2) IS
-- ==========================================================================
  BEGIN
    IF TRACEON THEN
      put(msg);
    END IF;
  END;  

-- ==========================================================================
-- return the id (elementid) for the platform defined by the input parameters
  FUNCTION platformIdOf(
     p_workspaceId IN NUMBER
    ,p_platformName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_workspaceId NUMBER := p_workspaceId;
    l_platformName VARCHAR2(255) := p_platformName;
    l_platformId NUMBER;
  BEGIN

      putTrace('platformIdOf(' || l_workspaceId || ', ' || l_platformName || ')');
      SELECT ELEMENTID
        INTO l_platformId
        FROM CMPPlatform_r
        WHERE NAME = l_platformName and WORKSPACEID = l_workspaceId;
      putTrace('  RETURN ' || l_platformId || EOL);

    RETURN l_platformId;
  END platformIdOf;  

-- ==========================================================================
-- return the id (elementid) for the type set defined by the input parameters
  FUNCTION typesetIdOf(
      p_workspaceId IN NUMBER
     ,p_ownerId   IN NUMBER
     ,p_typesetName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_workspaceId NUMBER := p_workspaceId;
    l_ownerId  NUMBER := p_ownerId;
    l_typesetName VARCHAR2(255) := p_typesetName;
    l_typesetId   NUMBER;
  BEGIN

      putTrace('typesetIdOf(' || l_workspaceId || ', ' || l_ownerId || ', ' || l_typesetName || ')');
      SELECT ELEMENTID
        INTO l_typesetId
        FROM CMPPlatformTypeset_r
        WHERE OWNINGPLATFORM=l_ownerId
          AND NAME=l_typesetName and WORKSPACEID = l_workspaceId;
      putTrace('  RETURN ' || l_typesetId || EOL);

    RETURN l_typesetId;
  END typesetIdOf;
  
-- ==========================================================================
-- return the id (elementid) for the type defined by the input parameters
  FUNCTION typeIdOf(
      p_workspaceId IN NUMBER
     ,p_ownerId   IN NUMBER
     ,p_typeName IN VARCHAR2
  ) RETURN NUMBER
-- ==========================================================================
  IS
    l_workspaceId NUMBER := p_workspaceId;
    l_ownerId  NUMBER := p_ownerId;
    l_typeName VARCHAR2(255) := p_typeName;
    l_typeId   NUMBER;
  BEGIN

      putTrace('typeIdOf(' || l_workspaceId || ', ' || l_ownerId || ', ' || l_typeName || ')');
      SELECT ELEMENTID
        INTO l_typeId
        FROM CMPDataType_r
        WHERE OWNINGPLATFORMTYPESET=l_ownerId
          AND NAME=l_typeName and WORKSPACEID = l_workspaceId;
      putTrace('  RETURN ' || l_typeId || EOL);

    RETURN l_typeId;
  END typeIdOf;  


-- ##########################################################################
-- ##########################################################################
BEGIN --    Outer block - utility functions/variables now defined
-- ##########################################################################
-- ##########################################################################  

  -- =======================================================
  DECLARE -- Inner block for utility function/variable usage
          -- aka 'user' declarations
  -- =======================================================	
	l_platformId NUMBER;
	l_typesetId NUMBER;
	l_typeId NUMBER;
  -- =======================================================
  BEGIN -- start handling data types defined in SeedData.xml
  -- =======================================================	
		put('Delete property definition of 8i.PARTITIONS and 8i.INDEXES in CMPWBTable.up.xml' || EOL);
		delete from cmppropertydefinition_r where rowid in (
		  select p.rowid from cmppropertydefinition_r p, cmppropertysetdefinition_r s, 
		                      cmppropertysetdefinition_r sp, cmpclassdefinition_r c 
		    where (p.definitionkey = '8i.PARTITIONS' or p.definitionkey = '8i.INDEXES')
		      and p.owningpropertysetdefinition = s.elementid 
		      and s.parentpropertyset = sp.elementid
		      and sp.owningclassdefinition = c.elementid
		      and (c.name = 'oracle.wh.repos.impl.dataProfile.CMPProfile' 
		           or c.name = 'oracle.wh.repos.impl.application.OracleNativeModule')
		);
  
  -- =======================================================
  END; -- Inner block for utility function/variable usage
  -- =======================================================    
  
-- ##########################################################################
END; -- Outer block for utility functions/variables
-- ##########################################################################
/