Forum Stats

  • 3,750,280 Users
  • 2,250,153 Discussions
  • 7,866,899 Comments

Discussions

Categories

Wrong standart format

user635344
user635344 Member Posts: 30 Blue Ribbon
edited Aug 30, 2018 11:55AM in SQLcl

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

· Share on TwitterShare on Facebook

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Aug 27, 2018 6:23PM

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

    Capture.JPG

    · Share on TwitterShare on Facebook
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Aug 28, 2018 5:47AM

    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?

    ...

    · Share on TwitterShare on Facebook
  • user635344
    user635344 Member Posts: 30 Blue Ribbon
    edited Aug 28, 2018 9:49AM

    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

    · Share on TwitterShare on Facebook
  • user635344
    user635344 Member Posts: 30 Blue Ribbon
    edited Aug 28, 2018 9:51AM

    pastedImage_0.png

    · Share on TwitterShare on Facebook
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Aug 29, 2018 11:42PM

    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> versionOracle SQLDeveloper Command-Line (SQLcl) version: 18.1.1.0SQL> set sqlformat ansiconsoleSQL> select * from v$version;BANNER                                                                            CON_IDOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production          0PL/SQL Release 12.2.0.1.0 - Production                                                0CORE    12.2.0.1.0      Production                                                            0TNS for Linux: Version 12.2.0.1.0 - Production                                        0NLSRTL Version 12.2.0.1.0 - Production                                                0SQL> set sqlformatSQL Format ClearedSQL> /BANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0PL/SQL Release 12.2.0.1.0 - Production                                                    0CORE    12.2.0.1.0      Production                                                                  0TNS for Linux: Version 12.2.0.1.0 - Production                                            0NLSRTL Version 12.2.0.1.0 - Production                                                    0SQL>

    ...and in sqlplus.

    $ echo -e "set lines 91\n select * from v\$version;" | sqlplus -s gaz/gazBANNER                                                                               CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0PL/SQL Release 12.2.0.1.0 - Production                                                    0CORE    12.2.0.1.0      Production                                                                0TNS for Linux: Version 12.2.0.1.0 - Production                                            0NLSRTL Version 12.2.0.1.0 - Production                                                    0$
    · Share on TwitterShare on Facebook
  • user635344
    user635344 Member Posts: 30 Blue Ribbon
    edited Aug 30, 2018 11:55AM

    Hi

    No extra parameters

    I can see the same result in putty and VirtualBox console

    output in sqlplus works fine

    pastedImage_0.png

    pastedImage_1.png

    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
    --------------------
    1.  USER Home dir
        /home/oracle
    2.  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:
    [email protected]: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

    Login possible locations
    --------------------
    /home/oracle/sqlcl/
    ./

    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

    · Share on TwitterShare on Facebook