@ symbol in script file path

ciphersol
ciphersol used Ask the Experts™
on
Is there anyway to execute a script where the file path has an @ symbol.

Example:

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

Double quotes around the path do not work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA
Commented:
Have you tried setting the SQLPATH environment variable to include c:\temp@\

SQLPATH is a path that SQL*Plus uses to look for scripts besides the current directory.  If you commonly run scripts from a specific directory, it is very handy to add it to this path and not have to type it in all the time.
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
Sr. System Analyst
Commented:
SQL> @"c:\new@file.sql"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
just use " around your path as thisexample...

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

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

Author

Commented:
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.

Author

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


HainKurtSr. System Analyst

Commented:
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;

Author

Commented:
johnsone:

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

Author

Commented:
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
HainKurtSr. System Analyst

Commented:
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.
HainKurtSr. System Analyst

Commented:
try single quote

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

johnsoneSenior Oracle DBA

Commented:
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"
/

HainKurtSr. System Analyst

Commented:
try this

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

SYSDATE
---------
21-JUL-09
HainKurtSr. System Analyst

Commented:
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

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

just my 2 cents.
HainKurtSr. System Analyst

Commented:
or can you use this?

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

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

exit;

subst /d q:

Author

Commented:
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"


Author

Commented:
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.


Author

Commented:
Excellent solution but didn't work for me for some unknown reason.  
HainKurtSr. System Analyst

Commented:
or can you use this?

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

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

exit;
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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";

Open in new window

Author

Commented:
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>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial