本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 分析從 Oracle 到 PostgreSQL 的部分資料庫遷移的物件相依性
*anuradha chintha，Amazon Web Services*
## 總結
此模式說明將部分 Oracle 資料庫遷移至 Amazon Relational Database Service (Amazon RDS) 或 Amazon Aurora PostgreSQL 時，系統識別和管理系統相依性的重要性。在部分遷移中，只會遷移來自原始資料庫的資料庫物件和資料子集，而來源資料庫會繼續操作和提供依賴非遷移元件的應用程式
處理具有上游和下游相依性之緊密耦合應用程式的大規模資料庫時，您必須識別和分析遷移的範圍。若要開始部分遷移，請識別範圍物件，包括資料表、觸發條件、檢視、預存程序、函數和套件。範圍識別程序遵循全方位的方法：
+ 第一層範圍物件是透過應用程式程式碼和關鍵模組特定任務中的直接參考來識別。
+ 第二層物件是透過全面的相依性分析衍生的。
當您了解系統的不同部分如何互動時，您可以更好地規劃移動資料庫元件的正確順序，並降低遷移失敗的風險。下表列出不同類型的相依性分析。
|
|
| 分析類型 | 重點領域 | 用途 |
| --- |--- |--- |
| 物件相依性 | 表格檢視預存程序函數觸發 | 識別資料庫物件與其階層結構之間的關係 |
| 區段相依性 | 外部金鑰關係主要金鑰鏈跨結構描述參考 | 映射資料關係並維護參考完整性 |
| 安全相依性 | 使用者許可角色階層物件權限 | 確保適當的存取控制遷移和安全性維護 |
| 存取模式 | 讀取操作寫入操作 | 決定資料庫互動模式 |
若要維持來源和目標系統之間的一致性，請在轉換期間建立資料同步機制。您還必須修改應用程式程式碼和函數，以處理來源 Oracle 和目標 PostgreSQL 資料庫之間的資料分佈。
## 先決條件和限制
**先決條件**
+ 作用中 AWS 帳戶
+ Oracle 資料庫 （來源）
+ Amazon RDS 或 Amazon Aurora PostgreSQL （目標）
**產品版本**
+ Oracle 19c 或更新版本
+ PostgreSQL 16 或更新版本
## Architecture
**來源技術堆疊**
+ Oracle 19c 或更新版本
**目標技術堆疊**
+ Amazon RDS 或 Amazon Aurora PostgreSQL
**目標架構**
下圖顯示從內部部署 Oracle 資料庫到 Amazon RDS for Oracle 的遷移程序，其中包含下列項目：
+ 識別資料庫相依性
+ 使用 AWS Schema Conversion Tool (AWS SCT) 遷移資料庫程式碼和物件
+ 使用 AWS Database Migration Service (AWS DMS) 遷移資料
+ 使用 透過變更資料擷取 (CDC) 複寫持續變更 AWS DMS
如需詳細資訊，請參閱 AWS 文件中的[整合 AWS Database Migration Service 與 AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_DMSIntegration.html) 。
![\[alt text not found\]](http://docs.aws.amazon.com/zh_tw/prescriptive-guidance/latest/patterns/images/pattern-img/90160825-3199-4382-95a8-ad63139c5c89/images/b09c36a4-27fa-412e-877e-57a31bcce0dc.png)
## 工具
**AWS 服務**
+ [適用於 Oracle 的 Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) 可協助您在 中設定、操作和擴展 Oracle 關聯式資料庫 AWS 雲端。
+ Amazon Aurora 是一種全受管關聯式資料庫引擎，專為雲端而建置，並與 MySQL 和 PostgreSQL 相容。
+ AWS Schema Conversion Tool (AWS SCT) 透過自動將來源資料庫結構描述和大部分自訂程式碼轉換為與目標資料庫相容的格式，支援異質資料庫遷移。
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) 可協助您將資料存放區遷移到 AWS 雲端 或在雲端和內部部署設定的組合之間遷移。
**其他服務**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) 是一種整合的開發環境，可簡化傳統和雲端部署中 Oracle 資料庫的開發和管理。對於此模式，您可以使用 [SQL\$1Plus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm)。
## 最佳實務
如需佈建和遷移 Oracle 資料庫的最佳實務，請參閱[遷移至 Amazon RDS for Oracle 的最佳實務](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html)。
## 史詩
### 識別物件相依性
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 建立物件資料表。 | 識別對應用程式功能至關重要的物件，並建立名為 的資料表`DEPENDENT_ANALYSIS_BASELINE`。將每個物件的記錄新增至資料表。如需範例，請參閱*其他資訊*一節。 | 資料工程師，DBA |
| 建立資料庫程序。 | 建立名為 的預存程序`sp_object_dependency_analysis`，使用`DBA_DEPENDENCIES`資料表中的資料以雙向 （向前和向後） 分析物件相依性。如需範例，請參閱*其他資訊*一節。 | 資料工程師，DBA |
| 執行程序。 | 在每個連續層級執行指令碼，直到找不到新的物件相依性為止。所有相依性和關卡都存放在 `DEPENDENT_ANALYSIS_BASELINE` 資料表中。 | DBA，資料工程師 |
### 建立區段層級相依性的程序
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 建立相依性資料表。 | 建立名為 的區段層級相依性資料表`REFERENTIAL_ANALYSIS_BASELINE`。發現所有物件層級相依性時，請透過查詢資料表`DEPENDENT_ANALYSIS_BASELINE`來檢查 的父`DBA_CONSTRAINT`資料表。排除其他資料表參考基準資料表的相依性。回填會處理這些關係。以下是範例指令碼：CREATE TABLE REFERENTIAL_ANALYSIS_BASELINE
(CHILD_OWNER VARCHAR2(50 BYTE),
CHILD_NAME VARCHAR2(100 BYTE),
PARENT_OWNER VARCHAR2(50 BYTE),
PARENT_NAME VARCHAR2(50 BYTE),
REFERENCE_PATH VARCHAR2(1000 BYTE));
| 資料工程師，DBA |
| 建立資料庫程序。 | 建立名為 的程序`SP_OBJECT_REFERENTIAL_ANALYSIS`，並產生所有已識別物件的參考分析。如需範例，請參閱*其他資訊*一節。 | 資料工程師，DBA |
| 執行程序。 | 執行 程序以取得參考相依性。在 中產生參考分析物件詳細資訊`REFERENTIAL_ANALYSIS_BASELINE`。 | 資料工程師，DBA |
### 識別讀取和寫入的物件
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 建立讀取和寫入物件的資料表。 | 使用下列指令碼建立名為 的讀取物件資料表`TABLE_READ_OBJECT_DETAILS`和名為 的寫入物件資料表`TABLE_WRITE_OBJECT_DETAILS`：CREATE TABLE TABLE_READ_OBJECT_DETAILS
(OWNER VARCHAR2(50 BYTE),
TAB_NAME VARCHAR2(50 BYTE),
READER_OWNER VARCHAR2(50 BYTE),
READER_NAME VARCHAR2(50 BYTE),
READER_TYPE VARCHAR2(50 BYTE));
CREATE TABLE TABLE_WRITE_OBJECT_DETAILS
(TABLE_NAME VARCHAR2(100 BYTE),
WRITEOBJ_OWNER VARCHAR2(100 BYTE),
WRITEOBJ_NAME VARCHAR2(100 BYTE),
WRITEOBJ_TYPE VARCHAR2(100 BYTE),
LINE VARCHAR2(100 BYTE),
TEXT VARCHAR2(4000 BYTE),
OWNER VARCHAR2(50 BYTE));
| 資料工程師，DBA |
| 建立分析程序。 | 分別建立`SP_WRITER_OBJECTS_ANALYSIS`用於分析讀取物件和寫入物件的程序 `SP_READER_OBJECTS_ANALYSIS`和 。這些程序使用模式比對來尋找相關物件。如需範例，請參閱*其他資訊*一節。 | 資料工程師，DBA |
| 執行程序。 | 執行這些程序來識別相依物件。 | DBA，資料工程師 |
### 檢閱資料庫權限
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 建立用於檢閱權限的資料表。 | 建立資料表以分析名為 的權限`OBJECT_PRIVS_ANALYSIS`。若要以遞迴方式擷取`DEPENDENT_ANALYSIS_BASELINE`資料表中的物件權限，請使用下列指令碼：CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));
| 資料工程師，DBA |
| 建立檢閱權限的程序。 | 建立名為 的程序`SP_OBJECT_PRIVS_ANALYSIS`。產生已識別物件的權限分析。如需範例，請參閱*其他資訊*一節。 | DBA，資料工程師 |
| 執行 程序。 | 執行 程序，在 `OBJECT_PRIVS_ANALYSIS` 資料表中擷取它們。 | DBA，資料工程師 |
## 疑難排解
| 問題 | 解決方案 |
| --- | --- |
| 無法存取字典資料表 | 確保建立分析物件的使用者可以存取 DBA 資料表。 |
## 相關資源
**AWS 文件**
+ [Amazon RDS 和 Aurora 文件](https://docs.aws.amazon.com/rds/)
+ [Oracle 資料庫 19c 至 Amazon Aurora PostgreSQL 遷移手冊](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html)
+ [什麼是 AWS Database Migration Service？](https://docs.aws.amazon.com/dms/latest/userguide/)
+ [什麼是 AWS Schema Conversion Tool？](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/)
**其他文件**
+ [Oracle 資料庫物件](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)
## 其他資訊
**的指令碼 `DEPENDENT_ANALYSIS_BASELINE`**
```
CREATE TABLE DEPENDENT_ANALYSIS_BASELINE
(OWNER VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_NAME VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_TYPE VARCHAR2(20 BYTE),
DEPEDNCY_LEVEL NUMBER,
PROJECT_NEED VARCHAR2(20 BYTE),
CATAGORY VARCHAR2(4000 BYTE),
COMMENTS VARCHAR2(4000 BYTE),
CATAGORY1 CLOB,
COMMENTS1 CLOB,
CUSTOMER_COMMENTS VARCHAR2(1000 BYTE),
BACKFILL_TO_GUS VARCHAR2(1000 BYTE),
BACKFILL_NEAR_REAL_TIME_OR_BATCH VARCHAR2(1000 BYTE),
PK_EXISTS VARCHAR2(3 BYTE),
UI_EXISTS VARCHAR2(3 BYTE),
LOB_EXISTS VARCHAR2(3 BYTE),
MASTER_LINK VARCHAR2(100 BYTE),
CONSTRAINT PK_DEPENDENT_ANALYSIS_BASELINE PRIMARY KEY (OWNER,OBJECT_NAME,OBJECT_TYPE));
```
**的程序 `SP_WRITER_OBJECTS_ANALYSIS`**
```
CREATE OR REPLACE PROCEDURE SP_WRITER_OBJECTS_ANALYSIS IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_WRITE_OBJECT_DETAILS';
FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
LOOP
INSERT INTO TABLE_WRITE_OBJECT_DETAILS(OWNER, TABLE_NAME, WRITEOBJ_OWNER, WRITEOBJ_NAME, WRITEOBJ_TYPE, LINE, TEXT)
SELECT DISTINCT I.OWNER, I.OBJECT_NAME, OWNER WRITEOBJ_OWNER, NAME, TYPE, LINE, TRIM(TEXT)
FROM DBA_SOURCE
WHERE UPPER(TEXT) LIKE '%' || I.OBJECT_NAME || '%'
AND (UPPER(TEXT) LIKE '%INSERT%' || I.OBJECT_NAME || '%'
OR UPPER(TEXT) LIKE '%UPDATE%' || I.OBJECT_NAME || '%'
OR UPPER(TEXT) LIKE '%DELETE%' || I.OBJECT_NAME || '%'
OR UPPER(TEXT) LIKE '%UPSERT%' || I.OBJECT_NAME || '%'
OR UPPER(TEXT) LIKE '%MERGE%' || I.OBJECT_NAME || '%')
AND UPPER(TEXT) NOT LIKE '%PROCEDURE%'
AND UPPER(TEXT) NOT LIKE 'PROCEDURE%'
AND UPPER(TEXT) NOT LIKE '%FUNCTION%'
AND UPPER(TEXT) NOT LIKE 'FUNCTION%'
AND UPPER(TEXT) NOT LIKE '%TRIGGER%'
AND UPPER(TEXT) NOT LIKE 'TRIGGER%'
AND UPPER(TRIM(TEXT)) NOT LIKE '%AFTER UPDATE%'
AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE UPDATE%'
AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE INSERT%'
AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER INSERT%'
AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE DELETE%'
AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER DELETE%'
AND UPPER(TRIM(TEXT)) NOT LIKE '%GGLOGADM.GG_LOG_ERROR%'
AND (TRIM(TEXT) NOT LIKE '/*%' AND TRIM(TEXT) NOT LIKE '--%' )
AND (OWNER, NAME, TYPE) IN (SELECT OWNER, NAME, TYPE FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = I.OBJECT_NAME);
END LOOP;
END;
```
**的指令碼 `SP_READER_OBJECTS_ANALYSIS`**
```
CREATE OR REPLACE PROCEDURE SP_READER_OBJECTS_ANALYSIS IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_READ_OBJECT_DETAILS';
FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
LOOP
INSERT INTO TABLE_READ_OBJECT_DETAILS
SELECT DISTINCT i.owner, i.object_name, owner, name, type
FROM dba_dependencies
WHERE referenced_name = I.OBJECT_NAME
AND referenced_type = 'TABLE'
AND type NOT IN ('SYNONYM', 'MATERIALIZED VIEW', 'VIEW')
AND (owner, name, type) NOT IN (
SELECT DISTINCT owner, trigger_name, 'TRIGGER'
FROM dba_triggers
WHERE table_name = I.OBJECT_NAME
AND table_owner = i.owner
UNION ALL
SELECT DISTINCT owner, name, type
FROM dba_source
WHERE upper(text) LIKE '%' || I.OBJECT_NAME || '%'
AND (upper(text) LIKE '%INSERT %' || I.OBJECT_NAME || '%'
OR upper(text) LIKE '%UPDATE% ' || I.OBJECT_NAME || '%'
OR upper(text) LIKE '%DELETE %' || I.OBJECT_NAME || '%'
OR upper(text) LIKE '%UPSERT %' || I.OBJECT_NAME || '%'
OR upper(text) LIKE '%MERGE %' || I.OBJECT_NAME || '%')
AND upper(text) NOT LIKE '%PROCEDURE %'
AND upper(text) NOT LIKE 'PROCEDURE %'
AND upper(text) NOT LIKE '%FUNCTION %'
AND upper(text) NOT LIKE 'FUNCTION %'
AND upper(text) NOT LIKE '%TRIGGER %'
AND upper(text) NOT LIKE 'TRIGGER %'
AND upper(trim(text)) NOT LIKE 'BEFORE INSERT %'
AND upper(trim(text)) NOT LIKE 'BEFORE UPDATE %'
AND upper(trim(text)) NOT LIKE 'BEFORE DELETE %'
AND upper(trim(text)) NOT LIKE 'AFTER INSERT %'
AND upper(trim(text)) NOT LIKE 'AFTER UPDATE %'
AND upper(trim(text)) NOT LIKE 'AFTER DELETE %'
AND (trim(text) NOT LIKE '/*%' AND trim(text) NOT LIKE '--%'));
END LOOP;
END;
```
**的指令碼 `SP_OBJECT_REFERENTIAL_ANALYSIS`**
```
CREATE OR REPLACE PROCEDURE SP_OBJECT_REFERENTIAL_ANALYSIS IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE REFERENTIAL_ANALYSIS_BASELINE';
INSERT INTO REFERENTIAL_ANALYSIS_BASELINE
WITH rel AS (
SELECT DISTINCT c.owner, c.table_name, c.r_owner r_owner,
(SELECT table_name FROM dba_constraints
WHERE constraint_name = c.r_constraint_name
AND owner = c.r_owner) r_table_name
FROM dba_constraints c
WHERE constraint_type = 'R'
AND c.owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
AND c.r_owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')),
tab_list AS (
SELECT OWNER, object_name
FROM DEPENDENT_ANALYSIS_BASELINE
WHERE UPPER(OBJECT_TYPE) = 'TABLE')
SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
r_table_name parent, SYS_CONNECT_BY_PATH(r_table_name, ' -> ') || ' -> ' || table_name PATH
FROM rel
START WITH (r_owner, r_table_name) IN (SELECT * FROM tab_list)
CONNECT BY NOCYCLE (r_owner, r_table_name) = ((PRIOR owner, PRIOR table_name))
UNION
SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
r_table_name parent, SYS_CONNECT_BY_PATH(table_name, ' -> ') || ' -> ' || r_table_name PATH
FROM rel
START WITH (owner, table_name) IN (SELECT * FROM tab_list)
CONNECT BY NOCYCLE (owner, table_name) = ((PRIOR r_owner, PRIOR r_table_name));
END;
```
**的指令碼 `SP_OBJECT_PRIVS_ANALYSIS`**
```
CREATE OR REPLACE PROCEDURE SP_OBJECT_PRIVS_ANALYSIS IS
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
BEGIN
V_SQL := 'TRUNCATE TABLE OBJECT_PRIVS_ANALYSIS';
EXECUTE IMMEDIATE V_SQL;
FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
LOOP
INSERT INTO OBJECT_PRIVS_ANALYSIS(OWNER, OBJECT_NAME, USER_NAME, PRIVS)
WITH obj_to_role AS (
SELECT DISTINCT GRANTEE role_name,
DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE',
'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs
FROM DBA_TAB_PRIVS t, DBA_ROLES r
WHERE OWNER = I.OWNER
AND TYPE = 'TABLE'
AND TABLE_NAME = I.OBJECT_NAME
AND t.GRANTEE = r.ROLE
AND r.ROLE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
)
SELECT I.OWNER, I.OBJECT_NAME, grantee, privs
FROM (
-- Recursively Role to User mapping with privilege
SELECT DISTINCT grantee, privs
FROM (SELECT rp.granted_role, rp.grantee, privs,
(SELECT DECODE(COUNT(*), 0, 'ROLE', 'USER')
FROM (SELECT 'User' FROM DBA_users WHERE username = rp.GRANTEE)) grantee_type
FROM DBA_role_privs rp, obj_to_role r
WHERE rp.granted_role = r.role_name
AND grantee IN ((SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
UNION (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N'))
AND granted_role IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
START WITH granted_role IN (SELECT DISTINCT role_name FROM obj_to_role)
CONNECT BY granted_role = PRIOR grantee)
WHERE grantee_type = 'USER'
)
UNION
(
-- Direct Object grants to User
SELECT I.OWNER, I.OBJECT_NAME, GRANTEE,
DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE',
'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs
FROM DBA_TAB_PRIVS, DBA_USERS
WHERE GRANTEE = USERNAME
AND OWNER = I.OWNER
AND TYPE = 'TABLE'
AND TABLE_NAME = I.OBJECT_NAME
)
ORDER BY 2 DESC;
END LOOP;
END;
```
**的程序 `SP_OBJECT_DEPENDENCY_ANALYSIS`**
```
CREATE OR REPLACE PROCEDURE SP_OBJECT_DEPENDENCY_ANALYSIS (v_level NUMBER) IS
TYPE typ IS RECORD (
schema VARCHAR2(100),
obj_type VARCHAR2(100),
obj_name VARCHAR2(100),
path VARCHAR2(5000)
);
TYPE array IS TABLE OF typ;
l_data array;
c SYS_REFCURSOR;
l_errors NUMBER;
l_errno NUMBER;
l_msg VARCHAR2(4000);
l_idx NUMBER;
l_level NUMBER;
BEGIN
l_level := v_level + 1;
OPEN c FOR
WITH obj_list AS (
SELECT owner schema_name, object_type, object_name
FROM DEPENDENT_ANALYSIS_BASELINE
WHERE depedncy_level = v_level
),
fw_dep_objects AS (
SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
referenced_type, SYS_CONNECT_BY_PATH(name, ' -> ') || ' -> ' || referenced_name PATH
FROM dba_dependencies
START WITH (owner, CASE WHEN type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE type END, name)
IN (SELECT schema_name, object_type, object_name FROM obj_list)
CONNECT BY NOCYCLE (owner, type, name) =
((PRIOR referenced_owner, PRIOR referenced_type, PRIOR referenced_name))
),
bw_dep_objects AS (
SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
referenced_type, SYS_CONNECT_BY_PATH(name, ' <- ') || ' <- ' || referenced_name PATH
FROM dba_dependencies
START WITH (referenced_owner, CASE WHEN referenced_type = 'PACKAGE BODY' THEN 'PACKAGE'
ELSE referenced_type END, referenced_name) IN (SELECT schema_name, object_type, object_name FROM obj_list)
CONNECT BY NOCYCLE (referenced_owner, referenced_type, referenced_name) =
((PRIOR owner, PRIOR type, PRIOR name))
)
SELECT * FROM (
(SELECT DISTINCT referenced_owner schema, referenced_type obj_type,
referenced_name obj_name, path FROM fw_dep_objects)
UNION
(SELECT DISTINCT owner schema, type obj_type, name obj_name, path
FROM bw_dep_objects)
)
WHERE schema IN (SELECT username FROM all_users WHERE oracle_maintained = 'N')
ORDER BY obj_type;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT 100;
BEGIN
FORALL i IN 1..l_data.count SAVE EXCEPTIONS
INSERT INTO DEPENDENT_ANALYSIS_BASELINE (
owner, object_name, object_type, catagory, depedncy_level, project_need, comments
)
VALUES (
l_data(i).schema,
l_data(i).obj_name,
CASE WHEN l_data(i).obj_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE l_data(i).obj_type END,
'level ' || l_level || ' dependency',
l_level,
'',
'from dependency proc' || l_data(i).path
);
EXCEPTION
WHEN OTHERS THEN
l_errors := sql%bulk_exceptions.count;
FOR i IN 1..l_errors LOOP
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := SQLERRM(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;
UPDATE DEPENDENT_ANALYSIS_BASELINE
SET catagory1 = catagory1 || ', found in level' || l_level || ' dependent of ' || l_data(l_idx).path,
comments1 = comments1 || ', from dependency proc exception ' || l_data(i).path
WHERE owner = l_data(l_idx).schema
AND object_name = l_data(l_idx).obj_name
AND object_type = l_data(l_idx).obj_type;
END LOOP;
END;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END;
```