Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

SQLcl 23.x: Cannot connect with schema using proxy user which has a period (.) character in the name

GiliamBApr 7 2024 — edited Apr 8 2024

Connecting to a schema in a script, by using a proxy user which has a period (.) in its name, fails to connect and comes up with following error.

SQL> PROMPT Connect to "PROXY.USER"[schema_owner]
Connect to "PROXY.USER"[schema_owner]

SQL> conn "PROXY.USER"[schema_owner]/oracle@localhost/hmedb001
The connect request was not recognized: please type HELP CONNECT to find out more about the supported combinations of command options

Last known correct working version is SQLcl v21.3.1.281.1748. Don't know about 22.x , but none of the 23.x until 24.1 works..

Let me illustrate the complete picture here.

I created a script to validate if I can connect using various variants:

  • schema_name @ local_database ✔
  • “SCHEMA_NAME” @ local_database ✔
  • “PROXY” @ local_database ✔
  • "PROXY.USER" @ local_database ✔
  • “PROXY” [SCHEMA_NAME] @ local_database ✔
  • “PROXY.USER” [SCHEMA_NAME] @ local_database ❌ (notice the period . between proxy and user!)
-- Run as SYS 
set serveroutput on size unlimited 
set define on 
set verify off 
set termout on 


PROMPT Drop Existing Schemas for proxy-user testing 
DROP USER "PROXY"; 
DROP USER "PROXY.USER"; 
DROP USER "SCHEMA_OWNER"; 

PROMPT Creating Schema 'PROXY' 
CREATE USER "PROXY" IDENTIFIED BY "oracle" account unlock; 

PROMPT Creating Schema 'PROXY.USER' 
CREATE USER "PROXY.USER" IDENTIFIED BY "oracle" account unlock; 

PROMPT Creating Schema 'SCHEMA_OWNER' 
CREATE USER "SCHEMA_OWNER" IDENTIFIED BY "oracle" account unlock; 

PROMPT Granting Create Session to these users 
grant CREATE SESSION to "PROXY"; 
grant CREATE SESSION to "PROXY.USER"; 
grant CREATE SESSION to "SCHEMA_OWNER"; 

PROMPT Granting Connect as [proxy] user 
alter user "SCHEMA_OWNER" grant connect through "PROXY"; 
alter user "SCHEMA_OWNER" grant connect through "PROXY.USER"; 


PROMPT Connect to SCHEMA_OWNER 
conn schema_owner/oracle@localhost/hmedb001 

PROMPT Connect to "SCHEMA_OWNER" 
conn "SCHEMA_OWNER"/oracle@localhost/hmedb001 

PROMPT Connect to PROXY 
conn proxy/oracle@localhost/hmedb001 

PROMPT Connect to "PROXY" 
conn "PROXY"/oracle@localhost/hmedb001 

PROMPT Connect to "PROXY.USER" 
conn "PROXY.USER"/oracle@localhost/hmedb001 


PROMPT Connect to PROXY[schema_owner] 
conn proxy[schema_owner]/oracle@localhost/hmedb001 

PROMPT Connect to "PROXY"[schema_owner] 
conn "PROXY"[schema_owner]/oracle@localhost/hmedb001 

PROMPT Connect to "PROXY.USER"[schema_owner] 
conn "PROXY.USER"[schema_owner]/oracle@localhost/hmedb001

If you're running the script above with SQLcl 21.3 you will get the expected results.

Connect to SCHEMA_OWNER
Connected.

Connect to "SCHEMA_OWNER"
Connected.

Connect to PROXY
Connected.

Connect to "PROXY"
Connected.

Connect to "PROXY.USER"
Connected.

Connect to PROXY[schema_owner]
Connected.

Connect to "PROXY"[schema_owner]
Connected.

Connect to "PROXY.USER"[schema_owner]
Connected.

Run the script again using 23.x - 24.1 and the error appears.

Connect to SCHEMA_OWNER
Connected.

Connect to "SCHEMA_OWNER"
Connected.

Connect to PROXY
Connected.

Connect to "PROXY"
Connected.

Connect to "PROXY.USER"
Connected.

Connect to PROXY[schema_owner]
Connected.

Connect to "PROXY"[schema_owner]
Connected.

Connect to "PROXY.USER"[schema_owner]
The connect request was not recognized: please type HELP CONNECT to find out more about the supported 
combinations of command options

Error also appears when running the script in SQL Developer v22.x or later. Last known working version is SQL Developer v21.4.3.063

Error also appears in Extension: Oracle SQL Developer Extension for VSCode, which is using SQLcl 23.3 under the hood currently. I have tested most recent versions until 23.4.2 (pre-release).

Hopefully this bug can be fixed soon.

Comments
Post Details
Added on Apr 7 2024
0 comments
93 views