Select from Openquery from Oracle, missing quot...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from Openquery from Oracle, missing quot...

Author

Topic

entropus
Starting Member

5 Posts

Posted - 2012-12-06 : 12:55:01

I have a problem with running a select from openquery.
I will be extremely grateful for help! I just hit the wall, can't find a way to solve this issue.

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:

Quote:
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?

Code:

Code: [Select all] [Show/ hide]
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 13:06:40

Print out the @sqlInv string and see if that makes sense. From visual observation it seems to me like you need to remove the four single quotes just before TO_DATE and the matching four single quotes just before the AND keyword.

....      
from XYZ.V_HEADER 
where

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

Also, I assume @startDate and @endDate are character types - otherwise you will need to cast/convert them to character types (at least in SQL Server - don't know if Oracle will automatically convert).

entropus
Starting Member

5 Posts

Posted - 2012-12-06 : 13:11:42

Hey Sunitabeck, thanks for the input! Sadly, does not work, same error :/

Also, I remove the 'where... ' part of the procedure. Same error.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 13:15:59

You don't have those comments in the actual query, do you? If you do, remove them

[XYZ_3] [varchar](20),
-- more fields	
[XYZ_N] [varchar](50)

entropus
Starting Member

5 Posts

Posted - 2012-12-06 : 13:16:53

No, no comments.

(Also, I removed the 'where... ' part of the procedure. Same error.)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 13:29:59

Since you are not able to post the whole query (or even if you are able to), reduce the select list to just the 3 columns you posted and see if you can make that work. If not, replace the "exec sp_executesql @sqlInv" with "print @sqlInv" and look at the query string (or post it).

entropus
Starting Member

5 Posts

Posted - 2012-12-06 : 13:50:28

Hey Sunitabeck, thanks a lot for help!

I did that, and got (approximately) this result:

select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')
select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')select @ora_hrows=RET_QTY_HEADER, @ora_rrows=RET_QTY_ROWS from Openquery(XYZ2,'SELECT TO_NUMBER(RET_QTY_HEADER) RET_QTY_ROWS, TO_NUMBER(RET_QTY_ROWS) RET_QTY_ROWS FROM XYZ.STH123_LOG where NL_ID = ''3E09B4CB-1234-4123-1223-12341234''')

insert into #tmpXYZ Header (
[XYZ_1],
[XYZ_2],
[XYZ_3],

[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,

cast ('''' '''' as varchar(20)) as A19

from XYZ.V_HEADER
where

(DATE >= TO_DATE(''20121107'',

/result ends with the date

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-06 : 15:01:06

Are you still getting the same error?

entropus
Starting Member

5 Posts

Posted - 2012-12-06 : 15:29:03

Well there was no error while I used "print @sqlInv", but while using "exec sp_executesql @sqlInv" i get the same error.

I changed the procedure so that the exec sp_executesql @sqlInv is not used, and this:

'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'''' ) '' )
'

is executed directly. Sadly, I get an error: "ORA-01858: a non-numeric character was found where a numeric was expected".

However, I checked all the fields and they seem to be OK regarding data types.

Also, another procedure almost identical to this one is run, and works fine.
No need to "- otherwise you will need to cast/convert them to character types " I believe.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources