CODEWITHSUNDEEP
regexoracle-databaseoracle-apex
Koen van Zuijlen
Koen van Zuijlen

Reputation: 395

Regex not working in APEX, working on RegexPlanet

I am trying to create a validation for a field where the user should enter an Oracle JDBC Thin connection URL, like:

I tried testing the regex with RegexPlanet and it says it's working (the regex I am using):

^jdbc:oracle:thin:[@//]*[.\w]+:\d+[:]*[/]*[a-zA-Z0-9.]*$

But when I try to validate the form using this regex it shows the error message, even when I am using the exact same URL's as the one's above. Other validations are working just fine.

Upvotes: 0

Views: 243

Answers (1)

MT0
MT0

Reputation: 168361

A JDBC Thin Driver Connection string can take one of these formats:

jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

Where HOST can be an IP Address or a name.

If you ignore the username/password then a regular expression to match is something like:

^jdbc:oracle:thin:@(//)?(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|\w+):\d+[:/][a-zA-Z0-9.]+$

(although it's not perfect - but a perfect regular expression to match this would be huge)

Query:

WITH connections ( conn ) AS (
  SELECT 'jdbc:oracle:thin:@//192.168.2.1:1521/XE' FROM DUAL UNION ALL
  SELECT 'jdbc:oracle:thin:@192.168.2.1:1521:X01A' FROM DUAL UNION ALL
  SELECT 'jdbc:oracle:thin:@//192.168.2.1:1521/COM.EXAMPLE' FROM DUAL
)
SELECT *
FROM   connections
WHERE  REGEXP_LIKE( conn, '^jdbc:oracle:thin:@(//)?(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|\w+):\d+[:/][a-zA-Z0-9.]+$' );

Results:

CONN                                           
------------------------------------------------
jdbc:oracle:thin:@//192.168.2.1:1521/XE          
jdbc:oracle:thin:@192.168.2.1:1521:X01A          
jdbc:oracle:thin:@//192.168.2.1:1521/COM.EXAMPLE

Upvotes: 1

Related Questions