-
Remote Query failure Oracle to Non-Oracle
I am executing a sql query which will update the local table and upon update a trigger will fire to update the remote SQL server db table using DBlink.
Code:
SQL> update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379';
update ps_len_tmp set nd_rank_title = 'COL' where emplid = '0028379'
*
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Transparent gateway for MSSQL]
ORA-02063: preceding 2 lines from TG4MSQL
ORA-06512: at "ADM.LENINSERT", line 6
ORA-04088: error during execution of trigger 'ADM.LENINSERT'
> DBlink is working
> ODBC connection is fine
> trigger is valid and working
application guys point this to db related error
where to start trouble shoot?
"What is past is PROLOGUE"
-
You did not give any database link connection in update statement
-
Do a tnsping to the sqlserver database and check if it is connecting.
If it is connecting then do a select * from ps_len_tmp@SQLSERVErlink
-
Originally Posted by srt
You did not give any database link connection in update statement
Can you pl. read the thread again?
Trigger has it.
Do a tnsping to the sqlserver database and check if it is connecting.
If it is connecting then do a select * from ps_len_tmp@SQLSERVErlink
Ping is working, Yes it is getting the result through direct call.
"What is past is PROLOGUE"
-
-
Originally Posted by srt
Post the trigger
Code:
CREATE OR REPLACE TRIGGER ADM.LENINSERT
AFTER INSERT OR UPDATE
ON ADM.PS_LEN_TMP
FOR EACH ROW
BEGIN
IF :new.badge_expirdt is not null
and :new.badge_categry is not null
and :new.badge_categry <> ' ' THEN
INSERT INTO UDO_TEMP@tg4msql
(PERSONNEL_ID,
FIRST_NAME,
LAST_NAME,
MIDDLE_NAME,
RANK_TITLE_CODE,
PERSONNEL_CATG_CODE,
BADGE_EXPIRATION_DATE,
PERSONNEL_CLEARANCE,
CLEARANCE_SCI,
UNEMPLID)
VALUES
(:new.badge_number,
:new.first_name,
:new.last_name,
:new.middle_name,
:new.rank_title,
:new.badge_categry,
:new.badge_expirdt,
:new.clearance,
:new.sci,
:new.emplid);
END IF;
END ;
when the condition is met the trigger will fire, other wise the local update should happen which is failing.
"What is past is PROLOGUE"
-
did you turn on trace on your tg4msql and see what is happening there?
-
It may be that you need to see if new.badge_categry is 'empty'. Which would be something like and :new.badge_categry <> '', which in my limited understanding is not the same as null.
-
Originally Posted by dbtoo
It may be that you need to see if new.badge_categry is 'empty'. Which would be something like and :new.badge_categry <> '', which in my limited understanding is not the same as null.
You are right Dbtoo, this column value when null or empty has to be satisfied for the trigger to fire, but the normal dml on the local table is not happening pertaining to the local table only execution as well all the satisfying conditions listed in the trigger.
"What is past is PROLOGUE"
-
What is your DB version? There is a bug in 10g where the updates would not happen on the remote database through the DBLINK when you fire an instead of trigger.
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|