How do I select a static string in a SQL query and ensure its type is VARCHAR2?

Question

I have a query that is executed on a remote database:

select /*+ DRIVING_SITE(rd) */ 'test' as tst, rd.id from mytable@remotedb rd

When I execute this query I get:

ORA-22992: cannot use LOB locators selected from remote tables

Every column in mytable@remotedb is either INTEGER or VARCHAR2.

If I remove 'test' as tst there is no problem, so it appears the static string 'test' is somehow being formatted as a CLOB rather than the VARCHAR2 I assumed it would.

Do I need to cast this to a VARCHAR2? This seems odd... is there a better way to create static return strings in a query that ensures they will be VARCHAR2?

Answer 1

Not sure, but I notice that the query selects "mt.id" from the remote table, but the alias of the remote table is "rd".

Try the following:

select /*+ DRIVING_SITE(rd) */ 'test' as tst, rd.id from mytable@remotedb rd

I hope this helps.

Answer 2

I suspect the error has nothing to do with LOBs, and I suspect you're right that this is an Oracle bug. Try the workaround (i.e. replace the ANSI syntax with the older Oracle syntax) and if it works, that's probably what you'll have to put up with until you upgrade the database.