Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,775 Comments

Discussions

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

User_ZUUOD
User_ZUUOD Member Posts: 798 Bronze Badge
edited Jan 29, 2018 3:02PM in SQLcl

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

thatJeffSmith-Oracle

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 27, 2018 10:47AM Accepted 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 [email protected]_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.

    thatJeffSmith-OracleUser_ZUUOD

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Jan 18, 2018 4:19PM

    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...

    [email protected]> col abcd format a15 @INST_NAMESP2-0158: unknown COLUMN option "@INST_NAME"

    Rather than fixing the error, you could also try

    set echo off
  • User_ZUUOD
    User_ZUUOD Member Posts: 798 Bronze Badge
    edited Jan 18, 2018 4:40PM

    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.

  • User_ZUUOD
    User_ZUUOD Member Posts: 798 Bronze Badge
    edited Jan 18, 2018 4:46PM
  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Jan 19, 2018 1:15AM

    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

    thatJeffSmith-Oracle
  • User_ZUUOD
    User_ZUUOD Member Posts: 798 Bronze Badge
    edited Jan 19, 2018 11:35AM

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,931 Employee
    edited Jan 22, 2018 12:52PM

    Have someone at your org log it for you then.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jan 27, 2018 10:47AM Accepted 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 [email protected]_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.

    thatJeffSmith-OracleUser_ZUUOD
  • User_ZUUOD
    User_ZUUOD Member Posts: 798 Bronze Badge
    edited Jan 29, 2018 3:02PM

    Thanks Man.

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