using dblink integrity constraint violated error

Question

I am trying to insert the data to tables from a remote database using dblink. The first insertion into text_content table didnot happen/ commit didnt happen and then i am trying to insert into the translations table.

declare
  v_TextContent_Id NUMBER(19);
begin
    SELECT TEXT_CONTENT_SEQ.NEXTVAL@DBLINK1
    INTO v_TextContent_Id FROM DUAL;  
    --
     INSERT INTO TEXT_CONTENT@DBLINK1 (ID,TEXT_CONTENT_ID)
     VALUES(v_TextContent_Id,CONCAT(u'TextContent_',v_TextContent_Id));  
    dbms_output.put_line('v_TextContent_Id = ' || v_TextContent_Id);
     
  ----- here i need to perform a commit and this is not happening because of this  i get foreign key violation error. 

    INSERT INTO TRANSLATIONS@DBLINK1 (TEXT_CONTENT_ID,LANGUAGE_ID,TRANSLATION)
    VALUES(v_TextContent_Id,1,u'MobileAppD');
end;

Is it possible to perform multiple commit. I can see from some of the questions here says multiple commit not allowed. Can some one help me to solve this

Answer 1

Is it possible to perform multiple commit.

Yes. But it is often bad practice as multiple COMMITs will prevent you from ROLLBACK the entire transaction so you can get the situation where half the business logic has been carried out and saved with COMMIT and then the second half fails but you cannot undo the first half as it has been COMMITted and your database ends up in an unexpected state.

That does not mean that you cannot (or should not) use multiple COMMITs; just that if you find yourself using COMMIT multiple times (or at the end of every procedure) then you should step back and look closely at what you are doing and verify the appropriateness of your behaviour.

here i need to perform a commit and this is not happening because of this i get foreign key violation error.

You will not get an error for a foreign key not found if the referenced data was inserted in the same session and is uncommitted. You will only get a foreign key not found exception if:

1. the data does not exist; or
2. was inserted in a different session and has not been committed in that session (even if it is the same user).

---

Are you sure that you are not inserting the wrong value? As TRANSLATIONS has the column name TEXT_CONTENT_ID and when you inserted the value into the TEXT_CONTENT table for that column it had the value 'TextContent_' || v_TextContent_Id and not v_TextContent_Id:

declare
  v_TextContent_Id NUMBER(19);
begin
  SELECT TEXT_CONTENT_SEQ.NEXTVAL@DBLINK1
  INTO   v_TextContent_Id
  FROM   DUAL;  

  INSERT INTO TEXT_CONTENT@DBLINK1 (ID,TEXT_CONTENT_ID)
  VALUES(v_TextContent_Id, u'TextContent_' || v_TextContent_Id);  

  dbms_output.put_line('v_TextContent_Id = ' || v_TextContent_Id);
     
  INSERT INTO TRANSLATIONS@DBLINK1 (
    TEXT_CONTENT_ID,
    LANGUAGE_ID,
    TRANSLATION
  ) VALUES (
    u'TextContent_' || v_TextContent_Id,          -- ensure the correct prefix.
    1,
    u'MobileAppD'
  );
end;
/

Which then leads to the question, why is it necessary to add the prefix TextContent_? If that is a constant value then it adds little-to-no benefit and you could omit it on both sides of the referential constraint and nothing will change (other than your code will not be littered with "magic" strings and you do not need to store quite as large strings).