Mostly Making oracle easy, mostly:
  • CI/CD with Oracle Database and APEX
  • Four Options for working with ORDS & PLSQL REFCURSORs
  • Creating Users & Granting Storage Quota using Database Actions
  • Building REST APIs, with beer!
  • Downloading/Streaming Content with ORDS (File Downloads!)
  • Creating REST APIs for AWR with ORDS and SQL
  • Building a CRUD REST API for a table with a BLOB {JSON}
  • Launching SQL Developer Web from your Oracle Autonomous Cloud Service Console
Search for:
  • Twitter
  • YouTube
  • LinkedIn
ThatJeffSmith ThatJeffSmith
  • SQL Developer
  • SQL Developer Web
  • ORDS
  • Data Modeling
  • Ask A Question
  • About
In SQL Developer

Setting Default Script Execution Directory in SQLcl

April 29, 2015 2 Mins Read
Share
Share on Facebook Share on Twitter LinkedIn Email

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.

Here's where I'm at, directory-wise as I get set to launch SQLcl
Here’s where I’m at, directory-wise as I get set to launch SQLcl
I'm in, and I go to execute my employees.sql script
I’m in, and I go to execute my employees.sql script
But wait, where is it getting the employees.sql script from exactly?
But wait, where is it getting the employees.sql script from exactly?
Oh yeah...what's in there?
Oh yeah…what’s in there?
┌─[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…

Note we also support the /nolog option :)
Note we also support the /nolog option 🙂

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.

note the CD command at the top

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 🙂

cddirectoryloginscriptsqlclsqlplus
0
thatjeffsmith
Author thatjeffsmith

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

  • Website
Prev Post

Install Oracle REST Data Services w/SQL Developer

April 28, 2015 9 Mins Read
Next Post

Defining a RESTful Service in SQL Developer with :BINDS

May 1, 2015 2 Mins Read

Related Posts

CI/CD with Oracle Database and APEX

April 8, 2021

Creating Users & Granting Storage Quota using Database Actions

March 26, 2021

Creating REST APIs for AWR with ORDS and SQL

March 16, 2021

Launching SQL Developer Web from your Oracle Autonomous Cloud Service Console

February 22, 2021

Debugging your PL/SQL in Oracle Autonomous Databases

February 18, 2021

Oracle SQL Developer Web, JSON Searching and Sorting

February 16, 2021

7 Comments

  1. Avatar
    vladimir 4 years ago Reply

    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

    • thatjeffsmith
      thatjeffsmith Post Author 4 years ago

      Alter session set…

  2. Avatar
    Jocke T 5 years ago Reply

    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

  3. Avatar
    Fayyaz 5 years ago Reply

    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?

    • thatjeffsmith
      thatjeffsmith Post Author 5 years ago

      found and fixed, thanks for the report!

  4. Avatar
    Ketan Kothari 6 years ago Reply

    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> ‘

  5. Avatar
    Rajesh 6 years ago Reply

    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>

Write A Comment Cancel Reply

  • Search 900+ posts
  • What it is I do here
    About Me

    I'm a product manager at Oracle - I want YOU to be happy using Database Development tools.
    I publish weekly - tips and tricks for Oracle SQL Developer, SQLcl, SQL Developer Data Modeler, and Oracle REST Data Services.

    Twitter YouTube LinkedIn
  • Your Favorite Posts
    • How to Import from Excel to Oracle with SQL Developer
    • Keyboard Shortcuts in Oracle SQL Developer
    • Top 10 Preferences to Tweak when using SQL Developer
    • Enabling DBMS_OUTPUT by default in SQL Developer
    • Formatting Query Results to CSV in Oracle SQL Developer
    • Resetting Your Oracle User Password with SQL Developer
    • How To: Generate an ERD for Selected Tables in SQL Developer
    • Migrating Oracle SQL Developer Connections (with Passwords!)
  • Subscribe - Posts Emailed To You!

  • Mostly Making Oracle Easy, Mostly

    Privacy Policy 

  • Twitter
  • YouTube
  • LinkedIn
  • Download SQL Developer
  • Developer Tools Message Boards

© Jeff Smith and ThatJeffSmith, 2019

Top
ThatJeffSmith