Oracle存储过程开发需求：同步PUBUSER非系统表列至add_definitions

Got it, let's sort out this Oracle stored procedure problem for you. The goal is to reliably catch new non-system tables (and their columns) under the PUBUSER schema and insert their details into add_definitions—and make sure it works every time you run it, right? Let's replace that broken test_ad procedure with something solid.

First: Confirm Your add_definitions Table Structure #

Before we dive into the procedure, let's make sure your target table can store the necessary metadata. Here's a sample DDL (adjust columns if your existing table uses different names):

CREATE TABLE add_definitions (
    object_type VARCHAR2(20) NOT NULL, -- 'TABLE' or 'COLUMN'
    object_owner VARCHAR2(30) NOT NULL,
    object_name VARCHAR2(30) NOT NULL,
    column_name VARCHAR2(30), -- Null for table-level entries
    created_date DATE DEFAULT SYSDATE,
    CONSTRAINT pk_add_defs PRIMARY KEY (object_type, object_owner, object_name, column_name)
);

The primary key here prevents duplicate entries if you run the procedure multiple times—super important for idempotency.

The Working Stored Procedure #

Here's a robust procedure that handles both new tables and their columns, and skips any objects already tracked in add_definitions:

CREATE OR REPLACE PROCEDURE capture_new_pubuser_objects
IS
    -- Cursor to get NEW non-system tables in PUBUSER not already in add_definitions
    CURSOR c_new_tables IS
        SELECT table_name
        FROM all_tables
        WHERE owner = 'PUBUSER'
          AND table_name NOT LIKE 'BIN$%' -- Skip recycle bin objects
          AND table_name NOT IN (SELECT object_name 
                                 FROM add_definitions 
                                 WHERE object_type = 'TABLE' 
                                   AND object_owner = 'PUBUSER')
        ORDER BY table_name;

    v_table_name VARCHAR2(30);
BEGIN
    -- First, process new tables
    OPEN c_new_tables;
    LOOP
        FETCH c_new_tables INTO v_table_name;
        EXIT WHEN c_new_tables%NOTFOUND;

        -- Insert table-level record into add_definitions
        INSERT INTO add_definitions (object_type, object_owner, object_name)
        VALUES ('TABLE', 'PUBUSER', v_table_name);

        -- Now insert all columns for this new table
        INSERT INTO add_definitions (object_type, object_owner, object_name, column_name)
        SELECT 'COLUMN', 'PUBUSER', v_table_name, column_name
        FROM all_tab_columns
        WHERE owner = 'PUBUSER'
          AND table_name = v_table_name;
    END LOOP;
    CLOSE c_new_tables;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Successfully captured new tables and columns.');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        RAISE; -- Re-throw the error if you want calling code to handle it
END;
/

How This Procedure Works #

Let's break down what makes this reliable:

• Skips system/recycle bin objects: The table_name NOT LIKE 'BIN$%' clause excludes tables that are in the recycle bin, which are effectively deleted.
• Avoids duplicates: By comparing all_tables with existing entries in add_definitions, we only process tables that haven't been tracked before. The primary key on add_definitions adds an extra layer of protection against duplicates.
• Captures tables AND columns: For every new table, it first inserts a table-level entry, then pulls all columns from all_tab_columns and inserts those as column-level entries.
• Error handling: The exception block rolls back changes if something goes wrong, prints the error message, and re-raises the error so you can debug it easily.

How to Use It #

Just execute it like any other stored procedure:

-- SQL*Plus or SQL Developer
EXEC capture_new_pubuser_objects;

-- Or for PL/SQL blocks
CALL capture_new_pubuser_objects();

You can run this anytime you want to check for new tables and columns—no scheduling required (though you could set up a job if you want automatic checks).

内容的提问来源于stack exchange，提问作者Charlie