Forum Stats

  • 3,758,942 Users
  • 2,251,479 Discussions
  • 7,870,440 Comments

Discussions

Meeting some issues when using SQLcl 18.1.1

Quanwen Zhao
Quanwen Zhao Member Posts: 584 Blue Ribbon
edited Jun 28, 2018 4:16AM in SQLcl

Hello, experts

Recently I use SQLcl 18.1.1 to easily and conveniently generate CSV data, so I feel I gradually love this developer tool.

Till now, I meet some issues when using it. The following are total 4 key points,

(1) directly input "sql" command and enter, according to its prompt message then input Username and Password (on the part of Password it also needs to be entered TNS NAME or EZ Connect String), afterwards you'll find that the cursor key is always flashing on here - "Username? (RETRYING) ('test/*********'?)", at the very start I don't think that it could connect successfully until I continue to press Enter key. BTW there are still several boring error messages (IO Error) on it.

  • via TNS NAME

[[email protected] ~]$ sqlSQLcl: Release 18.1.1 Production on Thu Jun 14 08:38:36 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.Username? (''?) testPassword? (**********?) *****************  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/orcl  Error Message = IO Error: The Network Adapter could not establish the connection  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/xe  Error Message = IO Error: The Network Adapter could not establish the connectionUsername? (RETRYING) ('test/*********'?) <<==Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> show userUSER is "TEST"SQL> 

  • via EZ Connect String

[[email protected] ~]$ sqlSQLcl: Release 18.1.1 Production on Tue Jun 14 08:41:11 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.Username? (''?) testPassword? (**********?) ************************************  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/orcl  Error Message = IO Error: The Network Adapter could not establish the connection  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/xe  Error Message = IO Error: The Network Adapter could not establish the connectionUsername? (RETRYING) ('test/*********'?) <<==Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> show userUSER is "TEST"SQL> 

(2) this time input "sql/nolog" and then appear the prompt words "SQL> ", next use "conn Username" command to connect oracle db server and press Enter key and password has been inputted.

Unfortunately this format hasn't been connected to db, as you can see the following code.

[[email protected] ~]$ sql /nologSQLcl: Release 18.1.1 Production on Thu Jun 14 08:43:26 2018Copyright (c) 1982, 2018, Oracle.  All rights reserved.SQL> conn testPassword? (**********?) *****************  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/orcl  Error Message = IO Error: The Network Adapter could not establish the connection  USER          = test  URL           = jdbc:oracle:thin:@localhost:1521/xe  Error Message = IO Error: The Network Adapter could not establish the connectionSQL> show userUSER is ""SQL> 

(3) "history" command hasn't remembered that this command like this, "set linesize/pagesize and etc.", is the reason that "SET ..." is only belong to "SQL*Plus"?

SQL> set linesize 150SQL> set pagesize 150SQL> SQL> historyHistory:   1  tnsping ...  6  desc user   7  desc user_tables;  8  desc user_segments   9  select sum(bytes)/1024/1024 sizd_mb from user_segments where segment_name='TEST'; 10  select sum(bytes)/1024/1024 sizd_mb from user_segments where segment_name='TEST1'; 11  @spool_test.sql  12  @spool_test1.sql ... 67  col random_string for a20  68  desc test  69  desc test1  70  net  71  alias ... 75  select * from test where rownum <= 1000000 minus select * from test where rownum <= 999970;SQL> 

(4) if SQLcl 18.1.1 supports Oracle Wallet, it'll be more perfect.

Very appreciate you if any help.

Best Regards

Quanwen Zhao

Quanwen Zhao

Best Answer

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jun 20, 2018 4:20AM
    Recently I use SQLcl 18.1.1 to easily and conveniently generate CSV data, so I feel I gradually love this developer tool.

    As of sqlplus 12.2.0.1, you can use "set markup csv"

    (instantclient available here: Oracle Instant Client Downloads )

    Also worth noting, 12.2.0.1 also has history.

    As to the issues:

    (1).  A work around is to give the alias on the "Username:" prompt. e.g. [email protected]

           Same for ezconnect, at Username prompt, e.g. [email protected]:yourport/yourservicename

    (2). Same with sql /nolog, use workarounds above.

    The basic workaround for 1 and 2 is to NOT specify the connect string as part of the password.

    (3). The "set" command is a sqplus command AND a sqlcl command. The sqlcl "History" command will not remember set commands to history.

           Here's a link to sqlcl command-line reference: https://docs.oracle.com/database/sql-developer-18.1/SQCQR/toc.htm

    (4). You can get it to work, take a look at these threads:

    Quanwen ZhaoQuanwen Zhao
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,982 Employee
    edited Jun 20, 2018 10:49AM Accepted Answer

    for HISTORY, we have a black list, and SET is one of the things in that list, so you can just change the black list to not exclude SETs

    https://www.thatjeffsmith.com/archive/2016/11/blacklisting-commands-in-your-oracle-sqlcl-history/

    Quanwen ZhaoQuanwen Zhao
  • Quanwen Zhao
    Quanwen Zhao Member Posts: 584 Blue Ribbon
    edited Jun 20, 2018 9:49PM
    thatJeffSmith-Oracle wrote:for HISTORY, we have a black list, and SET is one of the things in that list, so you can just change the black list to not exclude SETshttps://www.thatjeffsmith.com/archive/2016/11/blacklisting-commands-in-your-oracle-sqlcl-history/

    Hi, Jeff

    According to your post linked, I test it on my SQLcl 18.1.1.

    I feel the design of this blacklist of HISTORY is more clever, meanwhile this funny thin connecting tool has given all of users to their decision (or right to choose). It looks like very well and humanized.

    Thank you very much.

    Best Regards

    Quanwen Zhao

  • Quanwen Zhao
    Quanwen Zhao Member Posts: 584 Blue Ribbon
    edited Jun 20, 2018 11:50PM
    As of sqlplus 12.2.0.1, you can use "set markup csv"(instantclient available here: Oracle Instant Client Downloads )Also worth noting, 12.2.0.1 also has history.As to the issues:(1).  A work around is to give the alias on the "Username:" prompt. e.g. [email protected]       Same for ezconnect, at Username prompt, e.g. [email protected]:yourport/yourservicename(2). Same with sql /nolog, use workarounds above.The basic workaround for 1 and 2 is to NOT specify the connect string as part of the password.

    That's to say, it's time to upgrade to 12.2 from 11.2.0.4.0 because 12.2 has a series of nice functions. BTW instantclient 12.2 also supports "set markup csv" ?

    On the other hand I'll read that two threads you supplied about WALLET.

    Thanks a lot.

    Best Regards

    Quanwen Zhao

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Jun 21, 2018 10:36PM
    BTW instantclient 12.2 also supports "set markup csv" ?

    instantclient12.2.0.1.0 basic/light libraries + instantclient12.2.0.1.0 sqlplus: Yes.

    ...and any other sqlplus 12.2 for that matter, from database server install, database client install etal.

    As will Oracle sqlplus 12.2 on any platform, any source, will obviously have the same functionality.

    How ever, sqlplus 12.2.0.1.0 does have an issue with reading SQLPATH and [gl]ogin.sql as a result. This is fixed in 18c.

    Having said that, I've been using sqlplus 12.2 quite happily for a year+

    At the moment only 18c database client is available (aka 12.2.0.2.0), so it would be better to install the instant client from that.

    Apparently, there's a swag of 18c releases due out in July, probably this will include the stand-alone instantclient zip's.

    Quanwen ZhaoQuanwen Zhao