oracle 正则表达式太长,Oracle正则表达式(REGEXP_LIKE)太长错误——ORA-12733。

I have to validate an IPv6 address in PL/SQL. I came up with the regular expression from here: Regular Expression (RegEx) for IPv6 Separate from IPv4

我必须在PL/SQL中验证IPv6地址。我从这里得到了正则表达式:正则表达式(RegEx)与IPv4分离。

I am getting an ORA-12733: regular expression too long error. Is there any way around this?

我得到了一个ORA-12733:正则表达式太长错误。有没有办法解决这个问题?

if ( REGEXP_LIKE(v,'^(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,7}:|([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|([0-a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|:((:[0-9a-fA-F]{1,4}){1,7}|:)|fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])|([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9]).){3,3}(25[0-5]|(2[0-4]1{0,1}[0-9]){0,1}[0-9]))$') ) then

self.success := 1;

self.message := null;

return;

else

self.success := 0;

self.message := 'Invalid IPv6 address. Example of a valid format: 2001:0db8:0000:0000:0000:ff00:0042:8329';

return;

end if;

The limit is 512 (https://stackoverflow.com/a/2694119/3112803), I'm at 657. I cannot think of any way to split this up.

限制是512 (https://stackoverflow.com/a/2694119/3112803)，我是657。我想不出有什么办法可以把它分割开。

将java类导入到Oracle中是唯一的方法吗?https://community.oracle.com/thread/1149462?start=0&tstart=0

UPDATE: I'm trying this but when I run the select to test it, it throws ORA-29531: no method isProperIPv6Address in class ipv6. I haven't messed with java in Oracle before so I'm not sure if I'm close or if this is a bad idea. (taken from https://community.oracle.com/message/8648095)

更新:我正在尝试这个，但是当我运行select来测试它时，它会抛出ORA-29531:在类ipv6中没有方法isProperIPv6Address。我以前没有在Oracle中使用过java，所以我不确定我是否接近，或者这是不是一个坏主意。(来自https://community.oracle.com/message/8648095)

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ipv6 as

public class ipv6

{

public static int isProperIPv6Address(String address)

{

try

{

java.net.Inet6Address.getByName(address);

return 1;

} catch(Exception e) { return 0; }

}

}

create or replace function isProperIPv6Address (string in varchar2)

return number as language java name 'ipv6.isProperIPv6Address(java.lang.Int) return java.lang.Int';

select isProperIPv6Address('blah') from dual;

It does appear that java.net.Inet6Address.getByName() will literally go out to the internet and see if this address really exists. Am I understanding that correctly? I don't need that. I just need to verify that they syntax is valid.

它确实显示了java.net.Inet6Address.getByName()将会真正地到internet上，看看这个地址是否真的存在。我理解的正确吗?我不需要这个。我只需要验证它们的语法是否有效。

1 个解决方案

#1

3

Replace your long regexp ^regexp1|regexp2|...$ with logical OR of many short regexps:

if ( REGEXP_LIKE(v,'^regexp1$') OR REGEXP_LIKE(v,'^regexp2$') OR ... ) then

替换您的长regexp regexp1|regexp2|…美元与逻辑或许多短的regexp:如果的(REGEXP_LIKE(v,“^ regexp1美元”)的或REGEXP_LIKE(v,“^ regexp2美元”)或…)然后

UPDATE:

更新:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ipv6 as

import java.net.Inet6Address;

public class ipv6

...

create or replace function isProperIPv6Address (string varchar2) return number

as language java name 'ipv6.isProperIPv6Address(java.lang.String) return int';