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 / erwdomin.fiv < prev    next >
Encoding:
Text File  |  1998-01-28  |  3.9 KB  |  121 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWDOMIN.FIV                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin UDDS                                  --
  6. --  into the Oracle CASE 5.1 dictionary as domains                         --
  7. ------------------------------------------------------------------------ */
  8. CREATE OR REPLACE FUNCTION ERWDOMIN(pName CI_DOMAINS.NAME%TYPE,
  9.                            pDefault CI_DOMAINS.DEFAULT_VALUE%TYPE,
  10.                            pFormat  CI_DOMAINS.FORMAT%TYPE,
  11.                            pMaxLen CI_DOMAINS.MAXIMUM_ATTRIBUTE_LENGTH%TYPE,
  12.                            pPrecision CI_DOMAINS.ATTRIBUTE_PRECISION%TYPE,
  13.                            pApp  CI_APPLICATION_SYSTEMS.NAME%TYPE,
  14.                            pVersion CI_APPLICATION_SYSTEMS.VERSION%TYPE
  15.                            )
  16.         RETURN CI_DOMAINS.ID%TYPE         
  17.         AS
  18.             TYPE recApp IS RECORD
  19.                 (ID            CI_APPLICATION_SYSTEMS.ID%TYPE,
  20.                  NAME         CI_APPLICATION_SYSTEMS.NAME%TYPE,
  21.                  VERSION    CI_APPLICATION_SYSTEMS.VERSION%TYPE);
  22.             TYPE recDomain IS RECORD
  23.                 (APPLICATION_SYSTEM_OWNED_BY    CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  24.                  DATATYPE        CI_DOMAINS.DATATYPE%TYPE,
  25.                  FORMAT            CI_DOMAINS.FORMAT%TYPE,
  26.                  ID                CI_DOMAINS.ID%TYPE,
  27.                  NAME            CI_DOMAINS.NAME%TYPE);
  28.             prDomain        recDomain;
  29.             stDomain        ciodomain.data;
  30.               act_status        varchar2(100);
  31.             act_warnings    varchar2(100);
  32.  
  33. BEGIN
  34.             if cdapi.initialized = false then
  35.                 RETURN -1;
  36.             end if;
  37.             cdapi.open_activity;
  38.             stDomain.v.NAME := pName;
  39.             stDomain.i.NAME := true;
  40.             stDomain.v.DEFAULT_VALUE := pDefault;
  41.             stDomain.i.DEFAULT_VALUE := true;
  42.             stDomain.v.FORMAT := pFormat;
  43.             stDomain.i.FORMAT := true;
  44.             stDomain.v.MAXIMUM_ATTRIBUTE_LENGTH := pMaxLen;
  45.             stDomain.i.MAXIMUM_ATTRIBUTE_LENGTH := true;
  46.             stDomain.v.ATTRIBUTE_PRECISION  := pPrecision;
  47.             stDomain.i.ATTRIBUTE_PRECISION  := true;
  48.             IF pVersion = 0 THEN
  49.                 SELECT APPLICATION_SYSTEM_OWNED_BY,DATATYPE,FORMAT, ID, NAME
  50.                     INTO prDomain
  51.                     FROM CI_DOMAINS
  52.                     WHERE    CI_DOMAINS.NAME=UPPER(pName) 
  53.                     AND CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY = 
  54.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  55.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  56.                         CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  57.             ELSE
  58.                 SELECT APPLICATION_SYSTEM_OWNED_BY,DATATYPE,FORMAT, ID, NAME
  59.                     INTO prDomain
  60.                     FROM CI_DOMAINS
  61.                     WHERE    CI_DOMAINS.NAME=UPPER(pName) 
  62.                     AND CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY = 
  63.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  64.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  65.                         CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  66.             END IF;
  67.             stDomain.v.ID := prDomain.ID;
  68.             stDomain.i.ID := true;
  69.             ciodomain.upd(prDomain.ID,stDomain);
  70.             cdapi.validate_activity(act_status, act_warnings);
  71.             cdapi.close_activity(act_status);
  72.             if act_status != 'Y' then
  73.                 cdapi.abort_activity;
  74.                 RETURN -1;
  75.             else
  76.                 RETURN prDomain.ID;
  77.             end if;
  78. EXCEPTION
  79.             WHEN NO_DATA_FOUND THEN
  80.                 BEGIN
  81.                     ciodomain.ins(null,stDomain);
  82.                     cdapi.validate_activity(act_status, act_warnings);
  83.                     cdapi.close_activity(act_status);
  84.                     if act_status != 'Y' then
  85.                         cdapi.abort_activity;
  86.                         RETURN -1;
  87.                     else
  88.                         RETURN stDomain.v.ID;
  89.                     end if;        
  90.                 EXCEPTION
  91.                     WHEN OTHERS THEN
  92.                         if cdapi.stacksize > 0 then 
  93.                             if cdapi.activity is not null then 
  94.                                 cdapi.abort_activity;
  95.                             end if;
  96.                         else
  97.                             if cdapi.activity is not null then 
  98.                                 cdapi.abort_activity;
  99.                             end if;
  100.                         end if;
  101.                     RETURN  -1;
  102.                 END;
  103.             WHEN TOO_MANY_ROWS THEN
  104.                 if cdapi.activity is not null then 
  105.                     cdapi.abort_activity;
  106.                 end if;
  107.                 RETURN -1;
  108.             WHEN OTHERS THEN
  109.                 if cdapi.stacksize > 0 then 
  110.                     if cdapi.activity is not null then 
  111.                         cdapi.abort_activity;
  112.                     end if;
  113.                 else
  114.                     if cdapi.activity is not null then 
  115.                         cdapi.abort_activity;
  116.                     end if;
  117.                 end if;
  118.                 RETURN  -1;
  119. END ERWDOMIN;
  120. /
  121.