There’s an update available for SQLcl on OTN, go get it now.

We’re closing bugs left & right, and we’re super close to 100% SQL*Plus support now.

As an example, we now support SET PAGESIZE 0…which comes in handy apparently if you want to spool a file w/o a leading blank line as described here.

Ta-da...
Ta-da…
Keep testing, keep sharing your feedback, and thanks!
Keep testing, keep sharing your feedback, and thanks!
Author

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

46 Comments

  1. I am facing some weird issue. I have used SQLcl 19.1 on few of our server but on some server we are getting issue. I tried with 19.2 also but same issue. Database is 12.1.0.2 on all servers. On few servers I get connected, get SQL prompt but when I try to run any command it gives java.lang.NullPointerException error and disconnects.
    What can be the reason behind this?

    SQLcl: Release 19.2.1 Production on Sun Aug 18 23:18:48 2019

    Copyright (c) 1982, 2019, Oracle. All rights reserved.

    Last Successful login time: Sun Aug 18 2019 23:18:49 -07:00

    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

    SQL>
    Aug 18, 2019 11:18:53 PM oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli log
    SEVERE: null
    java.lang.NullPointerException
    at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2843)
    at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2701)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1109)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)

    • Wondering if you ever figured this out – I am having the same issue…

    • I’d like to help but I need more information…what’s your exact configuration, versions of everything? Also, do you have a login.sql?

    • SQLcl: Release 19.4

      Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

      echo $JAVA_HOME
      /usr/java/latest

      latest -> /usr/java/jdk1.8.0_73/

      alias sqlcl
      sqlcl=/mnt/emssrv5/home/ka/sql/sqlcl/bin/sql

      I don’t believe I have a login.sql – I’m not finding it in my SQLPATH

      ———————
      >~/sql> sqlcl /nolog

      SQLcl: Release 19.4 Production on Thu Feb 20 13:13:52 2020

      Copyright (c) 1982, 2020, Oracle. All rights reserved.

      SQL>
      ———————

      hitting any key immediately results in:
      SQL> Feb 20, 2020 1:14:34 PM oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli log
      SEVERE: null
      java.lang.NullPointerException
      at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2843)
      at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2701)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1111)
      at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:494)

      Is this the info you need?

    • everything looks good here but THIS
      jdk1.8.0_73

      That’s ancient. You should be at update 150 or higher, or even use Java 11/12/13. I would try upgrading that.

    • Installed jdk 13 but still having the same issue:

      echo $JAVA_HOME
      /mnt/emssrv5/home/ka/sql/jdk-13.0.2

      Then I tried installing a jre, same issue:
      echo $JAVA_HOME
      /mnt/emssrv5/home/ka/sql/jre1.8.0_241

      I don’t want to take up more of your time, it’s not critical for me to have this, I just thought I’d try it. I appreciate the help.

  2. Younes El karama Reply

    Hi again

    Lokking at the options, I see this one:

    -noupdates Do not check update site for newer versions available.

    What if we are behind a proxy?

    what if this proxy is a windows authenticated (NTLM) one?

    I would advise the automatic update to be replaced by a -checkupdates switch that is simply opening a browser window and passing the current version to the checkupdates URL which would report whether an update us available or not.

    Thanks

  3. Younes El karama Reply

    Hi
    There is little issue when running SQLcl as a non-admin user in Windows. I get this warning when running it:

    WARNING: Could not open/create prefs root node Software\JavaSoft\Prefs at root 0x80000002. Windows RegCreateKeyEx(…) returned error code 5.

    I traced its execution using sysinternals procmon and I found it’s trying to write to “HKLM\SOFTWARE\JavaSoft\Prefs” which is denied.

    It should rather write to HKCU\SOFTWARE\JavaSoft\Prefs.

    • yup, that’s a java bug

      you can go and create that key so you don’t see the message anymore, hopefully the java team will have it sorted eventually

  4. John Thomas Reply

    DESC is not working with overloaded functions. (It’s slow too, sorry!)

    E:\>REM @E:\sqlcl\bin\sql.bat sys@zzz as sysdba

    SQLcl: Release 4.2.0.16.049.0842 RC on Wed Apr 13 11:59:00 2016

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Password? (**********?) **************
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning and Real Application Testing options

    SQL>
    SQL> desc dbms_workload_replay
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN
    START_DELAY_SECONDS NUMBER IN
    STOP_REPLAY PL/SQL BOOLEAN IN
    STOP_REPLAY VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT PL/SQL BOOLEAN IN

    E:\>sqlplus sys@zzz as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 13 12:00:35 2016

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
    With the Partitioning and Real Application Testing options

    SYS@(node:zzz):9009,435> desc dbms_workload_replay
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN DEFAULT
    STOP_REPLAY BOOLEAN IN DEFAULT
    TAKE_BEGIN_SNAPSHOT BOOLEAN IN DEFAULT
    TAKE_END_SNAPSHOT BOOLEAN IN DEFAULT
    QUERY_ONLY BOOLEAN IN DEFAULT
    FUNCTION ADD_CAPTURE RETURNS NUMBER
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    CAPTURE_DIR_NAME VARCHAR2 IN
    START_DELAY_SECONDS NUMBER IN
    STOP_REPLAY VARCHAR2 IN
    TAKE_BEGIN_SNAPSHOT VARCHAR2 IN DEFAULT
    TAKE_END_SNAPSHOT VARCHAR2 IN DEFAULT
    QUERY_ONLY VARCHAR2 IN DEFAULT

    • sorry, it’s not immediately obvious what’s not working for the overloaded functions/procedures…what is the DESC in SQLcl missing?

    • John Thomas

      Hi Jeff, there’s only one CAPTURE_DIR_NAME, START_DELAY_SECONDS etc parameter per version of the function. In this case there are two overloaded versions, but it seems DESC in SQLcl is not breaking on the function name.

      Regards, JT

  5. John Thomas Reply

    I have used a more elaborate SQL prompt than the standard for a while. But it doesn’t work in the latest release – 4.2.0.16.049.0843.

    SET sqlprompt “&USERNAME’@'(&HOST_NAME:&INSTANCE_NAME):&SID,&SERIALno> ”

    Seems likely it’s the NEW_VALUE issue above.

  6. SQLcl is also distributed with Oracle SQL Developer (OSD). As OSD was not released when SQLcl got a new release, I’m assuming that OSD contains an older version of SQLcl. How come the “sql.exe” binary in OSD has a higher version number in it than the one contained in SQLcl release?

    • the 3 digit dot number is the julian day of the calendar year, so since 413 came out in december, it’s higher than one that came out in February…not sure about other numbers w/o looking

  7. Jeff-
    Is there somewhere to look for a roadmap of what sqlplus features are planned to be supported in sqlcl and what features will never be supported.

    I’m wondering if sqlcl is ever going to behave exactly like sqlplus in regard to quitky things like not creating PL/SQL objects if the slash to run what’s in the buffer is forgotten.

  8. Andriy Dmytrenko Reply

    Hello,

    I have the following issue: first time I try repeat feature it throws and exception, second try in the same session it says “Nested REPEAT Commands will be skipped”

    $ sql user@devdb1

    SQLcl: Release 4.2.0.16.049.0842 RC on ╤З╤В ╨▒╨╡╤А. 10 15:35:25 2016

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Password? (**********?) *************
    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

    SQL> SET sqlformat ansiconsole
    SQL> SELECT systimestamp FROM dual;
    SYSTIMESTAMP
    10.03.2016 15:35:38,354232000 +02:00

    SQL> repeat 15 0.55
    схЁ. 10, 2016 3:35:42 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
    SEVERE: oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)
    java.lang.NullPointerException
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.findEndOfSQLAndPLSQL(ScriptParser.java:952)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.identifyEndOfStatement(ScriptParser.java:1092)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.next(ScriptParser.java:251)
    at oracle.dbtools.raptor.newscriptrunner.ScriptParser.parse(ScriptParser.java:134)
    at oracle.dbtools.raptor.scriptrunner.commands.RepeatSQLBufferCmd.printTop(RepeatSQLBufferCmd.java:112)
    at oracle.dbtools.raptor.scriptrunner.commands.RepeatSQLBufferCmd.handleEvent(RepeatSQLBufferCmd.java:71)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:387)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:197)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:295)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:199)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:293)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:303)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:880)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:352)

    SQL>

    SQL> SET sqlformat ansiconsole

    SQL> SELECT systimestamp FROM dual
    SYSTIMESTAMP
    10.03.2016 15:35:56,826355000 +02:00

    SQL> repeat 15 0.55
    Nested REPEAT Commands will be skipped
    SQL>

  9. I tried to resolve my connection issue several different ways but what ended up resolving the problem was changing the order of the directories in my $PATH environment variable.

    I put $ORACLE_HOME/bin first – I have the SQLcl bin directory under the instant client path and the lib directory is under the instant client path as well. I set my ORACLE_HOME to

    /Users//oracle/product/instantclient_64/11.2.0.4.0 and my path now has $ORACLE_HOME/bin:$ORACLE_HOME/lib:$ORACLE_HOME:$PATH:.

    and with that order my connectivity issues have been resolved. My apologies if you spent any time on this. What is interesting that with the previous version of the SQLcl the order wasn’t an issue. In any case I’m happy to have my access to SQLcl utility again. I love this tool and the fact that it works under OS X.

    I look forward to the official release of the product. I enjoy reading your blog too. Great hints and tips and I hope the support of OS X continues.

    • I figured out what why my connection wasn’t working. When I am in the office on the local network I would get the jdbc error. When I was out of the office connected to the VPN sqlcl would work. I figured out the problem was the Proxy Server was allowing the jdbc connection over the VPN when not on the local network but wasn’t when I attempted to connect on the local network.
      I added the subnets that the Oracle Databases are located on to the list of hosts/networks to exclude from the Proxy server and all is well now.

      My apologies for posting the previous messages and not figuring out that the Proxy server was to blame. When I saw the connection requests in the output from netted -m tcp on my machine I realized the problem right away.

  10. Jeff after I installed the latest SQLcl client I am unable to connect to any of my Oracle 12c databases. Before installing the latest update the client was working fine. I’m using a Macbook Pro running 10.11.4 and I’m getting the no ocijdbc12 in java.library.path error.

    Do you have any suggestions for I might do to fix this issue. Being able to use the command line utility in OS X was great. I tried to find an older version of the software but haven’t had any luck.

  11. Hi Jeff,
    Question about /*html*/ hint….
    Is it possible to modify the css style output, like modify the default css style to include my company’s style sheet?.

    Thanks

  12. A feature I rely on is explained on my site http://plsql.me/afiedt-buf-whats-with-that

    I set my editor and the name of the file I edit so I can have syntax highlighting and other features known to the editor based on file extension. And not overwriting code I’ve written in other sessions.

    The editor as set with
    define _editor=’vim -c colorscheme evening’
    throw the java exception

    java.io.IOException: Cannot run program “vi -c ‘colorschema eveing'” (in directory “/home/trudkin”): error=2, No such file or directory
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1041)
    at oracle.dbtools.raptor.scriptrunner.cmdline.editor.EditCommand.processExternalEditor(EditCommand.java:152)

    I can fix this with a shell script and calling that script.

    But I have not found a way to set the name of the file being edited.
    set editfile ‘somefile.sql’

    If there is a way to do this and dynamically it would go a long way to moving to SQLcl!

    • my login.sql has

      define efile=sqltmp1.SQL

      -- now we make it unique to the session and user in /tmp for system cleanup
      column file_name new_value efile
      SELECT '/tmp/' || SYS_CONTEXT ('USERENV', 'SESSION_USER' )
      || SYS_CONTEXT ('USERENV', 'SESSIONID' )
      || '.sql' file_name from dual;
      set editf '&efile'

      I now look at the help HELP COLUMN and see

      COLUMN
      ——
      (Currently only NEW_V[ALUE] variable syntax is supported)

      So I read that as NEW_VALUE works. But I think this means NEW_VALUE won’t throw an error . And a post below with similar issues and a bug filed. So that is likely it.

  13. Chuck Stoker Reply

    And still

    DEFINE _CONNECT_IDENTIFIER = “eca01tst” (CHAR)
    DEFINE _USER = “SYSTEM” (CHAR)

    SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER>”

    SYSTEM@eca0%tst>

    • that’s working for me

      >SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER>”
      HR@eca01tst>

      note i’m on the latest version on OTN, came out about 3 weeks ago

  14. Hi Jeff,

    Is there a way to run a query in sqlcl in silent mode, like sqlplus -s ….

    Thanks

    • Yes, that’s what I’m saying. I still see the query output on my screen.
      This is how I run the query
      /home/oracle/sqlcl/bin/sql -s harriague/HARRIAGUE@PERDIDO @/home/oracle/CYR/Scripts/service_level.sql 25.02.2016 25.02.2016 OSSRC3 22388 23018

      Thanks

    • what do you see?

      -S Sets silent mode which suppresses the display of
      the SQLcl banner, prompts, and echoing of
      commands.

      running sql*plus in silent mode still shows query results.

    • Commands are being echoed per my example below. It would be great if that could be fixed.

  15. Hi Jeff,

    Look. Using sqlplus:

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 29 08:32:23 2016

    Copyright (c) 1982, 2014, Oracle. All rights reserved.

    Last Successful login time: Thu Feb 25 2016 07:46:27 +01:00

    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

    SQL>

    col xxx new_value xxx
    SQL>

    select dummy as xxx from dual;
    X

    X

    1 row selected.

    SQL>

    select ‘&xxx’ from dual;


    X

    1 row selected.

    SQL>

    SQL>

    SQL>

    def xxx
    DEFINE XXX = “X” (CHAR)
    SQL>

    Variable xxx is defined with ‘X’ value. And now Using sqlc:

    SQLcl: Release 4.2.0.16.049.0842 RC on lun feb 29 08:33:32 2016

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    SQL> connect j/j@db12_pdb1
    Conectado.
    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    Introduzca un valor para xxx:
    Antiguo:select ‘&xxx’ from dual
    Nuevo:select ” from dual


    SQL> def xxx
    SP2-0135: El símbolo XXX NO ESTÁ DEFINIDO
    SQL>

    So using sqlc no variable xxx is denfined.

  16. It appears that set echo off does not work. I would love this tool to work, but I need a clean csv file strictly with the header row and data. Please let us omit the sql statement and spool off commands.

    sql -s …/…

    set pagesize 0
    set echo off
    set feedback off
    set term off
    set sqlformat csv
    spool test2.csv
    select * from env_auth_user;
    “AUTH_AUID”,”AUTH_ROLE”,”DATE_CREATED”
    “…”,”HAZMAN”,30-NOV-11
    ….
    spool off
    exit

    cat test2.csv
    SQL> select * from env_auth_user;
    “AUTH_AUID”,”AUTH_ROLE”,”DATE_CREATED”
    “…”,”HAZMAN”,30-NOV-11

    SQL> spool off

    • P.S. on the echo SQL problem – version hidden:

      SQLcl: Release 4.2.0.16.049.0842 RC

    • it works for me

      vi test_echo.sql
      set echo off
      spool echo_test.log
      select 1 from dual;
      select 2 from dual;
      spool off

      ./sql hr/oracle

      @echo_text.sql

      cat echo_test.log
      └─>more echo_test2.txt

      1
      ———-
      1

      2
      ———-
      2

      no queries…

      SET ECHO {ON | OFF}
      Controls whether or not to echo commands in a script that is executed with @, @@ or START. ON displays the commands on screen. OFF suppresses the display. ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.

    • I am looking forward to the completion of this product as it is much needed for jobs and quick access things. I am having the sql statement “echo” problem from a shell script that is executed by cron (interactively executed for this demo). The SQL echo is not a issue with SQL*Plus (it is just a pain for data format reasons). Perhaps you have a newer version than I downloaded on Thursday (2/25 – 4.2.0.16.049.0842)?

      Here is sql(cl) vs sqlplus (one has csv format but extra stuff before and after, other has whitespace even with colsep “,”):

      sql -s /nolog <<EOF
      connect —/$1
      set linesize 500;
      set echo off;
      set termout off;
      set trimspool on;
      set pagesize 0;
      set wrap off;
      set feedback off;
      set sqlformat csv;
      set sqlprompt "";

      /* Gap Report #8 */
      spool equip_summary_sc.csv
      select bldg_num "MAXIMO_BLDG_NUM",
      round( 100 * (select count(*)
      from sde_larc.maximo_equipment eq1, maximo_equipment me2
      where eq1.asset_num = me2.asset_num
      and me2.bldg_num = me1.bldg_num) /
      (select count(*)
      from maximo_equipment me2
      where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
      from maximo_equipment me1
      where bldg_num is not null
      group by bldg_num
      order by 1;
      spool off;

      exit
      EOF
      #
      sqlplus -s /nolog <<EOF
      connect —/$1
      set linesize 500;
      set echo off;
      set termout off;
      set trimspool on;
      set pagesize 0;
      set colsep ",";
      set wrap off;
      set feedback off;
      set sqlprompt "";

      /* Gap Report #8 */
      spool equip_summary_sp.csv
      select bldg_num "MAXIMO_BLDG_NUM",
      round( 100 * (select count(*)
      from sde_larc.maximo_equipment eq1, maximo_equipment me2
      where eq1.asset_num = me2.asset_num
      and me2.bldg_num = me1.bldg_num) /
      (select count(*)
      from maximo_equipment me2
      where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
      from maximo_equipment me1
      where bldg_num is not null
      group by bldg_num
      order by 1;
      spool off;

      exit
      EOF

      more equip_summary_sc.csv
      select bldg_num "MAXIMO_BLDG_NUM",
      round( 100 * (select count(*)
      from sde_larc.maximo_equipment eq1, maximo_equipment me2
      where eq1.asset_num = me2.asset_num
      and me2.bldg_num = me1.bldg_num) /
      (select count(*)
      from maximo_equipment me2
      where me2.bldg_num = me1.bldg_num)) "Filtered Completed %"
      from maximo_equipment me1
      where bldg_num is not null
      group by bldg_num
      order by 1;
      "MAXIMO_BLDG_NUM","Filtered Completed %"
      "1236",64
      "1236A",89
      "1236B",93
      "1236C",71
      "1236D",75
      "2101",96
      spool off

      more equip_summary_sp.csv
      1236 , 64
      1236A , 89
      1236B , 93
      1236C , 71
      1236D , 75
      2101 , 96

  17. Gary McLean Reply

    That’s correct as far as it goes but then after that if you try to use the variable it prompts you for the ampersand variable as below.

    SQL> select ‘&xxx’ from dual;
    Enter value for xxx

    You should get:

    SQL> select ‘&xxx’ from dual;

    old:select ‘&xxx’ from dual
    new:select ‘X’ from dual



    X
    SQL>

  18. Hi Jeff,

    It seems like I might be having problems similar to what Joaquin encountered after updating to the most recent version of SQLcl.

    Before the update (as well as in SQL*Plus), I would get:

    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    原值 1: select ‘&xxx’ from dual
    新值 1: select ‘X’ from dual



    X

    In the updated version of SQLcl (Release 4.2.0.16.049.0842 RC), however, I get the following:

    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> select ‘&xxx’ from dual;
    Enter value for xxx

    Does this have something to do with the SQLcl update or is it because of my setup?

  19. Hi Jeff,

    It seems that setting sqlplus variables with column values no longer works. Look:

    SQLcl: Release 4.2.0.16.049.0842 RC on mi├® feb 24 15:04:33 2016

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    SQL> connect j/**@db12_pdb1
    Conectado.
    SQL> def
    DEFINE _DATE = “24/02/16” (CHAR)
    DEFINE _CONNECT_IDENTIFIER = “db12_pdb1” (CHAR)
    DEFINE _USER = “J” (CHAR)
    DEFINE _PRIVILEGE = “” (CHAR)
    DEFINE _SQLPLUS_RELEASE = 0402001649842 (NUMBER)
    DEFINE _EDITOR = “notepad” (CHAR)
    DEFINE _O_VERSION = “Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options” (CHAR)
    DEFINE _O_RELEASE = 1201000000 (NUMBER)
    DEFINE _PWD = “c:\sql” (CHAR)
    SQL> col xxx new_value xxx
    SQL> select dummy as xxx from dual;

    X

    X

    SQL> def
    DEFINE _DATE = “24/02/16” (CHAR)
    DEFINE _CONNECT_IDENTIFIER = “db12_pdb1” (CHAR)
    DEFINE _USER = “J” (CHAR)
    DEFINE _PRIVILEGE = “” (CHAR)
    DEFINE _SQLPLUS_RELEASE = 0402001649842 (NUMBER)
    DEFINE _EDITOR = “notepad” (CHAR)
    DEFINE _O_VERSION = “Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options” (CHAR)
    DEFINE _O_RELEASE = 1201000000 (NUMBER)
    DEFINE _PWD = “c:\sql” (CHAR)
    SQL>

    • maybe i’m missing something

      SQL> col xxx new_value xxx
      SQL> select dummy as xxx from dual;

      X

      X

      That’s exactly what I get in SQL*Plus too. What are you expecting to see?

    • SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 09:00:45 2016

      Copyright (c) 1982, 2014, Oracle. All rights reserved.

      Enter password:
      — SECURITY ALERT
      — This system is monitored for access by unauthorized users.

      — AUDIT ALERT:
      — Access and use of this system is audited.

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options

      > define x
      SP2-0135: symbol x is UNDEFINED
      > column x_column new_value x
      > select ‘test’ x_column from dual;

      X_CO
      —-
      test

      1 row selected.

      > define x
      DEFINE X = “test” (CHAR)

      SQLcl

      SQLcl: Release 4.2.0.16.049.0842 RC on Wed Mar 02 09:02:55 2016

      Copyright (c) 1982, 2016, Oracle. All rights reserved.

      Username? (”?)
      Password? (**********?) *******
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management,
      OLAP,
      Data Mining and Real Application Testing options

      > define x
      SP2-0135: symbol X is UNDEFINED
      > column x_column new_value x
      > select ‘test’ x_column from dual;
      X_CO
      —-
      test

      1 rows selected.

      > define x
      SP2-0135: symbol X is UNDEFINED

Reply To Gary McLean Cancel Reply