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.3 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.