RE: Triggers are wonderful things... Ora-2069 global

From: Bowes, Chris <Chris.Bowes_at_kosa.com>
Date: Tue, 9 Jan 2001 15:14:47 -0500
Message-Id: <10736.126199@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C07A78.D060D026
Content-Type: text/plain;

charset="iso-8859-1"

No, that message comes from having an 18 month old whose favorite words are "winnie" for Winnie the Pooh and Britney for Britney Spears. I thought that since I had a trigger calling package question, that the winnie angle was better than singing a refrain of

"Sometime it works, sometimes it don't, sometimes I'm afraid to look, but all I really want is for it to work and stop screwing up, no more 2069. Oh someone please help me now" sung to the tune of Sometimes...

In reality though, I do really have a site that has a trigger calling a package that is updating a table across a database link and is dieing with an ORA-2069. Other packages in this system can update across the link, and this package will insert and delete just fine. It is dieing only on the updates to this one table. So if anyone has any suggestions (they can't enable global names) on where to look I really would appreciate it.

--Chris
Chris.Bowes_at_Kosa.com

-----Original Message-----
From: Rocky Welch [mailto:rockyw_99_at_yahoo.com] Sent: Tuesday, January 09, 2001 12:42 PM To: Multiple recipients of list ORACLE-L Subject: Re: Triggers are wonderful things... Ora-2069 global_names error

Not much going on at Kosa these days, Chris? ;o)

"Bowes, Chris" <Chris.Bowes_at_kosa.com> wrote:

"HELLO! I'm TRIGGER. T-R-I double GUH -ER TRIGGER!"

"You are?" Asked pooh pulling his nose out of the honey jar. "That's nice. What's a trigger?"

"Woo hoo hoo hoo hoo! He asked! WHAT'S A TRIGGER? WHY THAT'S EASY! The wonderful thing about triggers is triggers are wonderful things. Their tops are made out of headers, their bottoms can have lots of strings. They insert, update, delete and more, they're fun, fun, fun, fun, fun the most wonderful thing about triggers is, I'm the only one! Oh, I'm the only one. Grrrarrr"

"Well", said Pooh chuckling, "You are the only one that doesn't work. You can't call a package to update a record."

"I can't call a package?" Asked Trigger. "Of course I can. Calling packages to update records is what Triggers do best! So what's the problem?"

"Well, everytime you try to update across the database link to the other base, you mess it up. You cause an ORA-2069 error", replied Pooh, "I need you to call a routine to update records on this post insert or post update and succeed"

"Update after an insert or update. Why that's what Triggers to best. Here come some inserts and updates, watch me update that other base..."

"Hey you Blokes! C'mon. Update you chump! Global name? My name is global name is Trigger! That's good enough. Let's go. Update." Crash, bang, boom.

"Hmph", said Trigger folding his arms and looking defiant. "Triggers DO NOT like calling an update across a database link."

"Oh bother. Now what do I do?", sighed Pooh.

Just then, the NarrDBAtor made a suggestion. "if you turn to the middle of page 163, Trigger asks the Oracle List if they have seen this before." Trigger jumped up. "HOO HOO! Letters is what Triggers do best. I'll go do it. TTFN. Ta ta for now!" BOING, BOINg, BOIng, BOing, Boing, boing....

So then Trigger did indeed write the Oracle List to ask just that question. Pooh has a trigger that needs to needs to call a package to update a base across a database link. The problem is the "ORA-2069 global_names parameter must be set to TRUE for this operation" error hitting on a table. If the package calls for an insert, it works just fine. If it calls for a delete it works fine. Just on updates it causes this. This is a system at a friends site so I am not sure what kind of paticulars there are. It's an Oracle 8 base updating an Oracle 7 base via the database link. All the bases are on NT. Other packages work to other bases so I am pretty sure the listener stuff is set up right. I do not think they can turn on GLOBAL_NAMES as there are several interfaces going to and from these bases and that may mess some of them up. Anyone have a suggestion? Thank you in advance

--Chris
Chris.Bowes_at_Kosa.com

Rocky Welch
Senior Consultant - Internet Services Group Arthur Andersen

Do You Yahoo!?
Yahoo! Photos <http://photos.yahoo.com/> - Share your holiday photos online!

------_=_NextPart_001_01C07A78.D060D026
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.00.2919.6307" name=GENERATOR></HEAD>
<BODY>
<DIV>No,
that message comes from having an 18 month old whose favorite words are "winnie" for Winnie the Pooh and Britney for Britney Spears.  I thought that since I had a trigger calling package question, that the winnie angle was better than singing a refrain of </DIV>
<DIV><SPAN
class=704280720-09012001> </DIV>
<DIV><SPAN
class=704280720-09012001>"Sometime it works, sometimes it don't, sometimes I'm afraid to look, but all I really want is for it to work and stop screwing up, no more 2069.  Oh someone please help me now" sung to the tune of Sometimes...</DIV>
<DIV><SPAN
class=704280720-09012001> </DIV>
<DIV>In
reality though, I do really have a site that has a trigger calling a package that is updating a table across a database link and is dieing with an ORA-2069.  Other packages in this system can update across the link, and this package will insert and delete just fine.  It is dieing only on the updates to this one table.  So if anyone has any suggestions (they can't enable global names) on where to look I really would appreciate it.</DIV>
<DIV><SPAN
class=704280720-09012001> </DIV>
<DIV><SPAN
class=704280720-09012001>--Chris
 Chris.Bowes_at_Kosa.com </DIV>
<DIV> </DIV>
<BLOCKQUOTE>

 <DIV class=OutlookMessageHeader>-----Original Message----- From: Rocky Welch [mailto:rockyw_99_at_yahoo.com] Sent: Tuesday, January 09, 2001 12:42 PM To: Multiple recipients of list ORACLE-L Subject: Re: Triggers are wonderful things... Ora-2069 global_names error </DIV>
 Not much going on at Kosa these days, Chris? ;o)   "Bowes, Chris" <Chris.Bowes_at_kosa.com> wrote:
 

 <BLOCKQUOTE
 style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px"> <META content="MS Exchange Server version 5.5.2650.12" name=Generator> "HELLO!  I'm TRIGGER.  T-R-I double GUH  -ER  TRIGGER!" "You are?"  Asked pooh pulling his nose out of the honey jar.  "That's nice.  What's a trigger?" 
 "Woo hoo hoo hoo hoo!  He asked!  WHAT'S A TRIGGER?  WHY THAT'S EASY! The wonderful thing about triggers is triggers are wonderful things. Their tops are made out of headers, their bottoms can have lots of strings. They insert, update, delete and more, they're fun, fun, fun, fun, fun the most wonderful thing about triggers is, I'm the only one!  Oh, I'm the only one.  Grrrarrr" 
 "Well", said Pooh chuckling, "You are the only one that doesn't work.  You can't call a package to update a record." 
 "I can't call a package?" Asked Trigger.  "Of course I can.  Calling packages to update records is what Triggers do best!  So what's the problem?" "Well, everytime you try to update across the database link to the other base, you mess it up.  You cause an ORA-2069 error", replied Pooh,  "I need you to call a routine to update records on this post insert or post update and succeed" "Update after an insert or update.  Why that's what Triggers to best.  Here come some inserts and updates, watch me update that other base..." "Hey you Blokes!  C'mon.  Update you chump!  Global name?  My name is global name is Trigger!  That's good enough.  Let's go.  Update."  Crash, bang, boom. 
 "Hmph", said Trigger folding his arms and looking defiant.  "Triggers DO NOT like calling an update across a database link." 
 "Oh bother.  Now what do I do?", sighed Pooh. 
 Just then, the NarrDBAtor made a suggestion.  "if you turn to the middle of page 163, Trigger asks the Oracle List if they have seen this before."  Trigger jumped up.  "HOO HOO!  Letters is what Triggers do best.  I'll go do it. TTFN.  Ta ta for now!"   BOING, BOINg, BOIng, BOing, Boing, boing.... 
 So then Trigger did indeed write the Oracle List to ask just that question.  Pooh has a trigger that needs to needs to call a package to update a base across a database link.  The problem is the "ORA-2069 global_names parameter must be set to TRUE for this operation" error hitting on a table.  If the package calls for an insert, it works just fine.  If it calls for a delete it works fine.  Just on updates it causes this.  This is a system at a friends site so I am not sure what kind of paticulars there are.  It's an Oracle 8 base updating an Oracle 7 base via the database link.  All the bases are on NT.  Other packages work to other bases so I am pretty sure the listener stuff is set up right.  I do not think they can turn on GLOBAL_NAMES as there are several interfaces going to and from these bases and that may mess some of them up.  Anyone have a suggestion?  Thank you in advance</P! !> 
 --Chris Chris.Bowes_at_Kosa.com </BLOCKQUOTE> Rocky Welch Senior Consultant - Internet Services Group Arthur Andersen Received on Tue Jan 09 2001 - 14:14:47 CST

RE: Triggers are wonderful things... Ora-2069 global_names error