This is a similar version of the content I presented to some internal folks here at Oracle last week. I thought you might enjoy it as well.
I’ve ‘optimized’ it for Slideshare – meaning that there are NO animated GIFs, which is sad, but it does read better this way. There’s a YouTube video inside that does have some live demo if you prefer to watch instead of read.
Let us know if you have a any questions, and be sure to get the latest update, which we just pushed out last week.
48 Comments
Jeff –
Posted this question in asktom this morning . Then I saw the active discussion here . Thought of posting it here.
I am trying to test sqlci on a ubuntu 14.0.4 .
I am able to connect to the database using EZconnect . However , when I issue sql command , I am getting the following error.
lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04
Codename: trusty
SQLcl: Release 18.4 Production on Wed Mar 27 12:28:04 2019
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Mar 27 2019 12:28:50 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select * from dual;
Exception in thread "main" java.lang.InternalError: java.lang.reflect.InvocationTargetException
at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:86)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.desktop/sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:74)
at java.desktop/sun.font.SunFontManager.getInstance(SunFontManager.java:247)
at java.desktop/sun.font.FontDesignMetrics.getMetrics(FontDesignMetrics.java:265)
at java.desktop/sun.font.FontDesignMetrics.getMetrics(FontDesignMetrics.java:251)
at java.desktop/sun.awt.SunToolkit.getFontMetrics(SunToolkit.java:582)
at java.desktop/sun.awt.HeadlessToolkit.getFontMetrics(HeadlessToolkit.java:330)
at oracle.dbtools.db.ResultSetFormatter.getFontMetricsInternal(ResultSetFormatter.java:269)
at oracle.dbtools.db.ResultSetFormatter.getFontMetrics(ResultSetFormatter.java:219)
at oracle.dbtools.db.SQLPLUSCmdFormatter.rset2sqlplus(SQLPLUSCmdFormatter.java:1317)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:328)
at oracle.dbtools.db.ResultSetFormatter.rset2sqlplus(ResultSetFormatter.java:302)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:147)
at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:70)
at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:798)
at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:709)
at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:83)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:404)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:230)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:404)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:415)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1249)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:491)
Caused by: java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:84)
... 25 more
Caused by: java.lang.NullPointerException
at java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262)
at java.desktop/sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:225)
at java.desktop/sun.awt.FontConfiguration.init(FontConfiguration.java:107)
at java.desktop/sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:719)
at java.desktop/sun.font.SunFontManager$2.run(SunFontManager.java:367)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at java.desktop/sun.font.SunFontManager.(SunFontManager.java:312)
at java.desktop/sun.awt.FcFontManager.(FcFontManager.java:35)
at java.desktop/sun.awt.X11FontManager.(X11FontManager.java:56)
... 30 more
Is there an option in DDL to create directories that does noe exist when saving files?
I’m trying to write object-DDL to a file like this:
var ddl_path = “C:/path”;
…
//loop the results
for (i=0; i < ret.length; i++) {
var row = ret[i];
sqlcl.setStmt("DDL "+row.OWNER+"."+row.OBJECT_NAME+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql");
sqlcl.run();
}
And I get path does not exist for different object types if I haven't already created the directory for all object types from a schema.
I also experience problems when I try to specify the type of the object I’m saving. It doesn’t seem to recognize this option.
From the alphabetic list of sqlcl commands:
DDL [object_name [type] [SAVE filename]]
E.g.:
sqlcl.setStmt(“DDL “+row.OWNER+”.”+row.OBJECT_NAME+” “+row.OBJECT_TYPE+” SAVE “+ddl_path+”/”+row.PROJECT+”/”+row.OWNER+”/”+row.OBJECT_TYPE+”/”+row.OBJECT_NAME+”.sql “);
If I remove the first +row.OBJECT_TYPE+ from this command it runs ok. But it doesn’t produce files for TABLE PARTITION, INDEX PARTITION etc.
What are you trying to export, a partitioned table?
I’m trying to export all objects of all object types we have in the database, for each schema, and store these in a directory structure. This is then pushed to git:
sid
schema-1
dimension
function
index
materialized view
package
procedure
sequence
table
view
schema-2… etc
See this
Ah, thank you very much!
No – thank YOU!
I got the same error at an idle sqlcl prompt:
SQL> Exception in thread “AWT-EventQueue-0” java.lang.ClassCastException: sun.java2d.HeadlessGraphicsEnvironment cannot be cast to sun.awt.Win32GraphicsEnvironment
at sun.awt.windows.WToolkit$3.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Hi Jeff
I observed a very small issue with SQLCL. when we are keeping it inactive for long time I see below error
SQL> Exception in thread “AWT-EventQueue-0” java.lang.ClassCastException: sun.java2d.HeadlessGraphicsEnvironment cannot be cast to sun.awt.Win32GraphicsEnvironment
at sun.awt.windows.WToolkit$5.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$300(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
And its just error but i still see command line is connected to DB.
Its not a big one, but just anyway reporting.
Regards
Teja
hi, is there an equivalent of : SQLPLUS /nolog
thx
Yes
Can you also post your slide deck to blog? We can’t access slideshare.
Thanks!
no, why not?
This is the best piece of software 🙂
To make it even better please add cygwin support for the rest of us. I just don not like to patch 3-rd party sw everytime I download new version.
Modify “sql” shell script (quick and dirty solution I use):
—-
# cygwin classpath patch
cygwin=false
case “`uname`” in
CYGWIN*) cygwin=true;;
esac
if $cygwin; then
CPLIST=$(cygpath -pw “$CPLIST”)
fi
—-
Done, you should see this in the update that went out late yesterday afternoon.
Are there any open-source usage examples available that we could have a look at?
Speaking of open-source, are any parts of SQLcl open source?
open source examples of what?
SQLcl is NOT open source
CSV is great but tab delimited is better. We have data with embedded commas, and quotes etc thus we like tab delimited. I hope you will implement tab delimited instead of just csv.
You can have any delimiter you want, with text quoted or not. Just use SQL Developer and the grid > export feature. Set to ‘delimited’ and set your options.
Great, it worked for me after setting the environment variable. Thanks a lot Jeff!
Jeff:
I’m not able to connect to the database using the connect string which is configured in LDAP. Is LDAP supported in SQLCL?
Thanks,
Senthil
Yes
set LDAPCON jdbc:oracle:thin:@ldap://XYZ.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom
$export LDAPCON=jdbc:oracle:thin:@ldap://XYZ.us.oracle.com:389/#ENTRY#,cn=OracleContext,dc=ldapcdc,dc=lcom
$sql /nolog
SQLcl: Release 4.1.0 Beta on Fri Feb 20 10:37:02 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
SQL> connect barry/oracle@orclservice_test(Emily’s Desktop)
Connected
SQL>
I just set this up today. I’m excited to explore its json format feature. Two things:
1) I noticed that the json output does not currently escape the ‘”‘ embedded in data.
2) I haven’t tried yet, but how would I go about customizing the sql prompt?
for example, put this in your login.sql script
SET sqlprompt “_user ‘@’ _connect_identifier >”
Wow, that was fast! Thanks for replying.
I have that in my glogin.sql for both my 11g client and my 12c client. But sqlcl still comes up just SQL>. I tried copying glogin.sql to sqlcl, sqlcl/bin, and sqlcl/lib, just in case it was that easy. But it didn’t change anything. Then I took your advice literally. I created login.sql in sqlcl/bin, and voila! Thanks again.
Thanks for answering Jeff,
If that import command runs over jdbc that will be fine, even dirty : )
I hope that solution may simplify cases of exp/imp over inconsistent versions of Oracle (9 to 11). Execution speed is not the matter.
Thank you again for your answers. Your blog is definitely in my bookmarks.
you bet it’s over JDBC 🙂
I’ll blog this tomorrow
Hi Jeff,
Sqlcl looks great, i think i’ll have some use cases with it.
I worked on a similar personnal project (that i use at work too) that aimed to deploy easily sql operations over shell scripts WITHOUT having to install Oracle Client.
So thank you and your team for that !
What about the same approach to exp/imp commands ?
imp – we build a quick and dirty IMPORT command that will read a local CSV file and import it to a table for you
exp – you can run
set sqlformat csv
spool file.csv
select * from hr.employees;
spool off
If you need REAL imp and exp support – use DataPump or SQL*Loader. They are multi-threaded and designed to support large amounts of data for production purposes.
Quick and dirty, then we got ya taken care of.
I’ve been working on an Oracle command-line tool, some of SQLcl ideas seem similar. Unfortunately I haven’t got much time to spend on it last months… But if anyone wanted to have a look, here it is, enjoy: https://github.com/kmehkeri/oraora
Looks like we got a bit farther…let me know what you think of ours.
Of course. Mine is just a small hobby project 🙂 SQLcl looks nice, at least from the presentation, I’m going to take a closer look into it.
I use Cygwin and ammore fimiliar with Unix/Linux style than DOS command. This does not seem to be usable in unix/linux?
Would be awsome in portred to Unix/Linux…..
Port? It’s all java, so it will run wherever java 7 will run. It works just fine on Oracle Linux.
Latest sqlcl is throwing exception when I use ctas command.
If the source table is prefixed with schema name.. then I get following
exception.
Example: ctas schema.tablename dest_tablename
Mar 20, 2015 2:57:04 PM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: oracle.dbtools.raptor.newscriptrunner.commands.CTAS.handleEvent(CTAS.java:72)
java.lang.NullPointerException
at oracle.dbtools.raptor.newscriptrunner.commands.CTAS.handleEvent(CTAS.java:72)
at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:369)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:195)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:267)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:180)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:208)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:257)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:701)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:320)
I’d like to deploy SQLcl on Linux host where I do not have rights to upgrade JAVA version. How to create a package of SQLcl + JAVA7or8 and make the deployment of SQLcl totally independent from the JAVA version on server?
Thank you,
vr
Is java 7 a strict requirement ?
Okay, I love the ddl command (how would you invoke ddl for a schema or tablespace….I have historically used dbms_metadata.get_ddl) and the information command is not too shabby either.
I have been looking up the various things with the help command too. Where should question be posted as a matter of practice?
that’s what we’re using under the covers, the DMBS_METADATA package.
to extend it to other objects, which I WANT, we’d have to either parse to guess the object type, or search the DD for it. One is a bit unreliable, the other is expensive.
But I want to have and eat my cake. So keep your fingers crossed.
oh, but to answer your other question, the Forums is the best place.
A good name for the tool would be SQL*PlusPlus: sql++ or sqlpp
🙂
On the second slide you say the “preceding” can’t be used etc – I think it should be the “following”, shoudln’t it? 🙂
I especially like the DBA slide at the end, though…
Jeff, Great idea. I use SQL*Plus with Gvim (and SQL Developer with Gvim) and they both excel at different tasks. This kind of reminds me of how Microsoft released Powershell to provide more up to date technology offering to people whom use command environments. I will kick the tires.
Thanks!
yeah, except we ALWAYS had a command line interface. now we’re just trying to make that even better. let us know what you think! it’s still beta, and we have tons of bugs to fix yet.
I am kicking the tires and giving it a test drive. So far, so good.
1. SQLcl is cool.
2. SDSQL was a much better name for it.
Thanks John. I wouldn’t be surprised if the name changes, again.