For those Windows users out there who want both a BASH shell AND a really cool command line interface to Oracle Database – I have good news!

We updated SQLcl yesterday to include support for CYGWIN.

I have Cygwin64 and Windows 7.

it's a UNIX system! #JurassicParkJoke
it’s a UNIX system! #JurassicParkJoke

We also added a new command, SHOW TNS.

This will help you figure out how and where we’re finding your TNSNAMES* files.

SQL> show tns
 
TNS Lookup locations
--------------------
1.  USER Home dir
    C:\Users\jdsmith
2.  TNS_ADMIN
    D:\
 
Location used:
-------------
        C:\Users\jdsmith
 
Available TNS Entries
---------------------
DevDay11
DevDay12CDB
DevDay12PDB
SQL>

So we’ll look in your home directory, your TNS_ADMIN space, and your $ORACLE_HOME\network\admin space..and I think we peak into your registry as well…anyways, you’ll be able to troublehshoot now why you’re entry isn’t coming up.

You can also run a TNSPING on your TNS entry as well.

If you think this is cool, then it’s really not even the tip of the iceberg. Kris talks about yesterday’s BIG news here – you can now do client-side javascript scripting in SQLcl. Really opens up all what you can do with the tool…example, loading a BLOB from a file

script load blog with javascript sqlcl oracle

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

21 Comments

  1. Tried sqlcl version sqlcl-18.1.1

    Still getting error

    Error: Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli

    Issue : function checkCygwin is not called

    Solution : add checkCygwin function after setupClasspath

    modify following

    checkADE
    setupArgs
    setupSQLHome
    setupClasspath
    checkJavaLocation
    DetectSystemHttpProxySetting
    #Lets get your freak on
    bootStrap “[email protected]

    To

    checkADE
    setupArgs
    setupSQLHome
    setupClasspath
    checkCygwin <<<<<<<<<<<
    checkJavaLocation
    DetectSystemHttpProxySetting
    #Lets get your freak on
    bootStrap "[email protected]"

  2. Thought this might be useful for those facing below issue ….
    I have cygwin64 and windows 7. The sql command from cygwin was throwing this error
    “Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli” .

    On digging through sql script under /cygdrive/c/sqldeveloper/sqlcl/bin/ i found that the function checkCygwin() was not even called. Adding the function call at line 430 just after setupClasspath solved the issue.

  3. Dears,
    It is great tool, thanks for your effort.
    I have only one question.
    How make it to hide password when using in cygwin environment ?
    For the rest it works fine.

    Thanks again

  4. Has the handling for Cygwin without cmd.exe been updated in the last release in October. Not exactly what was meant by the next ‘cycle’ in the comment above.

  5. John Thomas Reply

    Jeff,

    Like the fact you support Cygwin.

    A few gremlins.

    In Cygwin the password is not hidden as you type.

    $ ./sql [email protected]:1521/db.world

    SQLcl: Release 4.2.0.15.296.0549 RC on Wed Nov 11 10:14:16 2015

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

    Password? (**********?) blabla

    (Not my password.)

    You are not reading the TNS_ADMIN Windows registry key or not using TNS_ADMIN environment variables. @ does not work, only @:1521:.

    Correction. I renamed my c:\users\bla\tnsnames.ora file to tnsnames.ora.local in order to use a global file defined at O:\TNS_ADMIN. You still used the local file.

    When I renamed it again to local.tnsnames.ora.local, you used the global file… too clever by half? (This was my glitch, but imagine lots of people will have a local TNSNAMES.ORA as well and may expect that by appending a suffix they can disable it.

    TNSPING still returns only ping:-1ms and returns the same if a nonsense string is pinged.

    Regards, JT

    • thatjeffsmith

      try
      show sqlpath

      show tns

      I think as soon as we find a tnsnames, we quit looking. If you have a local file, we’ll always find it first.

      We look for tnsnames* as that is what SQL*Plus does.

    • John Thomas

      Apologies, I should have checked that.

      So. It does find my file LOGIN.SQL and says it executed in Elapsed: 00:00:00.028.

      But I set the following with the result of some lookups so I can see useful info about my session. SQLcl just continues to display SQL>

      SET SQLPROMPT ‘&[email protected](&HOST_NAME.:&INSTANCE_NAME):&SID.,&SERIALno> ‘

      Looking at the variable values, they are defined in my session, but what’s wrong with the SQL prompt setting?

  6. Hi Jeff,

    Nice job. I have cygwin, but have not tried it with SQL Developer. I will try it.

    For the last week or two, I have been wondering what is going on that SQL Developer does not like my tnsname.ora file (Windows 7 and I have been using TNS_ADMIN environment variable for more than a year).

    With this fancy new SQLcl system variable (just updated my SQLcl installation), it confirmed to me that it was using my TNS_ADMIN entry, just that the first couple of many entries were archaic and obsolete. I removed the obsolete entries and it is now detecting/reading all my entries.

    Nice. Thanks!

  7. I gave it a try with my cygwin64 on windows 7.
    is there anything I need to config with the terminal?
    I can work with sqlplus without any problem.
    but when I try the sqlcl, the ENTER key doesn’t work.
    (I tried the same thing I showed here, with the password at the command line and I logged in, but couldn’t run any command within the tool)

    Yaniv

    [email protected] ~/sqlcl/bin
    $ sqlplus [email protected]

    SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 15 15:09:06 2015

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

    Enter password: expadminuser

    Connected to:
    Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

    SP2-0158: unknown SET option “sqlformat”
    EXPADMIN @ dbuat >select sysdate from dual;

    SYSDATE
    ——————
    15-OCT-15

    EXPADMIN @ dbuat >exit
    Disconnected from Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

    [email protected] ~/sqlcl/bin
    $ ./sql [email protected]

    SQLcl: Release 4.2.0.15.286.1242 RC on ▒ ▒▒▒ 15 15:09:41 2015

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

    Password? (**********?) dddddddddddddddddddd

    • thatjeffsmith

      No, i just had to set the path to include my cygwin64/bin folder.

      If you send the entire connect string does it at least get you into the db?

    • yes, it does connect. But once I get inside, I cannot do anything.

    • Scott Gamble

      If you just start cygwin’s bash shell it still does not work. you get the behavior that you describe. It has to do with the way windows works with pipes and pty. There is a link explaining the behavior which I was pointed to on the forums a while ago.

      The way to get it to work is to start in cmd.exe then from that window start bash then sqlcl works but you are then stuck with the limits of cmd.exe running bash.

      Unfortunately sqlplus works straight from a cygwin bash shell without using cmd.exe which for me means that is what I am using.

    • thatjeffsmith

      exactly, i opened a CMD window, and then ran bash

      for a better experience, you could dump CMD and try ConEmu64

    • Actually, we fixed the terminal thing too, it just didn’t make it into this release. The terminal settings needed to be hijacked to pull in the output streams that you are missing.

      Next Cycle will have it.
      B

    • thanx guys. with the CMD it works. Will also try the ConEmu64

Write A Comment