I'm trying to create a database link using the
CONNECT WITH clause that's mentioned in the documentation for CREATE DATABASE LINK
so that I can use a database credential in the DDL statement.
The doc I'm referring to is :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-DATABASE-LINK.html#GUID-D966642A-B19E-449D-9968-1121AF06D793 When I try to create a DB link with this clause, I get ORA-00946: missing TO keyword.
The test case below was executed on 19c Enterprise Edition in a vagrant box generated from the oracle Github repo at
https://github.com/oracle/vagrant-projects/tree/main/OracleDatabase --
-- Create and use "standard" db link as a sense check
--
create database link hr_local connect to hr identified by hr using 'orclpdb1';
-- Verify the link works
select 'NOLINK' as source, username from user_users
union
select 'LINK', username from user_users@hr_local
order by 1 desc;
-- Output
SOURCE USERNAME
------ ---------------
NOLINK MIKE
LINK HR
This verifies that the link itself works when created using the conventional syntax.
When I try using a credential, this happens :
--
-- Create credential for HR
--
exec dbms_credential.create_credential( 'hr_cred', 'hr', 'the_hr_password');
--
-- Now create a db link using the credential
--
create database link hr_link_cred
connect with HR_CRED
using 'orclpdb1'
/
--
-- Output from the command run in a SQL*Plus session on the database server
--
connect with HR_CRED
*
ERROR at line 2:
ORA-00946: missing TO keyword
Could possibly point out what I'm doing wrong ?
Thanks,
Mike
I suspect this is a doc bug, because I'm pretty sure support for this only came in 21c and above
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> exec dbms_credential.create_credential('SCOTT_CRED', 'SCOTT', 'tiger')
PL/SQL procedure successfully completed.
SQL> create database link mydblink connect with SCOTT_CRED using 'db19s';
create database link mydblink connect with SCOTT_CRED using 'db19s'
*
ERROR at line 1:
ORA-00946: missing TO keyword
versus
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.16.0.0.0
SQL> exec dbms_credential.create_credential('SCOTT_CRED', 'SCOTT', 'tiger')
PL/SQL procedure successfully completed.
SQL> create database link mydblink connect with SCOTT_CRED using 'pdb21a';
Database link created.
SQL> select * from dual@mydblink;
D
-
X
SQL>
*Perhaps* it will be backported in 19.29 or 19.30 - I'll know that once I upgrade to the latest RU