Cloud Platform

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

IO Error: could not resolve the connect identifier

Team KApr 14 2020 — edited Apr 15 2020

Hi guys,

I am trying to connect some Java code to ATP db via jdbc,

but above error pops as SQLException on connection.

Looking at the connection samples here https://github.com/oracle/oracle-db-examples/blob/master/java/jdbc/ConnectionSamples/DataSourceSample.java

here is my failing connection string:

ORACLE_REMOTE_URL = "jdbc:oracle:thin:@Wallet_db202004140937?TNS_ADMIN=E:/easyinc/Java/projects/08\\ multi\\ db\\ by\\ jdbc\\ driver/config/Oracle\\ Cloud";

This

ORACLE_REMOTE_URL = "jdbc:oracle:thin:@db202004140937_medium?TNS_ADMIN=E:/easyinc/Java/projects/08\\ multi\\ db\\ by\\ jdbc\\ driver/config/Oracle\\ Cloud";

also fails.

In terminal 'echo $TNS_ADMIN' correctly returns the directory where my wallet zip is extracted,

the ATP instance is up and ok, SQLDeveloper connection works fine.

Would appreciate some advice to fix my connection details.

Regards, K.

This post has been answered by Team K on Apr 15 2020

Jump to Answer

lee200

Use:

 REGEXP_INSTR(<col_name>, '[[:alpha:] -]')

789895

Hi,

Try this

SELECT *
FROM <TABLE> WHERE REGEXP_LIKE(<COLUMN>, '[a-z -][A-Z -]');

cheers

VT

BluShadow

VT wrote:
Hi,

Try this

SELECT *
FROM <TABLE> WHERE REGEXP_LIKE(<COLUMN>, '[a-z -][A-Z -]');

cheers

VT

That won't work as it's expecting at least two characters with the first having to be a-z (lower case) or space or "-" followed by A-Z (upper case) or space or "-".

The correct way is either:

[a-zA-Z -]

[[:alpha:] -]

using the alpha set is often preferable as it can work differently with different character sets/languages rather than restricting to just the a-zA-Z ranges.

Generating a reference for your own database characterset/language can be useful...

SQL> select level-1 as asc_code, decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), CHR(level-1)) as chr,
  2         decode(chr(level-1), regexp_substr(chr(level-1), '[[:graph:]]'), 1) is_graph,
  3         decode(chr(level-1), regexp_substr(chr(level-1), '[[:blank:]]'), 1) is_blank,
  4         decode(chr(level-1), regexp_substr(chr(level-1), '[[:alnum:]]'), 1) is_alnum,
  5         decode(chr(level-1), regexp_substr(chr(level-1), '[[:alpha:]]'), 1) is_alpha,
  6         decode(chr(level-1), regexp_substr(chr(level-1), '[[:digit:]]'), 1) is_digit,
  7         decode(chr(level-1), regexp_substr(chr(level-1), '[[:cntrl:]]'), 1) is_cntrl,
  8         decode(chr(level-1), regexp_substr(chr(level-1), '[[:lower:]]'), 1) is_lower,
  9         decode(chr(level-1), regexp_substr(chr(level-1), '[[:upper:]]'), 1) is_upper,
 10         decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), 1) is_print,
 11         decode(chr(level-1), regexp_substr(chr(level-1), '[[:punct:]]'), 1) is_punct,
 12         decode(chr(level-1), regexp_substr(chr(level-1), '[[:space:]]'), 1) is_space,
 13         decode(chr(level-1), regexp_substr(chr(level-1), '[[:xdigit:]]'), 1) is_xdigit
 14    from dual
 15  connect by level <= 256
 16  /

  ASC_CODE C   IS_GRAPH   IS_BLANK   IS_ALNUM   IS_ALPHA   IS_DIGIT   IS_CNTRL   IS_LOWER   IS_UPPER   IS_PRINT   IS_PUNCT   IS_SPACE  IS_XDIGIT
---------- - ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0                                                                   1
         1                                                                   1
         2                                                                   1
         3                                                                   1
         4                                                                   1
         5                                                                   1
         6                                                                   1
         7                                                                   1
         8                                                                   1
         9                                                                   1                                              1
        10                                                                   1                                              1
        11                                                                   1                                              1
        12                                                                   1                                              1
        13                                                                   1                                              1
        14                                                                   1
        15                                                                   1
        16                                                                   1
        17                                                                   1
        18                                                                   1
        19                                                                   1
        20                                                                   1
        21                                                                   1
        22                                                                   1
        23                                                                   1
        24                                                                   1
        25                                                                   1
        26                                                                   1
        27                                                                   1
        28                                                                   1
        29                                                                   1
        30                                                                   1
        31                                                                   1
        32                       1                                                                            1                     1
        33 !          1                                                                                       1          1
        34 "          1                                                                                       1          1
        35 #          1                                                                                       1          1
        36 $          1                                                                                       1          1
        37 %          1                                                                                       1          1
        38 &          1                                                                                       1          1
        39 '          1                                                                                       1          1
        40 (          1                                                                                       1          1
        41 )          1                                                                                       1          1
        42 *          1                                                                                       1          1
        43 +          1                                                                                       1          1
        44 ,          1                                                                                       1          1
        45 -          1                                                                                       1          1
        46 .          1                                                                                       1          1
        47 /          1                                                                                       1          1
        48 0          1                     1                     1                                           1                                1
        49 1          1                     1                     1                                           1                                1
        50 2          1                     1                     1                                           1                                1
        51 3          1                     1                     1                                           1                                1
        52 4          1                     1                     1                                           1                                1
        53 5          1                     1                     1                                           1                                1
        54 6          1                     1                     1                                           1                                1
        55 7          1                     1                     1                                           1                                1
        56 8          1                     1                     1                                           1                                1
        57 9          1                     1                     1                                           1                                1
        58 :          1                                                                                       1          1
        59 ;          1                                                                                       1          1
        60 <          1                                                                                       1          1
        61 =          1                                                                                       1          1
        62 >          1                                                                                       1          1
        63 ?          1                                                                                       1          1
        64 @          1                                                                                       1          1
        65 A          1                     1          1                                           1          1                                1
        66 B          1                     1          1                                           1          1                                1
        67 C          1                     1          1                                           1          1                                1
        68 D          1                     1          1                                           1          1                                1
        69 E          1                     1          1                                           1          1                                1
        70 F          1                     1          1                                           1          1                                1
        71 G          1                     1          1                                           1          1
        72 H          1                     1          1                                           1          1
        73 I          1                     1          1                                           1          1
        74 J          1                     1          1                                           1          1
        75 K          1                     1          1                                           1          1
        76 L          1                     1          1                                           1          1
        77 M          1                     1          1                                           1          1
        78 N          1                     1          1                                           1          1
        79 O          1                     1          1                                           1          1
        80 P          1                     1          1                                           1          1
        81 Q          1                     1          1                                           1          1
        82 R          1                     1          1                                           1          1
        83 S          1                     1          1                                           1          1
        84 T          1                     1          1                                           1          1
        85 U          1                     1          1                                           1          1
        86 V          1                     1          1                                           1          1
        87 W          1                     1          1                                           1          1
        88 X          1                     1          1                                           1          1
        89 Y          1                     1          1                                           1          1
        90 Z          1                     1          1                                           1          1
        91 [          1                                                                                       1          1
        92 \          1                                                                                       1          1
        93 ]          1                                                                                       1          1
        94 ^          1                                                                                       1          1
        95 _          1                                                                                       1          1
        96 `          1                                                                                       1          1
        97 a          1                     1          1                                1                     1                                1
        98 b          1                     1          1                                1                     1                                1
        99 c          1                     1          1                                1                     1                                1
       100 d          1                     1          1                                1                  1                           1
       101 e          1                     1          1                                1                  1                           1
       102 f          1                     1          1                                1                  1                           1
       103 g          1                     1          1                                1                  1
       104 h          1                     1          1                                1                  1
       105 i          1                     1          1                                1                  1
       106 j          1                     1          1                                1                  1
       107 k          1                     1          1                                1                  1
       108 l          1                     1          1                                1                  1
       109 m          1                     1          1                                1                  1
       110 n          1                     1          1                                1                  1
       111 o          1                     1          1                                1                  1
       112 p          1                     1          1                                1                  1
       113 q          1                     1          1                                1                  1
       114 r          1                     1          1                                1                  1
       115 s          1                     1          1                                1                  1
       116 t          1                     1          1                                1                  1
       117 u          1                     1          1                                1                  1
       118 v          1                     1          1                                1                  1
       119 w          1                     1          1                                1                  1
       120 x          1                     1          1                                1                  1
       121 y          1                     1          1                                1                  1
       122 z          1                     1          1                                1                  1
       123 {          1                                                                                    1     1
       124 |          1                                                                                    1     1
       125 }          1                                                                                    1     1
       126 ~          1                                                                                    1     1
       127                                                                   1
       128 Ç          1                                                                                    1     1
etc.
{code}




  
    
      
        Aketi Jyuuzou
        
        
      
      
          
      
    
  
  
     
     
    I like this Regex book ;-)

http://oreilly.com/catalog/9780596528126/


with t(Val) as(
select 'abc' from dual union all
select 'ABC' from dual union all
select '-'   from dual)
select Val,
case when RegExp_Like(Val,'[- a-zA-Z]')
     then 1 else 0 end as IsMatch
from t;

VAL  IsMatch
---  -------
abc        1
ABC        1
-          1
 
     
    
  
  
      
    
  



  
    
      
        MichaelS
        
        
      
      
          
      
    
  
  
     
     
    If your language settings support it you might use the somewhat shorter


   regexp_like (val, '[- a-Z]') 
instead of 


   regexp_like (val, '[- a-zA-Z]') 
 
     
    
  
  
      
    
  



  
    
      
        BluShadow
        
        
      
      
          
      
    
  
  
     
     
    MichaelS wrote:

If your language settings support it you might use the somewhat shorter


regexp_like (val, '[- a-Z]') 
Yeah, but most people will have standard ASCII chr sets so "a-Z" will give them some extra characters they weren't expecting. 
     
    
  
  
      
    
  



  
    
      
        MichaelS
        
        
      
      
          
      
    
  
  
     
     
    so "a-Z" will give them some extra characters they weren't expecting.
Don't think there'll be any different characters between [a-zA-Z] and [a-Z]: I think it is more just a matter of how the character class was implemented. It just seems it is not supoorted for every language:


SQL> alter session set nls_language=german
/
Session altered.

SQL> with t (val) as (
 select 'abc' from dual union all
 select 'ABC' from dual union all
 select 'Ab-C' from dual union all
 select '-' from dual
)
--
--
select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch
  from t
/
VAL     ISMATCH
---- ----------
abc           1
ABC           1
Ab-C          1
-             1

4 rows selected.

SQL> alter session set nls_language=english
/
Session altered.

SQL> with t (val) as (
 select 'abc' from dual union all
 select 'ABC' from dual union all
 select 'Ab-C' from dual union all
 select '-' from dual
)
--
--
select val, case when regexp_like (val, '[- [a-Zthen 1 else 0 end as ismatch
  from t
                                        *
Error at line 9
ORA-12728: invalid range in regular expression
 
     
    
  
  
      
    
  



  
    
      
        BluShadow
        
        
      
      
          
      
    
  
  
     
     
    MichaelS wrote:
so "a-Z" will give them some extra characters they weren't expecting.
Don't think there'll be any different characters between [a-zA-Z] and [a-Z]
Ooo, but there is (see the list of ASCII character in my post above)...


SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'HeresMyAlphaStringwith]anothercharacter' as txt from dual)
  2  --
  3  select case when regexp_like(txt,'^[A-z]+$') then 'like A-z' else null end as "Atoz"
  4        ,case when regexp_like(txt,'^[a-zA-Z]+$') then 'like a-zA-Z' else null end as "atozAtoZ"
  5* from t
SQL> /

Atoz     atozAtoZ
-------- -----------
like A-z

SQL>
"A-z" (rather than "a-Z" because ASCII "A" is before ASCII "z" in English characterset) contains other characters like "]" etc. Specifying the two sets "a-z" and "A-Z" is more specific and limiting which gives what is expected. 
     
    
  
  
      
    
  



  
    
      
        John Spencer
        
        
      
      
          
      
    
  
  
     
     
    Michael:



It is not that it is unimplemented, its just that you asked it to do something unreasonable. My nls_language is english


SQL> with t as (
  2   select 'a' val from dual union all
  3   select 'Z' from dual union all
  4   select 'A' from dual union all
  5   select 'z' from dual)
  6  SELECT val FROM t
  7  ORDER BY val;
 
V
-
A
Z
a
z
 
SQL> with t as (
  2   select 'a' val from dual union all
  3   select 'Z' from dual union all
  4   select 'A' from dual union all
  5   select 'z' from dual
  6  SELECT val FROM t
  7  ORDER BY NLSSORT(val, 'NLS_SORT = German');
 
V
-
a
A
z
Z
John 
     
    
  
  
      
    
  



  
    
      
        MichaelS
        
        
      
      
          
      
    
  
  
     
     
    Thanks John, Blu - all makes sense now ;)


1 - 10

Added on Apr 14 2020

#general-questions, #jdbc

2 comments

4,462 views

Cloud Platform

IO Error: could not resolve the connect identifier

Comments

Post Details