2

I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 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
Improve this question
9
  • What happens if you pass you data through a temp table first? E.g. Insert Into a temp table then use that temp table to do the cross server insert. I suggest explicitly declaring your temp table if an implicit one doesn't work.
    – Graham
    Sep 17, 2020 at 15:58
  • @Graham are you suggesting on the temp table on the Oracle side or the SQL side?
    – CaM
    Sep 18, 2020 at 15:05
  • I tried switching back to a standard insert into .. select query and that failed. I've tried rebooting the SQL server; that failed too. I've even tried dropping the Oracle table and recreating it.
    – CaM
    Sep 18, 2020 at 15:06
  • 1
    For giggles, try adding an OPTION (RECOMPILE) on the INSERT.
    – Graham
    Sep 24, 2020 at 19:37
  • 1
    I'll add that as an answer so in the future someone with the same issue doesn't need to wade through all the missteps to find out if things got sorted.
    – Graham
    Sep 29, 2020 at 19:33

2 Answers 2

Reset to default
2

Use OPTION (RECOMPILE) on the INSERT

Improve this answer
4
  • 1
    Thank you! This one drove me nuts. May I ask why this works?
    – Metaphor
    Nov 23, 2020 at 22:17
  • 1
    For an unknown reason reusing the a query plan is causing a failure. Instead of addressing the root cause (because I have no idea the root cause), I provided a workaround; the recompile avoids reusing a plan.
    – Graham
    Dec 4, 2020 at 23:57
  • I wonder if this isn't some kind of bug on MS's part?
    – CaM
    Dec 7, 2020 at 18:39
  • Turns out it's a bug on Oracle's part.
    – CaM
    Jan 21, 2021 at 14:58
0

It turns out this problem is an Oracle Bug. Patch 31892754: 19C OLEDB PROVIDER EXECUTION FAILS WITH INCONSISTENT METADATA IN A LINKED SERVER addresses this problem.

It requires Oracle 19.3 and CU 31247621 (but no more recent CUs than that).

Oracle says this patch will be included in Oracle 19.11, so I'm assuming that's a CU due in 2021.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.