Hi, I've made some tests, here are results and questions:
Postgres:
CREATE TABLE t_dump
(
str character varying(100)
)
Oracle: [ORADB: db link to Postgrers]
INSERT INTO "t_dump"@ORADB VALUES ('test');
Works fine, both from SQL as from PL/SQL
However:
Declare
m_str Varchar2 (10) := 'test';
Begin
INSERT INTO "t_dump"@ORADB VALUES (m_str);
End;
Gives me an error:
Error report:
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DB)(PORT=1521))(CONNECT_DATA=(SID=pg)))
ORA-02055: distributed update operation failed; rollback required
ORA-02063: preceding lines from ORADB
ORA-06512: at line 8
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Dynamic SQL gives exact same error:
Declare
m_str Varchar2 (10) := 'test';
Begin
EXECUTE IMMEDIATE
'INSERT INTO "t_dump"@ORADB VALUES (:v0)'
Using m_str;
End;
Is there any way I could use variables into queries?
One more issiue:
I have created partitioned table in Postgres, and simple INSERT statement works fine [SQL from Oracle]; however- the same statement, enclosed with begin/end [PL/SQL] needs about 60 seconds to finish. Is it normal behaviour? Is there any config/pragma/.. I could use to speed it up?
Regards
Bart Dabr