Missing quotation - openquery to oracle

Question

I am not able to successfully run a code with openquery to a oracle server.

Please do not take into account field names/data types, as I had to present only a part of the whole procedure. It's quite long. I believe the problem lies in quotation marks, etc...

The procedure compiles all right. Each time I execute the procedure, an error occurs:

Msg 105, Level 15, State 1, Line 53
Unclosed quotation mark after the character string 'SELECT TO_NUMBER(XYZ_1) XYZ_1,
XYZ_2,

cast (''''0'''' as number(5)) as B1, 
cast(''''1753-01-01'''' as date) NULL_DATE

I am lost - where the heck should I put those missing quotation mark?

CREATE TABLE #tmpXYZ Header (
    [XYZ_1] [int] PRIMARY KEY,
    [XYZ_2] [varchar](20),
    [XYZ_3] [varchar](20),
        -- more fields  
    [XYZ_N] [varchar](50)
 )

declare @sqlInv nvarchar(3000)
 set @sqlInv = 

        'insert into #tmpXYZ Header (
        [XYZ_1],
        [XYZ_2],
        [XYZ_3],
        -- more fields
        [XYZ_N]

    )

    select
        * FROM OPENQUERY(XYZ_ORACLE, 
        ''SELECT TO_NUMBER(XYZ_1) XYZ_1,
        XYZ_2,

        cast (''''0'''' as number(5)) as B1, 
        cast(''''1753-01-01'''' as date) NULL_DATE,
        -- more fields
        cast ('''' '''' as varchar(20)) as A19 

    from XYZ.V_HEADER 
    where

        (DATE >= ''''TO_DATE(''''' + @startDate + ''''', ''''YYYYMMDD'''')'''' AND 
        DATE <= ''''TO_DATE(''''' + @endDate + ''''', ''''YYYYMMDD'''')'''' AND 
        QWE = ''''0'''' AND
        ABC = ''''13'''' ) '' )
    ' 

    exec sp_executesql @sqlInv

Answer 1

I think that your escaped quotes are too deep when you get to

cast (''''0'''' as number(5)) as B1, 

My guess is it would be

cast ('''0''' as number(5)) as B1, 

Etc for the portion in the quoted select statement string.

I think, however the portions that would be right in that would be

TO_DATE(''''' + @startDate + '''''

I am a little confused on the quotes surrounding the to_date calls, as to why you have those.

My guess in total, assuming the to_date surrounding quotes are not wrong:

CREATE TABLE #tmpXYZ Header (
    [XYZ_1] [int] PRIMARY KEY,
    [XYZ_2] [varchar](20),
    [XYZ_3] [varchar](20),
        -- more fields  
    [XYZ_N] [varchar](50)
 )

declare @sqlInv nvarchar(3000)
 set @sqlInv = 

        'insert into #tmpXYZ Header (
        [XYZ_1],
        [XYZ_2],
        [XYZ_3],
        -- more fields
        [XYZ_N]

    )

    select
        * FROM OPENQUERY(XYZ_ORACLE, 
        ''SELECT TO_NUMBER(XYZ_1) XYZ_1,
        XYZ_2,

        cast ('''0''' as number(5)) as B1, 
        cast('''1753-01-01''' as date) NULL_DATE,
        -- more fields
        cast (''' ''' as varchar(20)) as A19 

    from XYZ.V_HEADER 
    where

        (DATE >= '''TO_DATE(''''' + @startDate + ''''', '''YYYYMMDD''')''' AND 
        DATE <= '''TO_DATE(''''' + @endDate + ''''', '''YYYYMMDD''')''' AND 
        QWE = '''0''' AND
        ABC = '''13''' ) '' )
    ' 

    exec sp_executesql @sqlInv