When demonstrating SQLcl, I get the feeling that half of the audience is filled with dread.

Yes, they love everything that I’m showing them.

But deep, deep down they are scared that the things they personally liked in SQL*Plus are going away.

I’m here today to alleviate those concerns.

Instead of asking you to just take my word for it, I’ll show you.

Now of course you COULD just download it yourself and have a go, but folks are busy, and I understand.

The CHANGE Command

I just always knew it as ‘c’ as in C/OLD/NEW. It’s a bit more complicated than that [Docs], but here goes.

You can now also set your editor to SQLcl's inline editor and just arrow around your buffer and change stuff as you please...
You can now also set your editor to SQLcl’s inline editor and just arrow around your buffer and change stuff as you please…

& and &&

I know that looks funny, but you know what I mean, and…

Prompts for value each time...
Prompts for value each time…
Reuses value from query to query
Reuses value from query to query

Your Prompt

The PROMPT and pretty much all of the other program specific settings can still be managed with the SET command.

Like this one…

SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER _DATE> ”

You're seen Kris' beer emoji prompts, yes?
You’re seen Kris’ beer emoji prompts, yes?

A Side-Bar On Prompts

I’ve been chided by my boss to show the ‘cool’ PROMPT.

Since we’re a Java application, Unicode support is a given.

So, if your OS supports Unicode character display in your terminal window, then you can do cool things like this:

Do you even SQLDev?
Do you even SQLDev?

You can find a lot of the unicode emoji’s here.

Someone who has Windows 10, let me know if their CMD window has gotten any better. In Windows 7 I was unable to get this to ‘work’ in the default terminal or even a newer one like ConEmu64.

SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER ? > ” #FTW

Back to SET Commands…

A simple ‘SHOW ALL’ does just that. But, if you say HELP SHOW, you will see all of the things we can show.

Show me yours, and...
Show me yours, and…

Other Things

Yes, you can still use a LOGIN or GLOGIN script. Yes, you can insist on using DESC even though INFO is waaaaay better.

Our intent was not to break anything SQL*Plus offered, but to extend it with all the new stuff you’ve been seeing us add over the past year.

Still don’t believe me? That’s probably good, better to trust but verify.

If you have a question or if you are curious about a specific SQL*Plus ‘thing’ – drop me a note in the comments and I’ll reply as best I can.

thatjeffsmith
Author

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

28 Comments

  1. Which error messages are possible in SQLcl? It seems to me that there are no more SP2-XXXX or CPY-XXXX messages but only ORA-XXXXX, is this correct?
    And the error messages are always introduced by “Error starting at line …” instead of “ERROR at line …”, is it true?

  2. SQLcl is a nice tool. But many things are not working as expected. For example, I am producing a csv output and don’t want the column heading. So I did set sqlformat csv and set heading off. But headings are still displayed! Is it a bug? SQLPlus does not work that way.

  3. Hi Jeff

    After attending the sqlcl session at the HotSos conference, I decided to give it a try.
    I’m using LDAP configuration in sqlnet.ora and ldap.ora, which means my tnsnames.ora file is empty, but it looks like sqlcl doesn’t like that.

    So I ran tnsping and got the complete connection string like (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(CONNECT_DATA=(SERVICE_NAME=))) and was able to connect to the database using that.

    Is there a way to have sqlcl looking into the sqlnet.ora and ldap.ora files to find the correct connect string? Btw, I have already set TNS_ADMIN to the correct folder where I store my configuration files.

    Thanks

  4. Ciaran O'S Reply

    Hi Jeff,

    Quick Question about SQLCL. I am playing with it and I think I could make it part of my normal flow, but there is one SQL*Plus feature that I cannot replicate.

    On Windows 7, in SQL plus I have scripts created to connect to various databases. The scripts issue a “host color 0D” to set the command prompt window text colour to different colours for prod/dev/uat/etc… In SQLCl when I issue the host command the colour changes, but then immediatly changes back again. Is there a similar feature in SQLCl that I could use to replicate that behaviour?

    Thank you,
    Ciaran

    • thatjeffsmith

      That should work, might be a bug. In the meantime you can set your own custom prompts, complete with colors and text. See Kris’ blog for examples.

  5. Stuart Turton Reply

    SPOOL output still appears in the installation directory
    [
    Windows 7 64-BIT
    java version “1.7.0_79″
    Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
    Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)
    ]


    C:\..>sql %SCHEMA_CREDENTIALS%

    SQLcl: Release 4.2.0.15.275.1225 RC on Tue Oct 13 11:47:43 2015

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

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SQL> SPOOL HelloWorld.log

    SQL> SELECT ‘HelloWorld’ FROM dual;

    ‘HELLOWORL
    ———-
    HelloWorld

    SQL> SPOOL OFF

    SQL> EXIT 2

    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
    With the Partitioning, OLAP and Data Mining options
    ….
    C:\..>ls -ltr C:\Applications\Oracle\sqlcl\bin\HelloWorld.log
    -rw-rw-rw- 1 user group 93 Oct 13 11:48 C:\Applications\Oracle\sqlcl\bin\HelloWorld.log

    C:\..>rm C:\Applications\Oracle\sqlcl\bin\HelloWorld.log

    • Stuart Turton

      LOCAL set to host:port/service_name
      sql %SCHEMA_CREDENTIALS%
      sql %SCHEMA_CREDENTIALS%@%LOCAL%

      SPOOL output written to sqlcl\bin

      LOCAL set to TNS_ENTRY
      sql %SCHEMA_CREDENTIALS%
      sql %SCHEMA_CREDENTIALS%@%LOCAL%

      SPOOL output written to current working directory

    • thatjeffsmith

      I don’t know what directory you’re in when you launch the tool, but you need to tell us where to put it, either explicitly in the spool command or via the CD command beforehand.


      ┌─[07:54:53]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLcl/bin$
      └─>./sql hr/oracle

      SQLcl: Release 4.2.0.15.285.0846 RC on Tue Oct 13 07:55:08 2015

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

      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

      Your user now has this many database sessions running:
      COUNT(*)
      ----------
      5

      [email protected]?? >cd /users/wvu1999/Desktop

      [email protected]?? >spool test.csv

      [email protected]?? >set sqlformat csv

      [email protected]?? >select * from hr.employees;

      "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID","REVIEW"
      198,"Donald","OConnell","DOCONNEL","650.507.9833",21-JUN-01 04.30.00.000000000 PM,"SH_CLERK",26000,,124,50,"{""comments"":[{""Comment"":""Great work this quarter, helped Dom pull in that 1,000 dollar deal."",""CommentDate"":""2015-08-26T13:18:59.733Z""},{""Comment"":""ruh-roh, accidently dropped a production table, A PRODUCTION TABLE! !"",""CommentDate"":""2015-08-26T14:58:27.637Z""},{""Comment"":""showing this to Ashley"",""CommentDate"":""2015-08-27T20:28:21.090Z""}]}"
      199,"Douglas","Grant","DGRANT","650.507.9844",13-JAN-00 12.00.00.000000000 AM,"SH_CLERK",2600,,124,50,"{""comments"":
      [{""Comment"":""drake says great job, keep it up "", ""CommentDate"":""2015-09-04T18:18:17.512Z""},
      {""Comment"":""dom says it's crap "",""CommentDate"":""2015-09-04T18:18:43.690Z""},
      {""Comment"":""I HOPE THIS WORKS"",""CommentDate"":""2015-09-04T19:06:34.325Z""}],
      ""time_off"":180}"
      200,"Jennifer","Whalen","JWHALEN","515.123.4444",17-SEP-87 12.00.00.000000000 AM,"AD_ASST",4400,,101,10,"{""comments"": []}"
      201,"Michael","Hartstein","MHARTSTE","515.123.5555",17-FEB-96 12.00.00.000000000 AM,"MK_MAN",13000,,100,20,"{""comments"": []}"
      202,"Pat","Fay","PFAY","603.123.6666",17-AUG-97 12.00.00.000000000 AM,"MK_REP",6000,,201,20,"{""comments"":[{""Comment"":""bad quarter"",""CommentDate"":""2015-08-26T13:23:26.575Z""}]}"
      203,"Susan","Mavris","SMAVRIS","515.123.7777",07-JUN-94 12.00.00.000000000 AM,"HR_REP",6500,,101,40,"{""comments"": []}"
      204,"Hermann","Baer","HBAER","515.123.8888",07-JUN-94 12.00.00.000000000 AM,"PR_REP",10000,,101,70,"{""comments"": []}"
      205,"Shelley","Higgins","SHIGGINS","515.123.8080",07-JUN-94 12.00.00.000000000 AM,"AC_MGR",12000,,101,110,"{""comments"": []}"
      206,"William","Gietz","WGIETZ","515.123.8181",07-JUN-94 12.00.00.000000000 AM,"AC_ACCOUNT",8300,,205,110,"{""comments"": []}"
      100,"Steven","King","SKING","515.123.4567",17-JUN-87 12.00.00.000000000 AM,"AD_PRES",24000,,,90,"{""comments"": []}"
      ...
      197,"Kevin","Feeney","KFEENEY","650.507.9822",23-MAY-98 12.00.00.000000000 AM,"SH_CLERK",3000,,124,50,"{""comments"": []}"

      106 rows selected

      [email protected]?? >spool off

      [email protected]?? >exit

      Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      ┌─[07:55:52]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLcl/bin$
      └─>cd /users/wvu1999/Desktop
      ┌─[07:55:59]─[wvu1999]─[MacBook-Air-Smith]:/users/wvu1999/Desktop$
      └─>ls *.csv
      test.csv

  6. Nandakumar Reply

    Hi Jeff

    I downloaded SQLCL latest version and have problem in setting up… Confused on how to use the TNSNAMES.ORA file… I have PATH for tnsnames correctly and command line SQLPLUS is working properly.. How can SQLCL couldn’t connect…?

    Appreciate your help.

    • thatjeffsmith

      run
      sql -verbose

      Does it print back a line showing where it’s looking for your tnsnames file?

    • Nandakumar

      it shows “INFO: Checking found key for ORACLE_HOME:null”

      But I have ORACLE_HOME set in environment variables, should I setup in regedit too?

    • thatjeffsmith

      easiest fix is to just set your TNS_ADMIN OS env variable to point to the right directory

      not sure what you did exactly here:
      “I have PATH for tnsnames correctly”

    • Nandakumar

      I have the TNS_ADMIN pointed to tnsnames.ora file path. Still it doesn’t work….

    • Stuart Turton

      File path or directory path?
      This works for me (TNS_ADMIN= directory path):
      C:\…>set TNS_ADMIN
      TNS_ADMIN=C:\workspace\Applications\Oracle\TNS_ADMIN

      C:\…>ls -ltr %TNS_ADMIN%\tnsnames.ora
      -rw-rw-rw- 1 user group 7563 Jul 9 15:06 C:\workspace\Applications\Oracle\TNS_ADMIN\tnsnames.ora

      C:\…>sqlplus [email protected]_DEV

      SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 13 12:04:13 2015

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

      Enter password:

      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
      With the Partitioning, OLAP and Data Mining options

      SQL>

  7. OK, so I tried downloading 4.1 as suggested, and I still am getting unnecessary Line returns at the beginning, every 13 lines and at the end.
    Is there something that needs to be turned off in preferences that I am unaware of?

    • thatjeffsmith

      are you talking about script output in SQL Developer or in SQLcl?

      every 13 lines sounds like you’re seeing affects of PAGESIZE being SET to 13

  8. Jeff, I’m one of those guys (the ones filled with dread). I’m jumping on the band wagon for SQLcl. I think this is a great improvement. Thanks for keeping SQL*Plus scripts alive!

    • thatjeffsmith

      Thanks for the support Ric! Be sure to let us know if any of your existing scripts fail to work as expected in SQLcl.

  9. Pretty new to SQL Developer, using Version 4.0.2.15.

    I’m running a previously formatted .sql script written in SQL Plus; however, I get an error that states “SQLPLUS Command Skipped: set trimspool on”, as well as “SQLPLUS Command Skipped: set trimspool off”. It seems that these commands have been deprecated in SQL Developer 4.0.2.15. Is there some other way for me to get rid of trailing spaces without having to go to NotePad++ to get rid of these spaces?

Write A Comment