Cause
The above SQL statement is no longer valid or supported using any 11g gateway.
The 11g gateways do not support cursor callbacks in the same way the 10g gateways did.
Solution
Use PL/SQL code to handle select statement and inserts via a cursor loop.
Here is a sample
DECLARE
my_a varchar2(25);
my_b number;
my_c long;
CURSOR c IS
select "a","b","c" from my_oracle_table;
BEGIN
OPEN c;
LOOP
FETCH c
into my_a,my_b,my_c;
EXIT WHEN c%NOTFOUND;
insert into "dbo"."my_sql_table"@my_db_link values (my_a,my_b,my_c);
END LOOP;
COMMIT;
CLOSE c;
END;
/
FROM Oracle
create table my_oracle_table (a varchar2(25), b number,c long);
FROM non Oracle database - I used SQL Server
USE [scott]
GO
/****** Object: Table [dbo].[my_sql_table] Script Date: 12/08/2009 12:34:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[my_sql_table](
[a] [varchar](25) NULL,
[b] [int] NULL,
[c] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO