1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
| --
-- create journal
--
-- 13.01.04 JT erstellt
--
-- CREATE TABLESPACE "CCSJOURNAL"
-- LOGGING
-- DATAFILE 'D:\ORACLE\ORADATA\CCS\CCSJOURNAL.dbf' SIZE 50M
-- AUTOEXTEND
-- ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
-- SEGMENT SPACE MANAGEMENT AUTO
--/
--
-- SEQUENCE drop , zur Sicherheit
--
-- DROP SEQUENCE SEQJNLID /
--
-- SEQUENCE create , Nummernkreis für table JNL
--
CREATE SEQUENCE "CCS2000"."SEQJNLID"
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE
ORDER
/
--
-- FUNCTION create , generiert ID für surrogate Key in table JNL
--
create or replace function JNLPLUS return number is
newref number(10,0);
begin
select SEQJNLID.NEXTVAL into newref from DUAL;
return newref;
end JNLPLUS;
/
--
-- create table jnl
--
-- DROP TABLE "CCS2000"."JNL" /
CREATE TABLE "CCS2000"."JNL"
(
"JNLID" NUMBER(13) NOT NULL,
"POS" NUMBER(10) DEFAULT 1 NOT NULL,
"VNR" NUMBER(10) DEFAULT 0 NOT NULL,
"MODUL" VARCHAR2(80) DEFAULT 'None' NOT NULL,
"JLEVEL" NUMBER(10) DEFAULT 1 NOT NULL,
"EVENT" VARCHAR2(25) DEFAULT 'None' NOT NULL,
"JSTAT" NUMBER(10) DEFAULT 1 NOT NULL,
"CATEGORY" VARCHAR2(25) DEFAULT 'None' NOT NULL,
"CATEGORYSUB" VARCHAR2(25) DEFAULT 'None' NOT NULL,
"USERNAME" VARCHAR2(25) DEFAULT 'None' NOT NULL,
"TIMESTAMP" DATE NOT NULL,
"MSGCONSTID" VARCHAR2(256) DEFAULT 'None' NOT NULL,
"MSG" VARCHAR2(1024) DEFAULT 'None' NOT NULL,
"SATTR1" VARCHAR2(80),
"SATTR2" VARCHAR2(80),
"SATTR3" VARCHAR2(80),
"MARK" NUMBER(10) DEFAULT 1 NOT NULL,
CONSTRAINT "PK_JOURNAL" PRIMARY KEY("JNLID")
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10K
NEXT 10K
MINEXTENTS 1
MAXEXTENTS 121
PCTINCREASE 50
)
TABLESPACE "CCSJOURNAL"
)
/
-- Trigger create , T_BI_JNL Before Insert ---------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "CCS2000"."T_BI_JNL" BEFORE INSERT ON "CCS2000"."JNL" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE
ri_error EXCEPTION ;
error_number INTEGER ;
error_message CHAR(200) ;
row_count INTEGER ;
BEGIN
:New.JNLID:=JNLPLUS;
EXCEPTION
WHEN ri_error THEN
raise_application_error(error_number, error_message) ;
END;
/
--
-- SEQUENCE drop , zur Sicherheit
--
-- DROP SEQUENCE SEQJNLMSGID /
--
-- SEQUENCE create , Nummernkreis für table JNLMSG
--
CREATE SEQUENCE "CCS2000"."SEQJNLMSGID"
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE
ORDER
/
--
-- FUNCTION create , generiert ID für surrogate Key in table JNLMSG
--
create or replace function JNLMSGPLUS return number is
newref number(10,0);
begin
select SEQJNLMSGID.NEXTVAL into newref from DUAL;
return newref;
end JNLMSGPLUS;
/
--
-- create table jnlmsg
--
-- DROP TABLE "CCS2000"."JNLMSG" /
CREATE TABLE JNLMSG
(
MSGID NUMBER(10,0) NOT NULL ,
MSG VARCHAR2(1024) NULL ,
CONSTRAINT PK_JNLMSG PRIMARY KEY (MSGID)
USING INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 100K
NEXT 10K
MINEXTENTS 1
MAXEXTENTS 300
PCTINCREASE 1
)
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 100K
NEXT 10K
MINEXTENTS 1
MAXEXTENTS 300
PCTINCREASE 1
)
TABLESPACE CCSJournal
/
-- Trigger create , T_BI_JNL Before Insert ---------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "CCS2000"."T_BI_JNLMSG" BEFORE INSERT ON "CCS2000"."JNLMSG" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE
ri_error EXCEPTION ;
error_number INTEGER ;
error_message CHAR(200) ;
row_count INTEGER ;
BEGIN
:New.MSGID:=JNLMSGPLUS;
EXCEPTION
WHEN ri_error THEN
raise_application_error(error_number, error_message) ;
END;
/
--
-- synonyme drop
--
-- DROP PUBLIC SYNONYM SEQJNLID /
-- DROP PUBLIC SYNONYM JNLPLUS /
-- DROP PUBLIC SYNONYM JNL /
-- DROP PUBLIC SYNONYM SEQJNLMSGID /
-- DROP PUBLIC SYNONYM JNLMSGPLUS /
-- DROP PUBLIC SYNONYM JNLMSG /
--
-- synonyme create
--
CREATE PUBLIC SYNONYM SEQJNLID FOR "CCS2000"."SEQJNLMSGID" /
CREATE PUBLIC SYNONYM JNLPLUS FOR "CCS2000"."JNLPLUS" /
CREATE PUBLIC SYNONYM JNL FOR "CCS2000"."JNL" /
CREATE PUBLIC SYNONYM SEQJNLMSGID FOR "CCS2000"."SEQJNLMSGID" /
CREATE PUBLIC SYNONYM JNLMSGPLUS FOR "CCS2000"."JNLMSGPLUS" /
CREATE PUBLIC SYNONYM JNLMSG FOR "CCS2000"."JNLMSG" /
--
-- ready
-- |
