views:

44

answers:

1

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.

+1  A: 

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.

Jeffrey Kemp
What a pity ... Ok, then I will make a table where all developers have to add their table,column,sequence relation. For a first migration they can use my "best guess" query. Thanks!
christian
Interesting approach - you could do it that way, I guess - perhaps you could even enforce it with a DDL trigger (i.e. can't create a sequence unless it's already in your table) - not sure if that's a good idea or not! - but personally I prefer to enforce a naming convention. Anyway, it's ultimately down to how changes are reviewed and standards enforced.
Jeffrey Kemp