Oracle Variable substitute - Synonym

Question

I want to substitute a variable in oracle.

The var is coming in via SQLPlus and is reference using &1

&1 in my scenario is the schema name - here's the SQL.

CREATE SYNONYM ACCOUNT FOR &1.ACCOUNT;

Assuming &1 equals ABC the synonym created is for ABCAccount, instead of ABC.Account. For some reason the period is removed.

Any ideas why?

Answer 1

DECLARE
  schema_name VARCHAR2(30) := '&1';
BEGIN
    EXECUTE IMMEDIATE 'CREATE SYNONYM ACCOUNT FOR ' || schema_name || '.ACCOUNT';
END;
/

Answer 2

The period is being treated as the termination of the variable name. It's often optional (e.g. when there's whitespace after the name) but you sometimes have to have it to distinguish between the variable and the next bit of text. Say you actually wanted to end up with the string ABCACCOUNT; if you had &1ACCOUNT it would prompt for a variable called 1ACCOUNT, which isn't what you want. The period tells it where the variable name ends.

In this case you need to have a period to show the end of the variable, and then another one to have it treated as the schema:

CREATE SYNONYM ACCOUNT FOR &1..ACCOUNT;