SQL Plus change current directory

Question

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.

---

Resolution

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.

Answer 1

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/user.

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!)

Answer 2

Have you tried creating a windows shortcut for sql plus and set the working directory?

Answer 3

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 @@.

Answer 4

Could you use the SQLPATH environment variable to tell sqlplus where to look for the scripts you are trying to run? I believe you could use HOST to set SQLPATH in the script too.

There could potentially be problems if two scripts have the same name and both directories are in the SQLPATH.

Answer 5

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).