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.