如何通过REGEXP_SUBSTR提取ORA-00904错误中的目标标识符？

Let's get that target column name (PRODUCT_C) pulled correctly from both your error message formats! The problem with your original regex is that it grabs the second sequence of non-quote characters, which lands on the schema name (TFSE) in the first error case. Here are two reliable fixes:

Solution 1: Grab the last quoted value (works for both cases) #

This approach targets the final set of double quotes in the error message—no matter if a schema prefix exists or not, this will always be your column name:

SELECT 
  regexp_substr(error_msg, '"([^"]+)"', 1, 0, 'i', 1) AS extracted_column
FROM (
  -- Test both error formats
  SELECT 'PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier' AS error_msg FROM dual
  UNION ALL
  SELECT 'PL/SQL: ORA-00904: "PRODUCT_C": invalid identifier' AS error_msg FROM dual
);

Regex breakdown: #

• "([^"]+)": Matches any string wrapped in double quotes, capturing the inner content (your column name) in a group
• 1, 0: The 0 tells regexp_substr to return the last occurrence of the match—perfect for skipping schema names if present
• 'i': Optional case-insensitive flag (remove if you need strict case matching)
• 1: Returns the content of the first capture group (the text inside the quotes, not including the quotes themselves)

Solution 2: Fallback between schema-prefixed and non-prefixed cases #

If you want to explicitly target schema-prefixed identifiers first, then fall back to standalone ones, use this:

SELECT 
  nvl(
    -- First try to match column names with a schema prefix (e.g., "TFSE"."PRODUCT_C")
    regexp_substr(error_msg, '\."([^"]+)"', 1, 1, 'i', 1),
    -- If no schema prefix, grab the first quoted identifier
    regexp_substr(error_msg, '"([^"]+)"', 1, 1, 'i', 1)
  ) AS extracted_column
FROM (
  SELECT 'PL/SQL: ORA-00904: "TFSE"."PRODUCT_C": invalid identifier' AS error_msg FROM dual
  UNION ALL
  SELECT 'PL/SQL: ORA-00904: "PRODUCT_C": invalid identifier' AS error_msg FROM dual
);

Both solutions will return PRODUCT_C for both of your error message examples.

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