@ symbol in script file path

Answer:  To execute a script file in SQLPlus, type @ and then the file name.

SQL >  @{file}

For example, if your file was called script.sql, you'd type the following command at the SQL prompt:

SQL >  @script.sql

The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.)



If you need to execute a script file that is not in the current directory, you would type:

SQL >  @{path}{file}

For example:

SQL >  @/oracle/scripts/script.sql

This command would run a script file called script.sql that was located in the /oracle/scripts directory

just use " around your path as thisexample...

SQL> @"c:\new@file.sql"

it will run "new@file.sql" under c:\

johnsone: That is not option for me at this time but thanks.

shru_0409: Sorry, this doesn't seem to address my questions.

HainKurt:  This doesn't work.  I've already tride this.

Example Again:

SQL> @"c:\temp@\script.sql"
SP2-0310: unable to open file "c:\temp@\script.sql"  


This works:
SQL> @"c:\temp\script.sql"

D
-
X


The contents of the script is "select * from dual".  Although, this is not important.

Please note that I changed the temp path to "temp@" for the first test prior to running the script in sqlplus.

on my c:\drive, i created a temp@ folder on c:\
created new@file.sql in c:\temp@\ folder and this is running fine :

SQL> @"c:\temp@\new@file.sql";

SYSDATE
---------
21-JUL-09

my file includes
select sysdate from dual;

johnsone:

I tried your solution and it does work but I don't have the option to do this at this time.

HainKurt:  What version of Oracle/SQL*Plus are you using?    I'm connecting to a 10g database with the following version of SQLPlus 10.2.0.1.0.  

I've tried what you said just with the path name change and I keep getting the same error.  
See attached picture with my example.  

Something must be different between our configurations.




MyExample.JPG

C:\>sqlplus/?

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jul 21 11:18:45 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

try single quote

SQL> @'c:\temp@\new@file.sql';

I'm not understanding why the quotes don't work either.

What about executing it from a command line?

sqlplus user/pass@db @"C:\TEMP@\SCRIPT.SQL"

Or what about a different approach from within SQL*Plus:

GET "C:\TEMP@\SCRIPT.SQL"
/

try this

SQL> set scan off;
SQL> @'c:\temp@\new@file.sql';

SYSDATE
---------
21-JUL-09

what about this...

create a test.sql under root: and put this into the file

@'c:\temp@\new@file.sql';

and run

SQL> @c:\test.sql;

SYSDATE
---------
21-JUL-09

are you connecting to a remote database?
the file must be on the server, not on the client ...

just my 2 cents.

or can you use this?

subst q: c:\temp@
sql plus .....

sql> @q:\script.sql
...

exit;

subst /d q:

angelIII,

Do you mean the file must be on the server in order to use the double quotes?  To answer your question the database is remote.

The quotes work for me but not when using the @ symbol in the directory name.  

I just created a directory called "c:\temp 1" and placed the file script.sql into and ran the following command successfully:

SQL> @"c:\temp 1\script.sql"

HainKurt,

Thanks for all your suggestions.  I don't know why the quoting doesn't work.  I think I'll leave this problem be for now.

johnsone,  

Thank you too.

Excellent solution but didn't work for me for some unknown reason.  

or can you use this?

set scr_path = c:\temp@
sql plus .....

sql> @%scr_path%\script.sql
...

exit;

please try this version (I found something that seems to indicate it should help):
double quotes, plus single quotes around the @

@"c:\temp'@'newfile.sql";

Select allOpen in new window

angelIII:

I tried it but it didnt work.  See below:

C:\>dir c:\temp@\script.sql
 Volume in drive C has no label.
 Volume Serial Number is 8CE8-3319

 Directory of c:\temp@

07/21/2009  10:23 AM                21 script.sql
               1 File(s)             21 bytes
               0 Dir(s)  134,619,398,144 bytes free

C:\>sqlplus readonly/password@dev1 @"c:\temp'@'\script.sql"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 21 20:49:31 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SP2-0310: unable to open file "c:\temp'@'\script.sql"
SQL> @"c:\temp'@'\script.sql";
SP2-0310: unable to open file "c:\temp'@'\script.sql"
SQL>