SQLcl :sql Results with the SET SQLFORMAT
Oracle SQLcl :sql Results with the SET SQLFORMAT
sqlcl
sqlcl available from [Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production;
# which java
/bin/java
to install JAVA
yum install -y java-1.8.0-openjdk
Trying other mirror.
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.332.b09-1.el7_9 will be installed
--> Processing Dependency: java-1.8.0-openjdk-headless(x86-64) = 1:1.8.0.332.b09-1.el7_9 for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjava.so(SUNWprivate_1.1)(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjvm.so(SUNWprivate_1.1)(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: xorg-x11-fonts-Type1 for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libgif.so.4()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjava.so()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: libjvm.so()(64bit) for package: 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
--> Running transaction check
---> Package giflib.x86_64 0:4.1.6-9.el7 will be installed
---> Package java-1.8.0-openjdk-headless.x86_64 1:1.8.0.332.b09-1.el7_9 will be installed
--> Processing Dependency: copy-jdk-configs >= 3.3 for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: tzdata-java >= 2021e for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: jpackage-utils for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: lksctp-tools(x86-64) for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
--> Processing Dependency: pcsc-lite-libs(x86-64) for package: 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
---> Package xorg-x11-fonts-Type1.noarch 0:7.5-9.el7 will be installed
--> Processing Dependency: ttmkfdir for package: xorg-x11-fonts-Type1-7.5-9.el7.noarch
--> Processing Dependency: ttmkfdir for package: xorg-x11-fonts-Type1-7.5-9.el7.noarch
--> Running transaction check
---> Package copy-jdk-configs.noarch 0:3.3-10.el7_5 will be installed
---> Package javapackages-tools.noarch 0:3.4.1-11.el7 will be installed
--> Processing Dependency: python-javapackages = 3.4.1-11.el7 for package: javapackages-tools-3.4.1-11.el7.noarch
--> Processing Dependency: libxslt for package: javapackages-tools-3.4.1-11.el7.noarch
---> Package lksctp-tools.x86_64 0:1.0.17-2.el7 will be installed
---> Package pcsc-lite-libs.x86_64 0:1.8.8-8.el7 will be installed
---> Package ttmkfdir.x86_64 0:3.0.9-42.el7 will be installed
---> Package tzdata-java.noarch 0:2022a-1.el7 will be installed
--> Running transaction check
---> Package libxslt.x86_64 0:1.1.28-6.0.1.el7 will be installed
---> Package python-javapackages.noarch 0:3.4.1-11.el7 will be installed
--> Processing Dependency: python-lxml for package: python-javapackages-3.4.1-11.el7.noarch
--> Running transaction check
---> Package python-lxml.x86_64 0:3.2.1-4.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================================================
Installing:
java-1.8.0-openjdk x86_64 1:1.8.0.332.b09-1.el7_9 ol7_x86_64_latest 314 k
Installing for dependencies:
copy-jdk-configs noarch 3.3-10.el7_5 ol7_x86_64_latest 21 k
giflib x86_64 4.1.6-9.el7 ol7_x86_64_latest 39 k
java-1.8.0-openjdk-headless x86_64 1:1.8.0.332.b09-1.el7_9 ol7_x86_64_latest 33 M
javapackages-tools noarch 3.4.1-11.el7 ol7_x86_64_latest 72 k
libxslt x86_64 1.1.28-6.0.1.el7 ol7_x86_64_latest 241 k
lksctp-tools x86_64 1.0.17-2.el7 ol7_x86_64_latest 87 k
pcsc-lite-libs x86_64 1.8.8-8.el7 ol7_x86_64_latest 34 k
python-javapackages noarch 3.4.1-11.el7 ol7_x86_64_latest 31 k
python-lxml x86_64 3.2.1-4.el7 ol7_x86_64_latest 758 k
ttmkfdir x86_64 3.0.9-42.el7 ol7_x86_64_latest 47 k
tzdata-java noarch 2022a-1.el7 ol7_x86_64_latest 190 k
xorg-x11-fonts-Type1 noarch 7.5-9.el7 ol7_x86_64_latest 521 k
Transaction Summary
================================================================================================================================================================================================================
Install 1 Package (+12 Dependent packages)
Total download size: 35 M
Installed size: 116 M
Downloading packages:
(1/13): copy-jdk-configs-3.3-10.el7_5.noarch.rpm | 21 kB 00:00:00
(2/13): giflib-4.1.6-9.el7.x86_64.rpm | 39 kB 00:00:00
(3/13): java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64.rpm | 314 kB 00:00:00
(4/13): java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64.rpm | 33 MB 00:00:00
(5/13): javapackages-tools-3.4.1-11.el7.noarch.rpm | 72 kB 00:00:00
(6/13): libxslt-1.1.28-6.0.1.el7.x86_64.rpm | 241 kB 00:00:00
(7/13): lksctp-tools-1.0.17-2.el7.x86_64.rpm | 87 kB 00:00:00
(8/13): pcsc-lite-libs-1.8.8-8.el7.x86_64.rpm | 34 kB 00:00:00
(9/13): python-javapackages-3.4.1-11.el7.noarch.rpm | 31 kB 00:00:00
(10/13): python-lxml-3.2.1-4.el7.x86_64.rpm | 758 kB 00:00:00
(11/13): ttmkfdir-3.0.9-42.el7.x86_64.rpm | 47 kB 00:00:00
(12/13): tzdata-java-2022a-1.el7.noarch.rpm | 190 kB 00:00:00
(13/13): xorg-x11-fonts-Type1-7.5-9.el7.noarch.rpm | 521 kB 00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 29 MB/s | 35 MB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libxslt-1.1.28-6.0.1.el7.x86_64 1/13
Installing : python-lxml-3.2.1-4.el7.x86_64 2/13
Installing : python-javapackages-3.4.1-11.el7.noarch 3/13
Installing : javapackages-tools-3.4.1-11.el7.noarch 4/13
Installing : ttmkfdir-3.0.9-42.el7.x86_64 5/13
Installing : xorg-x11-fonts-Type1-7.5-9.el7.noarch 6/13
Installing : lksctp-tools-1.0.17-2.el7.x86_64 7/13
Installing : pcsc-lite-libs-1.8.8-8.el7.x86_64 8/13
Installing : copy-jdk-configs-3.3-10.el7_5.noarch 9/13
Installing : giflib-4.1.6-9.el7.x86_64 10/13
Installing : tzdata-java-2022a-1.el7.noarch 11/13
Installing : 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64 12/13
Installing : 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64 13/13
Verifying : tzdata-java-2022a-1.el7.noarch 1/13
Verifying : giflib-4.1.6-9.el7.x86_64 2/13
Verifying : xorg-x11-fonts-Type1-7.5-9.el7.noarch 3/13
Verifying : copy-jdk-configs-3.3-10.el7_5.noarch 4/13
Verifying : python-javapackages-3.4.1-11.el7.noarch 5/13
Verifying : python-lxml-3.2.1-4.el7.x86_64 6/13
Verifying : 1:java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64 7/13
Verifying : libxslt-1.1.28-6.0.1.el7.x86_64 8/13
Verifying : pcsc-lite-libs-1.8.8-8.el7.x86_64 9/13
Verifying : 1:java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64 10/13
Verifying : javapackages-tools-3.4.1-11.el7.noarch 11/13
Verifying : lksctp-tools-1.0.17-2.el7.x86_64 12/13
Verifying : ttmkfdir-3.0.9-42.el7.x86_64 13/13
Installed:
java-1.8.0-openjdk.x86_64 1:1.8.0.332.b09-1.el7_9
Dependency Installed:
copy-jdk-configs.noarch 0:3.3-10.el7_5 giflib.x86_64 0:4.1.6-9.el7 java-1.8.0-openjdk-headless.x86_64 1:1.8.0.332.b09-1.el7_9 javapackages-tools.noarch 0:3.4.1-11.el7
libxslt.x86_64 0:1.1.28-6.0.1.el7 lksctp-tools.x86_64 0:1.0.17-2.el7 pcsc-lite-libs.x86_64 0:1.8.8-8.el7 python-javapackages.noarch 0:3.4.1-11.el7
python-lxml.x86_64 0:3.2.1-4.el7 ttmkfdir.x86_64 0:3.0.9-42.el7 tzdata-java.noarch 0:2022a-1.el7 xorg-x11-fonts-Type1.noarch 0:7.5-9.el7
Complete!
JAVA installed !!!!
]# which java
/bin/java
if required !!
export JAVA_HOME=/bin/java
=======================================================================================================================================================================
su - oracle
Last login: Wed Jul 13 05:00:56 EDT 2022 on pts/0
which sql
/u01/app/oracle/product/12.2.0/dbhome_1/bin/sql
[oracle@irac02 ~]$ sql /nolog
SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:33:33 2022 <<<<<<< SQLcl
Copyright (c) 1982, 2022, Oracle. All rights reserved.
idle sqlplus>
in tnsnames.ora
pdb9=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ibrac02.xxxxxxxx)(PORT=1523))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=pdb9)))
on unix prompt
sql /nolog
SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:18:45 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
idle sqlplus> conn test/vihaan123@pdb9
Connected.
or
Via EZ Connect
sql /nolog
SQLcl: Release 12.2.0.1.0 RC on Wed Jul 13 05:23:45 2022
Copyright (c) 1982, 2022, Oracle. All rights reserved.
idle sqlplus>
idle sqlplus> CONN test/vihaan123@//irac02.XXXXX:1523/pdb9
Connected.
primary:test@vihcdbd8-vihcdbd8 sqlplus> SET SQLFORMAT insert;
primary:test@vihcdbd8-vihcdbd8 sqlplus> SELECT * FROM emp;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_timestamp('17-11-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-05-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-06-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-04-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-12-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-02-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-02-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-09-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-09-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-12-11 00:00','DD-MON-RR HH.MI.SSXFF AM'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-12-12 00:00','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-01-13 00:00','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-04-14 00:00','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7655,'ADAMS','CLERK',7788,to_timestamp('13-07-14 00:00','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20);
14 rows selected.
====
Examples
SELECT /*ansiconsole*/ * FROM emp where rownum <3 csv="" delimited="" emp="" fixed="" from="" html="" insert="" json-formatted="" json="" loader="" primary:test="" rownum="" select="" sqlplus="" text="" vihcdbd8-vihcdbd8="" where="" xml=""> SELECT /*csv*/ * FROM emp where rownum <3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""> SELECT /*loader*/ * FROM emp where rownum <3 00:00:00="" 7698="" 7839="" no="" primary:test="" rows="" selected="" sqlplus="" vihcdbd8-vihcdbd8=""> SELECT /*delimited*/ * FROM emp where rownum <3 00:00="" 7698="" 7839="" primary:test="" sqlplus="" vihcdbd8-vihcdbd8=""> SELECT /*insert*/ * FROM emp where rownum <3 00:00="" am="" define="" emp="" hh.mi.ssxff="" insert="" inserting="" into="" null="" off="" primary:test="" rem="" set="" sqlplus="" to_timestamp="" values="" vihcdbd8-vihcdbd8=""> SELECT /*json*/ * FROM emp where rownum <3 00:00="" columns="" deptno="" empno="" ename="" hiredate="" items="" job="" mgr="" name="" primary:test="" results="" sal="" sqlplus="" type="" vihcdbd8-vihcdbd8=""> SELECT /*json-formatted*/ * FROM emp where rownum <3 --------------="" ----------="" ---------="" 00:00="" 01-05-11="" 10="" 17-11-11="" 2850="" 30="" 5000="" 7698="" 7839="" blake="" comm="" deptno="" empno="" ename="" hiredate="" job="" king="" manager="" mgr="" president="" primary:test="" sal="" sqlplus="" vihcdbd8-vihcdbd8=""> SELECT /*xml*/ * FROM emp where rownum <3 encoding="UTF8" version="1.0" xml="">
<![CDATA[7839]]>
<![CDATA[KING]]>
<![CDATA[PRESIDENT]]>
<![CDATA[]]>
<![CDATA[17-11-11 00:00]]>
<![CDATA[5000]]>
<![CDATA[]]>
<![CDATA[10]]>
<![CDATA[7698]]>
<![CDATA[BLAKE]]>
<![CDATA[MANAGER]]>
<![CDATA[7839]]>
<![CDATA[01-05-11 00:00]]>
<![CDATA[2850]]>
<![CDATA[]]>
<![CDATA[30]]>
primary:test@vihcdbd8-vihcdbd8 sqlplus> SELECT /*html*/ * FROM emp where rownum <3 html="">
Responsive Table
| EMPNO |
ENAME |
JOB |
MGR |
HIREDATE |
SAL |
COMM |
DEPTNO |
| 7839 |
KING |
PRESIDENT |
|
17-11-11 00:00 |
5000 |
|
10 |
| 7698 |
BLAKE |
MANAGER |
7839 |
01-05-11 00:00 |
2850 |
|
30 |
primary:test@vihcdbd8-vihcdbd8 sqlplus> SELECT /*text*/ * FROM emp where rownum
