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 🙂
7 Comments
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…
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>