SQL Plus change current directory

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

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

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.