SQLcl

Both Oracle SQL*Plus and SQLcl support bind variables and substitution (& and &&) variables, but it looks like the SQLcl ALIAS syntax does not allow substitution variables.

For reference, here is a discussion on how these work in SQL*Plus: https://blogs.oracle.com/opal/sqlplus-101-substitution-variables

For substitution variables, use a sql script rather than an alias, and pass in the variables.  For example, one of the first scripts (CntByGrp) I ever wrote

SELECT &2 , count(*) FROM &1 GROUP BY &2 ORDER BY &2
/ 

displays an ordered list of the occurrences and frequency of values in one or more columns for a table.  Example...

SQL> @CntByGrp Employees Manager_Id
or 
SQL> @CntByGrp Employees Manager_Id,Last_Name 

Thanks Glen,

I know substitution variables (where this works for tables), but I wanted to know if I can use this in alias command in SQLcl where this would have a real sense so you don't have to repeat typing the same code over again.

Taking a step back, I really should have said

1. Substitution relates to values being substituted into the code by the interface before submission to the database.
2. Bind variables are placeholders for values within a statement.  They cannot be used for schema / object names.

So the SQLcl interface's ALIAS command is only supporting what the Oracle database supports.  More references:

https://stackoverflow.com/questions/41594284/bind-variable-substitution-in-oracle

I'm not sure if I understood you correctly regarding the object names. Isn't a database table an object name? This SQL for is fine if you run it for example from SQL Dev:

Select *
From &TableName;

Above I can type any table name and I get correct records. Fro the beginning my Q was why then I cannot use the same code (logic) as alias in SQLcl. Is this a bug?

I guess there are two points to clarify here.

First, going back to the bind variable example in your original post,

alias test2 = Select * From :MyPar;

I am saying this does not work because Oracle database does not allow specification of object names via bind variables.

Second, regarding the use of substitution variables in an ALIAS definition, the trick is to prevent variable substitution when processing the definition of the alias, but then enable it again when "running" the alias.  This is done using the SQL*PLus SET DEFINE command, which is also available in SQLcl.  Here is an example showing how this all works.  First, by default

SQL> show define
define "&" (hex 26)
SQL>

So if we try to define an alias with a SQL statement using variable substitution, this is what happens

SQL> alias test=select * from &tab.;
Enter value for tab: dual
SQL> alias list test
test
----
select * from dual

But that just hard-wires the DUAL table name into the alias definition, which does us absolutely no good.

Next, disable substitution, create the alias, then re-enable substitution:

SQL> alias drop test
Alias test dropped
SQL> set define off
SQL> alias test=select * from &tab.;
SQL> alias list test
test
----
select * from &tab.
SQL> test
Error starting at line : 1 File @ \ALIAS\test
In command -
select * from &tab.
Error at Command Line : 1 Column : 15 File @ \ALIAS\test
Error report -
SQL Error: ORA-00903: invalid table name
SQL> set define on
SQL> test
Enter value for tab: dual
old:select * from &tab.
new:select * from dual
D
-
X
SQL>

Hope this makes everything crystal clear.

Cheers