SQLcl: MCP Server & SQL Prompt

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Wrong standart format

user635344Aug 27 2018 — edited Aug 30 2018

I am getting wrong format for sql likes:

select employee_id,first_name,last_name from employees;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven         King
        101 Neena         Kochhar
        102 Lex              De Haan
        103 Alexander   Hunold
        104 Bruce          Ernst
        105 David          Austin
        106 Valli             Pataballa
        107 Diana          Lorentz
        108 Nancy         Greenberg

Second varchar2 column is not adjusted to column header (Last Name).

Any ideas?

SQL> desc employees

Name Null? Type

-------------- -------- ------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

All sets are standard

Thank you

Glen Conway

What version of SQLcl are you running? With the latest 18.2 version, this is what I see:

Gaz in Oz

What sqlcl version are you running?

What OS are you running sqlcl on?

What terminal are you running sqlcl in?

What settings are you using in your terminal session?

What font are you using?

What version of Oracle database are you connecting to?

...

user635344

1.sqlformat command works but I need to format column by myself

2. Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0

3. Linux4.1.12-61.1.18.el7uek.x86_64 = Oracle Linux Server release 7.3

4, putty 0.67

5.Courier New 14p

8. Oracle RDBMS 12.2.0.1

9. JDK 8.181

10. I do not see any issue in SQLplus

user635344

Gaz in Oz

Thanks for posting the extra info.

In your screenshot, it appears you "set sqlformat" to something, ansiconsole?

What else do you set?

Post output from "show all+"

What changes have you made to the putty settings?

...as for your screenshot, looks like an issue with the view v$version in the first part:

SQL> version

Oracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0

SQL> set sqlformat ansiconsole

SQL> select * from v$version;

BANNER CON_ID

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

SQL> set sqlformat

SQL Format Cleared

SQL> /

BANNER CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

SQL>

...and in sqlplus.

$ echo -e "set lines 91\n select * from v\$version;" | sqlplus -s gaz/gaz

BANNER CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

user635344

No extra parameters

I can see the same result in putty and VirtualBox console

output in sqlplus works fine

SQL> show all+
Show All:
========================================
_prelim OFF
appinfo is OFF and set to "SQLcl"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
BTitle OFF
SQL*Plus mode: OFF
Clear Screen mode: top
oracle.net.tns_admin=null
javax.net.ssl.trustStore=null
javax.net.ssl.trustStorePassword=null
javax.net.ssl.keyStore=null
javax.net.ssl.keyStorePassword=null
oracle.net.ssl_server_dn_match=null
oracle.net.ssl_version=null
colinvisible OFF
colsep " "
concat "." (hex 2e)
copycommit 0
define "&" (hex 26)
show desc is obsolete.
echo OFF
editfile "afiedt.buf"

embedded OFF
Encoding:UTF-8
errorlogging is OFF
escape OFF
exitcommit ON
FEEDBACK ON for 6 or more rows
heading ON
headsep "|" (hex 7c)
HISTORY
enabled
blacklist: show,history,connect,set
Do not show failed statements
linesize 150
long 80
longchunksize 80
newpage 1
null /*notset*/
numformat ""
numwidth 10
pagesize 38
PAUSE is OFF
release 1202000100
scan ON
secureliterals DEFAULT
serveroutput OFF
showmode OFF
space " "
spool OFF
sqlcode 0
sqlprompt "SQL> "
suffix "sql"
termout ON
time OFF
timing OFF
TNS Lookup locations
--------------------

USER Home dir
/home/oracle
ORACLE_HOME
/u00/app/product/12.2/network/admin

Location used:
-------------
/u00/app/product/12.2/network/admin

Available TNS Entries
---------------------
DEMO1
DEMO2
LISTENER_MOS2
LISTENER_MOS4
MOS2
MOS2PDB1
MOS3PDB1
MOS4
PAPX1
PDB2
PDB3
PDB4
PDB5
PGG1
PGG1
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statementUSER is "SYS"
verify ON
Oracle SQLDeveloper Command-Line (SQLcl) version: 18.2.0.0
No Wallet location defined

wrap : lines will be wrapped

Show All+ (Single Line):
========================================
_
12.1_longident mode: OFF
_restrict 0
Auto Reload NLS:null
blockterminator "." (hex 2e)
buffer SQL
CD : Default

SQLcl unsupported SQL*Plus command: show CLOSECURSOR COMMANDLINE CON_ID CON_NAME EDITION ENCODING ENCODINGS ERR JAVA LDAPCON LOAD NET NOVERWRITE SECUREDCOL SPPARAMETER SQLDEV SQLDEV2 SQLFORMAT SQLPATH SQLPLUSCOMPAT SYSTEMOUT TABLE TOPLEVEL UFI URL XMLFORMAT XQUERY
commandline true
CON_ID
------------------------------
3
CON_NAME
------------------------------
PDB1
EDITION
--------
ORA$BASE
Encoding:UTF-8
current Encoding:UTF-8
List of available encodings:
Big5
Big5-HKSCS
CESU-8
EUC-JP
EUC-KR
GB18030
GB2312
GBK
IBM-Thai
IBM00858
IBM01140
IBM01141
IBM01142
IBM01143
IBM01144
IBM01145
IBM01146
IBM01147
IBM01148
IBM01149
IBM037
IBM1026
IBM1047
IBM273
IBM277
IBM278
IBM280
IBM284
IBM285
IBM290
IBM297
IBM420
IBM424
IBM437
IBM500
IBM775
IBM850
IBM852
IBM855
IBM857
IBM860
IBM861
IBM862
IBM863
IBM864
IBM865
IBM866
IBM868
IBM869
IBM870
IBM871
IBM918
ISO-2022-CN
ISO-2022-JP
ISO-2022-JP-2
ISO-2022-KR
ISO-8859-1
ISO-8859-13
ISO-8859-15
ISO-8859-2
ISO-8859-3
ISO-8859-4
ISO-8859-5
ISO-8859-6
ISO-8859-7
ISO-8859-8
ISO-8859-9
JIS_X0201
JIS_X0212-1990
KOI8-R
KOI8-U
Shift_JIS
TIS-620
US-ASCII
UTF-16
UTF-16BE
UTF-16LE
UTF-32
UTF-32BE
UTF-32LE
UTF-8
UTF8
windows-1250
windows-1251
windows-1252
windows-1253
windows-1254
windows-1255
windows-1256
windows-1257
windows-1258
windows-31j
x-Big5-HKSCS-2001
x-Big5-Solaris
x-COMPOUND_TEXT
x-euc-jp-linux
x-EUC-TW
x-eucJP-Open
x-IBM1006
x-IBM1025
x-IBM1046
x-IBM1097
x-IBM1098
x-IBM1112
x-IBM1122
x-IBM1123
x-IBM1124
x-IBM1166
x-IBM1364
x-IBM1381
x-IBM1383
x-IBM300
x-IBM33722
x-IBM737
x-IBM833
x-IBM834
x-IBM856
x-IBM874
x-IBM875
x-IBM921
x-IBM922
x-IBM930
x-IBM933
x-IBM935
x-IBM937
x-IBM939
x-IBM942
x-IBM942C
x-IBM943
x-IBM943C
x-IBM948
x-IBM949
x-IBM949C
x-IBM950
x-IBM964
x-IBM970
x-ISCII91
x-ISO-2022-CN-CNS
x-ISO-2022-CN-GB
x-iso-8859-11
x-JIS0208
x-JISAutoDetect
x-Johab
x-MacArabic
x-MacCentralEurope
x-MacCroatian
x-MacCyrillic
x-MacDingbat
x-MacGreek
x-MacHebrew
x-MacIceland
x-MacRoman
x-MacRomania
x-MacSymbol
x-MacThai
x-MacTurkish
x-MacUkraine
x-MS932_0213
x-MS950-HKSCS
x-MS950-HKSCS-XP
x-mswin-936
x-PCK
x-SJIS_0213
x-UTF-16LE-BOM
X-UTF-32BE-BOM
X-UTF-32LE-BOM
x-windows-50220
x-windows-50221
x-windows-874
x-windows-949
x-windows-950
x-windows-iso2022jp
Java Detail
-----------
java.home= /u00/app/product/jdk181/jre
java.vendor= Oracle Corporation
java.vendor.url= http://java.oracle.com/
java.version= 1.8.0_181
------------------------------------------------------------------------------------------------------------------------------------------------------
os.arch= amd64
os.name= Linux
os.version= 4.1.12-61.1.18.el7uek.x86_64
path.separator= :
file.separator= /
line.separator=

user.dir= /home/oracle
user.home= /home/oracle
user.name= oracle
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_HOME=/home/oracle/sqlcl
------------------------------------------------------------------------------------------------------------------------------------------------------
Classpath
/u00/app/product/12.2/jdbc/lib/ojdbc8.jar
/u00/app/product/12.2/ojdbc8.jar
/u00/app/product/12.2/jdbc/lib/ojdbc7.jar
/u00/app/product/12.2/ojdbc7.jar
/u00/app/product/12.2/jdbc/lib/ojdbc6.jar
/u00/app/product/12.2/ojdbc6.jar
$SQL_HOME/lib/drivers/*
$SQL_HOME/lib/ext/*
$SQL_HOME/../rdbms/jlib/xdb6.jar
$SQL_HOME/../jdbc/lib/ojdbc8.jar
$SQL_HOME/../jlib/orai18n-utility.jar
$SQL_HOME/../jlib/orai18n-mapping.jar
$SQL_HOME/../jlib/orai18n.jar
$SQL_HOME/../modules/oracle.xdk/xmlparserv2.jar
$SQL_HOME/lib/javax.json.jar
$SQL_HOME/lib/xmlparserv2.jar
$SQL_HOME/lib/commons-logging.jar
$SQL_HOME/lib/orai18n.jar
$SQL_HOME/lib/xmlparserv2-sans-jaxp-services.jar
$SQL_HOME/lib/orai18n-collation.jar
$SQL_HOME/lib/jackson-databind.jar
$SQL_HOME/lib/xdb6.jar
$SQL_HOME/lib/jackson-core.jar
$SQL_HOME/lib/stringtemplate.jar
$SQL_HOME/lib/oraclepki.jar
$SQL_HOME/lib/jackson-annotations.jar
$SQL_HOME/lib/osdt_core.jar
$SQL_HOME/lib/httpmime.jar
$SQL_HOME/lib/osdt_cert.jar
$SQL_HOME/lib/httpcore.jar
$SQL_HOME/lib/orajsoda.jar
$SQL_HOME/lib/httpclient.jar
$SQL_HOME/lib/orai18n-utility.jar
$SQL_HOME/lib/ojdbc8.jar
$SQL_HOME/lib/dbtools-sqlcl.jar
$SQL_HOME/lib/dbtools-net.jar
$SQL_HOME/lib/dbtools-http.jar
$SQL_HOME/lib/dbtools-common.jar
$SQL_HOME/lib/orai18n-servlet.jar
$SQL_HOME/lib/jsch.jar
$SQL_HOME/lib/commons-codec.jar
$SQL_HOME/lib/jdbcrest.jar
$SQL_HOME/lib/orai18n-mapping.jar
$SQL_HOME/lib/jline.jar
$SQL_HOME/lib/antlr-runtime.jar
$SQL_HOME/lib/jansi.jar

------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/sqlcl
LDAPCON : Default
net: ON
noverwrite: WARN

SQLcl unsupported SQL*Plus command: show SECUREDCOL SPPARAMETER SQLDEV SQLDEV2 SQLFORMAT SQLPATH SQLPLUSCOMPAT SYSTEMOUT TABLE TOPLEVEL UFI URL XMLFORMAT XQUERY
securedcol is OFF
SQL Format : Default
SQLPATH : /home/oracle/:.
sqlpluscompatibility 12.2.0
systemout "true"
Toplevel TRUE
url top_base_url "" base_url "" node_url (Parent) "file:/home/oracle"
xmlformat is OFF
xquery BASEURI "" CONTEXT "" NODE DEFAULT ORDERING DEFAULT

Show All+ (Multi Line):
========================================

CONNECTION:
========================================
CONNECTION:
SYS@jdbc:oracle:oci8:@mos2pdb1 AS SYSDBA
CONNECTION_IDENTIFIER:
mos2pdb1
CONNECTION_DB_VERSION:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
NOLOG:
false
PRELIMAUTH:
false

DDL:
========================================
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON

DEFINES:
========================================
DEFINE _DATE = "30-AUG-18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "mos2pdb1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1802000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000000" (CHAR)
DEFINE _PWD = "/home/oracle" (CHAR)

INSTANCE:
========================================
Instance "local"

INTERNALERRORS:
========================================
sqldev.error "false"
sqldev.error.any.in "false"
script.runner.autocommit.errorflag ""
sqldev.last.err.message ""
sqldev.last.err.message.forsqlcode ""

JDBC:
========================================
-- Database Info --
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Database Major Version: 12
Database Minor Version: 2
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.2.0.1.0
Driver Major Version: 12
Driver Minor Version: 2
Driver URL: jdbc:oracle:oci8:@mos2pdb1
Driver Location:
resource: oracle/jdbc/OracleDriver.class
jar: /u00/app/product/12.2/jdbc/lib/ojdbc8.jar
JarSize: 4036257
JarDate: Tue Dec 13 16:39:52 EST 2016
resourceSize: 2604
resourceDate: Tue Dec 13 08:39:48 EST 2016

LOGIN:
========================================
Glogin possible locations
--------------------
/home/oracle/sqlcl/
/u00/app/product/12.2/sqlplus/admin/

glogin.sql
---------------------
/u00/app/product/12.2/sqlplus/admin/glogin.sql

NLS:
========================================
DB_TIMEZONE +00:00
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
SESSION_TIMEZONE America/Toronto
SESSION_TIMEZONE_OFFSET -04:00

PDBS:
========================================

CON\_ID CON\_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO

SGA:
========================================
Total System Global Area 1241513984 bytes
Fixed Size 8792248 bytes
Variable Size 872417096 bytes
Database Buffers 352321536 bytes
Redo Buffers 7983104 bytes

Thank you

1 - 6

Added on Aug 27 2018

6 comments

969 views

SQLcl: MCP Server & SQL Prompt

Wrong standart format

Comments

Post Details