home *** CD-ROM | disk | FTP | other *** search
/ Programmer Plus 2007 / Programmer-Plus-2007.iso / Programming / SQL and Data Base / Logic Works ERwin 3.5 / data.1 / erwatttx.six < prev    next >
Encoding:
Text File  |  1998-01-28  |  2.8 KB  |  78 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWATTTX.SIX                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored procedure to insert ERwin attribute descriptions              --
  6. --  into the Designer 2000 dictionary                                     --                                                                 --
  7. ------------------------------------------------------------------------- */
  8. CREATE OR REPLACE PROCEDURE ERWATTTX(pEntName CI_ENTITIES.NAME%TYPE    ,
  9.                             pName CI_ATTRIBUTES.NAME%TYPE,
  10.                             pApp     CI_APPLICATION_SYSTEMS.NAME%TYPE,
  11.                             pVersion CI_APPLICATION_SYSTEMS.VERSION%TYPE,
  12.                             pSeq  CDI_TEXT.TXT_SEQ%TYPE,
  13.                               pLine CDI_TEXT.TXT_TEXT%TYPE,
  14.                             pType CDI_TEXT.TXT_TYPE%TYPE)
  15.         AS
  16.         TYPE recEntity IS RECORD
  17.             (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  18.              ID CI_ENTITIES.ID%TYPE,
  19.              NAME CI_ENTITIES.NAME%TYPE);
  20.         TYPE recAttribute IS RECORD
  21.             (ENTITY_REFERENCE CI_ATTRIBUTES.ENTITY_REFERENCE%TYPE,
  22.              ID                  CI_ATTRIBUTES.ID%TYPE,
  23.              NAME              CI_ATTRIBUTES.NAME%TYPE);
  24.         TYPE recText   IS RECORD
  25.             (REF    CDI_TEXT.TXT_REF%TYPE,
  26.              SEQ    CDI_TEXT.TXT_SEQ%TYPE,
  27.              TXTY    CDI_TEXT.TXT_TYPE%TYPE,
  28.              TXTX    CDI_TEXT.TXT_TEXT%TYPE);
  29.         prText            recText;
  30.         prAttribute        recAttribute;
  31.         prEntity        recEntity;
  32.         EntityRef        CI_ENTITIES.ID%TYPE;
  33. BEGIN
  34.         BEGIN
  35.             IF pVersion = 0 THEN
  36.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  37.                     FROM CI_ENTITIES
  38.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName) 
  39.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  40.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  41.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  42.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  43.             ELSE
  44.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  45.                     FROM CI_ENTITIES
  46.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName) 
  47.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  48.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  49.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  50.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  51.             END IF;    
  52.             EntityRef := prEntity.ID;
  53.         EXCEPTION
  54.             WHEN OTHERS THEN
  55.                 RETURN;
  56.         END;            
  57.         BEGIN
  58.             SELECT ENTITY_REFERENCE, ID, NAME INTO prAttribute
  59.                 FROM CI_ATTRIBUTES
  60.                 WHERE    CI_ATTRIBUTES.NAME=UPPER(pName) 
  61.                 AND CI_ATTRIBUTES.ENTITY_REFERENCE = EntityRef;
  62.         EXCEPTION
  63.             WHEN OTHERS THEN
  64.                 RETURN;
  65.         END;
  66.         SELECT TXT_REF, TXT_SEQ, TXT_TYPE, TXT_TEXT INTO prText 
  67.             FROM CDI_TEXT WHERE CDI_TEXT.TXT_REF = prAttribute.ID AND 
  68.                      CDI_TEXT.TXT_SEQ = pSeq AND CDI_TEXT.TXT_TYPE = pType;
  69.         UPDATE CDI_TEXT SET CDI_TEXT.TXT_TEXT = pLine
  70.                 WHERE CDI_TEXT.TXT_REF = prAttribute.ID AND 
  71.                      CDI_TEXT.TXT_SEQ = pSeq AND CDI_TEXT.TXT_TYPE = pType;
  72. EXCEPTION
  73.             WHEN NO_DATA_FOUND THEN
  74.                 INSERT INTO CDI_TEXT VALUES(prAttribute.ID,pSeq,pType,pLine);
  75.  
  76. END ERWATTTX;
  77. /
  78.