8 Replies Latest reply on Feb 19, 2020 8:06 AM by daniel_hauke

    sqlcl describe alias

    daniel_hauke

      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

        • 1. Re: sqlcl describe alias
          Gaz in Oz

          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 cls
          cls - desc cls Clear screen and set cursor to top left.
          -------------------------------------------------------
          
          clear screen
          
          SQL> 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 cls
          cls - Clear screen and set cursor to top left.
          ----------------------------------------------
          
          clear screen
          
          SQL>
          
          • 2. Re: sqlcl describe alias
            daniel_hauke

            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) $ sqlcl
            SQLcl: Release 19.2.1 Production on Thu Feb 13 10:31:38 2020
            
            SYS @ instance >alias list instance
            instance - desc instance - Shows informations about the instance!
            
            -------------------------------------------------------------------------------------------------------
            select instance_name,host_name,status from v$instance
            
            SYS @ instance >exit
            
            Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
            Version 19.6.0.0.0
            PWD=/home/oracle"
            "x86_64-host(oracle_instance) $ vi .sqlcl/aliases.xml
            PWD=/home/oracle"
            "x86_64-host(oracle_instance) $ sqlcl
            SQLcl: Release 19.2.1 Production on Thu Feb 13 10:32:27 2020
            
            SYS @ instance >alias list instance
            instance - desc instance - Shows informations about the instance!
            
            -------------------------------------------------------------------------------------------------------
            select instance_name,host_name,status from v$instance
            
            SYS @ 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

            • 3. Re: sqlcl describe alias
              Gaz in Oz

              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.

              • 4. Re: sqlcl describe alias
                daniel_hauke

                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

                • 5. Re: sqlcl describe alias
                  Gaz in Oz

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

                  Yes.

                  1 person found this helpful
                  • 6. Re: sqlcl describe alias
                    daniel_hauke

                    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?

                    • 7. Re: sqlcl describe alias
                      Gaz in Oz

                      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.

                      • 8. Re: sqlcl describe alias
                        daniel_hauke

                        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