-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathcreate_hash_view.sql
More file actions
95 lines (92 loc) · 2.86 KB
/
create_hash_view.sql
File metadata and controls
95 lines (92 loc) · 2.86 KB
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
alter session set current_schema=&&v_username.;
DECLARE
VCODE CLOB;
VVERS VARCHAR2(20) := '&&P_VERS.';
VSER VARCHAR2(10) := '&&P_SER.';
VPSU NUMBER := &&P_PSU.;
VSID VARCHAR2(30) := 'VIEW_TESTE';
CURSOR OBJS IS
SELECT OBJECT_OWNER OWNER,
OBJECT_NAME NAME,
OBJECT_TYPE TYPE,
ID CON_ID,
PARENT_ID ORIGIN_CON_ID,
OTHER_XML TEXT
FROM PLAN_TABLE
WHERE STATEMENT_ID = VSID-- and rownum < 1000;
ORDER BY 1,2;
FUNCTION replaceClob
( srcClob IN CLOB,
replaceStr IN varchar2,
replaceWith IN varchar2 )
RETURN CLOB
IS
l_buffer VARCHAR2 (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_clob_len INTEGER;
newClob clob := EMPTY_CLOB;
BEGIN
-- initalize the new clob
dbms_lob.CreateTemporary( newClob, TRUE );
l_clob_len := DBMS_LOB.getlength (srcClob);
WHILE l_pos <= l_clob_len
LOOP
DBMS_LOB.READ (srcClob,l_amount,l_pos,l_buffer);
IF l_buffer IS NOT NULL
THEN
-- replace the text
l_buffer := regexp_replace(l_buffer,replaceStr,replaceWith);
-- write it to the new clob
DBMS_LOB.writeAppend(newClob, LENGTH(l_buffer), l_buffer);
END IF;
l_pos := l_pos + l_amount;
END LOOP;
RETURN newClob;
END;
BEGIN
$IF DBMS_DB_VERSION.VER_LE_11
$THEN
INSERT INTO PLAN_TABLE (STATEMENT_ID, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID, OTHER_XML)
SELECT VSID,
OWNER,
VIEW_NAME,
'VIEW',
NULL CON_ID,
NULL ORIGIN_CON_ID,
TO_LOB(TEXT)
FROM DBA_VIEWS;
$ELSE
INSERT INTO PLAN_TABLE (STATEMENT_ID, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID, OTHER_XML)
SELECT VSID,
OWNER,
VIEW_NAME,
'VIEW',
SYS_CONTEXT('USERENV','CON_ID') CON_ID,
ORIGIN_CON_ID,
TO_LOB(TEXT)
FROM DBA_VIEWS;
INSERT INTO PLAN_TABLE (STATEMENT_ID, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID, OTHER_XML)
SELECT VSID,
OWNER,
VIEW_NAME,
'VIEW',
CON_ID,
ORIGIN_CON_ID,
TEXT_VC
FROM CDB_VIEWS
WHERE CON_ID <> SYS_CONTEXT('USERENV','CON_ID') -- AND CON_ID IN (1,2)
AND ORIGIN_CON_ID = CON_ID;
$END
FOR I IN OBJS
LOOP
VCODE := UPPER(I.TEXT);
VCODE := replaceClob(VCODE,'[[:space:]]*',''); -- Remove all space characters
VCODE := replaceClob(VCODE,'"',''); -- Remove all quotes
INSERT INTO T_HASH (OWNER, NAME, TYPE, ORIGIN_CON_ID, CON_ID, MD5_ENC, SHA1_ENC, SERIES, ORAVERSION, PSU, CODE)
VALUES
(I.OWNER, I.NAME, I.TYPE, I.ORIGIN_CON_ID, I.CON_ID, SYS.DBMS_CRYPTO.HASH(VCODE, SYS.DBMS_CRYPTO.HASH_MD5), SYS.DBMS_CRYPTO.HASH(VCODE, SYS.DBMS_CRYPTO.HASH_SH1), VSER, VVERS, VPSU, I.TEXT);
END LOOP;
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID = VSID;
END;
/