3

I have a Heterogeneous Link between Oracle 11.2.0.3 and SQL Server 2008. When I attempt to create views, or procedures that reference the Heterogeneous Services views for system tables, I get 4 errors listed below:

ORA-00604: error occured at recursive SQL level 1

ORA-02051: another session or branch in same transaction failed or finalized

ORA-06512: at "SYS.HS$_DDTF_SQLTABLES", line 58

ORA-06512: at line 1

As an example, these errors are created when I try to execute the following code:

CREATE VIEW ALLMYTABLES AS
SELECT * FROM ALL_TABLES@DBLINKNAME;

I am however able to create views on any normal table:

CREATE VIEW RANDOMTABLE AS
SELECT * FROM RANDOMTABLE@DBLINKNAME;

I have done some research to issues similar to this one, but have not found any references to this specific issue. I believe it may have something to do with the conflict between the concurrent operations of creating the view and the select * statement call to the SQL server database. Or perhaps that the records are not entirely fetched when the view is created.

I do not know the specifics of the HS link configuration and properties.

Mackers
  • 1,039
  • 8
  • 21
  • Try spelling out the field names instead of *. Also try executing the create statement as dynamic sql to defer schema checking. Make sure the link user account has access to the Oracle system objects being referenced. – Matthew MacFarland Sep 21 '15 at 15:48

0 Answers0