5 Replies Latest reply on Oct 12, 2018 2:48 PM by Glen Conway

    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

        • 1. Re: How to create alias with input table as parameter?
          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_Id

          or

          SQL> @CntByGrp Employees Manager_Id,Last_Name

          1 person found this helpful
          • 2. Re: How to create alias with input table as parameter?
            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.

            • 3. Re: How to create alias with input table as parameter?
              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

              PL/SQL 101 : Substitution vs. Bind Variables

              • 4. Re: How to create alias with input table as parameter?
                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?

                • 5. Re: How to create alias with input table as parameter?
                  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 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