Openquery for SQL Server issue:ORA-00920: invalid relational operator

Question

DECLARE @text AS VARCHAR(10) = '//text()'
DECLARE @TSQL VARCHAR(8000)

SET @TSQL = 'select * from openquery(HHOBI,''select rtrim(xmlagg(xmlelement(e,id,'''','''').extract('''''+@text+''''') order by id).GetClobVal(),'''','''') from (select level as id from dual
connect by level)'')'

EXEC(@TSQL)

I get an error:

OLE DB provider "OraOLEDB.Oracle" for linked server "HHOBI" returned message "ORA-00920: invalid relational operator

So, what’s the minimal NON DYNAMIC Swl case that reproduces the issue? — user2864740
– user2864740, Commented Jan 26, 2019 at 6:05
Your query 'select level as id from dual connect by level' is wrong. What would you like to achieve by it? Also, it seems the 'e' variable in the xmlelement call is undefined. — Petro K
– Petro K, Commented Jan 26, 2019 at 7:30

Caius Jard · Accepted Answer · 2019-01-26 08:28:56Z

3

Oracle requires a query of the form:

select level as id from dual
connect by <BOOLEAN EXPRESSION HERE>

You have passed an INT to a place where it expects a boolean

Try something like this:

select level as id from dual
connect by level <= 100

If you want oracle to generate you 100 arbitrary rows (a single column counting from 1 to 100)

Obviously I cannot guess as to how many rows you need oracle to create in order to use them to rip your XML doc apart; that's for you to decide and finish off

answered Jan 26, 2019 at 8:28

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Openquery for SQL Server issue:ORA-00920: invalid relational operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related