Comments 7

  1. 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



    1. thatjeffsmith Post
  2. 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> ”


  3. 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?

    1. thatjeffsmith Post
  4. 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. After adding the last line to my login.sql file


    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]

    [email protected]> select * from dual;



    1 rows selected

    [email protected]> @printtbl “select * from dual”

    PL/SQL procedure successfully completed.

    DUMMY : “X”

    [email protected] * from dual0G>
    [email protected] * from dual0G>

Leave a Reply

Your email address will not be published. Required fields are marked *