Skip to Main Content
  • Questions

  • Using defined variable (ampersand) for a part of column name

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 11, 2018 - 2:16 pm UTC

Last updated: February 15, 2018 - 6:58 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Good day.

I am having problem to define only part of a column name as a variable (using ampersand).


Script example:

Select &Product._ID,
&Product._NAME,
&Product._SALES,
&Product._DEBT
From TABLE_A;

How should I do this in a best way?

Thank you in advance for any help!
Denis.

and Connor said...

Can you give us a test case ? It works fine for me

SQL> create table t ( XXX_ID int , YYY_ID int );

Table created.

SQL>
SQL> insert into t values (10,20);

1 row created.

SQL>
SQL> define product = XXX
SQL>
SQL> select &&product._ID from t;
old   1: select &&product._ID from t
new   1: select XXX_ID from t

    XXX_ID
----------
        10

1 row selected.

SQL>
SQL> define product = YYY
SQL>
SQL> select &&product._ID from t;
old   1: select &&product._ID from t
new   1: select YYY_ID from t

    YYY_ID
----------
        20

1 row selected.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Denis, February 13, 2018 - 11:03 am UTC

Hello, Connor.

Thank you for your answer. I didn't mention it, but I am using PL-SQL developer and running there any of the scripts below gives me the following error - ORA-00911: invalid character.

Select &&Product._ID From T;
Select &Product._ID From T;

Unfortunately, I cannot use Oracle SQL Live (copy and paste links) due to restrictions of my company's security policies.


Connor McDonald
February 14, 2018 - 1:38 am UTC

Take a look here

https://stackoverflow.com/questions/7103715/escape-ampersand-in-pl-sql-developer

Some options presented.

Have you tried ...

A reader, February 13, 2018 - 2:43 pm UTC

Have you tried:

Select &{Product}._ID,
&{Product}._NAME,
&{Product}._SALES,
&{Product}._DEBT
From TABLE_A;



Another Try

Denis, February 14, 2018 - 1:57 pm UTC

I didn't manage to find anything regarding my issue in the attached link.

As well, any of the scripts below returns the following error - ORA-01745: invalid host/bind variable name.

Any additional suggestions will be appreciated.
Connor McDonald
February 15, 2018 - 6:58 am UTC

Unfortunately its not really an Oracle problem, its a PL/SQL Developer software issue.

I think you'll need to try whatever support forums they offer.

Another Try (b)

Denis, February 14, 2018 - 1:59 pm UTC

Theses scripts return the following error - ORA-01745: invalid host/bind variable name.

Select &{Product}._ID From T;
Select &{Product.}_ID From T;
Select &{Product}_ID From T;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.