Forum Stats

  • 3,733,344 Users
  • 2,246,744 Discussions
  • 7,856,663 Comments

Discussions

sqlcl describe alias

daniel_hauke
daniel_hauke Member Posts: 211 Bronze Badge
edited February 2020 in SQLcl

Hi everybody,

i know that i can create my own alias in sqlcl and that i can describe this alias with desc.

When i issue alias list <my_own_alias> it shows me the following in sqlcl:

SYS @ instance >alias list my_own_alias

my_own_alias - desc my_own_alias - Shows informations about the instance!

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

select instance_name,host_name,status from v$instance

When i issue alias list on a build-in alias it shows the following:

SYS @ instance >alias list tables

tables - tables <schema> - show tables from schema

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

select table_name "TABLES" from user_tables

With my own alias the alias list command always shows the desc keyword. The build-in alias didn't contain this keyword.

Does anybody know a trick how i can get rid of the keyword desc when issuing the alias list command in sqlcl?

We are using SQLcl: Release 19.2.1

Greetings Daniel

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited February 2020 Accepted Answer

    Remove the sqlcl alias creation from login.sql, create an aliases.sql and add them to that.

    Now all you have to do from your various servers is login to sqlcl, and if needed run the aliases.sql script.

    SQL> @aliases.sql

    Another possibility is to put some javascript code/logic in the login.sql with the "script" command, to check for the existence of the aliases, if they don't exist then create them.

    With respect to the "desc" text that gets added to the comment for the alias on creation, other than editing aliases.xml after the fact, I don't see how you can get round that.

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited February 2020

    Look for the file %APPDATA%\sqlcl\aliases.xml, back it up then edit it.

    The line to edit will be obvious as it will contain your comment string, just delete the 2 words "desc my_own_alias" and save.

    Re-login to sqlcl and viola! (or some other stringed instrument...)

    For example:

    SQL> alias cls=clear screen;SQL> alias desc cls Clear screen and set cursor to top left.;SQL> alias list clscls - desc cls Clear screen and set cursor to top left.-------------------------------------------------------clear screenSQL> exit...f:\Oracle> vi %APPDATA%\sqlcl\aliases.xml &: use what ever text editor you use......Edit the line:      <description><![CDATA[desc cls Clear screen and set cursor to top left.]]></description>to      <description><![CDATA[Clear screen and set cursor to top left.]]></description>... and save, then login to sqlcl again:f:\Oracle> sql /nolog...SQL> alias list clscls - Clear screen and set cursor to top left.----------------------------------------------clear screenSQL>
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited February 2020

    Hi,

    thanks for the hint. But for me this does not work.

    I am running sqlcl under Linux, so i edited the aliases.xml under the following folder /home/<os_user>/.sqlcl

    But when i log into sqlcl again and issue alias list my_own_alias it still shows the desc keyword.

    "x86_64-host(oracle_instance) $ sqlclSQLcl: Release 19.2.1 Production on Thu Feb 13 10:31:38 2020SYS @ instance >alias list instanceinstance - desc instance - Shows informations about the instance!-------------------------------------------------------------------------------------------------------select instance_name,host_name,status from v$instanceSYS @ instance >exitDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.6.0.0.0PWD=/home/oracle""x86_64-host(oracle_instance) $ vi .sqlcl/aliases.xmlPWD=/home/oracle""x86_64-host(oracle_instance) $ sqlclSQLcl: Release 19.2.1 Production on Thu Feb 13 10:32:27 2020SYS @ instance >alias list instanceinstance - desc instance - Shows informations about the instance!-------------------------------------------------------------------------------------------------------select instance_name,host_name,status from v$instanceSYS @ instance >exit

    Any other hint? Is it due to the fact that i have defined my aliases in my login.sql under $SQLPATH

    Greetings Daniel

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited February 2020
    Any other hint? Is it due to the fact that i have defined my aliases in my login.sql under $SQLPATH

    Yes, of course, as every time you login the alias is being recreated. As the aliases are stored in aliases.xml, you only need to define them ONCE, not every time you login.

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited February 2020

    Hi,

    i do not define them every time i login.

    I have created my aliases in the login.sql file wich is located under the $SQLPATH. This login.sql is a softlink to a shared file so i can use the defined aliases from all my oracle db servers.

    Or do you mean that if i use the login.sql file the aliases are recreated every time i login?

    Greetings Daniel

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited February 2020
    Or do you mean that if i use the login.sql file the aliases are recreated every time i login?

    Yes.

    daniel_hauke
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited February 2020

    Ok,

    but if i use the aliases.xml i have to change every xml file on every oracle db server if i want to add a new sqlcl alias.

    That's not really useful for our use case.

    Any other hints?

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited February 2020 Accepted Answer

    Remove the sqlcl alias creation from login.sql, create an aliases.sql and add them to that.

    Now all you have to do from your various servers is login to sqlcl, and if needed run the aliases.sql script.

    SQL> @aliases.sql

    Another possibility is to put some javascript code/logic in the login.sql with the "script" command, to check for the existence of the aliases, if they don't exist then create them.

    With respect to the "desc" text that gets added to the comment for the alias on creation, other than editing aliases.xml after the fact, I don't see how you can get round that.

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited February 2020

    Hi,

    thank for the tips.

    I think i will take some time in creating a aliases.sql and run them when i log into sqlcl.

    Thanks for your help

Sign In or Register to comment.