CODEWITHSUNDEEP
sqldatabaseoracle-database
User
User

Reputation: 211

oracle sql - using double ampersand (&&) and double dot (..)

I'm new to Oracle SQL. I came across the following statement:

CREATE TABLE &&DATABASE_ONE..TABLE_ONE(...);

The statement above is trying to create a table. I'm not sure if I have correct understanding on .. and &&.

I have done some Googling, I found out this link that says double dot(..) will choose the default schema for the query.
DATABASE_ONE should be the schema name, and TABLE_ONE should be the table name.

I'm confused of using && in front of the database name. After Googling, I found out some posts say that && means "Oracle will prompt the user for a value and will create a permanent variable".

Could someone explain how && works in the statement above? Please correct me if I have any misunderstanding.

Upvotes: 1

Views: 10295

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

@Gary_W has covered the difference between a single and a double ampersand.

The double period is perhaps slightly less obvious. From the SQL*Plus documentation:

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character.

If you had a single period:

&&DATABASE_ONE.TABLE_ONE

then that period would be treated as the terminator for the substituion variable name, and would be consumed in the process. Say the value you entered was 'HR'; the substitution would be:

old:select &&DATABASE_ONE.TABLE_ONE from dual
new:select HRTABLE_ONE from dual

select HRTABLE_ONE from dual

As you can see, there is now no period between the schema and table names, giving you a combined identifier which will not represent what you intended.

With the form you have:

&&DATABASE_ONE..TABLE_ONE

the second period is the 'normal' one that sits between those two elements; once the first has been consumed by the substitution, the second remains to fulfil that function:

old:select &&DATABASE_ONE..TABLE_ONE from dual
new:select HR.TABLE_ONE from dual

select HR.TABLE_ONE from dual

Upvotes: 5

Gary_W
Gary_W

Reputation: 10360

The double ampersand will prompt once and everywhere that variable is used it will use what was entered. If it had only one ampersand, you would get a prompt for every occurrence.

Try it yourself by creating a dummy script with multiple prompts for the same variable. First use single ampersands, note how it works then switch to double ampersands.

This link has a good explanation: https://www.oreilly.com/library/view/oracle-sqlplus-the/1565925785/ch04s02.html

Upvotes: 2

Related Questions