Anurag Varma wrote:
Unfortunately, I wasn't given much info on exactly when or how each app
will access the rows. Basically, the Maximo people don't want to be
responsible for anything in Lawson and vice versa since they only
support their own products and there hasn't been much in the way of
detailed information sharing so far. The reason I got pulled into this
is that I'm helping a co-worker at a remote location who is a
subcontractor to the client who bought the software from the 2 different
vendors and we're trying to manually integrate them to work together
ASAP. At first it seemed simple, "Hey Paul, can you mirror a couple of
tables for me?", "No problem, I'll take care of it today." At least it's
been a good learning experience for me!

I'll just finish writing the triggers since they want it done by
tomorrow and I'll try do get all the vendors on a conference call next
week and see if we can't arrange for this to be done with just synonyms.
Luckily this is only in a testing environment and we have a wide window
to make changes before we go to production.

Thanks to everyone for your help.
-Paul

Sybrand Bakker wrote:

Given my experience with these products I second Sybrand's commentary. These
tools are not designed
with Oracle in mind ... and it shows.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

OK. Here's an update. I used this trigger:

CREATE OR REPLACE TRIGGER UZBMETRSYNC
AFTER INSERT OR UPDATE OR DELETE ON UZB1.UZBMETR
BEGIN
IF INSERTING THEN
DELETE FROM UZB2.UZBMETR;
INSERT INTO UZB2.UZBMETR (SELECT * FROM UZB1.UZBMETR);
ELSIF UPDATING THEN
DELETE FROM UZB2.UZBMETR;
INSERT INTO UZB2.UZBMETR (SELECT * FROM UZB1.UZBMETR);
ELSIF DELETING THEN
DELETE FROM UZB2.UZBMETR;
INSERT INTO UZB2.UZBMETR (SELECT * FROM UZB1.UZBMETR);
END IF;
END;
/

This worked great. It kept the tables in the 2 schemas synched and it
was very fast. I initially tried a truncate instead of a delete
knowing that it is a faster operation, but I obviously found out you
can't use truncate in a trigger. Since there are only a few hundred
rows and it's fast, I figured a delete would work fine.

My database background is from a web programming perspective and I'm
used to just being able to execute any SQL code I want to against a
database. I was somewhat shocked that I couldn't issue a truncate or a
commit from inside the trigger.

I wrote a stored procedure using the second account (the one whose
tables the first account's trigger's are modifying, granted permission
and created a private synonym in the other schema) I planned to call
the SP from inside the trigger. Here's the code:

CREATE OR REPLACE PROCEDURE UZBMETRCOMMIT
IS
BEGIN
COMMIT;
END;
/

I tried CALL UZBMETRCOMMIT; from inside the trigger, but that didn't
work. I tried to find some syntax for calling a stored procedure from
inside a trigger and I'm still looking with no luck so far.

Here's my question:

I looked in 6 Oracle books for trigger syntax and every single one of
them gave examples of issuing inserts, updates and deletes inside a
trigger, but none of the books said how to commit these changes. I'm
definitely missing something that the authors think is quite obvious.
I assume I have to issue any truncates or commits inside a stored
procedure and call that procedure from the trigger, but none of these
authors actually do this in their examples.

TIA for any shoves in the right direction...Paul

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote

Your code is a DBA's worst nightmare. I will never recommend or accept this
as a
solution!

You are:
* Locking the whole table in the other schema ... even if a single row is
updated/deleted/inserted
in the first schema.
Make that: This code will have locking problems hundreds of times greater
than the synonym solution!
* If these tables have significant dml happening to them then:
* You'll experience major locking problems
* You have increased I/O to the database unnecessarily
* The buffer pool will start getting filled up with dirty blocks of these
tables.
* You have contributed to the overall slowness of the database.

Now:

* If you really really need to do this... make it a "FOR EACH ROW" trigger
and simulate the exact insert/update/delete
on the other table.
* Don't worry about commits. You should not need to commit inside a trigger
unless you have a specific need to do so
(like logging). The commit/rollback will be done by the original
transaction that fires the trigger.
If you need to commit in the trigger (which in your case you DONT) then
read about "autonomous_transaction"

Of course your code might work fine when testing under a limited load. But
you might see the real problems in production.

Good luck! By my experience ... bad solutions like these .. once they get
implemented tend to linger on for a long time
until they break the database's backbone.

Anurag

First, let me thank you and everyone else for the very good advice.
Luckily, I convinced everyone to wait until next week to impliment
something. Now I have time to thoroughly read about all the topics I've
been asking the group about. It's quite a learning curve going from web
programming against MySQL and Access to trying to do things the right
way in Oracle. I passed the 9i SQL exam no problem and conceptually I
have a grasp of an RDBMS, but I have very little real world experience
with the specifics of running one securely and efficiently enough for a
production system. There's a heck of a lot more to consider than knowing
SQL and I know I have a lot to learn. Hopefully this thread will be a
good teaching tool to any newcomers that are lurking. Sometimes doing
things wrong and struggling to get it right is the best way to learn
your lessons. Thanks again, Paul

pmurphy (AT) scsinet (DOT) com (Paul Murphy) wrote in message news:<b6ab6d71.0307030716.fc7bf4f (AT) posting (DOT) google.com>...
Was one of them Export One-on-one Oracle by Kyte? If not, get one
quick since the publisher has bombed. web book sellers might have
some.

http://asktom.oracle.com/pls/ask/f?p=4950:1:
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/u...b3calbrfs.html

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote


This is an *excellent* attitude to have. Good software management is about
much more than technical skills.

I have to say though that I am surprised to say the least that at least 1
3rd party would countenance real time triggered updates to their tables from
another third party's application, at least not without increasing the
charge for support. I'd strongly suggest that the real time mirroring
requirement is dropped and you consider defining one DB to be the master and
performing a scheduled batch update process of the slave tables from the
master.

You mention that you are worried about security and efficiency and because
of the use of tables in a way that application designers did not anticipate
and the potential locking overhead of the triggers you may well find that
this solution fails both of those tests.


--
Niall Litchfield
Oracle DBA
Audit Commission UK

Paul Murphy wrote:
Great news. Thanks to the help I received on this group, I was able to
convince everyone that we should just use a single set of tables and use
private synonyms for the other software to access them. No triggers, no
duplicate tables, just a couple of synonyms.

So, if any of you run into a Banner / Maximo integration problem and the
vendors try to get you to mirror tables in the same database with
triggers...tell them NO.

Thanks again. I look forward to the day when I've got enough experience
to help the future newbies that visit c.d.o.m.

Paul Murphy

Paul Murphy <pmurphy (AT) scsinet (DOT) com> wrote

I saw a maximo rep totally bogositize a maximo upgrade. Absolutely no
clue about unix shells or Oracle. DBA involved me because she
couldn't quite believe anyone could be so clueless and yet be
suposedly following vendor instructions. So be forewarned if you ever
let outsiders in.

You've got enough!

jg
--
@home.com is bogus.
export not backed up.