sql - Oracle 正则表达式 (REGEXP_LIKE) 太长错误 - ORA-12733

Question

我必须在 PL/SQL 中验证 IPv6 地址。我从这里想出了正则表达式:Regular Expression (RegEx) for IPv6 Separate from IPv4

我遇到了 ORA-12733: regular expression too long 错误。有什么办法解决这个问题吗？

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;

限制是 512 ( https://stackoverflow.com/a/2694119/3112803 )，我在 657。我想不出任何方法来拆分它。

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

更新: 我正在尝试这个，但是当我运行 select 来测试它时，它抛出 ORA-29531: no method isProperIPv6Address in class ipv6。我以前没有在 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;

看来 java.net.Inet6Address.getByName() 确实会去 Internet 看看这个地址是否真的存在。我理解正确吗？我不需要那个。我只需要验证它们的语法是否有效。

Answer 1

将您的长正则表达式 ^regexp1|regexp2|...$ 替换为许多短正则表达式的逻辑或:
if ( REGEXP_LIKE(v,'^regexp1$') OR REGEXP_LIKE(v,'^regexp2$') OR ... ) then

更新:

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';