We have a script execution directory setting in SQL Developer…but what about our new SQLcl command line utility?
That was a question posed to me this morning.
My answer was..well, let me show you my answer.
┌─[16:31:49]─[wvu1999]─[MacBook-Air-Smith]:~/sqlclNEW$ └─>cat login.sql SET sqlprompt "_user '@' _connect_identifier >" SET sqlformat ansiconsole cd /Users/wvu1999/scripts ┌─[16:31:57]─[wvu1999]─[MacBook-Air-Smith]:~/sqlclNEW$ └─>
Now you can see why:
- My prompt is what it is
- My output is formatted nicely
- My script is able to be found
In Case This is All New to You…
SQLcl is a new take on SQL*Plus brought to you by the SQL Developer team.
CD is a new command available. From the HELP…
SET SQLFORMAT allows to you predefine your output to JSON, CSV, HTML, INSERTS..and also to set a ANSICONSOLE for pretty coloring and nicer text spacing and formatting.
This works in SQL Developer, too!
Use the CD command to change where the GUI looks for your files to execute AND where files will be written to.
By default, it’s the %user/temp% directory, as supplied by the OS.
When the script engine goes to write the CSV file, it will do so in my c:\users\jdsmith directory. And this $SQLPATH will be maintained for the duration of my SQL Developer instance….until I change it again 🙂
14 Comments
I use both SQL*Plus and SQLcl. There are some set up commands I use in my login.sql that are conflicting depending on which tool I’m starting. It seems that SQLPATH is used in both. Is there a way to ‘route’ each tool to it’s own location for login.sql (and ONLY login.sql)?
I don’t want to mess with the “Start in” locations.
Thanks Jeff.
Easiest answer is to put the sqlcl login in a place only SQLcl will see it.
Could you let me know where those ‘only SQLcl’ locations are documented?
Thank you.
Show login
That’ll show you where we look.
Thanks Jeff!
Anyone else checking this thread, look into the SQLPATH and ORACLEPATH environment variables.
ORACLEPATH relevant to SQL*Plus and SQLPATH to SQLcl.
Hello Jeff,
how do I set NLS_DATE_FORMAT for sqlcli?
my query returns NLS_DATE_FORMAT DD-MON-RR, but I’d like to use the db setting, which is YYYY-MM-DD-HH24:MI
Regards,
Vladimir
Alter session set…
Well, I don’t know quite how I convinced myself earlier that SQL*Plus would ‘respect’ the ORACLEPATH but that is not happening. Both SQL*Plus and SQLcl reference SQLPATH.
So I’m back to wondering how to get these two tools to look for different versions of LOGIN.SQL…
Like I said, put it somewhere sqlplus won’t find it, like in the sqlcl bin directory.
Or just stop using sqlplus…or write a single login script for both. Setting a sqlcl specific property won’t break sqlplus, you’ll just get an unknown cmd response… easily ignored.
I have a similar problem. In my login.sql I have
SET sqlprompt “&[email protected]&_connect_identifier> ”
Which works fine in SQL*Plus but in SQLcl the prompt doesn’t change from “SQL> ” to my combination.
If I remove the two ampersands (&) then it works fine in SQLcl but in SQL*Plus I get
[email protected]_connect_identifier>
So not the actual value in _connect_identifer but the string itself.
After some trail and error, this works in both applications:
SET sqlprompt “_user’@’_connect_identifier> ”
/Jocke
after downloading the new SQL CLI, I have realized that there is something wrong with the cd command. it doesn’t recongnize “\” it seems. Here are more details:
22:04:57 starfox:[email protected]> cd c:\mytemp\spool
CD-001: c:mytempspool is not a directory.
22:05:38 starfox:[email protected]>
I tried putting this in login.sql as well but no luck. Any workaround?
found and fixed, thanks for the report!
When I am trying to set the sqlprompt, using the below in SQLCL, it does not work. This works fine in SQLPlus.
column global_name new_value gname
set termout off
define gname=idle
select lower(user) || ‘@’ || instance_name global_name from v$instance;
select substr(‘&[email protected]&_CONNECT_IDENTIFIER’,1,48) global_name from dual;
set termout on
SET SQLPROMPT ‘&gname> ‘
After adding the last line to my login.sql file
login.sql
column global_name new_value gname
select lower(user) || ‘@’ ||
substr( global_name, 1, decode( dot,
0, length(global_name),
dot-1) ) global_name
from (select global_name, instr(global_name,’.’) dot
from global_name );
set sqlprompt ‘&gname> ‘
cd c:\users\179818.CTS
sql prompt looks good before script execution, but not after. (details below)
C:\Users\179818.CTS>sql /nolog
SQLcl: Release 4.1.0 Release Candidate on Thu Apr 30 13:22:25 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
SQL> conn rajesh/[email protected]
Connected
[email protected]> select * from dual;
D
–
X
1 rows selected
[email protected]> @printtbl “select * from dual”
PL/SQL procedure successfully completed.
DUMMY : “X”
—————–
[email protected] * from dual0G>
[email protected] * from dual0G>