Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> DDL for triggers.
I would like a script to grab this. It seems to be a little trickier
than one would expect.
So far the closest for my needs is the simple orafaq mktrig, but it has a problem with very long trigger bodies - I have some that approach 12000 characters, and I haven't found a combination of sqlplus settings that doesn't break a variable name in the middle. If someone can fix that, please say something!
One from metalink seems to be confused about WHEN clauses. Others on this group seem limited by age.
I have several hundred triggers that are mysteriously generated by a 4GL, and the newer versions of the product seem to have lost a previous ability to handle all this when importing and changing schema names. The vendor is all "don't change schema names" (as if I want schemata named "*prod" in my test environment! Not to mention the need of having more than one schema per database, since what I'm really working on is splitting out information.), and oracle is all "recreate the triggers after import." imp manages to come up with create trigger test.triggername ... on prod.tablename, oh, duh, that doesn't exist on the test db (and what if it did?? - as it will when this is no longer in test)!
I've managed to get through this cycle by manually fixing the broken creates, either through fixing what mktrig almost did, or saving the ddl on a pc through EM, editing it in notepad for shorter lines and ftping, or using the show with imp (ugh! that was the worst!). But I really would like a script that:
Gives all the triggers for a schema (nothing too obscure featurewise,
just some triggers are big).
Keeps them in a format I can edit with vi. (I could perhaps live with
sed if I can't)
Works without manual intervention.
On hp-ux 9206 and above.
Using native tools.
I don't see anything about triggers in dbms_metadata... am I missing something?
I may yet go back to strings on the dump... but that's slow because it's a big dump.
TIA! jg
-- @home.com is bogus. http://edelivery.oracle.com/ has linux.Received on Thu Oct 26 2006 - 13:54:45 CDT