上面的类型对象是作为元数据存储器的,用于存放多个表的完整的元数据信息集合。所有上面的类型都包含在程序包规范中(列表2所示)。我还介绍了下面一些API:. K, R4 _) |8 a8 G; C( K& G1 A
' c) p! i: K& Q! a, q1 [. B' v
· MetaDataPkg.Load()过程:把特定表的元数据信息载入存储器中。
+ m. ~8 U7 l) m8 h1 z, ?/ j: V
- r% q, V5 W) c' { P( \2 u · MetaDataPkg.GetMeta()函数:它从存储器中检索tFullMetaObject类型的对象。
1 ?$ s3 j8 n7 s& `2 z% |6 \; i0 k S* p2 I2 P @
· MetaDataPkg.SetMeta()过程(重载的):把对象的元数据存储到存储器中。 ! W; J% t. ^( g$ Q; J4 C
) P2 z. e$ ?3 w) v. b · MetaDataPkg.Reset过程(重载的):对存储器复位。 ; u' X% [' x( t5 b9 v' w' [* |/ @
' ?2 T% a* y" R/ C3 V, j- N3 E
· MetaDataPkg.Show过程(重载的):显示存储器的内容。/ f% S6 o/ o2 L. e8 i. {8 T ~
, ?* C& K. J! E i4 _1 q ]
实现所有这些事务的代码4 _ H5 _9 V O+ N$ v1 [+ U
/ D9 D* f, w/ P7 b% V- p
列表3显示了MetaDataPkg程序包主体代码的一些解释。私有过程SetEnvironment()包含了所有的环境设置代码。在程序包的初始化部分会调用这个过程,因此在每个对话中它都只执行一次,符合你的需求(你希望在开头设置一次)。程序包提供了用于设置环境参数的API:DBMS_METADATA.SET_TRANSFORM_PARAM()过程。2 {1 M& H9 Y& r
" i, L0 p# d' ^; Q6 a4 q 列表3:MetaDataPkg程序包主体
6 i8 _7 J: j6 o( U7 m
' n4 z- K$ a5 f$ n2 K" mvMetaData tArrayFullMetaObjectByString;5 d( G# u T. H, N7 h2 Y7 v
) E6 H* H0 J7 W* g) d
procedure SetEnvironment
5 F9 c: Z+ D" x _& i. Nis
# C( A2 x3 g1 m9 }begin" `3 T$ y9 Y Y# ~5 p
dbms_metadata.set_transform_param(
5 i) k Q& ~# ]+ K+ \& ~ dbms_metadata.session_transform, ’PRETTY’, false);/ B3 A8 y4 x5 L1 }( c9 u, K3 D
dbms_metadata.SET_TRANSFORM_PARAM(
' P" ]- |! D1 A. V2 q: t( d) s dbms_metadata.session_transform, ’SEGMENT_ATTRIBUTES’, true);% E9 X( [! @+ s, A6 A8 ^6 G
dbms_metadata.set_transform_param(
1 U+ |$ K! [4 Q dbms_metadata.session_transform, ’STORAGE’, true);% b& }' u3 H5 r+ u
dbms_metadata.set_transform_param(( ]" s& r ^3 B4 k
dbms_metadata.session_transform, ’TABLESPACE’, true); ) J. Q& Q% X8 W- N! p* v7 F* P2 M
dbms_metadata.set_transform_param(; w# n/ G9 |7 ~+ C
dbms_metadata.session_transform, ’CONSTRAINTS’, false);
" Z8 B$ K& ?% e9 V) s dbms_metadata.set_transform_param(* \9 I' e5 M x- i! r& u8 t; ]0 N% g
dbms_metadata.session_transform, ’REF_CONSTRAINTS’, false);
/ o1 \7 G `' G9 @& l3 H dbms_metadata.set_transform_param(2 J3 U! J$ c0 D1 `2 o
dbms_metadata.session_transform, ’CONSTRAINTS_AS_ALTER’, false);. Z% W6 [" U8 V% |
; L( R! b; K3 |9 P6 i h1 Mend SetEnvironment;% @8 n: W) G6 w! y, g+ N
, q2 i; v5 l% Vprocedure Print) W5 S; ^# O- C: j- ^& e
(, q: e& @; _8 S3 D2 ]7 X
pString varchar2,; l& @. a1 r v/ L: R, }$ @7 Q
pLineSize positive := 80
0 u* b- Z1 @" w' _)
2 q$ e( `- S6 |. e0 Mis
. g9 v) c1 p& ]* U
; w5 A# y6 r8 e8 UvLineSize pls_integer := least(nvl(pLineSize, 80), 255);
; a( s# a& J5 x: ]& s4 r% X2 q5 n: z, b: T! r9 u# _
begin
% d; m0 I& k9 Y$ X6 t dbms_output.enable(1000000);% W1 j: F* |: }. S, D( `$ m9 s
' @' p' ] V+ ?, o A2 x: \ if (length(pString) > vLineSize)5 F: R* k, v- c/ T- s
then
& ^* U F# b( C# o; K' S+ z$ {, ? dbms_output.put_line(substr(pString, 1, vLineSize));* E N/ @/ B" I3 q* T5 _4 w3 p" h
Print(substr(pString, pLineSize + 1), vLineSize);/ P# M2 h; I0 n9 j# M
else! S2 Q. m- T6 V1 h
dbms_output.put_line(pString);" k! z8 O! L; A6 N' w, U; a
end if;
) B8 ]* Q! k- \5 Q: D9 Z8 |+ G( ]: B3 V5 E d6 S, P8 H
end Print;3 Z5 q5 H s0 K) u
- ?9 R3 S' W+ A" f) W+ i
procedure Show
% O. S; q, c0 J' w0 c/ G- W(
+ ]5 p8 s7 T2 o pMetaObject in tMetaObject
6 G2 j6 M- e7 N2 i9 _)3 j% _6 b$ }# Y5 m. ?* {# b1 C
is
9 K+ n# k0 p# ]2 q$ |begin
; ^8 T1 t6 I- {2 Z& l7 P; W5 `dbms_output.put_line(’***’);9 y: S v# y- v
dbms_output.put_line(’Name: ’ || pMetaObject.aName);
9 n) z: X7 t" i+ }+ _/ R# Kdbms_output.put_line(’Type: ’ || pMetaObject.aType);
% y7 }+ U7 y. n# q ^8 odbms_output.put_line(’Logging: ’ || pMetaObject.aLogging);
6 J9 I% _8 w2 V! k3 Z+ Mdbms_output.put_line(’Parallel: ’ ||6 \ m2 i# H8 d7 C
to_char(pMetaObject.aParallel));
- G+ B1 c/ d, Q; E3 ]dbms_output.put_line(’Status: ’ || pMetaObject.aStatus);
2 d x: D6 y; [dbms_output.put_line(’Validated: ’ ||
+ z! c+ f L. R* qpMetaObject.aValidated);
0 f: R$ t& ~( C* _3 n4 U3 ]$ bdbms_output.put_line(’Rely: ’ || pMetaObject.aRely);
3 ]" w K; {( r* p6 ]' g! w( ~print(’DDL String: ’ || pMetaObject.aDDLString, 255);; ]6 ~7 X( `5 Q! f
dbms_output.put_line(’***’);
) c# O0 ]0 K, Pend Show;8 K: b! l4 n4 H) Q
% g, r2 T8 [) C- b! cfunction GetDDL' P& l+ |" W7 p& _0 o9 p
(! p/ m* m/ r# \2 f5 A. H3 ^0 s
pName in tString,
k& N) M/ P7 D9 p/ e6 H0 { pType in tString
: v) t6 ~$ n% a)
4 O) `7 d0 P' R1 b' [" ?' Ereturn tLongString2 ?4 {% ^( f1 m+ Y
is
# v0 l' K, O4 t- zvClob clob;+ W; O9 B+ V7 O# I* r
- u, [5 Z6 q6 _3 z% `* W
vLongStrings tArrayLongString;. G8 [( b* S5 A0 D
7 S) L) E# a7 D I4 QvFullLength pls_integer := 0;$ M% B5 i( d' }% ^: a0 ?
vOffSet pls_integer := 0;
- @3 S# N0 M+ j! I! S; ~7 F) o% q v: _vLength pls_integer := 0;
9 _8 `# p, W: y# e6 x6 ^0 \# c g% [: G% b! N7 w
begin
0 B2 n/ Z/ G6 N! \/ i' A% k" E) \! M. N) K* {$ g
vClob := dbms_metadata.get_ddl(pType, upper(pName));
3 U# n% U$ R( X/ R5 g
' N3 w$ q& k% X6 Q- ^, xvFullLength := dbms_lob.GetLength(vClob);$ f: I% s; v9 l5 P# N; I
0 R: h. v+ g1 y2 B
for nIndex in 1..ceil(vFullLength / 32767)
) u# @" C1 x) N- |loop
$ j' A; H& Y; O2 J# A vOffSet := vLength + 1;% G. J: C- A: @9 s, c
vLength := least(vFullLength - (nIndex - 1) * 32767, 32767);* _9 @* ]) J2 D- ]% f/ k8 k
. T/ O+ \; H+ _2 `
dbms_lob.read(vClob, vLength, vOffSet, vLongStrings(nIndex));8 o% L1 t5 ]& p' a8 w* e
9 e2 `$ ^& x; I+ T4 T7 \, k
vLongStrings(nIndex) := replace(vLongStrings(nIndex),. f4 U" C8 X4 O, D0 J( K
cDoubleQuote || user || cDoubleQuote || ’.’,6 }1 y4 m. C# s- H9 Q" j# f l
’’);0 X: a+ x: S" M$ R H- ]. x! b
5 |6 T3 V! I4 \" d7 C$ s8 N
vLongStrings(nIndex) :=ltrim(rtrim(replace(vLongStrings(nIndex), chr(10), ’’)));
+ p4 Y4 _7 K. Fend loop;
/ O; x) X6 }1 t2 Z% L% D" G& ^0 L/ v3 S- E ]. u3 @# f# h" C- o
if (dbms_lob.isOpen(vClob) > 0)
$ l- B6 i& W" H% K# L7 N! e+ cthen
2 K: `: G; c4 A. P6 Z dbms_lob.close(vClob);
% v- C% h8 h5 I) w v6 Jend if;( d' i" X! ?0 o# O5 y
/ V; |$ [8 w+ _; C
return vLongStrings(1);5 p4 K1 e4 Q [5 g7 ^
/ Y, |/ L: a) l$ o! r8 s7 g, `end GetDDL;* }/ q" Z) x0 l" e# J5 O
9 J; Y5 l1 v w/ hfunction ObjectExists$ |2 v# ]$ |$ ~ P$ n) T9 R$ T6 q$ O
(
1 n) ]& S; y P* { pObjectName in tString,
3 [5 `; j4 A4 ?2 t, \+ [ pObjectType in tString,
% Z% o: G# y4 p$ g8 T& ? pTableName in tString := null" }3 }, |0 i8 z$ a" r. u2 n
)
( D* r# P* `4 b; g( Q( Y* a3 Greturn boolean4 @6 T3 d y8 T3 g8 S3 |
is9 c) D( V0 @! E
) U7 E" A- n4 J; T& s0 ]
vCount pls_integer := 0;
' ?, N7 a* S( _6 W% GvObjectName tString := ltrim(rtrim(pObjectName));* @, b- x8 H5 l
vObjectType tString := upper(ltrim(rtrim(pObjectType)));
0 M Y, T8 o I5 z- _vTableName tString := upper(ltrim(rtrim(pTableName)));
# n# x+ r4 S& r0 G& w" T1 a) Q. l a8 D6 ~8 \
begin4 p4 W& F& X5 e7 f0 I2 [ R
: B$ s3 T& u' a% ~* ~
case
, r! }. D9 f t" `% k2 D# P when vObjectType = cColumn3 ~$ G9 }8 j! O) |; Q
then
% w) |6 T. O( r. l" ` select count(*)
$ @+ l+ N' n1 H8 R9 o% p: {( H into vCount
$ U9 n A; R/ M, x% I. K from Dual
+ ^4 X1 R+ M( Z* k1 P where exists (select ’1’
5 c) i+ i! Y/ b1 Z- ? V from user_tab_columns% y# V. J* R; j1 P- v4 B
where column_name = upper(vObjectName)7 n! ~9 J2 i' o$ \1 J
and table_name = vTableName);
' L- N: x- |' f, V8 \
% g( U" ~* G* [% _5 T& C. z when vObjectType = cConstraint
3 t! Y! {+ V( J# n4 v% W% G then: V- B3 }( q3 b; y; Z% `
select count(*), g. _ q3 o9 U& w& z
into vCount
0 r# p0 L- a9 a from Dual
9 O# l h M, [ where exists (select ’1’2 \) v- ]8 k6 R
from user_constraints
, s) Q8 S0 x9 b% _0 r8 b' X where constraint_name = upper(vObjectName)
, H# P' j) B9 }! _% ]; _, c and table_name = vTableName);
8 w4 ]# p6 ^9 g
$ T& [1 j4 m$ b7 A6 t. swhen vObjectType in (cJavaSource, cJavaClass)2 B; B2 b7 y! }
then
5 P/ `+ j& U9 U* \& ?9 ^2 h. w" {" }( p select count(*)
; ]/ P- q" _" M- ` into vCount
; ?; { _! Y2 w6 o$ a from Dual
' h3 j) M1 j* g8 q+ B; Z5 s: w where exists (select ’1’( ?+ c2 T4 M, b' |# W
from user_objects+ k6 X* l/ ~1 y+ y9 P2 \
where object_name = vObjectName and object_type = vObjectType);% f3 I8 T# ]" K
9 }( Z1 ~7 q; t( j) ^ else; J' E8 N4 r0 Z! C/ ^8 V* J
select count(*)7 N" G# Q7 s& g" g
into vCount; p- `# m9 v" k
from Dual& r* _' m* \# s; q; q) S$ B# M, U
where exists (select ’1’
& `7 O; D% Y/ I from user_objects) n; O5 ], i" a+ Q3 w
where object_name = upper(pObjectName)
b* {" D- C4 W, L4 |" c and object_type = vObjectType);) T6 \5 a4 x! X4 \$ ?% Y
end case;2 W, t5 A- ?8 |# j0 n: P
' E' I4 o3 {3 x- X$ N/ y' @
return (vCount > 0);
. Z3 O( R8 Y W) I
! X% y" E- ?) V9 V% j2 O9 {* vend ObjectExists;
4 w8 T y+ W6 P( G* A4 @) D: ? p' v
procedure Load
9 J9 f# E8 r- x: X0 K! N3 k( A$ `* Z(
. I/ H8 N3 y/ w* D+ X+ U pTable in tString,
6 Z5 F8 h* L1 D' P, L pForce in boolean := false* D7 q |) U9 x9 ^5 J( `, e
)* r2 H. G3 R6 L" C1 G9 L
is
2 k" o* D- m/ f; ?/ v( {. n" }& `4 c
vFullMetaObject tFullMetaObject;6 g N6 ]8 _$ n6 u0 a% S/ W+ R
8 `% ~) r; ^) X5 o4 q& c0 c- EvTable tString := upper(ltrim(rtrim(pTable)));$ J3 M5 O. m7 G" m6 C7 U" d" q
vCount pls_integer := 0;
. @" u. N' N0 ^* d, e" W; C5 ]% n* y% ^' C+ b# f$ c$ P" k
begin$ n# T5 a7 j; c( m% g$ J
) u; u$ i r5 \# R6 K q
if (not vMetaData.exists(vTable) or nvl(pForce, false))
! M1 R; k/ U: E then/ y* ~* ~" H0 M6 N
if not ObjectExists(pTable, cTable)1 S* Q! L/ F- N. t4 `
then
( ^% K/ j2 q1 |. A$ Z2 K8 u! e raise_application_error(-20500,# ~. ] X& u4 L" w* K# H p+ S! O0 K
’Unable to load metadata for ’ || nvl(pTable, ’NULL’) ||
& O4 }4 r I- ]' `# W" a9 N* N, y ’. ’ || ’Table does not exist.’' A4 W: x/ ]+ _4 L7 m
);
! w1 n6 n- w' _. wend if;
; x4 ~/ G; M( d" X4 H3 B
$ w" l- c) j/ r( X/ Zfor rec in (select table_name,: q0 l. s0 D# I+ X& Z: L/ i0 a
logging,0 u! s* g) Q+ y
ltrim(rtrim(degree)) as degree,$ ~& n* \/ _# x z+ v1 V* T" z
partitioned,
# n s% U: `0 K! E; t4 [; v$ [" ebacked_up
0 g4 G( h) F2 K5 t% d# ]& d7 T) Ofrom user_tables
1 Q! Q2 z/ Z1 c" W/ C0 Vwhere table_name = vTable)) U6 F2 s+ q O# X% {* n
loop
# I& u, t+ q. h
8 {2 \( D# F8 F0 j2 C3 N0 O vFullMetaObject.aTable.aName := rec.table_name;
! m7 L' @9 q3 m6 e% z& Y4 B vFullMetaObject.aTable.aType := rec.partitioned;$ K n& E2 q# { j+ i# k
vFullMetaObject.aTable.aLogging := rec.logging;
! I0 v V. g' |& R$ R9 N vFullMetaObject.aTable.aParallel := ltrim(rtrim(rec.degree));- G6 h) z4 n0 `. K z
vFullMetaObject.aTable.aStatus := rec.backed_up; 6 K L, M# D& G$ e! F1 O0 u
vFullMetaObject.aTable.aDDLString := GetDDL(rec.table_name, cTable);; U- B T- ?% ? Y( Z7 f; [9 a
end loop;
' g5 o6 r8 U9 V; L0 Z& J8 B4 |: Q( F2 h. j
for rec in (select index_name,, K' W4 V! z: j2 V
uniqueness,
4 l9 S" x+ @' [$ u; Xlogging,1 N& j# Z1 e% G* l. {; b
ltrim(rtrim(degree)) as degree,
/ W2 t+ p+ Y% U' jstatus5 P9 }. g! N2 l2 N' A/ }
from user_indexes4 a: s2 R* P7 D5 e6 Z
where table_name = vTable
$ Y! G3 e2 t) H* ]and index_type != cLobType)
7 e+ z. a9 g0 K- ]" r+ p G- h2 D7 cloop4 ~% i8 H; ]; `3 V0 F, u7 c' u
* k5 }) ]0 l; q& PvCount := vCount + 1;/ I- U% J& h/ m/ V! }; E
vFullMetaObject.aIndexes(vCount).aName := rec.index_name;1 ?0 [2 h% N; Z8 S! s
vFullMetaObject.aIndexes(vCount).aType := rec.uniqueness;( ?# T, [3 U6 z6 M
vFullMetaObject.aIndexes(vCount).aLogging := rec.logging;: `5 v$ t5 G d3 Q
vFullMetaObject.aIndexes(vCount).aParallel :=
& z, E {' G0 ^* ~ltrim(rtrim(rec.degree));- i% L5 u0 W3 M7 z: j0 h* a4 L
vFullMetaObject.aIndexes(vCount).aStatus := rec.status;+ z: @: r) g, \5 c6 ~( b* r
vFullMetaObject.aIndexes(vCount).aDDLString :=
! |* W, q' [$ |7 Z& c3 BGetDDL(rec.index_name, cIndex);
& X. v% A& y6 V9 ~, |0 t7 L) u" a6 W2 V
end loop;
r; T$ ^/ y% ^* W6 a/ X0 v
0 R0 _8 S& Y N6 d) L/ uvCount := 0;& X- a4 l6 O9 g. y
for rec in (select constraint_name,constraint_type,status,search_condition,validated,rely from user_constraints where table_name = vTable" G4 |: h3 D% j+ g; ^$ y2 O
order by decode(constraint_type,* C) ?0 K% Y' @) u- G
cPKConsType, 10,
6 Z" `$ W6 i$ E. F! z7 _ cUNConsType, 20,
' T: a- v9 K, J' {, o! I) c1 N. _ cFKConsType, 30,) E3 n E5 t" Z @: g7 p
cCKConsType, 40,
" u+ P: ^" U2 x2 N+ r 100),- g) k* P& L8 s# F7 D
constraint_name)3 C' G q6 M0 }9 I( m0 z& I
loop * p& k! Y4 n/ R: y' ^, ?
vCount := vCount + 1;4 z, S! L) Z' r, f
vFullMetaObject.aConstraints(vCount).aName := rec.constraint_name;
, `& t: @* I0 t6 B8 j vFullMetaObject.aConstraints(vCount).aType := rec.constraint_type;
( ^1 w" Q- O* C' ]0 d Z! L vFullMetaObject.aConstraints(vCount).aLogging := null;9 j9 [5 g+ o1 b0 x% j- d
vFullMetaObject.aConstraints(vCount).aParallel := null;2 `' _7 x9 F1 A; ^* n; _3 o. U
vFullMetaObject.aConstraints(vCount).aStatus := rec.status;5 M6 E- v0 o# _
vFullMetaObject.aConstraints(vCount).aValidated := rec.validated;
+ J- G+ G5 } `3 e) X7 V' [ vFullMetaObject.aConstraints(vCount).aRely := rec.rely;
6 v5 t) v! J( S( @" D/ K5 [. n. m# C( y! Z( q; o, u7 Q- L$ `
if substr(rec.constraint_name, 1, length(cSYSPrefix)) = cSYSPrefix and; [3 L! @0 ]+ T5 V; I
upper(rec.search_condition) like ’%IS ’ || cNotNull || ’%’
) x+ a+ a" H3 K# ] then. ~/ B) t% \0 k8 s# `, D
vFullMetaObject.aConstraints(vCount).aDDLString :=
) v$ j: N6 J4 ?- }! E% ? ’ALTER TABLE ’ || cDoubleQuote || vFullMetaObject.aTable.aName || cDoubleQuote || ’ ’ ||
/ K, z8 S1 `1 G: \ q* d3 o ’MODIFY ’ || replace(rec.search_condition, ’IS ’ || cNotNull, cNotNull) ||
* o. x8 A" [. D* m; o& E (case when vFullMetaObject.aConstraints(vCount).aValidated = cNotValidated' n G9 [# g( a. G1 J0 l
then ’ ’ || cNovalidate# A% ?2 K" y I
else ’’end);% s5 D! N2 e9 g' r+ ~* K
else
" K: {8 ^1 D' ?& B' Y6 q vFullMetaObject.aConstraints(vCount).aDDLString := GetDDL(rec.constraint_name, cConstraint);- p, S' [' d# n- Z0 @
end if;
" O2 T6 y3 e* s' i% l1 rend loop;, M: a" p) @0 \% X9 i: q
1 m1 C) D `% P: N& Z/ J) |SetMeta(pTable, vFullMetaObject);
& m7 n9 R: \ Y( y! aend if;
4 f) `% @1 Q$ t+ T3 _+ |8 k8 Oend Load;
! G5 t3 z. W) p f) M- N
5 L$ Z! u' `. u! \procedure Reset- A; D Z/ o& S* {( }4 A6 M
(
* b5 Z: K7 T' r pTable in tString% j8 g w7 h, z7 Q1 F& i! s1 r: E( _
)( f' [3 a) M& k5 G
is
: G. y0 e: i2 |1 Y5 |2 ybegin
3 c8 v3 d8 p5 A+ Z! O+ A' u- Q4 I5 W' ` G7 Z* T
vMetaData.delete(pTable);+ b5 I9 l* g0 y; R( g! K$ h
8 u d1 H5 b5 ]end Reset; c: ]" p: ]0 s# M6 f3 f& P
9 p% O# y5 R; j! H0 T
procedure Reset
) u: _* a5 A* bis
/ [" v+ g; w8 Y0 N O0 zbegin. B' W* v( g8 l* T0 o( z% H
vMetaData.delete;
- a1 N: W) [& Vend Reset;
5 {2 x3 A4 ~8 v# ]' Z
" s/ B% k. h8 }; Cfunction GetMeta
& C6 D# W, T9 Q& A9 h! V+ S' y(
. h- c* o$ [# y pTable in tString,
$ u& D C( _6 b l; J. r pForce in boolean := false: K3 X r" L3 D: |3 a4 `8 u
)
/ W0 }% Z7 `5 i( {4 _$ c* @return tFullMetaObject
4 Q6 m! v9 C3 L0 g7 q5 |is2 \+ B+ |$ R% m/ V9 n/ T
begin
/ N/ h4 y2 [: n# `
2 [. h$ ^1 K) m4 s5 gif (not vMetaData.exists(pTable) or nvl(pForce, false))
3 V/ \& T8 o. s* S: v5 `& v% Uthen) D. n! I2 Z8 \
Load(pTable, pForce);
; {. I c: R' G3 ]; V |9 V$ R' _, C& Q% e
if not vMetaData.exists(pTable)
% Y! O# u/ e% @/ F' m) [ then! j# ?: p, h) n
raise_application_error(-20501, ’Unable to find metadata for ’ || pTable || ’ in repository.’);: }, a ^. M4 H
end if;
8 |) O7 {" H4 [8 Dend if;
$ X I5 F( s# [6 k9 ~7 g7 _1 i& _' C$ A4 {* m
return vMetaData(pTable);% d" t" j9 x/ P
4 j# |2 e& b* g9 a# ^! U/ Vend GetMeta;
L. f- ]1 }+ ^( I) S" J
. \! C- c6 A+ ofunction GetMeta6 p% u0 J6 H6 r, _& X! {' E6 \
return tArrayFullMetaObjectByString
# G- t( I4 V6 @5 r" Uis
% D# i2 ~8 o- Y/ g6 m, E0 \begin
2 \, {* R7 r4 i4 H* E& H2 B7 j$ A4 c. E; W) ?4 R w C$ I: a: j1 |
return vMetaData; 6 s! [9 U& v" k, m( s% L
* N! P2 v5 f+ Mend GetMeta;1 V* G5 m% U/ D; z" P. c- @
8 X* P( w/ [) `procedure SetMeta
a3 ` J8 y5 p: v9 U9 F! r(1 B4 V1 `0 @0 X
pTable in tString,
0 P0 k4 R4 \+ G0 D) g) c pFullMetaObject in tFullMetaObject# ^3 ^# x( q8 E( P2 D1 e6 o
)
6 U7 m' G( O3 |is# j, w% J; {9 _# o5 |: F( D( n
begin% O6 a7 n! u* E8 v; q4 Y" _' f1 U
7 O- j) h8 k1 o# l' y4 k
vMetaData(pTable) := pFullMetaObject;
1 y& Q% y% O# I2 H% ]( ~5 p h. ^3 L0 [9 F5 n$ Z- j6 Q
end SetMeta;' g! l3 C. p+ C
8 P1 K. y: l3 e2 k5 K
procedure SetMeta
7 z& p, k. u! E7 i& f() r8 d$ Y; L9 ^ ^! W& i
pArrayFullMetaObjectByString in tArrayFullMetaObjectByString
8 x/ N- i% n1 X' n7 f! ^4 `* Q)
2 F4 ^/ o' i+ b; O4 k$ H6 M0 `is
' v- @5 Y! q2 N& E: w y' c. p& A# |begin* ?% T3 _; f7 N- @) Z5 V+ e
3 x; ^ s" L. R$ c
vMetaData := pArrayFullMetaObjectByString;. Q) q5 g+ e* q$ J% ]
% R" W' m9 D- N, Q& o, D2 g
end SetMeta;' ]# B8 e2 p( f1 y
! E$ f6 F8 X( _5 w/ {# Z( G+ ]! qprocedure Show
3 m4 H8 k! j9 \% G( m7 s$ k* `+ h y: L
pTable in tString
7 R! A1 L( I' [# B- @7 e)% ^4 \4 J1 X2 O& }
is5 w) o- O$ k" T% {6 N
. I. i/ ]6 M; UvFullMetaObject tFullMetaObject;' m/ q3 o+ ]( i# b$ k
0 V; \2 |" ~& L
begin0 `' }/ I( E) N3 q0 N* j
2 L' k( q6 u% [0 D0 H9 _& v; gif (vMetaData.exists(pTable))6 `$ O' j/ H; i/ K& {, ]$ Q6 L
then8 C" K# A% k/ D0 Z+ M
dbms_output.enable(1000000);6 v5 Z/ @& q9 j0 x& y, [
: B* L* a9 k" S: t
vFullMetaObject := vMetaData(pTable);7 L# t" }9 a+ [! s
dbms_output.put_line(’Start Full Object: ’ || pTable); W5 Q! t& B6 |. A' A
dbms_output.put_line(’Start Table: ’ || pTable);
: o* i: N* \6 q6 u% Q5 S/ O! d' A) HShow(vFullMetaObject.aTable);
$ r7 z \5 h9 ] `, h$ H. @dbms_output.put_line(’Finish Table: ’ || pTable);. n& F/ v2 w6 ?% e. ?! Y
6 ^, I3 [2 S& ]4 Z( i: N( O
dbms_output.put_line(’Start Indexes: ’ || pTable);
* d/ k, R" m( r9 C: P- l& qif (vFullMetaObject.aIndexes.count > 0)
# `2 X) B! E7 ^; f f6 Wthen* Y( t0 @; ~+ M. M
for nIndex in vFullMetaObject.aIndexes.first..vFullMetaObject.aIndexes.last0 r1 L! o! R( K5 f5 j
loop
2 {* z/ z3 H* ^0 \) [Show(vFullMetaObject.aIndexes(nIndex));7 [# F/ q# v! b* h
end loop;% }; ]! H) d' p$ o0 S% m
end if;
' Y( P, o1 R$ M6 W7 m+ v4 Pdbms_output.put_line(’Finish Indexes: ’ || pTable);; f. H* t/ e, l
dbms_output.put_line(’Start Constraints: ’ || pTable);
5 b# r5 ~& L4 @; P+ hif (vFullMetaObject.aConstraints.count > 0), _ y8 Y* w: _- P
then
5 k3 u! a p% H: i( C9 V6 r4 C- o; hfor nIndex in vFullMetaObject.aConstraints.first..vFullMetaObject.aConstraints.last
3 b% l# s- ~. `* w5 |7 Y* \. n7 }loop$ `% s4 n. c, |
Show(vFullMetaObject.aConstraints(nIndex));9 o' D* H7 s. F6 n$ m; M* p% F
end loop;
3 W5 c' X+ w; l+ S: l: Z$ Tend if;) f# j( ~' T" A7 }) A. |
dbms_output.put_line(’Finish Constraints: ’ || pTable);6 x. s/ d A) N! H' c3 k/ |4 D8 e
dbms_output.put_line(’Start Triggers: ’ || pTable);3 Q" b& A8 q2 T2 ~" l' s
if (vFullMetaObject.aTriggers.count > 0)
! J# A1 T6 E! G) I z6 F% fthen. X, Z1 T* Z- N% z
for nIndex in vFullMetaObject.aTriggers.first..vFullMetaObject.aTriggers.last
! l+ F3 p4 \* d2 q* Lloop
& e: r0 U0 T' PShow(vFullMetaObject.aTriggers(nIndex));
: V% j5 I; C' c6 r7 zend loop;
6 y! e! q; e7 M$ i. D2 D* kend if;
$ o5 X' q: V+ \3 H3 q- s rdbms_output.put_line(’Finish Triggers: ’ || pTable); 9 o y( j- |- u+ Q
dbms_output.put_line(’Finish Full Object: ’ || pTable); 8 e$ {: w& R5 R; m- O# e' P& }6 ?
end if;
! E0 J- j, Z cend Show;
: v# x. I, K, a: x q, b% y. m9 f( Q+ |
procedure Show
5 q3 r6 B5 N4 o# Jis) H; o7 H/ \8 B2 j
vTable tString;$ |* D V$ ]# U# U& ?+ {
begin
! G8 t+ u0 c; _3 v7 V if vMetaData.count > 0
! {6 g. |2 Z; {3 h then
+ G* P8 p3 o& z1 d( p dbms_output.put_line(’Total Meta Objects: ’ || to_char(vMetaData.count));
% A$ q/ E7 V$ Z1 c) s( I* { vTable := vMetaData.first;/ B7 i3 N z3 v" D. q+ @6 K0 l
while (vTable is not null)
/ J; }) [4 [) W' f% ^7 ^# c loop) e+ [: l: c h% m5 r1 @
Show(vTable);' s4 e5 ]$ L/ m ~9 m
vTable := vMetaData.next(vTable);( k, S9 L, m. I4 h
end loop;
' F3 j' f# r7 e( @6 ?; x! X end if;
# ]9 [. U9 I8 O% _7 u. r8 gend Show;" ]% r6 @- G, \- \
- E' \$ Q$ W' ^, P5 R2 i) Jbegin
1 c" J* n' n/ s) wSetEnvironment;
: U# n/ P' F7 i) N; v2 uend MetaDataPkg; |