Oracle中提取触发器DDL内指定INSERT语句子串的问题求助

First, let's break down why your original query failed, then walk through two reliable solutions to extract the INSERT INTO delete_table statement from your trigger's DDL.

Why Your Original Query Didn't Work #

Your current query has two key issues that prevent it from returning the correct substring:

1. Incorrect end marker: You're searching for ');' as the end of the INSERT statement, but your actual DDL uses a standalone ; to terminate the INSERT. This mismatch means INSTR returns 0, so SUBSTR can't resolve the right end position.
2. Unnecessary repeated function calls: You invoke dbms_metadata.get_ddl three times. While this might not break things here, it's inefficient and could lead to inconsistencies if the DDL formatting (like line breaks or spacing) changes between calls.

Solution 1: Corrected SUBSTR + INSTR Approach #

First, cache the trigger DDL in a CTE to avoid repeated calls, then fix the position calculations:

WITH trigger_ddl AS (
    -- Cache the DDL once to avoid redundant calls
    SELECT dbms_metadata.get_ddl('TRIGGER', 'trigger_delete') AS ddl FROM dual
)
SELECT 
    SUBSTR(
        ddl,
        -- Find the start of the INSERT statement
        INSTR(ddl, 'INSERT INTO delete_table'),
        -- Calculate length: end position minus start position +1 to include the trailing semicolon
        INSTR(ddl, ';', INSTR(ddl, 'INSERT INTO delete_table')) 
        - INSTR(ddl, 'INSERT INTO delete_table') 
        + 1
    ) AS extracted_insert_stmt
FROM trigger_ddl;

Solution 2: More Robust Regex Approach #

If your trigger DDL includes line breaks between the INSERT clause and the semicolon (common in formatted DDL), a regex solution handles this better by ignoring line breaks:

WITH trigger_ddl AS (
    SELECT dbms_metadata.get_ddl('TRIGGER', 'trigger_delete') AS ddl FROM dual
)
SELECT 
    REGEXP_SUBSTR(
        ddl,
        'INSERT INTO delete_table.*?;', -- Non-greedy match to stop at the first semicolon
        1,
        1,
        'n' -- Allow the dot (.) to match line breaks
    ) AS extracted_insert_stmt
FROM trigger_ddl;

The 'n' flag ensures the regex works even if the INSERT statement spans multiple lines, and the non-greedy .*? ensures we stop at the first semicolon (the end of the INSERT, not the end of the entire trigger).

Testing with Your DDL #

Using your provided trigger DDL, both solutions will return exactly the substring you need:

INSERT INTO delete_table (ID, key, object, type, keytype, objecttype, created, deleted, deletedby) VALUES( :old.id, :old.key, 'delete', null, :old.keytype, null, :old.whencreated, (CURRENT_TIMESTAMP AT TIME ZONE 'GMT'), SYS_CONTEXT('TMCtx', 'USERKEY') );

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