Is there a clean way to get table-trigger-column-sequence relation?

Question

I have built a "best guess" query to get the related table.column for a sequence. Is there a better, cleaner method to get this relation?

select ut.table_name, ut.table_owner, ut.trigger_name, us.sequence_name 
  ,upper(dbms_metadata.get_ddl('TRIGGER', ut.trigger_name, ut.table_owner
)) triger_ddl
  ,to_char(regexp_substr(
     upper(dbms_metadata.get_ddl('TRIGGER', ut.trigger_name, ut.table_owner))
    ,'.*(;|BEGIN)[^;]*' || us.sequence_name || '.NEXTVAL[^;]*;.*'
  )) sequence_stmt
  ,regexp_substr(
     to_char(regexp_substr(
        upper(dbms_metadata.get_ddl('TRIGGER', ut.trigger_name, ut.table_owner))
       ,'.*;|BEGIN[^;]*' || us.sequence_name || '.NEXTVAL[^;]*;.*'
     ))
    ,'(INTO[[:blank:]]*)*:NEW\.([[:alpha:]]+|"[^"]+")([[:blank:]]*:=)*'
  ) sequence_column
from user_triggers ut
    ,user_dependencies ud
    ,user_sequences us
  where UD.NAME = ut.trigger_name 
  and   ud.type = 'TRIGGER'
  and   UD.REFERENCED_NAME = us.sequence_name
  and   UD.REFERENCED_TYPE = 'SEQUENCE'

I need this for a table api code_generator determinating the default value for the insert procedure.

Answer 1

The answer, perhaps unfortunately, is no. There is no concept of a relationship between a sequence and a table column in Oracle.

The only semi-reliable way to derive it is by reviewing the source code (not just triggers, but also any code that inserts). You can also make educated guesses based on the Next Value of a sequence, which should (if everything's working as expected) be a little higher than the maximum value in the table column.

If you're lucky, the columns and sequences have been named using a consistent convention, e.g. "MYTABLE.MY_ID" is always populated by a sequence named "MY_ID_SEQ" or something. But there's no guarantee that someone hasn't broken the convention.