📄 plgdoir.spb
字号:
CREATE OR REPLACE PACKAGE BODY PLGdoir
IS
/* Interface to Object Information Repository */
/*----------------------------------------------------------------
|| PL/Generator from Quest Software
||----------------------------------------------------------------
|| File: plgdoir.spb
|| Author: Steven Feuerstein
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1998-1999 Quest Software, Inc.
|| All rights reserved.
||
|| For more information, call Quest Software at 1-800-REVEAL4
|| or check out our Web page: www.Quest Software.com
-----------------------------------------------------------------*/
/***************** Modification History **************************
|| Date By Description
|| -------- ------ -----------------------------------------------
|| 06/97 SEF Created.
******************************************************************/
c_pkg CONSTANT PLGadmin.identifier := 'plgdoir';
g_recurse BOOLEAN := TRUE;
g_drvfoundation BOOLEAN := FALSE;
g_hash_srcinfo BOOLEAN := TRUE;
g_schema PLGadmin.identifier := USER;
g_trueval PLGadmin.identifier := PLGbool.tval;
g_just_like_drv oir_driver.just_like%TYPE;
g_just_like_drv_set BOOLEAN := FALSE;
/* Short cut values */
g_use_shortcuts BOOLEAN:= FALSE;
TYPE shortcut_rectype IS RECORD (
nm1 PLGadmin.identifier,
sc1 PLGadmin.identifier,
nm2 PLGadmin.identifier,
sc2 PLGadmin.identifier,
nm3 PLGadmin.identifier,
sc3 PLGadmin.identifier
);
g_shortcut shortcut_rectype;
-- NOT USED alias_hash PLGadmin.vc2000_table;
CURSOR tab_cur (nm VARCHAR2, sch VARCHAR2)
IS
SELECT *
FROM /* dba_ */ ALL_objects
WHERE object_name LIKE UPPER (nm)
AND object_type = c_table
AND owner = NVL (UPPER (sch), g_schema)
ORDER BY object_name;
CURSOR col_cur (sch VARCHAR2, tab VARCHAR2, col VARCHAR2)
IS
SELECT column_name
FROM /* dba_ */ ALL_tab_columns
WHERE table_name = UPPER (tab)
AND column_name = UPPER (col)
AND owner = sch;
CURSOR seq_cur (sch VARCHAR2, seq VARCHAR2)
IS
SELECT sequence_name
FROM /* dba_ */ ALL_sequences
WHERE sequence_name = UPPER (seq)
AND sequence_owner = sch;
CURSOR alias_cur (
drv IN VARCHAR2,
orig IN VARCHAR2,
atype IN VARCHAR2,
sch IN VARCHAR2,
tab IN VARCHAR2,
needdoc IN VARCHAR2 := c_false,
restrict_objid IN VARCHAR2 := c_false
)
IS
SELECT objid, owner, objname, info, doc
FROM PLG_doir
WHERE driver = UPPER (drv)
AND ( ( restrict_objid = c_true
AND objid < 0)
OR objid IS NOT NULL)
AND ( ( owner = UPPER (NVL (sch, g_schema))
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = PLGdoir.c_global)
OR ( owner = UPPER (NVL (sch, g_schema))
AND objname = PLGdoir.c_global))
AND objtype = PLGdoir.c_alias
AND attrname = UPPER (orig)
AND ( attrtype = UPPER (atype)
OR atype IS NULL)
AND ( needdoc = c_false
OR doc IS NOT NULL)
ORDER BY SIGN (objid) desc,
DECODE (
owner,
'*ALL', DECODE (objname, '*ALL', 99, 98),
DECODE (objname, '*ALL', 97, 96)
),
DECODE (attrtype, c_misc_alias, 1, 2) /* MISC overrides IDENT */
;
CURSOR curinfo_cur (
drv IN VARCHAR2,
sch IN VARCHAR2,
tab IN VARCHAR2,
restrict_objid IN VARCHAR2 := c_false
)
IS
SELECT objid,
SUBSTR (info, 1, INSTR (info, CHR (8)) - 1) curname,
SUBSTR (info, INSTR (info, CHR (8)) + 1) collist
FROM PLG_doir
WHERE driver = UPPER (drv)
AND ( ( restrict_objid = c_true
AND objid < 0)
OR objid IS NOT NULL)
AND ( ( owner = UPPER (NVL (sch, g_schema))
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = PLGdoir.c_global)
OR ( owner = UPPER (NVL (sch, g_schema))
AND objname = PLGdoir.c_global))
AND objtype = 'TABLE'
AND infotype = 'CURINFO'
ORDER BY SIGN (objid) desc, /* Negative objid means core driver definition */
DECODE (
owner,
'*ALL', DECODE (objname, '*ALL', 99, 98),
DECODE (objname, '*ALL', 97, 96)
),
infoseq;
CURSOR tabinfo_cur (
drv IN VARCHAR2,
sch IN VARCHAR2,
tab IN VARCHAR2,
infotype_in IN VARCHAR2,
seq_in IN INTEGER,
restrict_objid IN VARCHAR2 := NULL,
user_only IN VARCHAR2 := NULL
)
IS
SELECT *
FROM PLG_doir
WHERE ( driver = UPPER (drv)
OR driver = PLGdoir.c_global)
AND ( ( restrict_objid = c_true
AND objid < 0)
OR objid IS NOT NULL)
AND objtype = c_table
AND infotype = UPPER (infotype_in)
AND NVL (infoseq, 1) = NVL (seq_in, 1)
AND ( ( owner = UPPER (NVL (sch, g_schema))
AND objname = UPPER (tab))
OR (user_only != g_trueval
AND ( ( owner = PLGdoir.c_global
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = PLGdoir.c_global)
OR ( owner = UPPER (NVL (sch, g_schema))
AND objname = PLGdoir.c_global))))
ORDER BY SIGN (objid) desc,
DECODE (driver, PLGdoir.c_global, 1, 0),
DECODE (
owner,
'*ALL', DECODE (objname, PLGdoir.c_global, 99, 98),
DECODE (objname, PLGdoir.c_global, 97, 96)
);
CURSOR colinfo_cur (
drv IN VARCHAR2,
sch IN VARCHAR2,
tab IN VARCHAR2,
col IN VARCHAR2,
infotype_in IN VARCHAR,
seq_in IN INTEGER,
restrict_objid IN VARCHAR2,
user_only IN VARCHAR2
)
IS
SELECT *
FROM PLG_doir
WHERE driver = UPPER (drv)
AND ( ( restrict_objid = c_true
AND objid < 0)
OR objid IS NOT NULL)
AND ( ( owner = UPPER (NVL (sch, g_schema))
AND objname = UPPER (tab))
OR ( user_only != g_trueval
AND ( ( owner = PLGdoir.c_global
AND objname = UPPER (tab))
OR ( owner = PLGdoir.c_global
AND objname = PLGdoir.c_global)
OR ( owner = UPPER (NVL (sch, g_schema))
AND objname = PLGdoir.c_global))))
AND attrname LIKE UPPER (col) /* support wildcarding */
AND objtype = c_table
AND attrtype = c_column
AND infotype = UPPER (infotype_in)
AND NVL (infoseq, 1) = NVL (seq_in, 1)
ORDER BY DECODE (
owner, '*ALL',
DECODE (objname, '*ALL', 99, 98),
DECODE (objname, '*ALL', 97, 96));
CURSOR array_cur (
drv IN VARCHAR2,
sch IN VARCHAR2,
nm IN VARCHAR2)
IS
SELECT objname arrayname, attrname pkgname, doc description
FROM plg_doir
WHERE driver = UPPER (drv)
AND owner = sch
AND objtype = c_array
AND objname = UPPER (nm);
/* NOT CURRENTLY USED
CURSOR excl_info_cur (
drv IN VARCHAR2, sch IN VARCHAR2, tab IN VARCHAR2, infotype_in IN VARCHAR2, seq_in IN INTEGER)
IS
SELECT *
FROM PLG_doir
WHERE ( driver = drv
OR v_drv = PLGdoir.c_global)
AND owner = NVL (UPPER (sch), g_schema)
AND objname = UPPER (tab)
AND objtype = c_table
AND infotype = UPPER (infotype_in)
AND NVL (infoseq, 1) = NVL (seq_in, 1);
*/
CURSOR src_cur (drv IN VARCHAR2, src IN VARCHAR2)
IS
SELECT objid, objtype, info, infotype, attrname, attrtype
FROM PLG_doir
WHERE driver = UPPER (drv)
AND owner = c_global
AND objname = c_global
AND attrname = UPPER (src);
FUNCTION PLGdoir_call (drv IN VARCHAR2, srcstring IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN c_pkg ||
'.' ||
srcstring ||
' (''' ||
drv ||
''', [objname], [schema])';
END;
FUNCTION schnn (sch IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN NVL (UPPER (sch), g_schema);
END;
FUNCTION plgalias RETURN VARCHAR2
IS
BEGIN
RETURN 'plg>';
END;
FUNCTION plgalias (str IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN plgalias || str;
END;
/* Put together a standard name */
FUNCTION stdname (
nm IN VARCHAR2,
addon IN VARCHAR2,
use_first IN BOOLEAN := FALSE
)
RETURN VARCHAR2
IS
v_addon PLGadmin.identifier := addon;
retval PLGadmin.identifier;
BEGIN
IF UPPER (nm) = c_global
THEN
retval := nm;
ELSIF INSTR ('$_#', SUBSTR (v_addon, 1, 1)) > 0
THEN
retval := nm || v_addon;
ELSIF INSTR ('$_#', SUBSTR (v_addon, LENGTH (v_addon))) >
0
THEN
retval := v_addon || nm;
ELSE
retval := PLGadmin.ifelse (use_first, nm, v_addon);
END IF;
RETURN UPPER (retval);
END;
/* Get information values */
FUNCTION global
RETURN VARCHAR2
IS
BEGIN
RETURN c_global;
END;
PROCEDURE get_info_and_seq (
drv IN VARCHAR2,
objname_in IN VARCHAR2,
objtype_in IN VARCHAR2,
attrname_in IN VARCHAR2,
attrtype_in IN VARCHAR2,
infotype_in IN PLG_doir.infotype%TYPE,
info_out OUT PLG_doir.info%TYPE,
infoseq_out OUT PLG_doir.infoseq%TYPE,
sch IN VARCHAR2 := NULL
)
IS
v_found BOOLEAN;
CURSOR info_cur (
owner_in IN VARCHAR2,
obj_in IN VARCHAR2,
attr_in IN VARCHAR2
)
IS
SELECT info, infoseq
FROM PLG_doir
WHERE driver = UPPER (drv)
AND owner = NVL (owner_in, g_schema)
AND objtype = UPPER (objtype_in)
AND objname = UPPER (obj_in)
AND attrtype = UPPER (attrtype_in)
AND attrname = UPPER (attr_in)
AND infotype = UPPER (infotype_in);
PROCEDURE getrow (
sch_in IN VARCHAR2,
obj_in IN VARCHAR2,
attr_in IN VARCHAR2,
strval OUT VARCHAR2,
numval OUT INTEGER,
recfound OUT BOOLEAN
)
IS
info_rec info_cur%ROWTYPE;
BEGIN
OPEN info_cur (sch_in, obj_in, attr_in);
FETCH info_cur INTO info_rec;
IF info_cur%found
THEN
strval := info_rec.info;
numval := info_rec.infoseq;
recfound := TRUE;
ELSE
recfound := FALSE;
END IF;
CLOSE info_cur;
END;
BEGIN
getrow (sch, objname_in, attrname_in, info_out, infoseq_out, v_found);
IF NOT v_found
/* ignore attrname as in ins vs. upd */
THEN
getrow (sch, objname_in, c_global, info_out, infoseq_out, v_found);
END IF;
IF NOT v_found
/* ignore objname as in across all tables in schema. */
THEN
getrow (sch, c_global, attrname_in, info_out, infoseq_out, v_found);
END IF;
IF NOT v_found
/* ignore objname and attrname as in across all tables in schema. */
THEN
getrow (sch, c_global, c_global, info_out, infoseq_out, v_found);
END IF;
/* Now look across all schemas */
IF NOT v_found
THEN
getrow (
c_global,
objname_in,
attrname_in,
info_out,
infoseq_out,
v_found
);
END IF;
IF NOT v_found
/* ignore attrname as in ins vs. upd */
THEN
getrow (
c_global,
objname_in,
c_global,
info_out,
infoseq_out,
v_found
);
END IF;
IF NOT v_found
/* ignore objname as in across all tables in schema. */
THEN
getrow (c_global, c_global, c_global, info_out, infoseq_out, v_found);
END IF;
END;
PROCEDURE reset_just_like_drv /* Set cached value to NULL */
IS
BEGIN
g_just_like_drv := NULL;
g_just_like_drv_set := FALSE;
END;
PROCEDURE set_just_like_drv (
drv IN VARCHAR2, cache_only IN BOOLEAN := FALSE)
IS
v_drv PLGadmin.identifier := UPPER (drv);
BEGIN
IF cache_only
THEN
/* Only set the global value to speed up queries. */
g_just_like_drv := drv;
g_just_like_drv_set := TRUE;
ELSE
UPDATE plg_doir
SET infotype = drv
WHERE driver = drv
AND objname = drv
AND objtype = c_driver
AND attrname = c_global
AND attrtype = c_global
AND infotype IS NOT NULL; /* Has to be a JUST LIKE already. */
END IF;
END;
FUNCTION just_like_drv (drv IN VARCHAR2)
RETURN VARCHAR2
IS
retval oir_driver.just_like%TYPE;
BEGIN
IF g_just_like_drv_set THEN retval := g_just_like_drv;
ELSE
SELECT just_like INTO retval
FROM oir_driver
WHERE driver = UPPER (drv);
END IF;
RETURN retval;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
PROCEDURE gettabrec (
drv IN VARCHAR2,
tab IN VARCHAR2,
infotype_in IN PLG_doir.infotype%TYPE,
seq_in IN PLG_doir.infoseq%TYPE := 1,
sch IN VARCHAR2 := NULL,
user_only IN BOOLEAN := FALSE,
tabinfo_out OUT PLG_doir%ROWTYPE,
restrict_objid IN VARCHAR2 := NULL
)
IS
v_user_only PLGadmin.identifier := PLGbool.stg (user_only);
v_drv PLGadmin.identifier := UPPER (drv);
tryitagain BOOLEAN;
retval PLG_doir%ROWTYPE;
BEGIN
/* NOT USED
IF NOT recursing (drv)
THEN
OPEN excl_info_cur (sch, tab, infotype_in, seq_in);
FETCH excl_info_cur INTO tabinfo_out;
tryitagain := excl_info_cur%notfound;
CLOSE excl_info_cur;
ELSE
*/
OPEN tabinfo_cur (drv, sch, tab, infotype_in, seq_in, restrict_objid, v_user_only);
FETCH tabinfo_cur INTO tabinfo_out;
tryitagain := tabinfo_cur%notfound;
CLOSE tabinfo_cur;
IF tryitagain
THEN
v_drv := just_like_drv (drv);
IF v_drv IS NOT NULL
THEN
OPEN tabinfo_cur (v_drv, sch, tab, infotype_in, seq_in, restrict_objid, v_user_only);
FETCH tabinfo_cur INTO tabinfo_out;
CLOSE tabinfo_cur;
END IF;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -