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

0
votes

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

2
mysqloracleheterogeneous-services
So, are you using Heterogeneous Services (to connect to a MySQL database?)? Is ATL1_SSO_ACCESS_REQ_DETAILS a table on the remote database? Have you verified the installation of the data dictionary view sys.hs$_class_init??? – APC
ATL1_SSO_ACCESS_REQ_DETAILS is synonym created in oracle. CREATE SYNONYM "ATL1_SSO_ACCESS_REQ_DETAILS" FOR "atl_sso"."ATL_SSO_ACCESS_REQ_DETAILS"@"MYSQL_ATULSSO.ATUL.CO.IN"; I have no idea about heterogeneous services. SELECT * FROM sys.hs$_class_init; this query return nothing. – sanket katariya
Heterogeneous Services is an Oracle product for connecting non-Oracle databases such as MySQL with Oracle databases. This is what you're trying to do but there seems to be a problem with your HS configuration. You need to talk to your DBA to get this sorted out. – APC
Can you help me out to get HS configuration? – sanket katariya
Sorry, no. I know enough about HS to recognise when it's in use, but I've never worked with it myself. This requires knowledge of your infrastructure. Is there really nobody in your organisation you can ask to help you? – APC

2 Answers

0
votes

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;
0
votes

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 ?