Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Inserting into Postgres via ODBC Gateway

BDOct 3 2011 — edited Oct 5 2011
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
This post has been answered by Kgronau-Oracle on Oct 5 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 2 2011
Added on Oct 3 2011
5 comments
887 views