CODEWITHSUNDEEP
mysqloracle-databaseheterogeneous-services
sanket katariya
sanket katariya

Reputation: 5

Update mysql Table from PL SQL error ORA-28536: error in processing Heterogeneous Services initialization parameters

UPDATE query without WHERE clause working fine. But when i use WHERE clause then it shows error

ORA-28536: error in processing Heterogeneous Services initialization parameters
ORA-28507: error in data dictionary view sys.hs$_class_init
ORA-02063: preceding 2 lines from MYSQL_ATULSSO
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required.

My code :

DECLARE
  A NUMBER(15) := 70;
  B NUMBER(15) := 69;
BEGIN
  UPDATE ATL1_SSO_ACCESS_REQ_DETAILS A SET A.REQ_ID = B;
  DBMS_OUTPUT.PUT_LINE('done11');
  UPDATE ATL1_SSO_ACCESS_REQ_DETAILS A SET A.REQ_ID = B WHERE A.REQ_ID = A;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('done');

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('not done1');
    COMMIT;
END;

Output: done1 not done

Upvotes: 0

Views: 506

Answers (2)

sanket katariya
sanket katariya

Reputation: 5

I got answer. You can update from Database Link. Below is syntax

UPDATE "*DB_NAME*"."*TABLE_NAME*"@"*DB_LINK*"
SET "*DB_NAME*"."*TABLE_NAME*"."*COLUMN_NAME*"@"*DB_LINK*"= 'r2' 
WHERE "*DB_NAME*"."*TABLE_NAME*"."*COLUMN_NAME*"@"*DB_LINK*"='r1';

example: 

UPDATE "atul_sso"."ATL_SSO_ACCESS_REQ_DETAILS"@"mysql_atulsso.atul.co.in"
SET "atul_sso"."ATL_SSO_ACCESS_REQ_DETAILS"."REQ_TYPE"@"mysql_atulsso.atul.co.in"= 'r2' 
WHERE "atul_sso"."ATL_SSO_ACCESS_REQ_DETAILS"."REQ_TYPE"@"mysql_atulsso.atul.co.in"='r1';

I still have one more doubt. I can not update using Synonym. is there any syntax for it ?

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

I think using table alias A in where clause may be the issue Try the below code

DECLARE
A NUMBER(15) := 70;
B NUMBER(15) := 69;
BEGIN
 UPDATE ATL1_SSO_ACCESS_REQ_DETAILS A SET 
 A.REQ_ID = B;
  DBMS_OUTPUT.PUT_LINE('done11');
  UPDATE ATL1_SSO_ACCESS_REQ_DETAILS A SET 
  A.REQ_ID = B WHERE A.REQ_ID = 70;
  DBMS_OUTPUT.PUT_LINE('done');

  EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('not done1');
   END;

Upvotes: 0

Related Questions