Repository navigation
Expand file tree
/
Copy pathObject_restore
More file actions
94 lines (69 loc) · 1.79 KB
/
Copy pathObject_restore
File metadata and controls
94 lines (69 loc) · 1.79 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
CREATE OR REPLACE PROCEDURE PLSQL_HISTORY_RESTORE
(
IN_VERSION IN NUMBER
, IN_OWNER IN VARCHAR2
, IN_TYPE IN VARCHAR2
, IN_NAME IN VARCHAR2
) AS
v_start varchar2(50);
v_clob clob;
v_version TIMESTAMP(6);
BEGIN
select tstamp into v_version
from (
select to_char(rownum) as version,tstamp
from (select distinct tstamp from plsql_history_source
where type=IN_TYPE
and name =IN_NAME
and owner =IN_OWNER))
where version =IN_VERSION;
begin
v_start:='Create or Replace ';
for rec in (
select
*
from (
select ph.*,
row_number() over ( order by ph.tstamp ) as seq,
count(*) over () as tot
from plsql_history ph
where type=IN_TYPE
and name =IN_NAME
and owner =IN_OWNER
and tstamp=v_version) v
join plsql_history_source s on
v.tstamp =s.tstamp
and
v.name = s.name
and v.owner = s.owner
and v.type = s.type
order by seq ,line )
loop
/**Append to Clob **/
v_clob := v_clob || CHR(10) || rec.text;
end loop;
v_clob:= v_start||v_clob;
begin
--execute immediate v_clob;
-- EXCEPTION WHEN OTHERS THEN NULL;
DBMS_OUTPUT.PUT_LINE( v_clob);
end;
End;
/** HOW TO USE **/
/**
select *
from (
select to_char(rownum) as version,tstamp,type,name,owner
from (select distinct tstamp,type,name,owner from plsql_history_source
));
**/
---set serveroutput on;
---begin
---PLSQL_HISTORY_RESTORE
---(
--- IN_VERSION =>2
---, IN_OWNER =>'MCC_PROD'
---, IN_TYPE =>'FUNCTION'
---, IN_NAME =>'F_DBA_OBJECT');
---end;
END PLSQL_HISTORY_RESTORE;