Forum Stats

  • 3,760,421 Users
  • 2,251,703 Discussions
  • 7,871,115 Comments

Discussions

How to create alias with input table as parameter?

Blue Bird
Blue Bird Member Posts: 350 Blue Ribbon
edited Oct 12, 2018 10:48AM in SQLcl

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

Answers

  • Glen Conway
    Glen Conway Member Posts: 859 Gold Badge
    edited Oct 10, 2018 1:14PM

    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
  • Blue Bird
    Blue Bird Member Posts: 350 Blue Ribbon
    edited Oct 11, 2018 1:40AM

    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
    Glen Conway Member Posts: 859 Gold Badge
    edited Oct 11, 2018 2:21PM

    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
    Blue Bird Member Posts: 350 Blue Ribbon
    edited Oct 12, 2018 9:09AM

    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
    Glen Conway Member Posts: 859 Gold Badge
    edited Oct 12, 2018 10:48AM

    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