CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
gfrobenius
gfrobenius

Reputation: 4067

Oracle Regular Expression (REGEXP_LIKE) Too Long Error - 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

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.

Upvotes: 3

Views: 11066

Answers (2)

Daniel Rust
Daniel Rust

Reputation: 552

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.

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

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

Upvotes: 6

Related Questions