oracle 如何在 SQL 查询中选择静态字符串并确保其类型为 VARCHAR2？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3807905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I select a static string in a SQL query and ensure its type is VARCHAR2?
提问by aw crud
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@remotedbis either INTEGERor VARCHAR2.
中的每一列mytable@remotedb都是INTEGER或VARCHAR2。
If I remove 'test' as tstthere is no problem, so it appears the static string 'test'is somehow being formatted as a CLOBrather than the VARCHAR2I assumed it would.
如果我删除'test' as tst没有问题，所以看起来静态字符串'test'以某种方式被格式化为 aCLOB而不是VARCHAR2我假设的。
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?
我需要将它转换为 aVARCHAR2吗？这看起来很奇怪......有没有更好的方法在查询中创建静态返回字符串以确保它们将是VARCHAR2？
回答by Bob Jarvis - Reinstate Monica
Not sure, but I notice that the query selects "mt.id" from the remote table, but the alias of the remote table is "rd".
不确定，但我注意到查询从远程表中选择了“mt.id”，但远程表的别名是“rd”。
Try the following:
请尝试以下操作：
select /*+ DRIVING_SITE(rd) */ 'test' as tst, rd.id from mytable@remotedb rd
I hope this helps.
我希望这有帮助。
回答by Jeffrey Kemp
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.
我怀疑该错误与 LOB 无关，我怀疑您认为这是 Oracle 错误是对的。尝试解决方法（即用旧的 Oracle 语法替换 ANSI 语法），如果它有效，那么在升级数据库之前您可能不得不忍受。
