Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 879
  • Last Modified:

Oracle Trigger problem after SQL Server conversion via Oracle Workbench...

This is the result of a trigger from the conversion:

CREATE OR REPLACE TRIGGER TR_S_322_1_T_SECURITY
BEFORE INSERT ON T_SECURITY
FOR EACH ROW
DECLARE
BEGIN
SELECT S_322_1_T_SECURITY.NEXTVAL
INTO omwb_emulation.globalPkg.identity
FROM dual;
:new.SECU_ID:=omwb_emulation.globalPkg.identity;
END;

Of course this fails now, which it did not in the SQL Server instance.  We are in the middle of an upgrade from SQL 2k to Oracle 10G and this is the last of the many problems we have encountered...A bit stumped on this.  

SELECT S_322_1_T_SECURITY.NEXTVAL
FROM dual;

The above query works just fine.....?

Any help would be greatly appreciated.

Thanks,

B
0
cyiamxtc
Asked:
cyiamxtc
  • 4
  • 3
  • 2
1 Solution
 
MikeOM_DBACommented:

Then the problem may be with the omwb_emulation.globalPkg, does it have the correct permissions?
0
 
actonwangCommented:
>>Of course this fails now

what is the error message you got?
0
 
actonwangCommented:
how do you define identity in your globalPkg in schema of omwb_emulation?

Does your current user or PUBLIC has execute permission on this globalPkg package?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cyiamxtcAuthor Commented:
omwb_emulation is part of an
0
 
cyiamxtcAuthor Commented:
omwb_emulation is part of an Oracle debacle that creates something that is not usable.  It gives the error message that "" must be declared.  Of course the only more obvious message would be table or view not found - but I am trying to figure out what is happening or more to the point, what should have happened instead of this temp creation from Oracle.

Here is something interesting that I have found on the web:

OMWB_Emulation Utilities Package
For release 9.2.0 of the Migration Workbench a user has to be added manually, or
the OMWB_emulation references in the generated code should be removed:

I was hoping that someone that went through a migration from SQL server 2k to Oracle 10G could shed light on how it ruined all their triggers too?
0
 
cyiamxtcAuthor Commented:
I just was granted access to the SQL server DB and here is the original trigger.....NOTHING like the nonsense that Oracle created....

create  TRIGGER t_security_iu
ON t_security
      FOR INSERT, UPDATE
AS
--Update update_login AND update_date to the correct system values
   UPDATE t_security
   SET        t_security.secu_update_login = system_user,
        t_security.secu_update_date = getdate()
   FROM t_security , inserted i
   WHERE t_security.secu_id = i.secu_id


Just to give the points - although much easier question, who can rewrite this in Oracle 10G for me?
0
 
MikeOM_DBACommented:

Create Or Replace Trigger T_Security_Iu
Before Insert Or Update On T_Security
For Each Row
Declare Identity Number;
Begin
  Select S_322_1_T_Security.Nextval
    Into Identitiy
    From Dual;
  :New.Secu_Id:=Identity;
  :New.Secu_Update_Date = Sysdate;
End;
/
0
 
MikeOM_DBACommented:

PS: Remove the TR_S_322_1_T_SECURITY trigger and look if there is a BEFORE UPDATE trigger you may need to remove also.


0
 
MikeOM_DBACommented:
Ooops, this is the correct trigger:

Create Or Replace Trigger T_Security_Iu
Before Insert Or Update On T_Security
For Each Row
Declare Identity Number;
Begin
  If Inserting Then
    Select S_322_1_T_Security.Nextval
      Into Identitiy
      From Dual;
    :New.Secu_Id:=Identity;
  End If;  
  :New.Secu_Update_Login = User;
  :New.Secu_Update_Date = Sysdate;
End;
/
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now