"ORA-00900: invalid SQL statement" and "ORA-02064 Distributed operation not supported" Errors When calling to Remote PL/SQL Procedure

================================
General
================================
A. When calling to a remote PL/SQL procedure from sqlplus - all works fine,
But when calling to the same remote PL/SQL procedure from PL/SQL code, an ORA-00900: invalid SQL statement error is thrown.

B. When calling a PL/SQL directly, all works fine, but when calling same procedure vi db link, it fails with ORA-02064 Distributed operation not supported

Why is that?

================================
ORA-00900 Example
================================

In this example, a DB_LINK named KUKU is created to the remote database.

CREATE DATABASE LINK KUKU CONNECT TO REMOTE_USER IDENTIFIED BY REMOTE_PASSWORD USING 'REMOTE_HOST_NAME';

In the remote database, a getDate Procedure in Package PKG_TEST should be activated.

In sqlplus
From sqlplus, any of these versions work:
EXEC PKG_TEST.getDate@KUKU;
EXEC REMOTE_USER.PKG_TEST.getDate@KUKU;

When creating a Synonym, it can be also used, to simplify code.

CREATE SYNONYM REMOTE_PKG FOR REMOTE_USER.PKG_TEST@KUKU

SELECT * FROM USER_SYNONYMS WHERE synonym_name = 'KUKU'

SYNONYM_NAME        TABLE_OWNER      TABLE_NAME      DB_LINK
------------------- ---------------- --------------- ---------------
REMOTE_PKG          REMOTE_USER      PKG_TEST        KUKU

So in sqlplus, it would be:
EXEC REMOTE_PKG.getDate;

In PL/SQL
When activating the same code in PL/SQL it MUST be inside a BEGIN END block.
Any other syntax, such as EXECUTE IMMEDIATE <sql_string> would fail with "ORA-00900: invalid SQL statement".

This is the correct code:

BEGIN
  PKG_TEST.getDate;
  EXCEPTION
    WHEN OTHERS THEN
      WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
      RAISE;
END;

================================
ORA-02064 Example
================================
In this example, same DB_LINK named KUKU is created to the remote database.
The procedure now is a DML Procedre, performing an update, and a commit.

The remote procedure has these API:

PKG_TEST.setDate(v_date    IN DATE,

                v_result  OUT NUMBER);

When calling the remote procedure in PL/SQL, it would be:


DECLARE
  v_date   DATE;
  v_result NUMBER;
BEGIN
  v_date := SYSDATE;
  PKG_TEST.setDate(v_date,v_result);
  EXCEPTION
    WHEN OTHERS THEN
      WRITE_LOG(v_module_name,'Error Running: '||v_sql_str||' '||SQLERRM);
      RAISE;

END;

The returned error is:

ORA-02064: distributed operation not supported
ORA-06512: at "REMOTE_USER.PKG_TEST", line 491

Per Oracle documentation, 

Error Cause:
One of the following unsupported operations was attempted:

1. array execute of a remote update with a subquery that references a dblink, or

2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or

3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.

Action:
Simplify remote update statement.

Per Oracle Metalink CALLING REMOTE PACKAGE RECEIVES ORA-2064 (Doc ID 1026597.6):

"This is Not A Bug. 

In the documentation for ORA-2064, it states that one of  the disallowed actions is "A commit is issued in a coordinated session from an RPC with OUT parameters." 

It happens that the return value of a function call counts as an OUT parameter for purposes of this rule. 

As a workaround for some cases Pragma AUTONOMOUS_TRANSACTION can be used in the called function or procedure in the remote site package."

So, a work around this problem would be to change the remote Procedure to be Autonomous Transaction.

PKG_TEST.setDate(v_date    IN DATE,
                v_result  OUT NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;