Comparing a table of invalid objects through aliases using the time and date?

Question

I recently posted a question using DBLinks and monitoring invalid objects. Now I've got to make a table and register by date all invalid objects and make comparisons between runs of this code and if there are any changes (objects changed its status, there are new objects, etc.) the code prints the changes like this:

DBLink 1:
(changes found)


DBLink 2:
No changes
.
.
.

Consecutively

for this I created a table

CREATE TABLE Invalid_Objects (
    id NUMBER PRIMARY KEY,
    alias VARCHAR2(30),
    user_db_links VARCHAR2(128),
    owner VARCHAR2(128),
    object_name VARCHAR2(128),
    status VARCHAR2(30),
    object_type VARCHAR2(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

and the code I used is:

-- We enable server output in the SQL client.
SET SERVEROUTPUT ON

DECLARE
    CURSOR cur_previous_results IS
        SELECT owner, object_name, status, object_type
        FROM Invalid_Objects
        WHERE alias = (SELECT MAX(alias) FROM Invalid_Objects);

    TYPE obj_details_type IS RECORD (
      owner         VARCHAR2(128),
      object_name   VARCHAR2(128),
      status        VARCHAR2(30),
      object_type   VARCHAR2(64)
    );

    TYPE obj_details_table IS TABLE OF obj_details_type;

    obj_details_list obj_details_table := obj_details_table();
    v_db_link_name VARCHAR2(128);
    v_row_count NUMBER := 0;
    v_current_alias VARCHAR2(30);
    v_previous_alias VARCHAR2(30);
    v_object_found NUMBER; -- Variable to store the search state of the object
BEGIN
    v_current_alias := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
    SELECT MAX(alias) INTO v_previous_alias FROM Invalid_Objects;

    FOR rec_link IN (SELECT db_link FROM user_db_links) LOOP
        v_db_link_name := rec_link.db_link;
        BEGIN
            EXECUTE IMMEDIATE 'SELECT owner, object_name, status, object_type
                               FROM dba_objects@'||rec_link.db_link||'
                              WHERE status = ''INVALID'''
                            BULK COLLECT INTO obj_details_list;

            FOR i IN 1 .. obj_details_list.COUNT LOOP
                v_row_count := v_row_count + 1;
                INSERT INTO Invalid_Objects (id, alias, user_db_links, owner, object_name, status, object_type)
                VALUES (v_row_count, v_current_alias, v_db_link_name, obj_details_list(i).owner, obj_details_list(i).object_name, obj_details_list(i).status, obj_details_list(i).object_type);
            END LOOP;

            IF v_previous_alias IS NOT NULL THEN
                dbms_output.put_line('Comparing with previous run (alias: ' || v_previous_alias || '):');

                FOR rec_current IN (SELECT owner, object_name, status, object_type FROM Invalid_Objects WHERE alias = v_current_alias) LOOP
                    -- We initialize v_object_found to 0 (false)
                    v_object_found := 0;

                    -- We move the cursor cur_previous_results to find the current object
                    FOR rec_previous IN cur_previous_results LOOP
                        IF rec_previous.owner = rec_current.owner AND rec_previous.object_name = rec_current.object_name THEN
                            -- If we find the current object in the results above, we set v_object_found to 1 (true)
                            v_object_found := 1;
                            EXIT;
                        END IF;
                    END LOOP;

                    -- If v_object_found is 0, the current object was not found in the previous results and is considered new
                    IF v_object_found = 0 THEN
                        dbms_output.put_line('New object: ' || rec_current.object_name);
                    END IF;
                END LOOP;
            ELSE
                dbms_output.put_line('There is no previous run to compare.');
            END IF;
            
            -- Close database link inside FOR loop
            dbms_session.close_database_link(v_db_link_name);
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('Error running dynamic query for link ' || v_db_link_name || ': ' || SQLERRM);
                dbms_session.close_database_link(v_db_link_name); -- we close the link in case of error
        END;
    END LOOP;

    -- Commit after closing all links
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN -- We do not allow a mistake to break the cycle, it continues
        dbms_output.put_line(v_db_link_name || ' errored: ' || SQLERRM);
END;
/

but it only prints this error:

There is no previous run to compare.
Error executing dynamic query for DBLINK link:
ORA-02080: database link is in use
DBLINK errored: ORA-02080: database link is in
use

PL/SQL procedure completed successfully.

Is my reasoning ok, or is there another way so I can close the DBLinks?

Answer 1

You need to issue a COMMIT before attempting to close a link your session has used in the current transaction, even if it's just a SELECT statement. Then it will let you close the link.

        COMMIT; -- add here
        dbms_session.close_database_link(v_db_link_name);
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error running dynamic query for link ' || v_db_link_name || ': ' || SQLERRM);
            COMMIT; -- and here
            dbms_session.close_database_link(v_db_link_name); -- we close the link in case of error
    END;