What is the difference between @ and @@ in Sql*plus?

From Sql*plus we can execute any script with START command. @ is similar to START. If you write SQL>@file.txt then the contents inside file.txt will be executed.
The command @@ is almost identical to the @ ("at" sign) command.

The only difference is @ is used to executed a script on the current location. And @@ is used to execute a script from within a script in the same directory as the script resides.

When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script.

When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory or from the same url as the script from which it was called.

I will make more clear with an example.

I created two scripts. script1.sql and script2.sql. Script1 contains desc test; and script2 contains select * from test. Default location is /export/home/oracle.
A)Create Two Scripts.
SQL> !pwd
/export/home/oracle

SQL> !vi script1.sql
desc test;

SQL> !vi script2.sql
select * from test;
B)Execute Scripts by @ and @@
SQL> @script2
A
----------
1
2

SQL> @@script1
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

At this stage @ and @@ works in the same way.

C)Now create a new script script3 which contains script2 and script1
SQL> !vi script3.sql
@script2

@@script1

SQL> @script3
A
----------
1
2
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
So it works . Edit script3 now and run then.
SQL> !vi script3.sql
@@script2

@@script1
SQL> @script3
A
----------
1
2

Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

So there is no difference between @ and @@ up to this moment.

D)Move script3 to another location.
SQL> !mv script3.sql /oradata2/script3.sql
Now execute.
SQL> @ /oradata2/script3.sql
SP2-0310: unable to open file "/oradata2/script2.sql"
SP2-0310: unable to open file "/oradata2/script1.sql"

Since I have moved script 3 to /oradata2/script3.sql and I run so @@ search the location as the location of script3.sql but did not found.

Now edit script3 and put@ replacing @@

SQL> !vi /oradata2/script3.sql
@script2

@script1
Now execute.
SQL> @ /oradata2/script3.sql
A
----------
1
2
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER

And we see it executed fine. As @ searches file script1 and script 2 to present working directory not to the location where script script3 resides.

Related Documents:
-------------------------