Oracle Regular Expression (REGEXP_LIKE) Too Long Error - ORA-12733

Question

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

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

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.

Is importing a java class into Oracle the only way? 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)

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.

Egor Skriptunoff · Accepted Answer · 2014-02-20T23:54:55.547

5

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

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

edited Feb 20 '14 at 23:54

answered Feb 20 '14 at 23:21

Egor Skriptunoff

23,359
2
34
64

That's just it, I don't know how to split this up because the "blocks" of numbers can vary in IPv6. From my research so far it seems like using java is the way to go. That's what I'm trying now. I updated the question. – gfrobenius Feb 20 '14 at 23:32
Hmmm, No joy, added the missing line, dropped java source, recompiled, same error. – gfrobenius Feb 20 '14 at 23:44
Good Eye! +1 Does this literally go out to the internet and see if the IPv6 address exists? I think that's what I'm reading. I can't have that. I just need to validate that the format/syntax is proper. – gfrobenius Feb 20 '14 at 23:58
1

@gfrobenius - No, it does not ping that address ))) – Egor Skriptunoff Feb 21 '14 at 00:28
The user here disagrees, he says it will "go out" if it's not valid: http://stackoverflow.com/questions/21922752/does-java-net-inet6address-getbyname-literally-go-out-and-see-if-the-address-exi – gfrobenius Feb 21 '14 at 00:47

score 0 · Answer 2 · answered Mar 31 '22 at 16:34

Besides the regex syntax length, REGEXP_LIKE only works in the WHERE condition. Please refer to Oracle documentation. You may use REGEXP_INSTR to bring the position of the 1st occurrence. In your case, it will always be either 1 (found on 1st character) or 0 (not found). To play on the safe side, I wrote "regexp_instr >0".

SELECT
CASE 
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,7}:$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}$]') > 0 THEN 1                     
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}$]') > 0 THEN 1                
    WHEN REGEXP_INSTR (dummy, q'[^([0-9a-fA-F]{1,4}:)((:[0-9a-fA-F]{1,4}){1,6})$]') > 0 THEN 1        
    WHEN REGEXP_INSTR (dummy, q'[^:((:[0-9a-fA-F]{1,4}){1,7}|:)$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}$]') > 0 THEN 1
    WHEN REGEXP_INSTR (dummy, q'[^::(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 THEN 1                     
    WHEN REGEXP_INSTR (dummy, q'[^([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])$]') > 0 THEN 1                
    ELSE 0
END AS IPV6_CHECK FROM DUAL

Note: please double check the IPV6 syntax before using it.

Oracle Regular Expression (REGEXP_LIKE) Too Long Error - ORA-12733

2 Answers2

Linked