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.fiv < prev    next >
Encoding:
Text File  |  1998-01-28  |  2.8 KB  |  77 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWATTTX.FIV                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored procedure to insert ERwin attribute descriptions              --
  6. --  into the Oracle CASE 5.1 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.         AS
  15.         TYPE recEntity IS RECORD
  16.             (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  17.              ID CI_ENTITIES.ID%TYPE,
  18.              NAME CI_ENTITIES.NAME%TYPE);
  19.         TYPE recAttribute IS RECORD
  20.             (ENTITY_REFERENCE CI_ATTRIBUTES.ENTITY_REFERENCE%TYPE,
  21.              ID                  CI_ATTRIBUTES.ID%TYPE,
  22.              NAME              CI_ATTRIBUTES.NAME%TYPE);
  23.         TYPE recText   IS RECORD
  24.             (REF    CDI_TEXT.TXT_REF%TYPE,
  25.              SEQ    CDI_TEXT.TXT_SEQ%TYPE,
  26.              TXTY    CDI_TEXT.TXT_TYPE%TYPE,
  27.              TXTX    CDI_TEXT.TXT_TEXT%TYPE);
  28.         prText            recText;
  29.         prAttribute        recAttribute;
  30.         prEntity        recEntity;
  31.         EntityRef        CI_ENTITIES.ID%TYPE;
  32. BEGIN
  33.         BEGIN
  34.             IF pVersion = 0 THEN
  35.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  36.                     FROM CI_ENTITIES
  37.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName) 
  38.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  39.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  40.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  41.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  42.             ELSE
  43.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  44.                     FROM CI_ENTITIES
  45.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName) 
  46.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  47.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  48.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  49.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  50.             END IF;    
  51.             EntityRef := prEntity.ID;
  52.         EXCEPTION
  53.             WHEN OTHERS THEN
  54.                 RETURN;
  55.         END;            
  56.         BEGIN
  57.             SELECT ENTITY_REFERENCE, ID, NAME INTO prAttribute
  58.                 FROM CI_ATTRIBUTES
  59.                 WHERE    CI_ATTRIBUTES.NAME=UPPER(pName) 
  60.                 AND CI_ATTRIBUTES.ENTITY_REFERENCE = EntityRef;
  61.         EXCEPTION
  62.             WHEN OTHERS THEN
  63.                 RETURN;
  64.         END;
  65.         SELECT TXT_REF, TXT_SEQ, TXT_TYPE, TXT_TEXT INTO prText 
  66.             FROM CDI_TEXT WHERE CDI_TEXT.TXT_REF = prAttribute.ID AND 
  67.                      CDI_TEXT.TXT_SEQ = pSeq;
  68.         UPDATE CDI_TEXT SET CDI_TEXT.TXT_TEXT = pLine
  69.                 WHERE CDI_TEXT.TXT_REF = prAttribute.ID AND 
  70.                      CDI_TEXT.TXT_SEQ = pSeq;
  71. EXCEPTION
  72.             WHEN NO_DATA_FOUND THEN
  73.                 INSERT INTO CDI_TEXT VALUES(prAttribute.ID,pSeq,'CDIDSC',pLine);
  74.  
  75. END ERWATTTX;
  76. /
  77.