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.

Posted by
Labels: Connections glogin oracle Oracle Developer Day VM sqlcl SQLDEVELOPER sqlplus
10

View comments

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

    ReplyDelete

  2. Hi,

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

    Best regards,
    Mikhail.

    ReplyDelete
    Replies

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

      Delete

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

      Delete

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

    ReplyDelete

  4. yes,

    dont put it on the command line

    (bamcgill@daedalus.home)–(0|ttys000|-bash)
    (~) $sql barry

    SQLcl: Release 17.4.0 Production on Thu Sep 07 15:15:30 2017

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

    Password? (**********?) ******
    Last Successful login time: Thu Sep 07 2017 15:15:34 +01:00

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

    loading login.sql
    USER
    BARRY



    BARRY@orcl🍻🍺 >!ps -ef|grep barry
    501 3911 1389 0 3:15pm ttys000 0:00.01 /bin/bash /Users/bamcgill/code/sqlcl/common/sqlcl/built/bin/sql barry

    ReplyDelete

  5. Thanks Barry for your response , This is new feature in 17.4 version , earlier version 4.2 we user to ./sql nolog to connect a database.

    I am looking TCPS connection , what string we need to set a database connection and I have keystore.jks and its credential but do not know how to use it. while in jdbc we set in property file can you please help..

    Thanks
    Punit Porwal

    ReplyDelete

  6. The most effective method to Find Oracle Database Connection Details with Remote DBA Services
    Well! In the event that you are new on Oracle Database and in the event that you make another association through SQL designer however in the event that you are not ready to get the association points of interest, have name and port at that point rapidly contact to Database Administration for Oracle or Online Oracle DB Support. Aside from that on the off chance that you have any issue with respect to Oracle like association issue, establishment issue, arrangement issue et cetera then you can likewise contact to our Oracle Database Solution and experience the best help through most recent innovation.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete

  7. Hi Barry, I have just started to migrate over to sqlcl from sqlplus on windows. on 18.1.1 I cant get tns_admin to work. I have seen a ticket on oracle suggesting modifing sql.bat but its no longer shipped with. any idea how to resolve

    ReplyDelete

  8. Hi Barry, We have a shared tnsnames file that sits on a windows server box and I'm using a mac. I've tried unsuccessfully to point to that shared tnsnames file.
    Things I've tried:
    set TNS_ADMIN environment variable
    set ORACLE_HOME
    create a tnsnames file that points to the shared one

    Oracle 12cR2
    Oracle InstantClient 18.1
    Any thoughts?

    ReplyDelete
Try this in bash

git p

You get a file names p or a list of them.
When a project has been on the go for a while theres going to be all sorts of stuff in there from jars to zips and everything in between.
Run them with the code we use for Oracle SQL Developer, SQLcl and REST Data Services.

We've just released some of the code that underpins these tools in an attempt to help  others run SQL, PLSQL and SQL*Plus scripts confidently and repeatably from java.
So you want Docker and to install and Oracle Database locally.  So did I and while I have VirtualBox and the Oracle Developer Day VM, I wanted to setup docker and the Oracle Database

Well Colm Divilly (@cdivilly) has cleared it up for me.  His Gist on on Github had me up and running.
We're always trying to make SQLcl easier to connect to your database, whether its at your place or in the cloud.  So, one other thing we have added to enable you to drill into your cloud databases is an SSHTUNNEL command.  Lets take a look at the help for it, which you can get as follows.
Sometimes people want to connect to MySQL from Oracle and copy table data between the databases.  You can do that with Oracle Hetrogenous Services via ODBC.  This post will show how to create an odbc connection to your MySQL database which is the first part of this.
I find myself installing and running mysql of different versions in different places for different reasons all the time (well often enough to do it and not remember the little things that cost time when setting up)   Its with that in mind, I'm making notes for myself and you guys as well to help you
Ok, Finally, we have got to part 3 of Application Migration.  In Part 1, we outlined a program which runs in Sybase through iSQL.  We then followed this, in part 2 with 2 important pieces.
I was working through a bug the other day and using SQL*Plus, which for the most part doesn't annoy me too much.  However, one of the things that does, is having to retype lots of stuff. (We dont have that problem in SQL Developer).
About Me
About Me
My Blog List
My Blog List
Page Views
Page Views
9 3 5 6 6 8
Subscribe
Subscribe
Blog Archive
Blog Archive
Interesting Links
Interesting Links
Loading
Dynamic Views theme. Theme images by Jason Morrow. Powered by Blogger.