﻿* SET_DB_O.TXT HARASAWA
O-COMMENT_BEGIN
=MESSAGE{CREATE TABLESPACE};
=SET{W_PER='ZZZZ'};
NAME=TABLESPACE

REPEAT=4
=LOGIN_SQL{CREATE TABLESPACE ?W_IS??W_##?_?W_PER? DATAFILE '?W_D##??_P??W_IS??W_##?_?W_PER?.ORA'
= SIZE 2M AUTOEXTEND ON NEXT 1M DEFAULT STORAGE (INITIAL 2M NEXT 1M PCTINCREASE 0) ONLINE}
=   IF{_DB_ERROR!='O1543'}ERROR{?_DB_ERROR??_ERROR?};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=TABLESPACE
=SET{W_PER='ZZZZ'};
NAME=LOGIN
REPEAT=2 (W_RT##)
REPEAT2=4 (W_DIOL$$)
=MESSAGE{EXEC CREAT USER (?W_PER?)}; 
$1+=SET{W_MW='MAST'};
$2+=SET{W_MW='WORK'};
$1+=SET{WPAS=PASSWORD_?W_RT##?1};
$4+=SET{WPAS=PASSWORD_LINK1};
=LOGIN_SQL{CREATE USER ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER? IDENTIFIED BY ?WPAS? DEFAULT TABLESPACE ?W_IS??W_RT##?_?W_MW?_?W_PER?}
=  IF{_DB_ERROR!='O1920'}ERROR{?_DB_ERROR??_ERROR?};
=LOGIN_SQL{GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER?}
=  ERROR{?_DB_ERROR??_ERROR?};
=LOGIN_SQL{GRANT CREATE VIEW TO ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER?}ERROR{?_DB_ERROR??_ERROR?};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=LOGIN
********************************************************************
=MESSAGE{GRANT USER};
=LOGIN_SQL{GRANT DBA TO ?W_IS?REAL_DBO_ZZZZ}ERROR{?_DB_ERROR??_ERROR?};
*******( CREATE VIEW )******************************************************
=MESSAGE{CREATE VIEW};
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL'};
=SET{W_PER='ZZZZ'};
NAME=VIEW
=SET{W_M=_M_?W_REAL_TEST?};
REPEAT=2
*1+=SET{W_OWNER=' IN(''?W_IS??W_REAL_TEST?_DBO_?W_PER?'',''?W_IS??W_REAL_TEST?_DBO_ZZZZ'')'};
1+=SET{W_OWNER=' IN(''?W_IS??W_REAL_TEST?_DBO_?W_PER?'')'};
1-=SET{W_OWNER=' IN(''?W_IS??W_REAL_TEST?_INP_?W_PER?'',''?W_IS??W_REAL_TEST?_OUT_?W_PER?'')'};
* ***( VIEW ?W_M?_SYS_TABLE_VIEW )***/
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_TEXT,ZZZZ_ADD_DATE
=,ZZZZ_CHANGE_DATE)AS
= SELECT CAST('TABLE' AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(SUBSTR(T.OWNER,LENGTH(T.OWNER)-7,3) AS CHAR(3))
=,CAST(T.TABLE_NAME AS NCHAR(30))
=,CAST(NULL AS NVARCHAR2(2000)),CREATED,CAST(NULL AS TIMESTAMP)
= FROM ALL_ALL_TABLES T,ALL_OBJECTS O WHERE
*1+= T.TABLE_NAME LIKE '?W_PER?%' AND
= T.OWNER ?W_OWNER? AND T.TABLE_NAME=OBJECT_NAME
= AND T.TABLE_NAME NOT LIKE 'BIN$%'
= AND O.OWNER ?W_OWNER?
= UNION SELECT CAST('VIEW' AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(SUBSTR(O.OWNER,LENGTH(O.OWNER)-7,3) AS CHAR(3))
=,CAST(VIEW_NAME AS NCHAR(30)),CAST(NULL AS NVARCHAR2(2000))
=,O.CREATED,CAST(NULL AS TIMESTAMP) FROM ALL_VIEWS V
=,ALL_OBJECTS O WHERE
*1+= VIEW_NAME LIKE '?W_PER?%' AND
=     V.OWNER ?W_OWNER? AND V.VIEW_NAME=O.OBJECT_NAME
= AND O.OWNER ?W_OWNER?
= UNION SELECT CAST(SUBSTR(S.TYPE,1,4) AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4))
=,CAST(SUBSTR(S.OWNER,LENGTH(S.OWNER)-7,3) AS CHAR(3))
=,CAST(S.NAME  AS NCHAR(30))
=,CAST(S.TEXT AS NVARCHAR2(2000)),O.CREATED
=,CAST(NULL AS TIMESTAMP) FROM ALL_SOURCE S,ALL_OBJECTS O  
*1+=  S.NAME LIKE '?W_PER?%' AND
= WHERE S.OWNER ?W_OWNER?  AND S.NAME=O.OBJECT_NAME 
=  AND S.TYPE IN('PROCEDURE','FUNCTION','TRIGGER')
= AND O.OWNER ?W_OWNER?
=}IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
* /****( VIEW ?W_M?_SYS_GRANT_VIEW )***/ 
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_GRANT,ZZZZ_SELECT
=,ZZZZ_INSERT,ZZZZ_UPDATE,ZZZZ_DELETE,ZZZZ_EXECUTE)AS SELECT
= CAST(CASE G.OBJECT_TYPE WHEN 'TABLE' THEN 'TABLE'
= ELSE SUBSTR(G.OBJECT_TYPE,1,4) END AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4))
=,CAST(SUBSTR(G.OWNER,LENGTH(G.OWNER)-7,3) AS CHAR(3))
=,CAST(G.OBJECT_NAME  AS NCHAR(30))
=,CAST(CASE WHEN LENGTH(RTRIM(G0.GRANTEE))>8 THEN SUBSTR(G0.GRANTEE,LENGTH(RTRIM(G0.GRANTEE))-7)
= ELSE G0.GRANTEE END AS CHAR(8)),CAST(CASE G0.PRIVILEGE WHEN 'SELECT'
= THEN 'S' ELSE ' ' END AS CHAR(1))
=,CAST(SUBSTR(G1.PRIVILEGE,1,1) AS CHAR(1))
=,CAST(SUBSTR(G2.PRIVILEGE,1,1) AS CHAR(1))
=,CAST(SUBSTR(G3.PRIVILEGE,1,1) AS CHAR(1))
=,CAST(CASE G0.PRIVILEGE WHEN 'EXECUTE' THEN 'E'
= ELSE ' ' END AS CHAR(1)) FROM ALL_OBJECTS G
= INNER JOIN ALL_TAB_PRIVS G0 ON G0.PRIVILEGE
= IN('SELECT','EXECUTE') AND G0.GRANTOR=G.OWNER
= AND G0.TABLE_NAME=G.OBJECT_NAME
= LEFT JOIN ALL_TAB_PRIVS G1 ON G1.PRIVILEGE='INSERT'
= AND G1.GRANTOR=G.OWNER AND G1.TABLE_NAME=G.OBJECT_NAME
= AND G1.GRANTEE=G0.GRANTEE
= LEFT JOIN ALL_TAB_PRIVS G2 ON G2.PRIVILEGE='UPDATE'
= AND G2.GRANTOR=G.OWNER AND G2.TABLE_NAME=G.OBJECT_NAME 
= AND G2.GRANTEE=G0.GRANTEE
= LEFT JOIN ALL_TAB_PRIVS G3 ON G3.PRIVILEGE='DELETE'
= AND G3.GRANTOR=G.OWNER AND G3.TABLE_NAME=G.OBJECT_NAME
= AND G3.GRANTEE=G0.GRANTEE WHERE G.OWNER ?W_OWNER? 
*1+= G.OBJECT_NAME LIKE '?W_PER?%' AND
= AND (OBJECT_TYPE IN('TABLE','VIEW')
= AND G0.PRIVILEGE='SELECT'
= OR OBJECT_TYPE IN('PROCEDURE','TRIGGER')
= AND G0.PRIVILEGE='EXECUTE')
= AND (G0.GRANTEE LIKE 'I_?_SYSTEM?_?W_REAL_TEST?%'
= OR G0.GRANTEE ='PUBLIC')
= AND G.OBJECT_NAME NOT LIKE 'BIN$%' 
=}IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
* ***( VIEW ?W_M?_SYS_COLUMN_VIEW )***/ 
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_SEQ,ZZZZ_DATA_NAME
=,ZZZZ_DEFAULT_TYPE,ZZZZ_DEFAULT_LENGTH1,ZZZZ_DEFAULT_LENGTH2
=,ZZZZ_DEFAULT,ZZZZ_NULL)AS
= SELECT CAST(CASE G.OBJECT_TYPE WHEN 'TABLE' THEN 'TABLE' ELSE 
= SUBSTR(G.OBJECT_TYPE,1,4)END AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(SUBSTR(C.OWNER,LENGTH(C.OWNER)-7,3) AS CHAR(3))
=,CAST(C.TABLE_NAME AS NCHAR(30)),CAST(C.COLUMN_ID AS INT)
=,CAST(C.COLUMN_NAME AS NCHAR(30)),CAST(CASE C.DATA_TYPE
= WHEN 'NUMBER' THEN CASE WHEN C.DATA_PRECISION IS NULL
= THEN 'INT' ELSE 'DECIMAL' END
= ELSE SUBSTR(C.DATA_TYPE,1,9) END AS CHAR(9))
=,CAST(CASE WHEN C.DATA_TYPE='NUMBER'
= AND C.DATA_PRECISION IS NOT NULL 
= OR C.DATA_TYPE IN('CHAR','VARCHAR2','NCHAR','NVARCHAR2') 
= THEN CASE WHEN C.DATA_PRECISION IS NULL 
= THEN CASE WHEN C.DATA_TYPE IN('NCHAR','NVARCHAR2')
= THEN C.DATA_LENGTH/2 ELSE C.DATA_LENGTH END 
= ELSE C.DATA_PRECISION END
= WHEN C.DATA_TYPE LIKE 'TIMESTAMP%' THEN 17
= WHEN C.DATA_TYPE='TEXT' THEN 4000 ELSE 9 END AS INT)
=,CAST(CASE C.DATA_TYPE WHEN 'NUMBER' THEN CASE
= WHEN C.DATA_SCALE IS NULL THEN 0 ELSE C.DATA_SCALE END
= ELSE 0 END AS INT),DATA_DEFAULT
=,CAST(CASE NULLABLE WHEN 'Y'
= THEN '1' ELSE '0' END AS CHAR(1))
= FROM ALL_TAB_COLUMNS C,ALL_OBJECTS G WHERE 
*1+= C.TABLE_NAME LIKE '?W_PER?%' AND
= C.OWNER ?W_OWNER?
= AND C.OWNER=G.OWNER AND C.TABLE_NAME=G.OBJECT_NAME
= AND C.TABLE_NAME NOT LIKE 'BIN$%' 
=}IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
***************( INDEX_VIEW )******************************************
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}
={(ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_DATA_NAME,ZZZZ_IX,ZZZZ_NO)AS SELECT
= CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4))
=,CAST(SUBSTR(K.TABLE_OWNER,LENGTH(K.TABLE_OWNER)-7,3) AS CHAR(3))
=,CAST(K.TABLE_NAME AS NCHAR(30)),CAST(K.COLUMN_NAME AS NCHAR(30))
=,CAST(SUBSTR(I.INDEX_NAME,LENGTH(RTRIM(I.TABLE_NAME))+1,1) AS CHAR(1))
=,CAST(TO_CHAR(K.COLUMN_POSITION-CASE I.UNIQUENESS WHEN
= 'UNIQUE' THEN 1 ELSE 0 END) AS CHAR(1))
= FROM ALL_INDEXES I,ALL_IND_COLUMNS K WHERE 
*1+= K.TABLE_NAME LIKE '?W_PER?%' AND
=     I.TABLE_NAME=K.TABLE_NAME
= AND I.INDEX_NAME=K.INDEX_NAME 
= AND I.INDEX_NAME IN(I.TABLE_NAME||'0',I.TABLE_NAME||'1'
=,I.TABLE_NAME||'2',I.TABLE_NAME||'3',I.TABLE_NAME||'4'
=,I.TABLE_NAME||'5',I.TABLE_NAME||'6',I.TABLE_NAME||'7'
=,I.TABLE_NAME||'8',I.TABLE_NAME||'9')
= AND K.TABLE_OWNER ?W_OWNER? 
= AND K.TABLE_OWNER=I.TABLE_OWNER
=}IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=VIEW
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST'};
=SET{W_PER='ZZZZ'};
COPY=VIEW
=SET{W_PER='ZZZY'};
COPY=VIEW
* ***( FUNCTION )***/
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL'};
REPEAT=2
=SET{W_M=_M_?W_REAL_TEST?};
=MESSAGE{CREATE FUNCTION};
=DBO_SQL{DROP   FUNCTION ?W_M?_DAY_CHAR_FUNC}NOP{};
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_CHAR_FUNC(ZZZZ_IN IN CHAR,ZZZZ_ADD INT)RETURN CHAR AS ZZZZ_OUT CHAR(8);BEGIN SELECT TO_CHAR(TO_DATE(ZZZZ_IN,'YYYYMMDD')+ZZZZ_ADD,'YYYYMMDD') INTO ZZZZ_OUT FROM DUAL;RETURN (ZZZZ_OUT);END;}
=  IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_SQL{DROP   FUNCTION ?W_M?_DAY_DATE_FUNC}NOP{};
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_DATE_FUNC(ZZZZ_IN IN DATE,ZZZZ_ADD INT)RETURN CHAR AS ZZZZ_OUT CHAR(8);BEGIN SELECT TO_CHAR(ZZZZ_IN+ZZZZ_ADD,'YYYYMMDD') INTO ZZZZ_OUT FROM DUAL;RETURN (ZZZZ_OUT);END;}
=  IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_SQL{DROP   FUNCTION ?W_M?_WEEK_CHAR_FUNC}NOP{};
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_CHAR_FUNC(ZZZZ_IN IN CHAR) RETURN CHAR AS ZZZZ_OUT INT;BEGIN SELECT TO_CHAR(TO_DATE(ZZZZ_IN),'D') INTO ZZZZ_OUT FROM DUAL;RETURN (ZZZZ_OUT);END;}
=  IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_SQL{DROP   FUNCTION ?W_M?_WEEK_DATE_FUNC}NOP{};
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_DATE_FUNC(ZZZZ_IN IN DATE)RETURN CHAR AS ZZZZ_OUT INT;BEGIN SELECT TO_CHAR(ZZZZ_IN,'D') INTO ZZZZ_OUT FROM DUAL;RETURN (ZZZZ_OUT);END;}
= IF{_DB_ERROR!='O955'}ERROR{?_DB_ERROR??_ERROR?};
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST'};
REPEAT=
O-COMMENT_END
