Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
# Analizza le dipendenze degli oggetti per le migrazioni parziali del database da Oracle a PostgreSQL
*Nome utente, Amazon Web Services*
## Riepilogo
Questo modello descrive l'importanza di identificare e gestire sistematicamente le dipendenze del sistema durante la migrazione di un database Oracle parziale su Amazon Relational Database Service (Amazon RDS) o Amazon Aurora PostgreSQL. In una migrazione parziale, viene migrato solo un sottoinsieme di oggetti e dati del database originale, mentre il database di origine continua a funzionare e servire applicazioni che dipendono da componenti non migrati
È necessario identificare e analizzare l'ambito della migrazione quando si gestiscono database su larga scala con applicazioni strettamente collegate con dipendenze upstream e downstream. Per iniziare una migrazione parziale, identificate gli oggetti dell'ambito, tra cui tabelle, trigger, viste, stored procedure, funzioni e pacchetti. Il processo di identificazione dell'ambito segue un approccio globale:
+ Gli oggetti dell'ambito di primo livello vengono identificati tramite riferimenti diretti nel codice dell'applicazione e nei lavori critici specifici del modulo.
+ Gli oggetti di secondo livello sono derivati attraverso un'analisi completa delle dipendenze.
Una volta compreso come interagiscono le diverse parti del sistema, è possibile pianificare meglio l'ordine corretto per lo spostamento dei componenti del database e ridurre il rischio di errori di migrazione. La tabella seguente elenca diversi tipi di analisi delle dipendenze.
|
|
| Tipo di analisi | Aree di interesse | Scopo |
| --- |--- |--- |
| Dipendenze dagli oggetti | TabelleVisualizzazioniProcedure archiviateFunzioniTriggers | Identifica le relazioni tra gli oggetti del database e le relative strutture gerarchiche |
| Dipendenze dei segmenti | Relazioni con chiave esternaPortachiavi primariRiferimenti tra schemi | Mappa le relazioni tra i dati e mantiene l'integrità referenziale |
| Dipendenze di sicurezza | Autorizzazioni degli utentiGerarchie di ruoliPrivilegi degli oggetti | Garantisce il corretto controllo degli accessi, la migrazione e la manutenzione della sicurezza |
| Modelli di accesso | Operazioni di letturaOperazioni di scrittura | Determina i modelli di interazione del database |
Per mantenere la coerenza tra i sistemi di origine e di destinazione, stabilisci meccanismi di sincronizzazione dei dati durante il periodo di transizione. È inoltre necessario modificare il codice e le funzioni dell'applicazione per gestire la distribuzione dei dati tra i database Oracle di origine e PostgreSQL di destinazione.
## Prerequisiti e limitazioni
**Prerequisiti**
+ Un attivo Account AWS
+ Un database Oracle (origine)
+ Un Amazon RDS o Amazon Aurora PostgreSQL (destinazione)
**Versioni del prodotto**
+ Oracle 19c o versioni successive
+ PostgreSQL 16 o versione successiva
## Architecture
**Stack tecnologico di origine**
+ Oracle 19c o versione successiva
**Stack tecnologico Target**
+ Amazon RDS o Amazon Aurora PostgreSQL
**Architettura Target**
Il diagramma seguente mostra il processo di migrazione da un database Oracle locale ad Amazon RDS for Oracle, che prevede quanto segue:
+ Identificazione delle dipendenze del database
+ Migrazione del codice e degli oggetti del database utilizzando AWS Schema Conversion Tool ()AWS SCT
+ Migrazione dei dati utilizzando AWS Database Migration Service ()AWS DMS
+ Replica delle modifiche in corso tramite Change Data Capture (CDC) utilizzando AWS DMS
Per ulteriori informazioni, consulta [Integrazione AWS Database Migration Service con AWS Schema Conversion Tool nella documentazione](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_DMSIntegration.html). AWS
## Tools (Strumenti)
**Servizi AWS**
+ [Amazon Relational Database Service (Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html)) per Oracle ti aiuta a configurare, gestire e scalare un database relazionale Oracle in. Cloud AWS
+ Amazon Aurora è un motore di database relazionale completamente gestito creato per il cloud e compatibile con MySQL e PostgreSQL.
+ AWS Schema Conversion Tool (AWS SCT) supporta migrazioni di database eterogenee convertendo automaticamente lo schema del database di origine e la maggior parte del codice personalizzato in un formato compatibile con il database di destinazione.
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) consente di migrare gli archivi di dati all'interno Cloud AWS o tra combinazioni di configurazioni cloud e locali.
**Altri servizi**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) è un ambiente di sviluppo integrato che semplifica lo sviluppo e la gestione dei database Oracle nelle implementazioni tradizionali e basate sul cloud. [Per questo modello, è possibile utilizzare SQL\*Plus.](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm)
## Best practice
Per le best practice relative al provisioning e alla migrazione di un database Oracle, consulta [Best practice per la migrazione ad Amazon RDS for](https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-oracle-database/best-practices.html) Oracle.
## Epiche
### Identifica le dipendenze degli oggetti
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea una tabella di oggetti. | Identifica gli oggetti essenziali per la funzionalità dell'applicazione e crea una tabella denominata`DEPENDENT_ANALYSIS_BASELINE`. Aggiungi record per ogni oggetto alla tabella. Per un esempio, consulta la sezione *Informazioni aggiuntive*. | Ingegnere dei dati, DBA |
| Crea una procedura di database. | Create una procedura memorizzata denominata `sp_object_dependency_analysis` per analizzare le dipendenze degli oggetti in entrambe le direzioni (avanti e indietro) utilizzando i dati della `DBA_DEPENDENCIES` tabella. Per un esempio, consultate la sezione *Informazioni aggiuntive*. | Ingegnere dei dati, DBA |
| Esegui la procedura. | Esegui gli script a ogni livello successivo fino a quando non vengono trovate nuove dipendenze tra gli oggetti. Tutte le dipendenze e i livelli vengono memorizzati nella tabella. `DEPENDENT_ANALYSIS_BASELINE` | DBA, ingegnere dei dati |
### Crea una procedura per le dipendenze a livello di segmento
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea una tabella delle dipendenze. | Crea una tabella delle dipendenze a livello di segmento denominata. `REFERENTIAL_ANALYSIS_BASELINE` Quando vengono scoperte tutte le dipendenze a livello di oggetto, controlla le tabelle principali interrogando la tabella. `DEPENDENT_ANALYSIS_BASELINE` `DBA_CONSTRAINT`
Escludi le dipendenze in cui le tabelle di base sono referenziate da altre tabelle. Il backfilling gestisce queste relazioni. Quello che segue è uno script di esempio: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));
| Ingegnere dei dati, DBA |
| Crea una procedura di database. | Crea una procedura chiamata `SP_OBJECT_REFERENTIAL_ANALYSIS` e genera un'analisi referenziale per tutti gli oggetti identificati. Per un esempio, consultate la sezione *Informazioni aggiuntive*. | Ingegnere dei dati, DBA |
| Esegui la procedura. | Esegui la procedura per ottenere dipendenze referenziali. Genera i dettagli dell'oggetto di analisi referenziale in. `REFERENTIAL_ANALYSIS_BASELINE` | Ingegnere dei dati, DBA |
### Identifica oggetti che leggono e scrivono
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea tabelle per leggere e scrivere oggetti. | Utilizzate il seguente script per creare una tabella di oggetti di lettura denominata `TABLE_READ_OBJECT_DETAILS` e una tabella di oggetti di scrittura denominata`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));
| Ingegnere dei dati, DBA |
| Crea una procedura per l'analisi. | Create rispettivamente le procedure `SP_READER_OBJECTS_ANALYSIS` e `SP_WRITER_OBJECTS_ANALYSIS` quelle per l'analisi degli oggetti di lettura e degli oggetti di scrittura. Queste procedure utilizzano il pattern matching per trovare oggetti correlati. Per un esempio, consultate la sezione *Informazioni aggiuntive*. | Ingegnere dei dati, DBA |
| Esegui le procedure. | Eseguite queste procedure per identificare gli oggetti dipendenti. | DBA, ingegnere dei dati |
### Rivedi i privilegi del database
| Operazione | Description | Competenze richieste |
| --- | --- | --- |
| Crea una tabella per la revisione dei privilegi. | Crea una tabella per analizzare i privilegi denominati. `OBJECT_PRIVS_ANALYSIS` Per acquisire in modo ricorsivo i privilegi degli oggetti nella `DEPENDENT_ANALYSIS_BASELINE` tabella, utilizzate lo script seguente:CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));
| Ingegnere dei dati, DBA |
| Creare una procedura per la revisione dei privilegi. | Crea una procedura denominata`SP_OBJECT_PRIVS_ANALYSIS`. Genera un'analisi dei privilegi per gli oggetti identificati. Per un esempio, consulta la sezione *Informazioni aggiuntive*. | DBA, ingegnere dei dati |
| Esegui la procedura. | Esegui la procedura per acquisirli nella `OBJECT_PRIVS_ANALYSIS` tabella. | DBA, ingegnere dei dati |
## risoluzione dei problemi
| Problema | Soluzione |
| --- | --- |
| Impossibile accedere alle tabelle dei dizionari | Assicuratevi che l'utente che ha creato gli oggetti di analisi possa accedere alle tabelle DBA. |
## Risorse correlate
**Documentazione AWS**
+ [Documentazione di Amazon RDS e Aurora](https://docs.aws.amazon.com/rds/)
+ [Playbook di migrazione da Oracle Database 19c ad 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 Che cos'è?](https://docs.aws.amazon.com/dms/latest/userguide/)
+ [Cos'è AWS Schema Conversion Tool?](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/)
**Altra documentazione**
+ [Oggetti del database Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Objects.html)
## Informazioni aggiuntive
**Script per `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));
```
**Procedura per `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;
```
**Script per `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;
```
**Script per `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;
```
**Script per `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;
```
**Procedura per `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;
```