OCP Oracle DBA in Montreal at Desjardins
Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
What is SQLcl ?
SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable.
The tool does not need any Oracle client to be installed, however you need a JRE 7.0 or newer.
Why should I use SQLcl ?
The tool adds interesting features which are not available in SQL*PLUS :
- ALIAS : let you define an alias for a command you frequently use, for example :
SQL> alias instance=select instance_name,host_name,status from v$instance; SQL> instance Command=instance INSTANCE_NAME HOST_NAME STATUS ---------------- ------------------------------ ------------ ORCL MYHOST OPEN
- APEX : lists the APEX applications if APEX is installed on your database :
SQL> apex WORKSPACE APPLICATION_ID APPLICATION_NAME BUILD_STATUS LAST_UPDATED_ON INTERNAL 4000 APEX - Application Builder Run and Develop 09-09-23 INTERNAL 4050 APEX - Internal Administration Run and Develop 09-09-23 INTERNAL 4155 Scheme Authentication Login Run and Develop 09-09-23 INTERNAL 4300 APEX - Data Workshop Run and Develop 09-09-23 INTERNAL 4350 APEX - Workspace Admin Run and Develop 09-09-23 INTERNAL 4400 APEX - Application Migration Run and Develop 09-09-23 INTERNAL 4411 APEX - System Messages Run and Develop 09-09-23 INTERNAL 4500 APEX - SQL Workshop Run and Develop 09-09-23 INTERNAL 4550 APEX - Login Run and Develop 09-09-23 INTERNAL 4700 APEX - New Service Signup Run and Develop 09-09-23
- BRIDGE : bridge acts like a database link inside you sqlcl client, that means the database link doesn’t have to exist in the database you’re connected to.
Syntax: BRIDGE <targetTableName> as "<jdbcURL>"(<sqlQuery>); Example: BRIDGE table1 as "dbc:oracle:thin:scott/tiger@localhost:1521/orcl"(select * from dept); In the above example table1 is created in the current connection. Table1 is defined using the metadata from the query run against the database connection defined in the statement. The JDBC URL specified has to conform to the format defined by the driver. BRIDGE new_table as "jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID"(select * from scott.emp); BRIDGE new_table as "jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE"(select * from scott.dept);
- CD : allow you to change path once connected to SQLcl, it seems that relatives path are not supported.
SQL> host echo %cd% C:\sqlcl SQL> cd .. SQL> host echo %cd% C:\sqlcl SQL> CD c:\ SQL> host echo %cd% c:\
- CTAS : Uses DBMS_METADATA to retrieve the script for the table and modifies it to create a the new table with a “select * from”.
SQL> create table test1 as select * from user_objects where object_type='INDEX';
Table TEST1 created.
SQL> ctas test1 test2
CREATE TABLE "TEST2"
( "OBJECT_NAME",
"SUBOBJECT_NAME",
"OBJECT_ID",
"DATA_OBJECT_ID",
"OBJECT_TYPE",
"CREATED",
"LAST_DDL_TIME",
"TIMESTAMP",
"STATUS",
"TEMPORARY",
"GENERATED",
"SECONDARY",
"NAMESPACE",
"EDITION_NAME",
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
as
select * from TEST1
- DDL : Get DDL of a table , you can also write the generated DDL to a file :
SQL> ddl test1
CREATE TABLE "TEST1"
( "OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> ddl test1 c:\sqlcl\test1.sql
CREATE TABLE "TEST1"
( "OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL> host dir c:\sqlcl\
Volume in drive C is OS
Volume Serial Number is 7C4E-86D4
Directory of c:\sqlcl
01/04/15 15:16 <DIR> .
01/04/15 15:16 <DIR> ..
31/03/15 15:21 <DIR> bin
31/03/15 15:21 <DIR> classes
31/03/15 15:22 <DIR> lib
01/04/15 15:16 688 test1.sql
1 File(s) 688 bytes
5 Dir(s) 221 909 069 824 bytes free
FORMAT BUFFER/FILE : format an unformatted SQL statement. BUFFER takes the statement in the buffer but you can also pass an input file and output file as parameters by using the FORMAT FILE.
SQL> FORMAT FILE c:\sqlcl\input.sql c:\sqlcl\output.sql
Here is my input file :
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window
_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(
14) := :chainid; credential_owner varchar2(30) := :credown; credential_name
varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destinat
ion_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_i
d number := :log_id; BEGIN begin
for i in 1..3600 loop
dbms_lock.sleep(1);
insert_ash_data();
end loop;
end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
here is my output file :
DECLARE
job BINARY_INTEGER := :job;
next_date TIMESTAMP WITH TIME ZONE := :myda te;
broken BOOLEAN := FALSE;
job_name VARCHAR2(30) := :job_name;
job_subname VARCHAR2(30) := :job_subname;
job_owner VARCHAR2(30) := :job_owner;
job_start TIMESTAMP WITH TIME ZONE := :job_start;
job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
window_start TIMESTAMP WITH TIME ZONE := :window _start;
window_end TIMESTAMP WITH TIME ZONE := :window_end;
chain_id VARCHAR2( 14) := :chainid;
credential_owner VARCHAR2(30) := :credown;
credential_name VARCHAR2(30) := :crednam;
destination_owner VARCHAR2(30) := :destown;
destinat ion_name VARCHAR2(30) := :destnam;
job_dest_id VARCHAR2(14) := :jdestid;
log_i d NUMBER := :log_id;
BEGIN
BEGIN
FOR i IN 1..3600
LOOP
dbms_lock.sleep(1);
insert_ash_data();
END LOOP;
END;
:mydate := next_date;
IF broken THEN
:b := 1;
ELSE
:b := 0;
END IF;
END;
- INFO : Information about a table, like :
- INFO+ : A lot of Information about the table like :
- primary key
- Indexes
- Stats
- HISTORY : history acts like the linux command to show command history but this time it displays the last 100 sql queries you ran.
SQL> history 1 alias dual='select * from dual' 2 alias dual=select * from dual 3 alias instance=select * from v$instance 4 alias instance=select instance_name,host_name,status from v$instance 5 set lines 300 6 col HOST_NAME for a30 7 apex 8 help bridge 9 help CD 10 SQLPATH 11 pwd 12 show SQLPATH 13 CD .. 14 cd .. 15 CD c:\ 16 host echo %cd% 17 CTAS help 18 select table_name from user_tables 19 ctas table table2 . . .
- LOAD : loads a CSV file into a table. The first list og the file must contain a header rows with the column names corresponding to the destination table.
LOAD test.mytable csv_file
SQLcl formatting and Output options
SQLcl offers a lot of functions to format the data displayed or output it. For example if you frequently use SQL*PLUS you should know that we always need to format columns for the output to be displayed correctly.
SQLcl has a solution for that.
SQLcl column formatting
SQL> alias instance=select * from v$instance;
SQL> instance
Command=instance
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TIME STATUS PARALLEL THREAD#
----------------- --------------------------- ------------ -------- ----------
ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS
-------- --------------- ---------- ---------------- -----------------
INSTANCE_ROLE ACTIVE_STATE BLOCKED
------------------ ------------ -------
1 mydb
myhost.dbsoft.local
11.2.0.3.0 15-03-27 OPEN NO 1
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TIME STATUS PARALLEL THREAD#
----------------- --------------------------- ------------ -------- ----------
ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS
-------- --------------- ---------- ---------------- -----------------
INSTANCE_ROLE ACTIVE_STATE BLOCKED
------------------ ------------ -------
STOPPED ALLOWED NO ACTIVE
PRIMARY_INSTANCE NORMAL NO
SQL> set sqlformat ansiconsole
SQL> instance
Command=instance
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED
1 mydb myhost.dbsoft.local 11.2.0.3.0 15-03-27 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL>
SQLcl output formats
With SQLcl you can output a query to CSV, JSON or XML:
SQL> set sqlformat csv
SQL> select 1,2,3,4,5,6 from dual;
"1","2","3","4","5","6"
1,2,3,4,5,6
SQL> set sqlformat json
SQL> select 1,2,3,4,5,6 from dual;
{"items":[
{"1":1,"2":2,"3":3,"4":4,"5":5,"6":6},{}]}
SQL> set sqlformat xml
SQL> select 1,2,3,4,5,6 from dual;
<?xml version='1.0' encoding='UTF8' ?>
<RESULTS>
<ROW>
<COLUMN NAME="1"><![CDATA[1]]></COLUMN>
<COLUMN NAME="2"><![CDATA[2]]></COLUMN>
<COLUMN NAME="3"><![CDATA[3]]></COLUMN>
<COLUMN NAME="4"><![CDATA[4]]></COLUMN>
<COLUMN NAME="5"><![CDATA[5]]></COLUMN>
<COLUMN NAME="6"><![CDATA[6]]></COLUMN>
</ROW>
</RESULTS>
SQL>
Generate insert statements for a table
SQL> create table t1 (id number,text varchar2(10));
Table T1 created.
SQL> insert into t1 values (0,'zero');
1 row inserted.
SQL> insert into t1 values (1,'one');
1 row inserted.
SQL> insert into t1 values (2,'two');
1 row inserted.
SQL> set sqlformat insert
SQL> select * from t1;
REM INSERTING into t1
SET DEFINE OFF;
Insert into "t1" (ID,TEXT) values ('0','zero');
Insert into "t1" (ID,TEXT) values ('1','one');
Insert into "t1" (ID,TEXT) values ('2','two');
Conclusion
SQLcl is a very promising tool, the version is currently SQLcl: Release 4.1.0 Beta meaning it is not a final version. I experienced some crash during my tests but it is already usable. If you are convinced, you can download it on OTN.
You can also find additional resources here :
Jeff Smith’s blog
Kris Rice’s blog
Thanks for reading.
Hello,
How can I change the separator in csv or delimited format ?
Hi Laurent,
As far as I know this is not possible yet.
Cyrille
You can use “set format loader” or “set format csv”.
You can also use “SET SQLFORMAT DELIMITED “.
For example, “set sqlformat delimited @;” will change the separator to the @ symbol.
Thank you Henry !
Ok, thank you for your reply 🙂
So I don’t really understand the difference between delimietd and csv…
Hope it will be done in a future release. I’m french and our standard separator is ;
I have another question. The CSV output is great, but I always have an empty 1st line before header line?
ELEM,”STATUS”
ABCD1234567,”RSM”
any idea on how to remove this blank line it ?
My sql script is the following:
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set trimspool on
alter session set nls_date_format = ‘dd/mm/yyyy’;
set sqlformat csv
spool test.csv
SELECT
CT.ELEM,
CT.STATUS
FROM TABLE CT
WHERE
CT.ELEM=’ABCD1234567′
;
spool off
exit
thank you.
Hi Laurent,
Sorry, I have the same behavior here, I don’t don’t know how to workaround it.
Cyrille
Ok, i will post a message in oracle forum.
For now, workaround is to use powershell for me :
Get-content my csvfile | select-object -skip 1 | out-file mynew csvfile
This remove 1st line of the file
What is the CSV file used for ? do you use it with sql loader after that ? Anyway the shell is always the way to go for custom workflows.
I have created a scheduler in powershell un order to refresh automatically our dashboards(mainly excel). 1st part of scheduler is ability to execute SQL files (via sqlplus, and now sqlcl) toget result in csv. Then I drive excel via powershell to update my dashboards.
Nb: I don t use powershell capabilities to request data, with odac, because volume could be high. I received some errors…
Nb: I have only access to the do in read only in my company
What I usually do is to load the collected data into a database and then query the data from the dashboard tool, don’t you have the possibility to do that ?
From what I understand you use the data from excel directly ?
If you want we can continue the conversation by mail, use the contact form on my website, I’ll be happy to help if I can.
Cyrille
Yes for sure, I love send you a mail during the day.