How to create alias with input table as parameter?

Blue Bird

I'm using SQLcl 18.2.x. If I create alias with input parameter for where clause in sql statement alias work ok, but If I use parameter for table name doesn't. For example:

This working:

alias test = Select * From Emp Where DeptNo = :MyPar;

"Test 10" return appropriate records.

and this not working;

alias test2 = Select * From :MyPar;

I get error "Invalid table name" after calling "test2 Emp".

Is it possible to use parameter also for table name? I would like to create for example "disp TableName" shortcut to quickly display data from any table name I pass as parameter w/o writing everytime whole Select sentence.

Thanks,

BB

Glen Conway

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_Idor SQL> @CntByGrp Employees Manager_Id,Last_Name 

Blue Bird

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.

Glen Conway

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

Blue Bird

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?

Glen Conway

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 definedefine "&" (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: dualSQL> alias list testtest----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 testAlias test droppedSQL> set define offSQL> alias test=select * from &tab.;SQL> alias list testtest----select * from &tab.SQL> testError starting at line : 1 File @ \ALIAS\testIn command -select * from &tab.Error at Command Line : 1 Column : 15 File @ \ALIAS\testError report -SQL Error: ORA-00903: invalid table nameSQL> set define onSQL> testEnter value for tab: dualold:select * from &tab.new:select * from dualD-XSQL>

Hope this makes everything crystal clear.

Cheers