REM REM $Header: sdo/admin/catmd.sql /main/91 2009/12/10 15:09:17 sravada Exp $ REM REM Copyright (c) 1992, 2009, Oracle and/or its affiliates. REM All rights reserved. REM REM NAME: REM catmd.sql REM DESCRIPTION: REM Installs REM - SDO_GEOM package REM - SDO_ADMIN package REM - SDO_TUNE package REM - SDOTYPES The definition of Spatial Object Types REM - SDO_3GL package for trusted callouts REM - SDO_CS package for coordinate systems REM - SDO_LRS package for linear referencing systems REM - SDO_UTIL package REM REM REM - SDOGRT the definition of GeoRaster object types REM - SDOGRXML the GeoRaster metadata XML Schema(s) REM - SDO_GEOR package REM - SDO_GEOR_INT package for trusted callouts REM REM NOTES: REM Must be run as SYS. REM Packages obsoleted/removed in 8.2 REM - MD3 data dictionary tables REM - MD3 data dictionary user views REM - MDSQL packages for MD operations REM - MDWEX packages for extracting data REM - MDGTRIG package for generating triggers REM - SDO_SERV_PART package REM REM MODIFIED (DD-MON-YY) DESCRIPTION REM qxie 08-APR-02 Add GeoRaster components (types,pkgs,XML,sysdata) REM wexu 19-SEP-01 Add sdo_pridx package REM jcwang 21-SEP-01 Add LRS metadata sdolrsmd.sql REM wexu 24-MAY-01 Add sdo_util package REM qxie 15-DEC-00 Add sdocsdef for CS related tables REM jcwang 15-AUG-00 Add prvtlrsh.plb prvtlrsb.plb sdolrsh.sql sdolrsb.plb REM jsharma 29-JUL-99 Remove 7.3.2 functionality packages REM sravada 29-JUL-99 Add CS and LRS files REM pfwang 01-JUL-98 Change sdocat.sql to .plb, prvtcat.sql to .plb REM gvincent 10-MAY-98 Add sdomig.sql, prvtmig.plb REM sravada 14-APR-98 Add prvtoper.plb REM pfwang 10-APR-98 Add OGIS metadata support REM gvincent 08-APR-98 Put indexing load right after types REM gvincent 30-MAR-98 Fix ordering of calls - 3gl before md2 REM ranwei 18-FEB-98 Add trusted callout package SDO_3GL REM gvincent 13-FEB-98 Add call to create trusted lib ORDMDLIBS REM gvincent 23-JAN-98 Create relate table before installing md2 REM pfwang 01-JAN-98 Add relate lookup table setup REM jsharma 24-DEC-1997 Remove prvt{gtes,tess,mett,spop}.sql REM jsharma 18-DEC-1997 Add install of Spatial Object Types (sdotypes.sql REM and sdotypeg.sql) REM jsharma 18-DEC-1997 Add install of Validate Geometry REM jsharma 06-SEP-97 Add tuning utility functions (prvttune) REM Vretanos 19-JUN-96 Add second pass filter functions REM NRaabe 14-JUN-96 Add ADMIN & GEOM package installation. REM NAgarwal 11-JUN-96 Add Server Partitioning Installation REM Vretanos 28-MAY-96 Add METT package installation. REM NRaabe 30-MAY-96 Add package installation for new kernel functions. REM NRaabe 08-MAY-96 Remove the MDD (pod) package. REM NRaabe 08-MAY-96 Add a prompt before installing mdtrig package. REM Vretanos 24-JAN-96 Remove PRVTLIMS.SQL file. REM Feng 27-DEC-95 Reorder execution of prvtboot with md package REM Vretanos 28-SEP-95 Modify order of installation of MDD. REM Vretanos 22-SEP-95 Add MDLIMS pacakge. REM Vretanos 21-SEP-95 Update MD_WEX installation. REM Feng 01-AUG-95 Add MDDICT dictionary package REM Vretanos 22-NOV-94 Creation REM Rem Load the SDO component validation routine @@sdovalid.sql REM change the session to set the current schema Alter session set current_schema=MDSYS; declare begin begin execute immediate ' create user MDDATA identified by MDDATA '; exception when others then NULL; end; end; / grant connect,resource to MDDATA ; alter user mddata account lock; @@mdprivs.sql Alter user MDSYS default tablespace SYSAUX; REM Add component registry entry to mark sdo loading EXECUTE dbms_registry.loading('SDO','Spatial','validate_sdo','MDSYS'); EXECUTE dbms_registry.set_required_comps('SDO',dbms_registry.comp_depend_list_t('JAVAVM','XDB','ORDIM')); declare stmt varchar2(200); tblspc varchar2(64); begin tblspc := 'SYSAUX'; stmt := ' select tablespace_name from dba_tablespaces ' || ' where contents = ''TEMPORARY'' and rownum = 1 ' ; begin execute immediate stmt into tblspc; stmt := ' Alter user mdsys temporary tablespace '||tblspc; execute immediate stmt; exception When others then tblspc := 'SYSAUX'; end ; end; / @@catmdloc.sql @@catmd10i.sql declare begin begin execute immediate ' drop type mdsys.sdo_rid_array'; exception when others then NULL; end; end; / Rem Load Spatial Web Services Rem During the following script, the current_schema gets changed to OpenLS (OpenLS not used any more): @@sdows.sql Rem Let's change it back, now: alter session set current_schema=MDSYS; Rem Load WFS and CSW @@sdowfscs.sql Rem Let's change it back, now: alter session set current_schema=MDSYS; --Add 3d tin, point cloud types --no specific types for 3d geometry extensions @@sdotnpc.sql @@prvttnpc.plb @@sdopc.sql @@prvtpc.plb @@sdotin.sql @@prvttin.plb prompt start recompiling invalid RDF/OWL pkgs and types -- -- This logic is put before sdocmprg.sql and the select invalid -- SQL statement so that we don't see many annoying invalid objects -- declare stmt varchar2(4000); obj varchar2(32); otype varchar2(19); type cursor_type is REF CURSOR; query_crs cursor_type ; begin -- NOTE: for RDF/OWL, since 11.1.0.6 RDF/OWL does not install pkgs by default -- in upgrade case (or downgrade then upgrade case), some packages become invalid -- because of dependency changes. So here we need to re-compile more. stmt := 'select OBJECT_NAME, OBJECT_TYPE from all_objects where OWNER = ''MDSYS'' and status=''INVALID'' and ( object_name like ''RDF_%'' or object_name like ''SDO_RDF%'' or object_name like ''SDO_SEM%'' or object_name like ''SEM_%'' )'; OPEN query_crs FOR stmt; LOOP BEGIN fetch query_crs into obj, otype; EXIT when query_crs%NOTFOUND ; begin if (otype = 'PACKAGE BODY') then EXECUTE immediate ' alter package '||obj||' compile body'; elsif (otype = 'TYPE BODY') then EXECUTE immediate ' alter type '||obj||' compile body'; else null; -- no action for now end if; EXCEPTION WHEN OTHERS THEN null; end; commit; end; END LOOP; close query_crs; end; / prompt done recompiling invalid RDF/OWL pkgs and types select object_name, object_type, status from all_objects where owner='MDSYS' and status = 'INVALID'; prompt done listing invalid objects owned by MDSYS REM update the component registry REM this should be the last one in catmd (ALWAYS!!) @@sdocmprg.sql commit; select object_name, object_type, status from all_objects where owner='MDSYS' and status = 'INVALID'; REM change the session back to the SYS schema Alter session set current_schema=SYS;