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.
& and &&
I know that looks funny, but you know what I mean, and…
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> ”
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:
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.
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.
28 Comments
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?
No?
Ok, thank you.
I imported SQL files with SQLcl in batch mode and only had ORA errors so far…
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.
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
Hey, thanks for coming to our session!
Here’s what I have for our support on LDAP connectivity.
Working like a charm, thanks.
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
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.
Cheers Jeff.
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
“
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
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
HR@orcl?? >cd /users/wvu1999/Desktop
HR@orcl?? >spool test.csv
HR@orcl?? >set sqlformat csv
HR@orcl?? >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
HR@orcl?? >spool off
HR@orcl?? >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
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.
run
sql -verbose
Does it print back a line showing where it’s looking for your tnsnames file?
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?
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”
I have the TNS_ADMIN pointed to tnsnames.ora file path. Still it doesn’t work….
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 ci@BI_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>
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?
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
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!
Thanks for the support Ric! Be sure to let us know if any of your existing scripts fail to work as expected in SQLcl.
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?
upgrade, we added support for TRIMSPOOL in 4.1
Where can I find good copy of 4.1 Download?
In the usual place.
Thanks, Jeff…I did eventually find it in the spot you referenced.
So, I’m still seeing line returns at the beginning and end of results, as well as every 13 lines. Is there something in preferences that needs to be turned off that I’m not aware of?