Rem
Rem Copyright (c) 2000, 2005, Oracle. All rights reserved.  
Rem
Rem NAME
Rem   wb_rt_catalog.sql
Rem FUNCTION
Rem   Create OWB runtime catalog
Rem NOTES
Rem   - This file can be tested using SQL*Plus.
Rem   - OWB developers must make sure this file is also runnable
Rem     using Runtime Assistant. 
Rem
Rem MODIFIED
Rem    dallan     08/01/05 - 
Rem  G. Watters     04/04/2002 -  Created from wbrtcatalog.sql and wbrtutil.sql
Rem  Yu.Gong        04/06/2001 -  Specify index tablespace
Rem  Yu.Gong        12/06/2000 -  Created.
Rem

--------------------------------------------------------------------
-- WB_RT_PARAMETERS                                               -- 
-- There is a new structure in OWB 3i.                            --
-- 1. (job_id, name) is primary key. This allows each job to have --
--    a set of local parameters.                                  --
-- 2. A global parameter has job_id = -1.                         --
-- 3. A parameter can be modifiable (Y) or read-only (N).         --
--------------------------------------------------------------------
-- Create new
CREATE TABLE wb_rt_parameters (
  name           VARCHAR2(30)
 ,value          VARCHAR2(80)
 ,job_id         NUMBER
 ,modifiable     CHAR(1)
 ,last_modified  DATE
 ,CONSTRAINT wb_rt_parameters_pk PRIMARY KEY (job_id, name) USING INDEX TABLESPACE %tndex
)
;

CREATE OR REPLACE TRIGGER WB_rt_parameters_audit
BEFORE INSERT OR UPDATE
ON WB_RT_PARAMETERS
FOR EACH ROW
BEGIN
   :NEW.last_modified := SYSDATE;
END;
/

--------------------------
-- WB_RT_SEEDED_STRINGS --
--------------------------
Create Table WB_RT_SEEDED_STRINGS (
  rtl_type              VARCHAR2(30)                            NOT NULL
      CONSTRAINT pk_rtl PRIMARY KEY USING INDEX TABLESPACE %tndex
 ,rtl_value             VARCHAR2(80)                            NOT NULL
 ,created_by            VARCHAR2(30)        DEFAULT USER        NOT NULL
 ,creation_date         DATE                DEFAULT SYSDATE     NOT NULL
 ,last_update_date      DATE                DEFAULT SYSDATE     NULL
 ,last_updated_by       VARCHAR2(30)        DEFAULT USER        NULL
)
;

CREATE OR REPLACE TRIGGER WB_rt_seeded_strings_audit
BEFORE INSERT OR UPDATE 
ON WB_RT_SEEDED_STRINGS
FOR EACH ROW
DECLARE
BEGIN
  if INSERTING then
   if :NEW.created_by    is null then :NEW.created_by := USER; end if;
   if :NEW.creation_date is null then :NEW.creation_date := SYSDATE; end if;
   :NEW.last_updated_by := NULL;
   :NEW.last_update_date := NULL;
  end if;
  if UPDATING then
   if :NEW.last_updated_by  is null then :NEW.last_updated_by := USER; end if;
   if :NEW.last_update_date is null then :NEW.last_update_date := SYSDATE; end if;
  end if;
END;
/

--------------
--  TABLES  --
--------------

Create Table WB_RT_EXCEPTIONS (
   row_id               ROWID,
   owner                VARCHAR2(30),
   table_name           VARCHAR2(30),
   constraint           VARCHAR2(30)
)
;


GRANT SELECT,INSERT,UPDATE,DELETE ON WB_RT_EXCEPTIONS TO PUBLIC
;


Create Table WB_RT_POSTLOAD_EXCEPTIONS (
  row_id                ROWID                                   NULL
 ,owner                 VARCHAR2(30)                            NULL
 ,table_name            VARCHAR2(80)                            NULL
 ,constraint            VARCHAR2(30)                            NULL
)
;


---------------
-- SEQUENCES --
---------------
CREATE SEQUENCE wb_rt_temptable_seq START WITH 1 MAXVALUE 10000 CYCLE;
GRANT SELECT ON wb_rt_temptable_seq TO PUBLIC;

CREATE SEQUENCE wb_rt_tempindex_seq START WITH 1 MAXVALUE 10000 CYCLE;
GRANT SELECT ON wb_rt_tempindex_seq TO PUBLIC;

CREATE SEQUENCE wb_rt_tempcons_seq START WITH 1 MAXVALUE 10000 CYCLE;
GRANT SELECT ON wb_rt_tempcons_seq TO PUBLIC;

CREATE OR REPLACE FUNCTION wb_rt_scriptprocessor(script IN CLOB) RETURN NUMBER AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'oracle.wh.runtime.server.script.ScriptProcessor.executeScript(oracle.sql.CLOB) return int';
/