Rem
Rem $Header: caths.sql 29-nov-1999.15:30:09 rgmani Exp $
Rem
Rem caths.sql
Rem
Rem  Copyright (c) Oracle Corporation 1997, 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      caths.sql - CATalog script for Heterogeneous Services
Rem
Rem    DESCRIPTION
Rem      Create Heterogeneous Services data dictionary objects.
Rem
Rem      Contents of several tables and views are selected by
Rem      AGENT_ID and FDS_ID columns.  FDS_ID = NULL indicates a
Rem      default value, selected for any string that does not match
Rem      a non-null FDS_ID value.  AGENT_ID cannot be defaulted.
Rem
Rem      Additional HS data dictionary content often is created
Rem      by agent self-registration during normal server operation.
Rem
Rem
Rem      Objects created by this script are:
Rem
Rem          hs$_fds_class    Table:  Table containing legal FDS classes
Rem          hs_fds_class     View:   View corresponding to above table
Rem          hs$_fds_inst     Table:  Table containing info about FDS instances
Rem          hs_fds_inst      View:   View containing above info in more readable form
Rem          hs$_base_caps    Table:  Base capability table
Rem          hs_base_caps     View:   View containing above info in more readable form
Rem          hs$_class_caps   Table:  Table containing class-specific capabilities
Rem          hs_class_caps    View:   View containing above info in more readable form
Rem          hs$_inst_caps    Table:  Table containing instance-specific capabilities
Rem          hs_inst_caps     View:   View containing above info in more readable form
Rem          hs$_base_dd      Table:  Base DD translation table
Rem          hs_base_dd       View:   View containing above info in more readable form
Rem          hs$_class_dd     Table:  Table containing class-specific DD translations
Rem          hs_class_dd      View:   View containing above info in more readable form
Rem          hs$_inst_dd      Table:  Table containing instance-specific DD translations
Rem          hs_inst_dd       View:   View containing above info in more readable form
Rem          hs$_class_init   Table:  Table containing class-specific init params
Rem          hs_class_init    View:   View containing above info in more readable form
Rem          hs$_inst_init    Table:  Table containing instance-specific init params
Rem          hs_inst_init     View:   View containing above info in more readable form
Rem          hs$_external_objects Table: External objects table: procedures, libraries etc.
Rem          hs_external_objects  View:  View containing above info in more readable form
Rem          hs$_external_grantees Table: Access rights on external objects table
Rem          hs_external_grantees  View:  View containing above info in more readable form
Rem          hs_all_caps      View:   View that contains dblink-specific capabilities
Rem          hs_all_dd        View:   View that contains dblink-specific DD translations
Rem          hs_all_inits     View:   View that contains dblink-specific init params
Rem
Rem    NOTES
Rem      This script must be run while connected as SYS or INTERNAL.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rgmani      11/29/99 - Add base cap for HOACpublicSchema
Rem    rgmani      07/06/99 - Add base cap for 1970
Rem    rgmani      06/29/99 - Add base cap 1996
Rem    pravelin    01/12/99 - Bug 780957:  sqlplus doesn't recognize '#' commen
Rem    rgmani      10/20/98 - Add 1971 base cap
Rem    rgmani      10/12/98 - Add 1972 base cap
Rem    evoss       09/09/98 - bug 725369 (Subquery list in update capability)  
Rem    pravelin    09/03/98 - Bug 724831:  Eliminate 'set echo on'
Rem    ncramesh    08/06/98 - change for sqlplus
Rem    pravelin    07/20/98 - Eliminate comment referring to cathsAGT.sql scrip
Rem    pravelin    06/17/98 - Bug 679473:  Don't register TKHODDAU as base DD t
Rem    pravelin    02/04/98 - Bug 620715:  Add base_dd tables for test extensio
Rem    rgmani      08/13/97 - Add base cap for cap #1994
Rem    rgmani      07/24/97 - Add base cap for cap #1975
Rem    rgmani      07/23/97 - Add base caps for DB2-related capabilities
Rem    rgmani      07/10/97 - Add base caps for coercion capabilities
Rem    jdraaije    04/17/97 - remove ID columns from external objects view
Rem    jdraaije    03/26/97 - Name consistency: ho => hs
Rem    pravelin    03/20/97 - Eliminate 'use XA' capability, replaced by init p
Rem    pravelin    03/18/97 - Create pseudo-FDS class for BITE (Built-In Test E
Rem    ktarkhan    02/20/97 - add external grantees, change external objects
Rem    rgmani      02/13/97 - Add base caps for bundling and tcis call
Rem    pravelin    01/15/97 - Add setup for base dd translations
Rem    jdraaije    01/01/97 - external_procedures ==> external_objects
Rem    rgmani      11/06/96 - Add new base DD - TKHODDAU
Rem    celsbern    10/21/96 - Changed view names from hs to hs.
Rem    rgmani      10/25/96 - Add base caps for new HS datatypes
Rem    rgmani      10/14/96 - Add base caps for QuoteOwner and MapAlias
Rem    rgmani      09/18/96 - Add new capability 1992 (HOACdelimToFDS)
Rem    celsbern    09/06/96 - Removed dbms_hs.package and put in separate files
Rem    rgmani      08/21/96 - Add base cap 1993
Rem    asurpur     08/28/96 - Dictionary protection: granting execute on dbms_h
Rem    rgmani      07/16/96 - Fix bug
Rem    rgmani      07/15/96 - Switch to new HS data dictionary
Rem    asurpur     05/15/96 - Dictionary Protection: Granting privileges on vie
Rem    evoss       05/23/96 - add bind to parameter capability
Rem    jdraaije    04/03/96 - Add sys.hs_objects table for callouts
Rem    pravelin    03/13/96 - Create HS data dictionary tables and views
Rem    pravelin    03/13/96 - Created
Rem


-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- Create the role that is being used to protect access to the HS data
-- dictionary tables and to the dbms_hs, dbms_hs_extproc, and
-- dbms_hs_passthrough packages.
-- The role is immediately granted to select_catalog_role, and
-- execute_catalog_role such that users with generic data dictionary access
-- now also can access the HS data dictionary.
 
drop role hs_admin_role;
create role hs_admin_role;

grant hs_admin_role to select_catalog_role;
grant hs_admin_role to execute_catalog_role;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- The intention of the FDS class is that it contains all the information, 
-- e.g. capabilities, data dictionary translations, etc. that are specific for 
-- a whole group of FDSs. In general this group will be accessed by the same 
-- HS driver. 
-- 
-- HS$_FDS_CLASS 
 
drop table hs$_fds_class cascade constraints; 
create table hs$_fds_class 
( 
   fds_class_id number not null, 
      constraint hs$_fds_class_pk primary key (fds_class_id), 
   fds_class_name varchar2(30) not null, 
      constraint hs$_fds_class_uk1 unique (fds_class_name), 
   fds_class_comments varchar2(255) 
 ); 
 
drop sequence hs$_fds_class_s; 
create sequence hs$_fds_class_s; 
 
-- hs_fds_class view 
create or replace view hs_fds_class  
(fds_class_name,fds_class_comments,fds_class_id) as 
select fds_class_name,fds_class_comments,fds_class_id 
from hs$_fds_class; 

grant select on  hs_fds_class to hs_admin_role;

drop public synonym hs_fds_class;
create public synonym hs_fds_class for hs_fds_class;
 
-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- Besides having FDS class specific information, users also need 
-- to be able to set capabilities, DD translations and init parameters on 
-- a per FDS basis. Every RDB database, for instance, requires the RDB$HANDLE 
-- environment variable to be set to the name and location of the actual 
-- database file. This type of information is set in the FDS table. 
-- 
-- HS$_FDS_INST 
 
drop table hs$_fds_inst cascade constraints; 
create table hs$_fds_inst 
( 
   fds_inst_id                  number not null, 
      constraint hs$_fds_inst_pk primary key (fds_inst_id), 
   fds_class_id                 number not null, 
       constraint hs$_fds_inst_fk1 foreign key (fds_class_id) 
       references hs$_fds_class on delete cascade, 
   fds_inst_name                varchar2(30) not null, /*is this big enough?*/ 
      constraint hs$_fds_inst_uk1 unique (fds_inst_name,fds_class_id), 
   fds_inst_comments varchar2(255) 
); 
 
drop sequence hs$_fds_inst_s; 
create sequence hs$_fds_inst_s; 
 
-- hs_fds_inst view 
create or replace view hs_fds_inst 
(fds_inst_name,fds_inst_comments,fds_class_name,fds_inst_id,fds_class_id) as 
select f.fds_inst_name,f.fds_inst_comments,fc.fds_class_name,f.fds_inst_id,
  f.fds_class_id 
from hs$_fds_class fc, 
hs$_fds_inst f 
where f.fds_class_id = fc.fds_class_id; 

grant select on hs_fds_inst to hs_admin_role;

drop public synonym hs_fds_inst;
create public synonym hs_fds_inst for hs_fds_inst;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the base capability table. Besides this table we'll also create 
-- a table for the DRIVER (fds_class or agent) specific capabilities, and 
-- one for the FDS specific capabilities. 
-- 
-- HS$_BASE_CAPS 
 
drop table hs$_base_caps cascade constraints; 
create table hs$_base_caps 
( 
   cap_number               number not null, 
      constraint hs$_base_caps_pk primary key (cap_number), 
   cap_description          varchar2(255) 
); 
 
create or replace view hs_base_caps 
(cap_number,cap_description) as 
select cap_number, cap_description
from hs$_base_caps; 

grant select on  hs_base_caps to hs_admin_role;

drop public synonym hs_base_caps;
create public synonym hs_base_caps for hs_base_caps;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS class (DRIVER) specific capability table.  
--  
-- HS$_CLASS_CAPS 
 
drop table hs$_class_caps cascade constraints; 
create table hs$_class_caps 
( 
   fds_class_cap_id        number not null, 
      constraint hs$_class_caps_pk primary key (fds_class_cap_id), 
   fds_class_id            number not null, 
      constraint hs$_class_caps_fk1 foreign key (fds_class_id) 
      references hs$_fds_class on delete cascade, 
   cap_number              number not null, 
      constraint hs$_class_caps_fk2 foreign key (cap_number) 
      references hs$_base_caps on delete cascade, 
      constraint hs$_class_caps_uk1 unique (fds_class_id,cap_number), 
   context                 number, 
   translation             varchar2(255), 
   additional_info         number 
); 
 
drop sequence hs$_class_caps_s; 
create sequence hs$_class_caps_s; 
 
create or replace view hs_class_caps 
(cap_number, cap_description, context, translation, additional_info, 
 fds_class_name,  fds_class_id) as 
select cc.cap_number, bc.cap_description, cc.context, cc.translation, 
       cc.additional_info, fc.fds_class_name, fc.fds_class_id 
from   hs$_class_caps cc,
       hs$_base_caps bc,
       hs$_fds_class fc
where  bc.cap_number = cc.cap_number 
and cc.fds_class_id = fc.fds_class_id; 

grant select on  hs_class_caps to hs_admin_role;

drop public synonym hs_class_caps;
create public synonym hs_class_caps for hs_class_caps;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS instance specific capability table.  
--  
-- HS$_INST_CAPS 
 
drop table hs$_inst_caps cascade constraints; 
create table hs$_inst_caps 
( 
   fds_inst_cap_id         number not null,
      constraint hs$_inst_caps_pk primary key (fds_inst_cap_id), 
   fds_inst_id             number not null, 
      constraint hs$_inst_caps_fk1 foreign key (fds_inst_id) 
      references hs$_fds_inst on delete cascade, 
   cap_number              number not null, 
      constraint hs$_inst_caps_fk2 foreign key (cap_number) 
      references hs$_base_caps on delete cascade, 
      constraint hs$_inst_caps_uk1 unique (fds_inst_id,cap_number), 
   context                 number, 
   translation             varchar2(255), 
   additional_info         number 
); 
 
drop sequence hs$_inst_caps_s; 
create sequence hs$_inst_caps_s; 
 
create or replace view hs_inst_caps 
(cap_number, cap_description, context, translation, additional_info, 
 fds_class_name, fds_inst_name, fds_class_id, fds_inst_id) as 
select bc.cap_number, bc.cap_description, ic.context, ic.translation, 
       ic.additional_info, fc.fds_class_name, f.fds_inst_name,
       fc.fds_class_id, f.fds_inst_id 
from   hs$_inst_caps ic, 
       hs$_base_caps bc, 
       hs$_fds_class fc, 
       hs$_fds_inst f 
where  bc.cap_number = ic.cap_number 
and    ic.fds_inst_id = f.fds_inst_id 
and    f.fds_class_id = fc.fds_class_id; 
 
grant select on  hs_inst_caps to hs_admin_role;

drop public synonym hs_inst_caps;
create public synonym hs_inst_caps for hs_inst_caps;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the base DD translation table. Besides this table we'll also create 
-- a table for the DRIVER (fds_class or agent) specific capabilities, and 
-- one for the FDS specific capabilities. 
-- 
-- HS$_BASE_DD 
 
drop table hs$_base_dd cascade constraints; 
create table hs$_base_dd 
( 
   dd_table_id              number not null, 
      constraint hs$_base_dd_pk primary key (dd_table_id), 
   dd_table_name            varchar2(30) not null, 
      constraint hs$_base_dd_uk1 unique (dd_table_name), 
   dd_table_desc            varchar2(255) 
); 
 
drop sequence hs$_base_dd_s; 
create sequence hs$_base_dd_s; 
 
create or replace view hs_base_dd 
(dd_table_name,dd_table_desc, dd_table_id) as 
select dd_table_name, dd_table_desc, dd_table_id 
from hs$_base_dd; 

grant select on  hs_base_dd to hs_admin_role; 

drop public synonym hs_base_dd;
create public synonym hs_base_dd for hs_base_dd;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS class specific DD translation table 
-- 
-- HS$_CLASS_DD 
 
drop table hs$_class_dd cascade constraints; 
create table hs$_class_dd 
( 
   fds_class_dd_id          number not null, 
      constraint hs$_class_dd_pk primary key (fds_class_dd_id), 
   fds_class_id             number not null, 
      constraint hs$_class_dd_fk1 foreign key (fds_class_id) 
      references hs$_fds_class on delete cascade, 
   dd_table_id              number not null, 
      constraint hs$_class_dd_fk2 foreign key (dd_table_id) 
      references hs$_base_dd on delete cascade, 
      constraint hs$_class_dd_uk1 unique (fds_class_id,dd_table_id), 
   translation_type         char(1) not null, 
      constraint hs$_class_dd_c1 check (translation_type in ('T','M','S')),
   translation_text         varchar2(4000),
      constraint hs$_class_dd_c2 check 
      ((translation_type in('S','T') and translation_text is not NULL) or 
       (translation_type = 'M'))
); 
 
drop sequence hs$_class_dd_s; 
create sequence hs$_class_dd_s; 
 
create or replace view hs_class_dd 
(dd_table_name, dd_table_desc, translation_type, translation_text,  
 fds_class_name, dd_table_id, fds_class_id) as 
select bd.dd_table_name, bd.dd_table_desc, cd.translation_type,  
       cd.translation_text, fc.fds_class_name, bd.dd_table_id, fc.fds_class_id 
from   hs$_base_dd bd, hs$_class_dd cd, hs$_fds_class fc 
where  (bd.dd_table_id = cd.dd_table_id) and (cd.fds_class_id = 
fc.fds_class_id); 

grant select on  hs_class_dd to hs_admin_role;

drop public synonym hs_class_dd;
create public synonym hs_class_dd for hs_class_dd;
 
-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS instance specific DD translation table 
-- 
-- HS$_INST_DD 
 
drop table hs$_inst_dd cascade constraints; 
create table hs$_inst_dd 
( 
   fds_inst_dd_id          number not null, 
      constraint hs$_inst_dd_pk primary key (fds_inst_dd_id), 
   fds_inst_id             number not null, 
      constraint hs$_inst_dd_fk1 foreign key (fds_inst_id) 
      references hs$_fds_inst on delete cascade, 
   dd_table_id              number not null, 
      constraint hs$_inst_dd_fk2 foreign key (dd_table_id) 
      references hs$_base_dd on delete cascade, 
      constraint hs$_inst_dd_uk1 unique (fds_inst_id,dd_table_id), 
   translation_type         char(1) not null, 
      constraint hs$_inst_dd_c1 check (translation_type in ('T','M','S')),
   translation_text         varchar2(4000),
      constraint hs$_inst_dd_c2 check 
      ((translation_type in ('T','S') and translation_text is not NULL) or 
       (translation_type = 'M'))
); 
 
drop sequence hs$_inst_dd_s; 
create sequence hs$_inst_dd_s; 
 
create or replace view hs_inst_dd 
(dd_table_name, dd_table_desc, translation_type, translation_text,  
 fds_class_name, fds_inst_name, dd_table_id, fds_class_id, fds_inst_id) as 
select bd.dd_table_name, bd.dd_table_desc, id.translation_type,  
       id.translation_text, fc.fds_class_name, f.fds_inst_name, 
bd.dd_table_id,  
       fc.fds_class_id, f.fds_inst_id 
from   hs$_base_dd bd, hs$_inst_dd id, hs$_fds_class fc, hs$_fds_inst f 
where  (bd.dd_table_id = id.dd_table_id) and (id.fds_inst_id = f.fds_inst_id) 
       and (f.fds_class_id = fc.fds_class_id); 

grant select on  hs_inst_dd to hs_admin_role; 

drop public synonym hs_inst_dd;
create public synonym hs_inst_dd for hs_inst_dd;
 
-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS class specific init value table 
-- 
-- HS$_CLASS_INIT 
 
drop table hs$_class_init cascade constraints; 
create table hs$_class_init 
( 
   fds_class_init_id        number not null, 
      constraint hs$_class_init_pk primary key (fds_class_init_id), 
   fds_class_id             number not null, 
      constraint hs$_class_init_fk1 foreign key (fds_class_id) 
      references hs$_fds_class on delete cascade, 
   init_value_name          varchar2(64) not null, 
      constraint hs$_class_init_uk1 unique (fds_class_id,init_value_name), 
   init_value               varchar2(255) not null, 
   init_value_type          varchar2(1) not null, 
      constraint hs$_class_init_c1 check (init_value_type in ('F','T')) 
); 
 
drop sequence hs$_class_init_s; 
create sequence hs$_class_init_s; 
 
create or replace view hs_class_init 
(init_value_name, init_value, init_value_type, fds_class_name,  
 fds_class_init_id, fds_class_id) as 
select ci.init_value_name, ci.init_value, ci.init_value_type, 
       fc.fds_class_name, ci.fds_class_init_id, fc.fds_class_id 
from   hs$_class_init ci, hs$_fds_class fc 
where  (ci.fds_class_id = fc.fds_class_id); 

grant select on  hs_class_init to hs_admin_role;

drop public synonym hs_class_init;
create public synonym hs_class_init for hs_class_init;
 
-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- This is the FDS class specific init value table 
-- 
-- HS$_INST_INIT 
 
drop table hs$_inst_init cascade constraints; 
create table hs$_inst_init 
( 
   fds_inst_init_id         number not null, 
      constraint hs$_inst_init_pk primary key (fds_inst_init_id), 
   fds_inst_id              number not null, 
      constraint hs$_inst_init_fk1 foreign key (fds_inst_id) 
      references hs$_fds_inst on delete cascade, 
   init_value_name          varchar2(64) not null, 
      constraint hs$_inst_init_uk1 unique (fds_inst_id,init_value_name), 
   init_value               varchar2(255) not null, 
   init_value_type          varchar2(1) not null, 
      constraint hs$_inst_init_c1 check (init_value_type in ('F','T')) 
); 
 
drop sequence hs$_inst_init_s; 
create sequence hs$_inst_init_s; 
create or replace view hs_inst_init 
(init_value_name, init_value, init_value_type, fds_class_name,  
 fds_inst_name, fds_inst_init_id, fds_class_id, fds_inst_id) as 
select ii.init_value_name, ii.init_value, ii.init_value_type,
  fc.fds_class_name, f.fds_inst_name, ii.fds_inst_init_id,  
  fc.fds_class_id, f.fds_inst_id 
from  hs$_inst_init ii, 
  hs$_fds_class fc, 
  hs$_fds_inst f 
where ii.fds_inst_id = f.fds_inst_id 
and f.fds_class_id = fc.fds_class_id; 

grant select on  hs_inst_init to hs_admin_role;

drop public synonym hs_inst_init;
create public synonym hs_inst_init for hs_inst_init;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- Distributed External Procedure specific objects table 
-- 
-- HS$_EXTERNAL_OBJECTS 
 
drop table hs$_external_objects cascade constraints; 
create table hs$_external_objects 
( 
   extobj_id           number not null,
      constraint hs$_extobj_pk primary key (extobj_id),
   fds_inst_id              number not null, 
      constraint hs$_extobj_fk1 foreign key (fds_inst_id) 
      references hs$_fds_inst on delete cascade, 
   owner                    varchar2(30) not null, 
   object_name              varchar2(30) not null, 
   object_type              varchar2(13) not null, 
   object_text              long not null, 
      constraint hs$_extobj_uk1 unique 
        (fds_inst_id,owner,object_name) 
); 

drop sequence hs$_external_objects_s;
create sequence hs$_external_objects_s start with 1;

create or replace view hs_external_objects
(fds_class_name,fds_inst_name,owner,object_name,object_type, object_text) as
select c.fds_class_name,i.fds_inst_name,eo.owner,
eo.object_name,eo.object_type,eo.object_text
from hs$_external_objects eo,
hs$_fds_inst i,
hs$_fds_class c
where eo.fds_inst_id = i.fds_inst_id
and i.fds_class_id = c.fds_class_id;

grant select on  hs_external_objects to hs_admin_role;

drop public synonym hs_external_objects;
create public synonym hs_external_objects for hs_external_objects;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- Granted Users table 
-- 
-- HS$_GRANTED_USERS
 
drop table hs$_granted_users cascade constraints; 
create table hs$_granted_users
( 
   user_id                  number not null,
      constraint hs$_grantuser_pk primary key (user_id),
   fds_inst_id              number not null, 
      constraint hs$_grantuser_fk1 foreign key (fds_inst_id) 
      references hs$_fds_inst on delete cascade, 
   grantee_name             varchar2(30) not null, 
      constraint hs$_grantuser_uk1 unique 
        (fds_inst_id,grantee_name) 
); 

drop sequence hs$_granted_users_s;
create sequence hs$_granted_users_s start with 1;

--############################################################################# 
-- 
--############################################################################# 
--  
--Privileges table (preseeded)
-- 
-- HS$_PRIVILEGES
drop table hs$_privileges cascade constraints; 
create table hs$_privileges 
( 
   priv_id            number not null,
      constraint hs$_priv_pk primary key (priv_id),
   priv_name          varchar2(30) not null,
   create_access_type          char(2) not null,
      constraint hs$_priv_uk1 unique 
        (priv_name) 
); 

drop sequence hs$_privileges_s;
create sequence hs$_privileges_s;

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
-- External Objects Access Grantees table 
-- 
-- HS$_ACCESS_GRANTEES 
 
drop table hs$_access_grantees cascade constraints; 
create table hs$_access_grantees 
( 
   extobj_id              number not null, 
      constraint hs$_extgrant_fk1 foreign key (extobj_id) 
      references hs$_external_objects on delete cascade, 
   user_id            number not null,
      constraint hs$_accgrant_fk2 foreign key (user_id)
      references hs$_granted_users on delete cascade,
   priv_id	      number not null,
      constraint hs$_accgrant_fk3 foreign key (priv_id)
      references hs$_privileges on delete cascade,
      constraint hs$_accgrant_uk1 unique 
        (extobj_id,user_id,priv_id) 
); 

-- 
--############################################################################# 
-- 
--############################################################################# 
--  
--Create Rights Grantees table 
-- 
-- HS$_CREATE_GRANTEES 
drop table hs$_create_grantees cascade constraints; 
create table hs$_create_grantees 
( 
   priv_id              number not null, 
      constraint hs$_crtgrant_fk1 foreign key (priv_id) 
      references hs$_privileges on delete cascade, 
   user_id            number not null,
      constraint hs$_crtgrant_fk2 foreign key (user_id)
      references hs$_granted_users on delete cascade,
      constraint hs$_crtgrant_uk1 unique 
        (priv_id,user_id) 
); 

-- 
--#############################################################################
-- 
--#############################################################################
-- 
-- HS_EXTERNAL_OBJECT_PRIVILEGES view
-- shows all the privileges granted to users on objects

create or replace view hs_external_object_privileges
(object_name,privilege_name,grantee,fds_class_name,fds_inst_name) as
select eo.object_name,p.priv_name,
gu.grantee_name,c.fds_class_name,i.fds_inst_name
from hs$_external_objects eo,
hs$_privileges p,
hs$_granted_users gu,
hs$_fds_inst i,
hs$_fds_class c,
hs$_access_grantees ag
where ag.extobj_id = eo.extobj_id
and ag.user_id     = gu.user_id
and ag.priv_id     = p.priv_id
and gu.fds_inst_id = i.fds_inst_id
and i.fds_class_id = c.fds_class_id;

grant select on  hs_external_object_privileges to hs_admin_role;

drop public synonym hs_external_object_privileges;
create public synonym hs_external_object_privileges for
hs_external_object_privileges;

--#############################################################################
-- 
--#############################################################################
-- 
-- HS_EXTERNAL_USER_PRIVILEGES view
-- shows all the granted privileges that are not tied to any particular object

create or replace view hs_external_user_privileges
(privilege_name, grantee, fds_class_name, fds_inst_name) as
select p.priv_name, gu.grantee_name,c.fds_class_name,i.fds_inst_name
from hs$_privileges p,
hs$_granted_users gu,
hs$_fds_inst i,
hs$_fds_class c,
hs$_create_grantees cg
where cg.priv_id = p.priv_id
and cg.user_id = gu.user_id
and gu.fds_inst_id = i.fds_inst_id
and i.fds_class_id = c.fds_class_id;

grant select on  hs_external_user_privileges to hs_admin_role;

drop public synonym hs_external_user_privileges;
create public synonym hs_external_user_privileges for
hs_external_user_privileges;

-- 
--#############################################################################
-- 
--#############################################################################
-- 
-- HS_ALL_CAPS view
-- This view shows all of the available capabilities for a given class
-- and inst.  Class level capabilities override base capabilities and 
-- Inst level capabilities override class and base level capabilities.

create or replace view hs_all_caps
(cap_number, context, translation, additional_info, fds_class_name, 
fds_inst_name) as
/*clause for the fds_inst level*/
select ic.cap_number, ic.context, ic.translation, ic.additional_info, 
  fc.fds_class_name, fi.fds_inst_name
from hs$_inst_caps ic, hs$_fds_inst fi, hs$_fds_class fc
where ic.fds_inst_id = fi.fds_inst_id 
and fi.fds_class_id = fc.fds_class_id
union
/*clause for the fds_class level minus the fds_inst level*/
select cc.cap_number, cc.context, cc.translation, cc.additional_info,
  fc2.fds_class_name, fi2.fds_inst_name
from hs$_class_caps cc, hs$_fds_inst fi2, hs$_fds_class fc2
where cc.fds_class_id = fi2.fds_class_id 
and fi2.fds_class_id = fc2.fds_class_id 
and not exists 
  (select 1 from hs$_inst_caps ic2
   where ic2.cap_number = cc.cap_number 
   and ic2.fds_inst_id = fi2.fds_inst_id)
union
/*clause for the base level minus fds_inst and fds_class level*/
select bc.cap_number, 0, null, 0, fc.fds_class_name, 
  fi.fds_inst_name
from hs$_base_caps bc, hs$_fds_class fc, hs$_fds_inst fi
where fc.fds_class_id = fi.fds_class_id
and not exists
  (select 1 from hs$_inst_caps ic
   where ic.fds_inst_id = fi.fds_inst_id
   and ic.cap_number = bc.cap_number)
and not exists
  (select 1 from hs$_class_caps cc
   where cc.fds_class_id = fc.fds_class_id
   and cc.cap_number = bc.cap_number);

grant select on hs_all_caps to hs_admin_role;

drop public synonym hs_all_caps;
create public synonym hs_all_caps for hs_all_caps;

-- 
--#############################################################################
-- 
--#############################################################################
-- 
-- HS_ALL_DD
-- This view shows all of the data dictionary translations for a 
-- class and inst.  Class level translations override base translations
-- and inst level translations override class and base translations.

create or replace view hs_all_dd
(dd_table_name, translation_type, translation_text, fds_class_name, 
 fds_inst_name, dd_table_desc) as
/*clause for the inst level*/
select bd.dd_table_name, id.translation_type, id.translation_text, 
  fc.fds_class_name, fi.fds_inst_name, bd.dd_table_desc
from hs$_inst_dd id, hs$_base_dd bd, hs$_fds_inst fi, hs$_fds_class fc
where id.fds_inst_id = fi.fds_inst_id 
and id.dd_table_id = bd.dd_table_id 
and fc.fds_class_id = fi.fds_class_id
union
/*clause for the class level minus the inst level*/
select bd2.dd_table_name, cd.translation_type, cd.translation_text, 
  fc2.fds_class_name, fi2.fds_inst_name, bd2.dd_table_desc
from hs$_class_dd cd, hs$_base_dd bd2, hs$_fds_inst fi2, hs$_fds_class fc2
where cd.fds_class_id = fi2.fds_class_id 
and cd.dd_table_id = bd2.dd_table_id 
and fc2.fds_class_id = fi2.fds_class_id 
and not exists 
  (select 1 from hs$_inst_dd id2
   where id2.dd_table_id = cd.dd_table_id 
   and id2.fds_inst_id = fi2.fds_inst_id)
union
/*clause for the base level minus the class and inst levels*/
select bd.dd_table_name,NULL,NULL,fc.fds_class_name,fi.fds_inst_name,
  bd.dd_table_desc
from hs$_base_dd bd, hs$_fds_class fc, hs$_fds_inst fi
where fi.fds_class_id = fc.fds_class_id
and not exists
  (select 1 from hs$_class_dd cd2
   where cd2.dd_table_id = bd.dd_table_id
   and cd2.fds_class_id = fc.fds_class_id)
and not exists
  (select 1 from hs$_inst_dd id2
   where id2.dd_table_id = bd.dd_table_id
   and id2.fds_inst_id = fi.fds_inst_id);

grant select on  hs_all_dd to hs_admin_role;

drop public synonym hs_all_dd;
create public synonym hs_all_dd for hs_all_dd;

-- 
--#############################################################################
-- 
--#############################################################################
-- 
-- HS_ALL_INITS
-- This view shows all of the init values for a given class and inst.  Inst
-- level inits override inits defined at the class level.

create or replace view hs_all_inits
(init_value_name,init_value,init_value_type,fds_class_name,
fds_inst_name) as
/*this clause for the inst level inits*/
select i.init_value_name,i.init_value,i.init_value_type,fc.fds_class_name,
  fi.fds_inst_name
from hs$_inst_init i, hs$_fds_class fc, hs$_fds_inst fi
where i.fds_inst_id = fi.fds_inst_id
and fi.fds_class_id = fc.fds_class_id 
union
/*this clause for the class level inits*/
select c.init_value_name,c.init_value,c.init_value_type,fc.fds_class_name,
  fi.fds_inst_name
from hs$_class_init c, hs$_fds_class fc, hs$_fds_inst fi
where fc.fds_class_id = fi.fds_class_id
and fc.fds_class_id = c.fds_class_id
and not exists
  (select 1 from hs$_inst_init i
   where i.fds_inst_id = fi.fds_inst_id
   and i.init_value_name = c.init_value_name);
   
grant select on  hs_all_inits to hs_admin_role;

drop public synonym hs_all_inits;
create public synonym hs_all_inits for hs_all_inits;


-- Install the dbms_hs.package
@@dbmshs
@@prvths.plb

-- Install seed data
begin

  dbms_hs.create_base_caps(1, 'op1 > op2');
  dbms_hs.create_base_caps(2, 'op1 < op2');
  dbms_hs.create_base_caps(3, 'op1 >= op2');
  dbms_hs.create_base_caps(4, 'op1 <= op2');
  dbms_hs.create_base_caps(5, 'op1 = op2');
  dbms_hs.create_base_caps(6, 'op1 <> op2');
  dbms_hs.create_base_caps(7, 'INTERSECT');
  dbms_hs.create_base_caps(8, 'UNION');
  dbms_hs.create_base_caps(9, 'MINUS');
  dbms_hs.create_base_caps(10, 'op1 + op2 (arithmetic)');
  dbms_hs.create_base_caps(11, 'op1 - op2 (arithmetic)');
  dbms_hs.create_base_caps(12, 'op1 * op2 (arithmetic)');
  dbms_hs.create_base_caps(13, 'op1 / op2 (arithmetic)');
  dbms_hs.create_base_caps(14, '-op1 (arithmetic)');
  dbms_hs.create_base_caps(15, 'AVG(op1)');
  dbms_hs.create_base_caps(16, 'AVG(op1, op2)');
  dbms_hs.create_base_caps(17, 'SUM(op1)');
  dbms_hs.create_base_caps(18, 'SUM(op1, op2)');
  dbms_hs.create_base_caps(19, 'COUNT(op1)');
  dbms_hs.create_base_caps(20, 'COUNT(op1, op2)');
  dbms_hs.create_base_caps(21, 'MIN(op1)');
  dbms_hs.create_base_caps(22, 'MIN(op1, op2)');
  dbms_hs.create_base_caps(23, 'MAX(op1)');
  dbms_hs.create_base_caps(24, 'MAX(op1, op2)');
  dbms_hs.create_base_caps(25, 'DESC (descending)');
  dbms_hs.create_base_caps(26, 'TO_NUMBER(op1)');
  dbms_hs.create_base_caps(27, 'TO_NUMBER(op1, op2)');
  dbms_hs.create_base_caps(28, 'TO_NUMBER(op1, op2, op3)');
  dbms_hs.create_base_caps(29, 'TO_CHAR(op1)');
  dbms_hs.create_base_caps(30, 'TO_CHAR(op1, op2)');
  dbms_hs.create_base_caps(31, 'TO_CHAR(op1, op2, op3)');
  dbms_hs.create_base_caps(32, 'NVL(op1, op2)');
  dbms_hs.create_base_caps(33, 'CHARTOROWID(op1)');
  dbms_hs.create_base_caps(34, 'ROWIDTOCHAR(op1)');
  dbms_hs.create_base_caps(35, 'op1 LIKE op2');
  dbms_hs.create_base_caps(36, 'op1 NOT LIKE op2');
  dbms_hs.create_base_caps(37, 'op1 || op2');
  dbms_hs.create_base_caps(38, 'SUBSTR(op1, op2)');
  dbms_hs.create_base_caps(39, 'SUBSTR(op1, op2, op3)');
  dbms_hs.create_base_caps(40, 'LENGTH(op1)');
  dbms_hs.create_base_caps(41, 'INSTR(op1, op2)');
  dbms_hs.create_base_caps(42, 'INSTR(op1, op2, op3)');
  dbms_hs.create_base_caps(43, 'INSTR(op1, op2, op3, op4)');
  dbms_hs.create_base_caps(44, 'LOWER(op1)');
  dbms_hs.create_base_caps(45, 'UPPER(op1)');
  dbms_hs.create_base_caps(46, 'ASCII(op1)');
  dbms_hs.create_base_caps(47, 'CHR(op1)');
  dbms_hs.create_base_caps(49, 'ROUND(op1)');
  dbms_hs.create_base_caps(50, 'ROUND(op1, op2)');
  dbms_hs.create_base_caps(51, 'TRUNC(op1)');
  dbms_hs.create_base_caps(52, 'TRUNC(op1, op2)');
  dbms_hs.create_base_caps(53, 'MOD(op1, op2)');
  dbms_hs.create_base_caps(54, 'ABS(op1)');
  dbms_hs.create_base_caps(55, 'SIGN(op1)');
  dbms_hs.create_base_caps(56, 'VSIZE(op1)');
  dbms_hs.create_base_caps(57, 'op1 IS NULL');
  dbms_hs.create_base_caps(58, 'op1 IS NOT NULL');
  dbms_hs.create_base_caps(59, 'op1 + op2 (date + number)');
  dbms_hs.create_base_caps(60, 'op1 - op2 (date - number)');
  dbms_hs.create_base_caps(61, 'op1 - op2 (date - date)');
  dbms_hs.create_base_caps(62, 'ADD_MONTHS(op1, op2)');
  dbms_hs.create_base_caps(63, 'MONTHS_BETWEEN(op1, op2)');
  dbms_hs.create_base_caps(64, 'TO_DATE(op1)');
  dbms_hs.create_base_caps(65, 'TO_DATE(op1, op2)');
  dbms_hs.create_base_caps(66, 'TO_DATE(op1, op2, op3)');
  dbms_hs.create_base_caps(67, 'SYSDATE');
  dbms_hs.create_base_caps(68, 'LAST_DAY(op1)');
  dbms_hs.create_base_caps(69, 'NEW_TIME(op1, op2, op3)');
  dbms_hs.create_base_caps(70, 'NEXT_DAY(op1, op2)');
  dbms_hs.create_base_caps(71, 'Internal date-related conversion');
  dbms_hs.create_base_caps(72, 'Internal date-related conversion');
  dbms_hs.create_base_caps(73, 'Internal date-related conversion');
  dbms_hs.create_base_caps(74, 'TO_CHAR(op1) (date to string');
  dbms_hs.create_base_caps(75, 'TO_CHAR(op1, op2) (date to string)');
  dbms_hs.create_base_caps(76, 'TO_CHAR(op1, op2, op3) date to str');
  dbms_hs.create_base_caps(92, 'DUMP(op1)');
  dbms_hs.create_base_caps(93, 'DUMP(op1, op2)');
  dbms_hs.create_base_caps(94, 'DUMP(op1, op2, op3)');
  dbms_hs.create_base_caps(95, 'DUMP(op1, op2, op3, op4)');
  dbms_hs.create_base_caps(97, 'ROUND(op1) (date)');
  dbms_hs.create_base_caps(98, 'ROUND(op1, op2) (date)');
  dbms_hs.create_base_caps(99, 'TRUNC(op1) (date)');
  dbms_hs.create_base_caps(100, 'TRUNC(op1, op2) (date)');
  dbms_hs.create_base_caps(101, 'FLOOR(op1)');
  dbms_hs.create_base_caps(102, 'CEIL(op1)');
  dbms_hs.create_base_caps(103, 'DECODE(op1, op2, op3)');
  dbms_hs.create_base_caps(104, 'DECODE(op1, op2, op3, op4)');
  dbms_hs.create_base_caps(105, 'DECODE');
  dbms_hs.create_base_caps(106, 'LPAD(op1, op2)');
  dbms_hs.create_base_caps(107, 'LPAD(op1, op2, op3)');
  dbms_hs.create_base_caps(108, 'RPAD(op1, op2)');
  dbms_hs.create_base_caps(109, 'RPAD(op1, op2, op3)');
  dbms_hs.create_base_caps(111, 'POWER(op1, op2)');
  dbms_hs.create_base_caps(118, 'INITCAP(op1)');
  dbms_hs.create_base_caps(119, 'TRANSLATE(op1, op2, op3)');
  dbms_hs.create_base_caps(120, 'LTRIM(op1)');
  dbms_hs.create_base_caps(121, 'LTRIM(op1, op2)');
  dbms_hs.create_base_caps(122, 'RTRIM(op1)');
  dbms_hs.create_base_caps(123, 'RTRIM(op1, op2)');
  dbms_hs.create_base_caps(124, 'GREATEST(op1, op2)');
  dbms_hs.create_base_caps(125, 'GREATEST');
  dbms_hs.create_base_caps(126, 'LEAST(op1, op2)');
  dbms_hs.create_base_caps(127, 'LEAST');
  dbms_hs.create_base_caps(128, 'SQRT(op1)');
  dbms_hs.create_base_caps(129, 'VARIANCE(op1)');
  dbms_hs.create_base_caps(130, 'VARIANCE(op1, op2)');
  dbms_hs.create_base_caps(131, 'STDDEV(op1)');
  dbms_hs.create_base_caps(132, 'STDDEV(op1, op2)');
  dbms_hs.create_base_caps(133, 'op1 LIKE op2 (indexed column)');
  dbms_hs.create_base_caps(134, 'RAWTOHEX(op1)');
  dbms_hs.create_base_caps(135, 'HEXTORAW(op1)');
  dbms_hs.create_base_caps(137, 'NOT NVL(op1, op2)');
  dbms_hs.create_base_caps(138, 'USERENV(op1)');
  dbms_hs.create_base_caps(141, 'TO_CHAR(op1), (special case)');
  dbms_hs.create_base_caps(143, 'BITAND(op1, op2)');
  dbms_hs.create_base_caps(144, 'CONVERT(op1, op2)');
  dbms_hs.create_base_caps(145, 'CONVERT(op1, op2, op3)');
  dbms_hs.create_base_caps(146, 'REPLACE(op1, op2)');
  dbms_hs.create_base_caps(147, 'REPLACE(op1, op2, op3)');
  dbms_hs.create_base_caps(148, 'NLSSORT(op1)');
  dbms_hs.create_base_caps(149, 'NLSSORT(op1, op2)');
  dbms_hs.create_base_caps(153, 'op1 LIKE op2 ESCAPE op3');
  dbms_hs.create_base_caps(154, 'op1 NOT LIKE op2 ESCAPE op3');
  dbms_hs.create_base_caps(155, 'UNION ALL');
  dbms_hs.create_base_caps(156, 'COS(op1)');
  dbms_hs.create_base_caps(157, 'SIN(op1)');
  dbms_hs.create_base_caps(158, 'TAN(op1)');
  dbms_hs.create_base_caps(159, 'COSH(op1)');
  dbms_hs.create_base_caps(160, 'SINH(op1)');
  dbms_hs.create_base_caps(161, 'TANH(op1)');
  dbms_hs.create_base_caps(162, 'EXP(op1)');
  dbms_hs.create_base_caps(163, 'LN(op1)');
  dbms_hs.create_base_caps(164, 'LOG(op1)');
  dbms_hs.create_base_caps(179, 'INSTRB(op1, op2)');
  dbms_hs.create_base_caps(180, 'INSTRB(op1, op2, op3)');
  dbms_hs.create_base_caps(181, 'INSTRB(op1, op2, op3, op4)');
  dbms_hs.create_base_caps(182, 'LENGTHB(op1)');
  dbms_hs.create_base_caps(183, 'SUBSTRB(op1, op2)');
  dbms_hs.create_base_caps(184, 'SUBSTRB(op1, op2, op3)');
  dbms_hs.create_base_caps(194, 'LUB(op1)');
  dbms_hs.create_base_caps(195, 'GLB(op1)');
  dbms_hs.create_base_caps(196, 'LEAST_UB(op1, op2)');
  dbms_hs.create_base_caps(197, 'LEAST_UB');
  dbms_hs.create_base_caps(198, 'GREATEST_LB(op1, op2)');
  dbms_hs.create_base_caps(199, 'GREATEST_LB');
  dbms_hs.create_base_caps(1000, 'multicolumn: (a,b,c)=');
  dbms_hs.create_base_caps(1001, 'join');
  dbms_hs.create_base_caps(1002, 'outer join');
  dbms_hs.create_base_caps(1003, 'delimited IDs: "id"');
  dbms_hs.create_base_caps(1004, 'SELECT DISTINCT');
  dbms_hs.create_base_caps(1005, 'DISTINCT in aggregate functions');
  dbms_hs.create_base_caps(1006, 'ROWNUM');
  dbms_hs.create_base_caps(1007, 'subquery');
  dbms_hs.create_base_caps(1008, 'GROUP BY');
  dbms_hs.create_base_caps(1009, 'HAVING');
  dbms_hs.create_base_caps(1010, 'ORDER BY');
  dbms_hs.create_base_caps(1011, 'CONNECT BY');
  dbms_hs.create_base_caps(1012, 'START WITH');
  dbms_hs.create_base_caps(1013, 'WHERE');
  dbms_hs.create_base_caps(1014, 'callback');
  dbms_hs.create_base_caps(1015, 'add redundant local filters');
  dbms_hs.create_base_caps(1016, 'ROWID');
  dbms_hs.create_base_caps(1017, 'ANY');
  dbms_hs.create_base_caps(1018, 'ALL');
  dbms_hs.create_base_caps(1019, 'EXISTS');
  dbms_hs.create_base_caps(1020, 'NOT EXISTS');
  dbms_hs.create_base_caps(1021, 'nls parameters');
  dbms_hs.create_base_caps(1022, 'describe index');
  dbms_hs.create_base_caps(1023, 'distributed read consistency');
  dbms_hs.create_base_caps(1024, 'bundled calls');
  dbms_hs.create_base_caps(1025, 'evaluate USER, UID, SYDATE local');
  dbms_hs.create_base_caps(1026, 'KGL operation for PL/SQL RPC');
  dbms_hs.create_base_caps(1027, 'NVL: change ANSI to ORA compare');
  dbms_hs.create_base_caps(1028, 'remote mapping of queries');
  dbms_hs.create_base_caps(1029, '2PC type (RO-SS-CC-PREP/2P-2PCC)');
  dbms_hs.create_base_caps(1030, 'streamed protocol version number');
  dbms_hs.create_base_caps(1031, 'special non-optdef functions');
  dbms_hs.create_base_caps(1032, 'CURRVAL and NEXTVAL');
  dbms_hs.create_base_caps(1033, 'hints (inline comments and aliases');
  /* dbms_hs.create_base_caps(1982, 'Data Dictionary Translations'); */
  dbms_hs.create_base_caps(1969, 'How to handle PUBLIC schema');
  dbms_hs.create_base_caps(1970, 'Subquery in having clause is supported');
  dbms_hs.create_base_caps(1971, 'Do not close and re-parse on re-exec of SELECTs');
  dbms_hs.create_base_caps(1972, 'Informix related cap: Add space before negative numeric literals');
  dbms_hs.create_base_caps(1973, 'Informix related cap: Add extra parenthesis for update sub-queries to make it a list');
  dbms_hs.create_base_caps(1974, 'DB2-related cap: Change empty str assigns to null assigns');
  dbms_hs.create_base_caps(1975, 'DB2-related cap: Zero length bind not same as null bind');
  dbms_hs.create_base_caps(1976, 'DB2-related cap: Add space after comma');
  dbms_hs.create_base_caps(1977, 'DB2-related cap: Order-by clause contains only numbers');
  dbms_hs.create_base_caps(1978, 'DB2-related cap: Change empty string comparisons to is null');
  dbms_hs.create_base_caps(1979, 'Implicit Coercion cap: Comparison of two objrefs');
  dbms_hs.create_base_caps(1980, 'Implicit Coercion cap: Comparison of objref and bindvar');
  dbms_hs.create_base_caps(1981, 'Implicit Coercion cap: Comparison of objref and literal');
  dbms_hs.create_base_caps(1982, 'Implicit Coercion cap: Comparison of two bindvars');
  dbms_hs.create_base_caps(1983, 'Implicit Coercion cap: Comparison of bindvar and literal');
  dbms_hs.create_base_caps(1984, 'Implicit Coercion cap: Comparison of two literals');
  dbms_hs.create_base_caps(1985, 'Implicit Coercion cap: Assignment of objref to column');
  dbms_hs.create_base_caps(1986, 'Implicit Coercion cap: Assignment of bindvar to column');
  dbms_hs.create_base_caps(1987, 'Implicit Coercion cap: Assignment of literal to column');
  dbms_hs.create_base_caps(1988, 'RPC Bundling Capability');
  dbms_hs.create_base_caps(1989, 'hoatcis() call capability');
  dbms_hs.create_base_caps(1990, 'Quote Owner names in SQL statements');
  dbms_hs.create_base_caps(1991, 'Map Alias to table names in non-select statements');
  dbms_hs.create_base_caps(1992, 'Send Delimited IDs to FDS');
  dbms_hs.create_base_caps(1993, 'HOA Describe Table Call Capability');
  dbms_hs.create_base_caps(1994, 'Raw literal format');
  dbms_hs.create_base_caps(1995, 'FOR UPDATE syntax mapping');
  dbms_hs.create_base_caps(1996, 'Replace NULLs in select list with other constant');
  dbms_hs.create_base_caps(1997, 'flush describe table cache');
  dbms_hs.create_base_caps(1998, 'length of physical part of rowid');
  dbms_hs.create_base_caps(1999, 'Bind to parameter mapping');
  dbms_hs.create_base_caps(2000, 'SELECT ... FOR UPDATE');
  dbms_hs.create_base_caps(2001, 'SELECT');
  dbms_hs.create_base_caps(2002, 'UPDATE');
  dbms_hs.create_base_caps(2003, 'DELETE');
  dbms_hs.create_base_caps(2004, 'INSERT ... VALUES (...)');
  dbms_hs.create_base_caps(2005, 'INSERT ... SELECT ...');
  dbms_hs.create_base_caps(2006, 'LOCK TABLE');
  dbms_hs.create_base_caps(2007, 'ROLLBACK TO SAVEPOINT ...');
  dbms_hs.create_base_caps(2008, 'SAVEPOINT ...');
  dbms_hs.create_base_caps(2009, 'SET TRANSACTION READ ONLY');
  dbms_hs.create_base_caps(3000, 'VARCHAR2');
  dbms_hs.create_base_caps(3001, 'INTEGER');
  dbms_hs.create_base_caps(3002, 'DECIMAL');
  dbms_hs.create_base_caps(3003, 'FLOAT');
  dbms_hs.create_base_caps(3004, 'DATE');
  dbms_hs.create_base_caps(3005, 'VARCHAR');
  dbms_hs.create_base_caps(3006, 'SMALL INTEGER');
  dbms_hs.create_base_caps(3007, 'RAW');
  dbms_hs.create_base_caps(3008, 'VAR RAW');
  dbms_hs.create_base_caps(3009, '? RAW');
  dbms_hs.create_base_caps(3010, 'SMALL FLOAT');
  dbms_hs.create_base_caps(3011, 'LONG INT QUADWORD');
  dbms_hs.create_base_caps(3012, 'LEFT OVERPUNCH');
  dbms_hs.create_base_caps(3013, 'RIGHT OVERPUNCH');
  dbms_hs.create_base_caps(3014, 'ROWID');
  dbms_hs.create_base_caps(3015, 'LEFT SEPARATE');
  dbms_hs.create_base_caps(3016, 'RIGHT SEPARATE');
  dbms_hs.create_base_caps(3017, 'OS DATE');
  dbms_hs.create_base_caps(3018, 'OS FULL ==> DATE + TIME');
  dbms_hs.create_base_caps(3019, 'OS TIME');
  dbms_hs.create_base_caps(3020, 'UNSIGNED SMALL INTEGER');
  dbms_hs.create_base_caps(3021, 'BYTE');
  dbms_hs.create_base_caps(3022, 'UNSIGNED BYTE');
  dbms_hs.create_base_caps(3023, 'UNSIGNED INTEGER');
  dbms_hs.create_base_caps(3024, 'CHAR INTEGER');
  dbms_hs.create_base_caps(3025, 'CHAR FLOAT');
  dbms_hs.create_base_caps(3026, 'CHAR DECIMAL');
  dbms_hs.create_base_caps(3027, 'LONG');
  dbms_hs.create_base_caps(3028, 'VARLONG');
  dbms_hs.create_base_caps(3029, 'OS RDATE');
  dbms_hs.create_base_caps(3030, '(RELATIVE) RECORD ADDRESS');
  dbms_hs.create_base_caps(3031, '(RELATIVE) RECORD NUMBER');
  dbms_hs.create_base_caps(3032, 'VARGRAPHIC');
  dbms_hs.create_base_caps(3033, 'VARNUM');
  dbms_hs.create_base_caps(3034, 'NUMBER');
  dbms_hs.create_base_caps(3035, 'ANSI FIXED CHAR');
  dbms_hs.create_base_caps(3036, 'LONG RAW');
  dbms_hs.create_base_caps(3037, 'LONG VARRAW');
  dbms_hs.create_base_caps(3038, 'MLSLABEL');
  dbms_hs.create_base_caps(3039, 'RAW MLSLABEL');
  dbms_hs.create_base_caps(3040, 'CHARZ');
  dbms_hs.create_base_caps(3041, 'BINARY INTEGER');
  dbms_hs.create_base_caps(3042, 'ORACLE DATE');
  dbms_hs.create_base_caps(3043, 'BOOLEAN');
  dbms_hs.create_base_caps(3045, 'UNSIGNED LONG INTEGER');
  dbms_hs.create_base_caps(3046, 'ODBC CHAR DECIMAL');

/*------------------------------------------------*/
/*  DD translations used to test HS installation  */
/*------------------------------------------------*/

  dbms_hs.create_base_dd('DD_TKHODDTR1', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR3', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR5', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR6', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR7', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR8', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR9', NULL);
  dbms_hs.create_base_dd('DD_TKHODDTR10', NULL);
/*
    Registered elsewhere as a standard table name:
  dbms_hs.create_base_dd('USER_SYNONYMS', NULL);
    Self-registered:
  dbms_hs.create_base_dd('TKHODDAU', NULL);
*/

/*--------------------------------------------*/
/*  DD translations accessible to all agents  */
/*--------------------------------------------*/


  dbms_hs.create_base_dd('ALL_CATALOG', NULL);
  dbms_hs.create_base_dd('ALL_COL_COMMENTS', NULL);
  dbms_hs.create_base_dd('ALL_COL_PRIVS', NULL);
  dbms_hs.create_base_dd('ALL_COL_PRIVS_MADE', NULL);
  dbms_hs.create_base_dd('ALL_COL_PRIVS_RECD', NULL);
  dbms_hs.create_base_dd('ALL_CONSTRAINTS', NULL);
  dbms_hs.create_base_dd('ALL_CONS_COLUMNS', NULL);
  dbms_hs.create_base_dd('ALL_DB_LINKS', NULL);
  dbms_hs.create_base_dd('ALL_DEF_AUDIT_OPTS', NULL);
  dbms_hs.create_base_dd('ALL_DEPENDENCIES', NULL);
  dbms_hs.create_base_dd('ALL_ERRORS', NULL);
  dbms_hs.create_base_dd('ALL_INDEXES', NULL);
  dbms_hs.create_base_dd('ALL_IND_COLUMNS', NULL);
  dbms_hs.create_base_dd('ALL_OBJECTS', NULL);
  dbms_hs.create_base_dd('ALL_SEQUENCES', NULL);
  dbms_hs.create_base_dd('ALL_SNAPSHOTS', NULL);
  dbms_hs.create_base_dd('ALL_SOURCE', NULL);
  dbms_hs.create_base_dd('ALL_SYNONYMS', NULL);
  dbms_hs.create_base_dd('ALL_TABLES', NULL);
  dbms_hs.create_base_dd('ALL_TAB_COLUMNS', NULL);
  dbms_hs.create_base_dd('ALL_TAB_COMMENTS', NULL);
  dbms_hs.create_base_dd('ALL_TAB_PRIVS', NULL);
  dbms_hs.create_base_dd('ALL_TAB_PRIVS_MADE', NULL);
  dbms_hs.create_base_dd('ALL_TAB_PRIVS_RECD', NULL);
  dbms_hs.create_base_dd('ALL_TRIGGERS', NULL);
  dbms_hs.create_base_dd('ALL_USERS', NULL);
  dbms_hs.create_base_dd('ALL_VIEWS', NULL);
  dbms_hs.create_base_dd('AUDIT_ACTIONS', NULL);
  dbms_hs.create_base_dd('COLUMN_PRIVILEGES', NULL);
  dbms_hs.create_base_dd('DBA_CATALOG', NULL);
  dbms_hs.create_base_dd('DBA_COL_COMMENTS', NULL);
  dbms_hs.create_base_dd('DBA_COL_PRIVS', NULL);
  dbms_hs.create_base_dd('DBA_OBJECTS', NULL);
  dbms_hs.create_base_dd('DBA_ROLES', NULL);
  dbms_hs.create_base_dd('DBA_ROLE_PRIVS', NULL);
  dbms_hs.create_base_dd('DBA_SYS_PRIVS', NULL);
  dbms_hs.create_base_dd('DBA_TABLES', NULL);
  dbms_hs.create_base_dd('DBA_TAB_COLUMNS', NULL);
  dbms_hs.create_base_dd('DBA_TAB_COMMENTS', NULL);
  dbms_hs.create_base_dd('DBA_TAB_PRIVS', NULL);
  dbms_hs.create_base_dd('DBA_USERS', NULL);
  dbms_hs.create_base_dd('DICTIONARY', NULL);
  dbms_hs.create_base_dd('DICT_COLUMNS', NULL);
  dbms_hs.create_base_dd('DUAL', NULL);
  dbms_hs.create_base_dd('INDEX_STATS', NULL);
  dbms_hs.create_base_dd('PRODUCT_USER_PROFILE', NULL);
  dbms_hs.create_base_dd('RESOURCE_COST', NULL);
  dbms_hs.create_base_dd('ROLE_ROLE_PRIVS', NULL);
  dbms_hs.create_base_dd('ROLE_SYS_PRIVS', NULL);
  dbms_hs.create_base_dd('ROLE_TAB_PRIVS', NULL);
  dbms_hs.create_base_dd('SESSION_PRIVS', NULL);
  dbms_hs.create_base_dd('SESSION_ROLES', NULL);
  dbms_hs.create_base_dd('TABLE_PRIVILEGES', NULL);
  dbms_hs.create_base_dd('USER_AUDIT_OBJECT', NULL);
  dbms_hs.create_base_dd('USER_AUDIT_SESSION', NULL);
  dbms_hs.create_base_dd('USER_AUDIT_STATEMENT', NULL);
  dbms_hs.create_base_dd('USER_AUDIT_TRAIL', NULL);
  dbms_hs.create_base_dd('USER_CATALOG', NULL);
  dbms_hs.create_base_dd('USER_CLUSTERS', NULL);
  dbms_hs.create_base_dd('USER_CLU_COLUMNS', NULL);
  dbms_hs.create_base_dd('USER_COL_COMMENTS', NULL);
  dbms_hs.create_base_dd('USER_COL_PRIVS', NULL);
  dbms_hs.create_base_dd('USER_COL_PRIVS_MADE', NULL);
  dbms_hs.create_base_dd('USER_COL_PRIVS_RECD', NULL);
  dbms_hs.create_base_dd('USER_CONSTRAINTS', NULL);
  dbms_hs.create_base_dd('USER_CONS_COLUMNS', NULL);
  dbms_hs.create_base_dd('USER_DB_LINKS', NULL);
  dbms_hs.create_base_dd('USER_DEPENDENCIES', NULL);
  dbms_hs.create_base_dd('USER_ERRORS', NULL);
  dbms_hs.create_base_dd('USER_EXTENTS', NULL);
  dbms_hs.create_base_dd('USER_FREE_SPACE', NULL);
  dbms_hs.create_base_dd('USER_INDEXES', NULL);
  dbms_hs.create_base_dd('USER_IND_COLUMNS', NULL);
  dbms_hs.create_base_dd('USER_OBJECTS', NULL);
  dbms_hs.create_base_dd('USER_OBJ_AUDIT_OPTS', NULL);
  dbms_hs.create_base_dd('USER_RESOURCE_LIMITS', NULL);
  dbms_hs.create_base_dd('USER_ROLE_PRIVS', NULL);
  dbms_hs.create_base_dd('USER_SEGMENTS', NULL);
  dbms_hs.create_base_dd('USER_SEQUENCES', NULL);
  dbms_hs.create_base_dd('USER_SNAPSHOT_LOGS', NULL);
  dbms_hs.create_base_dd('USER_SOURCE', NULL);
  dbms_hs.create_base_dd('USER_SYNONYMS', NULL);
  dbms_hs.create_base_dd('USER_SYS_PRIVS', NULL);
  dbms_hs.create_base_dd('USER_TABLES', NULL);
  dbms_hs.create_base_dd('USER_TABLESPACES', NULL);
  dbms_hs.create_base_dd('USER_TAB_COLUMNS', NULL);
  dbms_hs.create_base_dd('USER_TAB_COMMENTS', NULL);
  dbms_hs.create_base_dd('USER_TAB_PRIVS', NULL);
  dbms_hs.create_base_dd('USER_TAB_PRIVS_MADE', NULL);
  dbms_hs.create_base_dd('USER_TAB_PRIVS_RECD', NULL);
  dbms_hs.create_base_dd('USER_TRIGGERS', NULL);
  dbms_hs.create_base_dd('USER_TS_QUOTAS', NULL);
  dbms_hs.create_base_dd('USER_USERS', NULL);
  dbms_hs.create_base_dd('USER_VIEWS', NULL);

/*--------------------------------------------*/
/*      HS Internal Privileges                */
/*--------------------------------------------*/

  dbms_hs_utl.create_privilege ('CREATE_LIBRARY', 'Y', 'N');
  dbms_hs_utl.create_privilege ('EXECUTE', 'N', 'Y');


/*--------------------------------------------------------*/
/*  Pseudo-FDS class for BITE (Built-In Test Environment  */
/*--------------------------------------------------------*/

  dbms_hs.create_fds_class('BITE', 'Built-In Test Environment');
end;
/

commit;
