Oracle Query in Excel works, but not in SQL

Question

I'm currently trying to query an Oracle server and pull in data into a SQL server.

Now what I've got is this query:

select * from openquery(databasename, 'SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate
WHERE DueDate.NAME = "com.ibm.team.apt.attribute.constraintdate"')

If I toss just the query:

SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate
WHERE DueDate.NAME = 'com.ibm.team.apt.attribute.constraintdate'

into excel, where I've created an oracle link, I can pull the data just fine.

However, when I pull the data in SQL, I can pull the whole table if I just do "SELECT * FROM RICALM.REQUEST_TIMESTAMP.EXT" but when I add in the WHERE clause I get an error message:

OLE DB provider "OraOLEDB.Oracle" for linked server "databasename" returned message "ORA-00972: identifier is too long".

Any workaround for this? I'd like to just schedule a query like this to run each night so I don't have to deal with refreshing an excel table.

Thanks!

Don't use double quotes when doing this in Oracle. Use single quotes to specify values, but double quotes means that you are trying to use exactly that string as a column or table name, and that is not what you want anyway. You highlighted the double quotes as if you were typing them as well, but maybe that was an accident. — MJB
– MJB, Commented Jun 12, 2014 at 20:18

troy · Accepted Answer · 2014-06-12 21:07:09Z

1

Try this for the openquery you are using:

select * from openquery(databasename, 'SELECT * FROM RICALM.REQUEST_TIMESTAMP_EXT DueDate WHERE DueDate.NAME = ''com.ibm.team.apt.attribute.constraintdate''')

Difference #1 being replacing the double quotes:

with two single quotes, which should resolve to a single quote:

''

Difference #2 being putting it as one line.

answered Jun 12, 2014 at 21:07

troy

4323 silver badges9 bronze badges

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

1 Comment

user3735564 Over a year ago

That worked! thanks so much! Didn't realize the double quotes were the issue!

Collectives™ on Stack Overflow

Oracle Query in Excel works, but not in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related