Skip to Main Content
Forums

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!

How to stop printing SQL statement of login.sql in sqlcl

Orcl ApexJan 18 2018 — edited Jan 29 2018

Hi All,

When I am running sqlcl it automatically calls login.sql but along with script execution it's also showing sql in display.

Can you please advise how to hide sql statement i.e. i don't want to display the line marked in yellow i.e. old: select

pastedImage_0.png
This post has been answered by Gaz in Oz on Jan 27 2018
Jump to Answer

Comments

Gary Graham-Oracle

You could start by adding

set termout off

to your login.sql, but you do not show where the unknown COLUMN option is used in your SQL statement so I can not test conclusively.

Edit:

But it looks like you have an error or typo in some COL definition in the login.sql, as in...

hr@XE> col abcd format a15 @INST_NAME
SP2-0158: unknown COLUMN option "@INST_NAME"

Rather than fixing the error, you could also try

set echo off
Orcl Apex

None of these helping.

I just don't want to show SQL query but want to display it's result, If i put terminal output off it not showing result.

Orcl Apex

Uploaded both files.

Gary Graham-Oracle

So I suppose the short answer is...

1. We will ignore the SP2-0158 since you commented out the offending COL definition, but SQL*Plus handles it while SQLcl does not.

2. Use set echo off to suppress display of the SQL statement, just like in SQL*Plus.

3. Use set verify off to suppress display of the variable substitution before (old:) and after (new:) states, just like in SQL*Plus.

4. Testing your sample login.sql and the i.sql script it calls, both on SQL*Plus and SQLcl, I notice an interesting difference:

     a) With the default of set verify on, SQL*Plus only displays old: and new: for substituted lines.  For example, in your case...

          old  21:      upper('&_connect_identifier') "_i_conn"
          new 21:      upper('xe') "_i_conn"

     b) Whereas SQLcl will display both old: and new: as containing the entire SQL statement, which is unfortunately a bit unwieldy.

Feel free to open a Service Request with My Oracle Support for the issues noted in (1) and (4). It is always nice to keep moving SQLcl behavior closer to that of SQL*Plus. 

1 person found this helpful
Orcl Apex

Hi ,

I can't open a SR with support as my organization won't allow to raise SR. Is there any other way to send this request ?

thatJeffSmith-Oracle

Have someone at your org log it for you then.

Gaz in Oz
Answer

Among a lot of things wrong with commands and sql in your login.sql and i.sql...

How about making more use of sys_context?

For example, instead of using &_connect_identifier, use the service_name instead:

       upper('&_connect_identifier') "_i_conn"

With:

       sys_context('userenv', 'service_name') "_i_conn"

That way no substitution prompting for your dodgily written sql statement, which gets rid of one of your problems with what you reported. (as a workaround anyway).

In login.sql,

def editor=...

for sqlplus and sqlcl to make use of that it should be:

def _editor=...

There seems to be many uses of "set feedback off, when just one would do.

...and your "set sqlprompt..." code isn't working.

Use of sql and PL/SQL populate the SQL buffer with the last executed statement. EXEC'd statements and sqlplus commands and some sqlcl commands do not.

A simpler way to set the prompt to USER@connect_string> would be:

   set sqlprompt "&_user.@&_connect_identifier> "

A trimmed down working version of your login.sql and the fixed (workaround) i.sql, working in sqlplus and sqlcl attached.

Marked as Answer by Orcl Apex · Sep 27 2020
2 people found this helpful
Orcl Apex

Thanks Man.

You saved Jeff and team's work on a non existing bug

1 - 8

Post Details

Added on Jan 18 2018
8 comments
1,270 views