I have a function in SQL Server 2019 that copies data from a local table to an Oracle 184.108.40.206 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, so I've updated the insert function to include 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, it also executes once and then starts throwing the error from then on. ALL 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.
Everything I can find says that if you're getting metadata errors, go to the OPENQUERY syntax, which is what I'm using.
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 cbd.WS_CBD_PACS') Select [Account_Number] ,[From_Date] ,[To_Date] ,[Factor] ,[Consumption] ,[Sample_date] ,[COD] ,[SS] , ( select min([Sample_date]) from dbo.ORA_Interface where Account_Number = p.Account_Number ) from dbo.ORA_Interface p ; Return @@rowcount; GO
The Oracle destination table definition 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
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