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.
46 Comments
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.
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
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
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?
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
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.
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
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.
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>
yeah, repeat got borked in that update – it will get fixed
Okay, thanks a lot.
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.
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.
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
Sorry, no. But you could use the script command maybe to edit the file to do the job…
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!
do you mean
set editfile ?
if you do
show editfile, it lists afiedt.buf
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.
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
Hi Jeff,
Is there a way to run a query in sqlcl in silent mode, like sqlplus -s ….
Thanks
are you saying that doesn’t work?
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.
yeah, i see the sql statement included in the spool, logged a bug – thanks!
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.
bug has been logged, thanks for the report(s)
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
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>
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?
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