Friday, 20 February 2015

Connections Types in SQLcl


We support many ways to connect in SQLcl, including lots from SQL*Plus which we need to support to make sure all your SQL*Plus scripts work exactly the same way using SQLcl as with SQL*Plus.

I've added several ways to show how to connect to SQLcl.  If there is one you want to see added that is not here, let me know and I'll add it to the list.  So far, We have below:
  • EZConnect
  • TWO_TASK
  • TNS_ADMIN
  • LDAP
At any time when connected you can use the command 'SHOW JDBC'  to display what the connection is and how we are connected.  Here's some details of the types above.

EZCONNECT

The easy connect naming method eliminates the need for service name lookup in the tnsnames.ora files for TCP/IP environments.  It extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database:

 $sql barry/oracle@localhost:1521/orcl  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:15:12 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production   
 SQL>   

TWO_TASK

The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection  is made in SQL*Plus or SQL*Plus Instant Client. 

In SQLcl, we can set this up as a jdbc style connection like this


$export TWO_TASK=localhost:1521/orcl  




TNS_ADMIN


Local Naming resolves a net service name stored in a tnsnames.ora file stored on a client.  We can set the location of that in the TNS_ADMIN variable.

 $export TNS_ADMIN=~/admin  

An example tons entry is shown here below.

 $cat tnsnames.ora   
 BLOG =  
 (DESCRIPTION =  
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )  
 (CONNECT_DATA=  
 (SERVICE_NAME=orcl) ) )  

we can then use the entry to connect to the database.

 $sql barry/oracle@BLOG  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:29:14 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production   
 SQL>   

LDAP

We've already written about LDAP connections here.  Here's a quick review.

  set LDAPCON jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom   


 $export LDAPCON=jdbc:oracle:thin:@ldap://scl58261.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom   
 $sql /nolog  
 SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:37:02 2015  
 Copyright (c) 1982, 2015, Oracle. All rights reserved.  
 SQL> connect barry/oracle@orclservice_test(Emily's Desktop)   
 Connected  
 SQL>   

If we have more types to add, then they will appear here.  Let us know what you want to see.

6 comments:

Franck Pachot said...

Hi,

The full connection strings is also working well, which is nice in some situations:

C:\Users\frp>F:\sd41\sqlcl\bin\sql.bat demo/demo@"(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.113)(PORT=1521)))"

SQLcl: Release 4.1.0 Beta on sam. févr. 21 21:56:21 2015

Copyright (c) 1982, 2015, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


Regards,
Franck.

Mikhail Velikikh said...

Hi,

Do you have any plans for support secure external password store?

Best regards,
Mikhail.

Barry McGillin said...

Yes, it works today. You need to set up a wallet and then make sure you are using the -oci flag which makes SQLcl use the thick driver where this functionality is supported by default.

Moritz Klein said...

HI Barry,

I just tried this with the latest version of sqlcl.
Couldn't get it going even though SQL*Plus can use wallet password store.
It seems like sqlcl first does a lookup in tnsnames and then connects using the derived connect string.
Is there any option to give a custome jdbc connection string like in SQLDeveloper?

Regards,
Moritz

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

punno said...

Hi Team,

I am using sqlcl to connect a database and able to connect as well but I can see some other user from my host can see my database credential by using this command (ps-ef|grep bash)
I have tried below method to connect
EZConnect ($sql barry/oracle@localhost:1521/orcl)
TWO_TASK export TWO_TASK=localhost:1521/orcl and then sql barry/barry
TNS_ADMIN export TNS_ADMIN=~/admin ora file and then $sql barry/oracle@Servicename
LDAP Not tried
but in my host if some one execute ps-ef|grep bash command then any one can see my db credential

below output for above command

/bin/bash /usr/local//isql/sqlcl/bin/sql barry/barry@hostname:1521/sid
or
/bin/bash /usr/local//isql/sqlcl/bin/sql barry/barry..

Can you please explain any other way to connect DB by using sqlcl to hide credential

Thanks
Punit Porwal