|
21 ELSIF V_POSITION = 3 THEN
22 V_RETURN := V_RETURN V_STR_PART;
23 ELSE
24 RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID');
25 END IF;
26
27 V_POSITION := INSTR(V_STR, ',');
28 END LOOP;
29 RETURN REPLACE(V_RETURN , ',');
30 END F_ADD_PREFIX_ZERO;
31
32 FUNCTION F_DUMP_FROM_ROWID
33 (
34 P_DUMP_ROWID IN UROWID,
35 P_OWNER IN VARCHAR2,
36 P_INDEX_NAME IN VARCHAR2,
37 P_COLUMN_POSITION IN NUMBER DEFAULT 1
38 )
39 RETURN VARCHAR2 AS
40 V_COLUMN_TYPE DBA_TAB_COLUMNS.DATA_TYPE%TYPE;
41
42 V_LENGTH_STR VARCHAR2(10);
43 V_LENGTH NUMBER DEFAULT 7;
44 V_DUMP_ROWID VARCHAR2(30000);
45
46 V_DATE_STR VARCHAR2(100);
47 TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48 V_DATE T_DATE;
49
50 BEGIN
51
52 --根据SCHEMA、INDEX_NAME和COLUMN_NAME得到COLUMN的数据类型
53 SELECT T.DATA_TYPE
54 INTO V_COLUMN_TYPE
55 FROM DBA_IND_COLUMNS I, DBA_TAB_COLUMNS T
56 WHERE I.TABLE_NAME = T.TABLE_NAME
57 AND I.TABLE_OWNER = T.OWNER
58 AND I.COLUMN_NAME = T.COLUMN_NAME
59 AND I.TABLE_OWNER = P_OWNER
60 AND I.INDEX_NAME = P_INDEX_NAME
61 AND I.COLUMN_POSITION = P_COLUMN_POSITION;
62
63 --根据COLUMN在索引中的位置，循环找到这个COLUMN对应的ROWID
64 FOR I IN 1..P_COLUMN_POSITION LOOP
65
66 --如果COLUMN的长度超过127，即表示长度的位超过7f，则开始用两位来存储长度，其中第一位以8开始。
67 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH, 1)) INTO V_LENGTH_STR FROM DUAL;
68 IF SUBSTR(V_LENGTH_STR, 1, 1) = '8' THEN
69 SELECT SUBSTR(F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH, 2)), 2) INTO V_LENGTH_STR FROM DUAL;
70 V_LENGTH_STR := TO_CHAR(TO_NUMBER(REPLACE(V_LENGTH_STR, ','), 'XXXX'));
71 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH + 2, TO_NUMBER(V_LENGTH_STR))) INTO V_DUMP_ROWID
72 FROM DUAL;
73 V_LENGTH := V_LENGTH + TO_NUMBER(V_LENGTH_STR) + 2;
74 ELSE
75 V_LENGTH_STR := TO_CHAR(TO_NUMBER(V_LENGTH_STR, 'XXX'));
76 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH + 1, TO_NUMBER(V_LENGTH_STR))) INTO V_DUMP_ROWID
77 FROM DUAL;
78 V_LENGTH := V_LENGTH + TO_NUMBER(V_LENGTH_STR) + 1;
79 END IF;
80 END LOOP;
81
82 IF V_COLUMN_TYPE = 'VARCHAR2' OR V_COLUMN_TYPE = 'CHAR' THEN
83
84 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID ',', INSTR(V_DUMP_ROWID, ','));
上一篇:Oracle如何配置逻辑备用数据库
下一篇:Sql server动态建立数据对象结构
|
精品推荐