Oracle 列别名中的双引号
好吧,这是一个有点晦涩的问题,但希望有人能帮助我解决这个问题。
我正在开发的系统构建了一个用于在存储过程中执行的动态 SQL 字符串,以及定义列别名的动态 SQL 的一部分,这些别名本身实际上是从用户生成的数据的另一个表中检索的值。
因此,例如,该字符串可能看起来像这样;
SELECT table1.Col1 AS "This is an alias" FROM table1
这很好用。 但是,用于别名的值可能包含双引号字符,这会破坏外部引号。 我想我也许可以以某种方式转义别名内的双引号,但我没有运气弄清楚如何做到这一点。 反斜杠不起作用,连续使用两个双引号会导致此错误;
SQL Error: ORA-03001: unimplemented feature
03001. 00000 - "unimplemented feature"
*Cause: This feature is not implemented.
以前有人有过处理过这个问题的经验吗? 为任何人的任何见解干杯。
ps 别名周围需要引号,因为它们可以包含空格。
Ok, this is bit of an obscure question, but hopefully someone can help me out with it.
The system I'm working on builds a dynamic SQL string for execution inside a stored procedure, and part of that dynamic SQL defining column aliases, which themselves are actually values retrieved from another table of user generated data.
So, for example, the string might look something like;
SELECT table1.Col1 AS "This is an alias" FROM table1
This works fine. However, the value that is used for the alias can potentially contain a double quote character, which breaks the outer quotes. I thought that I could maybe escape double quotes inside the alias somehow, but I've had no luck figuring out how to do so. Backslash doesn't work, and using two double quotes in a row results in this error;
SQL Error: ORA-03001: unimplemented feature
03001. 00000 - "unimplemented feature"
*Cause: This feature is not implemented.
Has anyone had any experience with this issue before?
Cheers for any insight anyone has.
p.s. the quotes are needed around the aliases because they can contain spaces.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
一个可能富有成效的研究领域是研究报价方法。
我的 $quotedString = $dbh->quote( $string );
a possibly fruitful area of investigation would be to look into the quote method.
my $quotedString = $dbh->quote( $string );
试试这个,两个单引号实际上在输出中看起来像一个双引号:
从 Dual 中选择 1 作为“大学‘约翰·史密斯’”;
Try this, two single quotes actually look like one double quote in output:
select 1 as "University ''John Smith''" from dual;
当我运行这个:
Oracle 返回这个(注意 Oracle 生成的列名):
Oracle 的列名不包含我的双引号这一事实告诉我 Oracle 可能无法表示这一点。
据我所知,最好的办法是在使用列名之前从数据中去掉双引号。 遗憾的是,这还要求您在选择这些列时执行相同的过滤,但我没有看到其他方法。
When I run this:
Oracle returns this (notice the Oracle-generated column name):
The fact that Oracle's column name doesn't include my double-quote tells me that Oracle probably cannot represent that.
Best bet as far as I can see is to strip double-quotes from your data prior to using column names. Sadly, that will also require that you do the same filtering when you select those columns, but I don't see another way.
使用 Oracle 引号运算符:
“#”可以替换为任何字符
use the Oracle quote operator:
the '#' can be replaced by any character
您可以在代码中添加另一个字符而不是双引号并将其替换为双引号吗?
像这样:
然后只需替换 | 与“。
我知道这是一个黑客,但我想不出任何更好的解决方案......而且你正在做的事情无论如何都是一个黑客。“好的”方法是分别选择值和列名,然后将它们关联到你的代码中会让事情变得更加清晰。
Can you just put another character instead of double quotes and replace that with double quotes in the code?
Something like this:
Then just replace | with ".
I know it's a hack, but I can't think of any better solution... And what you are doing there is a hack anyway. The "nice" way would be to select the values and the column names separately and associate them in your code. That would make things much cleaner.