How does one change the current directory in SQL Plus under windows.
I am trying to write a script with several "@ filename" commands.
I know that one can open a script with the File --> Open command, which will change the current directory, but I am looking for a way to do this automatically unattended.
Based on Plasmer's response, I set the SQLPATH environment variable in Windows, and got something that's good enough for me. I did not try to set it with the HOST command (I doubt that it will work).
Pourquoi Litytestdata's answer is a good one, but will not work for me (the directories are too far apart). And of course Guy's answer that it cannot be done is also correct. I will vote these two up, and accept Plasmer's answer.
I don't think you can!
/home/export/user1 $ sqlplus /
> @script1.sql
> HOST CD /home/export/user2
> @script2.sql
script2.sql has to be in /home/export/user1.
You either use the full path, or exit the script and start sqlplus again from the right directory.
#!/bin/bash
oraenv .
cd /home/export/user1
sqlplus / @script1.sql
cd /home/export/user2
sqlplus / @script2.sql
(something like that - doing this from memory!)
Have you tried creating a windows shortcut for sql plus and set the working directory?
I don't think that you can change the directory in SQL*Plus.
Instead of changing directory, you can use @@filename, which reads in another script whose location is relative to the directory the current script is running in. For example, if you have two scripts
C:\Foo\Bar\script1.sql C:\Foo\Bar\Baz\script2.sql
then script1.sql can run script2.sql if it contains the line
@@Baz\script2.sql
See this for more info about @@.
Have you tried creating a windows shortcut for sql plus and set the working directory?
I think that the SQLPATH environment variable is the best way for this - if you have multiple paths, enter them separated by semi-colons (;). Keep in mind that if there are script files named the same in among the directories, the first one encountered (by order the paths are entered) will be executed, the second one will be ignored.
I don't think you can!
/home/export/user1 $ sqlplus /
> @script1.sql
> HOST CD /home/export/user2
> @script2.sql
script2.sql has to be in /home/export/user1.
You either use the full path, or exit the script and start sqlplus again from the right directory.
#!/bin/bash
oraenv .
cd /home/export/user1
sqlplus / @script1.sql
cd /home/export/user2
sqlplus / @script2.sql
(something like that - doing this from memory!)
I think that the SQLPATH environment variable is the best way for this - if you have multiple paths, enter them separated by semi-colons (;). Keep in mind that if there are script files named the same in among the directories, the first one encountered (by order the paths are entered) will be executed, the second one will be ignored.
I don't think that you can change the directory in SQL*Plus.
Instead of changing directory, you can use @@filename, which reads in another script whose location is relative to the directory the current script is running in. For example, if you have two scripts
C:\Foo\Bar\script1.sql C:\Foo\Bar\Baz\script2.sql
then script1.sql can run script2.sql if it contains the line
@@Baz\script2.sql
See this for more info about @@.
Have you tried creating a windows shortcut for sql plus and set the working directory?
With Oracle's new SQLcl there is a cd command now and accompanying pwd. SQLcl can be downloaded here: http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html
Here's a quick example:
SQL>pwd
/Users/klrice/
NOT_SAFE>!ls *.sql
db_awr.sql emp.sql img.sql jeff.sql orclcode.sql test.sql
db_info.sql fn.sql iot.sql login.sql rmoug.sql
SQL>cd sql
SQL>!ls *.sql
003.sql demo_worksheet_name.sql poll_so_stats.sql
1.sql dual.sql print_updates.sql
SQL>
Here is what I do.
Define a variable to help you out:
define dir=C:\MySYSTEM\PTR190\Tests\Test1
@&dir\myTest1.sql
You can't cd in SQL*Plus (you can cd using the host command, but since it is a child process, the setting won't persist in your parent process).
Years later i had the same problem. My solution is the creation of a temporary batchfile and another instance of sqlplus:
In first SQL-Script:
:
set echo off
spool sqlsub_tmp.bat
prompt cd /D D:\some\dir
prompt sqlplus user/passwd@tnsname @second_script.sql
spool off
host sqlsub_tmp.bat
host del sqlsub_tmp.bat
:
Note that "second_script.sql" needs an "exit" statement at end if you want to return to the first one..
With Oracle's new SQLcl there is a cd command now and accompanying pwd. SQLcl can be downloaded here: http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html
Here's a quick example:
SQL>pwd
/Users/klrice/
NOT_SAFE>!ls *.sql
db_awr.sql emp.sql img.sql jeff.sql orclcode.sql test.sql
db_info.sql fn.sql iot.sql login.sql rmoug.sql
SQL>cd sql
SQL>!ls *.sql
003.sql demo_worksheet_name.sql poll_so_stats.sql
1.sql dual.sql print_updates.sql
SQL>
Years later i had the same problem. My solution is the creation of a temporary batchfile and another instance of sqlplus:
In first SQL-Script:
:
set echo off
spool sqlsub_tmp.bat
prompt cd /D D:\some\dir
prompt sqlplus user/passwd@tnsname @second_script.sql
spool off
host sqlsub_tmp.bat
host del sqlsub_tmp.bat
:
Note that "second_script.sql" needs an "exit" statement at end if you want to return to the first one..
for me shelling-out does the job because it gives you possibility to run [a|any] command on the shell:
http://www.dba-oracle.com/t_display_current_directory_sqlplus.htm
in short see the current directory:
!pwd
change it
!cd /path/you/want
Have you tried creating a windows shortcut for sql plus and set the working directory?
I don't think you can!
/home/export/user1 $ sqlplus /
> @script1.sql
> HOST CD /home/export/user2
> @script2.sql
script2.sql has to be in /home/export/user1.
You either use the full path, or exit the script and start sqlplus again from the right directory.
#!/bin/bash
oraenv .
cd /home/export/user1
sqlplus / @script1.sql
cd /home/export/user2
sqlplus / @script2.sql
(something like that - doing this from memory!)
for me shelling-out does the job because it gives you possibility to run [a|any] command on the shell:
http://www.dba-oracle.com/t_display_current_directory_sqlplus.htm
in short see the current directory:
!pwd
change it
!cd /path/you/want
Source: Stackoverflow.com