英文:
Oracle Apex regexp pass as bind variable failing in function
问题
我有一个用于查找给定变量的regexp_like的函数。
当我运行它如下：
select func_name('ABC123','^[[:alpha:]]{3,3}[[:digit:]]{3,3}$') from
dual;
它返回TRUE，如果我使用ABC1234而不是ABC123，它返回FALSE。
然而，如果我使用绑定变量：
select regex_func_name(:NAME,:FORMAT) from
dual;
并传递 :NAME = ABC123 和 :FORMAT = '^[[:alpha:]]{3,3}[[:digit:]]{3,3}$'，它在任何情况下都返回FALSE。
我该如何正确传递绑定变量？
我尝试将 :NAME 用引号括起来，即 'ABC123'，但没有任何作用。
英文:
I have a function to find out regexp_like for the given variable.
When i run it as
> select func_name('ABC123','^[[:alpha:]]{3,3}[[:digit:]]{3,3}$') from
> dual;
it returns TRUE and if instead of ABC123 i use ABC1234 it returns FALSE.
However, if i use bind variables :
> select regex_func_name(:NAME,:FORMAT) from
> dual;
And pass :NAME = ABC123 and :FORMAT = '^[[:alpha:]]{3,3}[[:digit:]]{3,3}$' it returns FALSE in any scenario.
How can i pass bind variable correctly?
I tried passing :NAME in quote, i.e. 'ABC123', but nothing is working.
答案1
得分: 1
我会说你正在使用 Apex SQL Workshop。如果是这样，它会因为某种原因（我不知道）在第14个字符处截断绑定变量。
是的，你可以复制/粘贴完整内容（即^[[:alpha:]]{3,3}[[:digit:]]{3,3}$）到绑定变量的项目中，但实际上它会被截断。如何知道呢？再次点击运行，你会看到之前使用的预填充值。
如果你将NAME缩短为ABC并将FORMAT修改为[[:alpha:]]{3}，查询会返回值。
除此之外，你的查询没有问题；你可以按原样执行它，将这些值（不包括单引号！）传递给其他 GUI 工具，如 TOAD，一切都可以正常运行。
我尝试寻找关于 Apex 绑定变量限制（特别是其长度）的参考，但找不到任何信息。也许这是一个特性，但我更愿意将其视为错误。
英文:
I'd say that you're using Apex SQL Workshop. If so, it - for some reason (unknown to me) - cuts bind variable at 14th character.
Yes - you can copy/paste its full contents (this: ^[[:alpha:]]{3,3}[[:digit:]]{3,3}$) into bind variable's item, but it is actually cut. How do I know? Hit Run again, and you'll see pre-populated values you used previously.
If you shorten NAME to ABC and modify FORMAT to [[:alpha:]]{3}, query returns value.
Besides that, there's no problem in your query; you can execute it as is, pass those values (without single quotes!) to other GUI tools such as TOAD and everything works just fine.
I tried to find reference to Apex' bind variable's limits (specially its length), but couldn't find anything. Maybe it is a feature, but I'd rather think of it as a bug.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论