I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table via an alter table statement, so I've updated the insert function to include data for this new column.
Now, whenever I modify the function, it will execute successfully one time and one time only. If I purge the query cache or execute it a second time, it fails. ALL subsequent execution attempts after the first time throw this error:
Msg 7353, Level 16, State 1, Procedure COD.Interface_Upload, Line 29 [Batch Start Line 20] The OLE DB provider "OraOLEDB.Oracle" for linked server "ORADEV-LINK" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
The only change to this procedure from the previous, working, version is adding the DATE_SUBMITTED field and the "select min([Sample_date]) ..." part to supply that column's data.
Everything I can find says that if you're getting metadata errors, go from using standard queries to the OPENQUERY syntax, which is what I'm using, though I've also tried standard insert syntax:
insert into [ORADEV-LINK]..COD.WS_PACS
...
just to see if that fixes it, but it gives the same error. We have also tried dropping and rebuilding the Oracle table, to see if there's something weird about the Add Column that is messing up OLE, but that didn't fix it either.
The function is:
ALTER Procedure [COD].[Interface_Upload]
as
SET NOCOUNT ON;
INSERT OPENQUERY([ORADEV-LINK], 'SELECT ACCOUNT_NUMBER, FROM_DATE, TO_DATE,
COD_TO_BOD_FACTOR, CONSUMPTION, SAMPLE_DATE, COD_CONTENT, SS_CONTENT,
DATE_SUBMITTED from COD.WS_PACS')
Select [Account_Number]
,[From_Date] ,[To_Date]
,[Factor] ,[Consumption]
,[Sample_date] ,[COD]
,[SS]
, ( select min([Sample_date])
from cod.WRS_Interface
where Account_Number = p.Account_Number ) as Date_Submitted
from cod.WRS_Interface p ;
return @@rowcount;
The Oracle table definition that I'm trying to insert into is:
Null? Type
----------------------- -------- -------
ACCOUNT_NUMBER NOT NULL NUMBER(13)
FROM_DATE NOT NULL DATE
TO_DATE NOT NULL DATE
COD_TO_BOD_FACTOR NOT NULL NUMBER(6,2)
CONSUMPTION NOT NULL NUMBER(15)
SAMPLE_DATE NOT NULL DATE
COD_CONTENT NOT NULL NUMBER(10)
SS_CONTENT NOT NULL NUMBER(10)
DATE_SUBMITTED DATE -- this is the new column
The SQL Server source table definition is:
[Account_Number] [varchar](30) NULL,
[From_Date] [datetime] NULL,
[To_Date] [datetime] NULL,
[Factor] [numeric](15, 2) NULL,
[Consumption] [numeric](15, 2) NULL,
[Sample_date] [datetime] NULL,
[COD] [numeric](15, 2) NULL,
[SS] [numeric](15, 2) NULL
Any ideas why this is failing and how to get past it? Everything I've been able to find says the issue is either around Oracle's varchar2(5) vs varchar2(5 char) style syntaxes (no varchar columns used) or to switch to OpenQuery, which I'm using. So I'm at a loss as how to get this to work.