SQLcl - Question about alias in SQLcl

daniel_hauke

Hi everybody,

i was able to create my own alias definiton in my login.sql file (located in our $SQLPATH).

When i login into sql i can see those alias after typing the command alias.

Later i changed the name of some of these alias. After doing this and logging in back again in sql, i can see the new alias and the old alias which are no longer present in the login.sql file.

Here is the content of my login.sql file:

SET sqlprompt "_user '@' _connect_identifier >"SET sqlformat ansiconsoledefine _EDITOR=inlineSET pagesize 25alias instance_info=select instance_name,host_name,status from v$instance;alias archivelog_mode=archive log list;

And here is the output of the command alias in sql:

SYS @ orcl_db >aliasarchivelog_modearchivelogmodeinstance_infoinstanceinfo

Can anyone provide me a hint how to refresh the list of alias after doing such an change in the login.sql?

Greetings Daniel

srg

@Daniel,

As best as I can tell, aliases are persisted from session to session - they cannot be "renamed", they must be dropped. If you type "help alias" at the sqlcl command prompt you will see the various options available with the command.

In Windows, aliases are stored in the user profile under the hidden folder AppData\Roaming\sqlcl\aliases.xml along with backups that are created when new aliases are added.

Hope this helps!

Regards

srg

@Daniel,

As best as I can tell, aliases are persisted from session to session - they cannot be "renamed", they must be dropped. If you type "help alias" at the sqlcl command prompt you will see the various options available with the command.

In Windows, aliases are stored in the user profile under the hidden folder AppData\Roaming\sqlcl\aliases.xml along with backups that are created when new aliases are added.

Hope this helps!

Regards

daniel_hauke

Hi SRG,

thanks for the hint. I have checked the following folder under Linux (because we are using sqlcl on our Linux machines):

/home/oracle/.sqlcl

In this folder i found the aliases.xml file. I deleted this and issued the command alias in sqlcl again.

Now it shows me only the aliases i defined in my login.sql.

And after checking the folder again, a new aliases.xml was created containing only my aliases.

So my way to see only my defined aliases is the following:

• Change a existing or create a new alias in the $SQLPATH/Login.sql
• Delete the aliases.xml under /home/oracle/.sqlcl
• Login into sqlcl
• Issue the alias command

So unless you are deleting the aliases.xml file you will see old and new aliases in sqlcl. So thank you!

Greetings Daniel

srg

Hey Daniel, just an additional comment, if you will permit, I may be misunderstanding your specific use case...

The sqlcl alias command provides all functionality required to manage aliases without deleting the aliases.xml file.

In your initial post you define 2 aliases within login.sql along with 2 unwanted aliases that had been defined in an earlier session.

you showed all aliases defined:

SQL> aliasarchivelog_modearchivelogmodeinstance_infoinstanceinfo

to permanently remove (delete) an alias:

SQL> alias drop instanceinfoAlias instanceinfo droppedSQL> aliasarchivelog_modearchivelogmodeinstance_info

you can even export the aliases to another file defined by you and then load them:

SQL> alias save srgaliastest.xmlALIAS-007 - Aliases saved to srgaliastest.xmlSQL> alias drop archivelogmodeAlias archivelogmode droppedSQL> aliasarchivelog_modeinstance_infoSQL> alias load srgaliastest.xmlAliases LoadedSQL> aliasarchivelog_modearchivelogmodeinstance_info

Please forgive me if this is known to you, but thought I would provide the additional examples just in case...

Regards!

daniel_hauke

Hi SRG,

thanks for providing me some more tipps about the alias command.

The possibility to eport the aliasses to a different file and load them is something new to me.

And i totally forgot about the drop alias command

So thanks.

Greetings Daniel