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
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-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;
v_result OUT NUMBER) IS
PRAGMA AUTONOMOUS_TRANSACTION;
