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
| DECLARE
I NUMBER;
BEGIN
SELECT COUNT(*)
INTO I
FROM USER_TABLES
WHERE TABLE_NAME = 'PRP_GMSTLXUSG002TLX002_____DH';
IF I=0
THEN
EXECUTE IMMEDIATE
'CREATE TABLE PRP_GMSTLXUSG002TLX002_____DH '||
'( ORIGINATING_FILE VARCHAR2(249 BYTE) NOT NULL ,'||
' REFERENCE_NUMBER NUMBER(38) NOT NULL ,'||
' ORIGINATOR_ADDRESS VARCHAR2(249 BYTE),'||
' NCC VARCHAR2(249 BYTE),'||
' DOUBLE_SIGNATURE VARCHAR2(249 BYTE),'||
' DESTINATION_ADDRESS VARCHAR2(249 BYTE),'||
' DESTINATION_IATA_COUNTRY VARCHAR2(249 BYTE),'||
' DESTINATION_IATA_CENTER VARCHAR2(249 BYTE),'||
' TELEX_DIAL_CODE NUMBER(38),'||
' NUMBER_OF_MESSAGES VARCHAR2(249 BYTE),'||
' CHARGEABLE_TRAFIC NUMBER(38),'||
' DURATION NUMBER(38),'||
' FIRST_DAY_OF_ACTIVITY VARCHAR2(249 BYTE),'||
' LAST_DAY_OF_ACTIVITY VARCHAR2(249 BYTE),'||
' STATUS NUMBER(38) NOT NULL ,'||
' AVAILABLE VARCHAR2(249 BYTE),'||
' CHG_WHO VARCHAR2(249 BYTE),'||
' FREE_COMMENT VARCHAR2(249 BYTE),'||
' ERROR_CODE VARCHAR2(249 BYTE),'||
' CHG_DATETIME DATE'||
')';
EXECUTE IMMEDIATE
'CREATE UNIQUE INDEX PK_GMSTLXUSG002TLX002_____DH ON PRP_GMSTLXUSG002TLX002_____DH '||
'(ORIGINATING_FILE, REFERENCE_NUMBER, STATUS)'||
'TABLESPACE TS_SVCCONFIG_IDX';
EXECUTE IMMEDIATE
'ALTER TABLE PRP_GMSTLXUSG002TLX002_____DH ADD ('||
'CONSTRAINT PK_GMSTLXUSG002TLX002_____DH '||
'PRIMARY KEY (ORIGINATING_FILE, REFERENCE_NUMBER, STATUS)'||
'USING INDEX TABLESPACE TS_SVCCONFIG_IDX )';
INSERT INTO PRP_GMSTLXUSG002TLX002_____DH
SELECT *
FROM PRP_GMSTLXAPI002TLX002_____DH;
EXECUTE IMMEDIATE 'drop table PRP_GMSTLXAPI002TLX002_____DH';
UPDATE PRP_FIELD_DESCRIPTION
SET PD_TABLE_NAME = 'PRP_GMSTLXUSG002TLX002_____DH'
WHERE PD_TABLE_NAME = 'PRP_GMSTLXAPI002TLX002_____DH';
UPDATE PRP_METATABLE
SET PM_TABLE_NAME = 'PRP_GMSTLXUSG002TLX002_____DH'
WHERE PM_TABLE_NAME = 'PRP_GMSTLXAPI002TLX002_____DH';
COMMIT;
END IF;
END; |
Partager