Changing directory in SQL PLUS

[skdas]

1. It is in NT environment.

2. I am running one SQL script S1.sql in the directory D1.

3. Inside the SQL S1.sql I want to change the directory to D2 and run all the sqls in the directory D2.

4. Is it possible to change directory when I am in SQL> prompt in NT environment...?

[stecal]

@c:\new_directory\file_name or
start c:\new_directory\file_name
are two ways to run a file NOT in your startup directory.
Once you start in a directory, it stays as the default.

If the path name is long, I'd change the start location property of the desktop shortcut, then all you have to do is enter "@file_name"

You can verify the start location by typing host at the SQL> prompt. You should get an MS-DOS shell window with the cwd shown.

[skdas]

1. Acutually I have to move to differnet directories and run multiple sqlfiles.

2. For example I move to new_directory1 and run so many files. Again go to new_directory2 and run so many files.

3. I do not want to give the complete path of the files.
Like I donot want to give.

@c:\new_directry1\s1.sql
@c:\new_directry1\s2.sql
@c:\new_directry2\s3.sql
@c:\new_directry2\s4.sql

4. Is there any way..?

[stecal]

Is this a one-time thing?

Yes) Suck it up and do a lot of file system navigation/typing

No) Turn the .sql files into batch commands, navigate to them via Windows Explorer, and double-click/right-click Open on them (which runs them).

Do the files have to stay in separate directories? If so, why/what possible reason would anyone do that?

[stecal]

Another way is to write a SQL script that calls the other SQL files in their various directories. If you don't need to run every file, comment out the line, then run the master SQL script file. Write once, do many, Grasshopper.

[skdas]

1. My requiremnet is the SQL files are in differnet directories.

2. Different development groups add thier SQLs to diffrenet directory.

3. Without giving full path, I just go to differnet directory, run the master script in which all the script names are there. There is no fullpath for each of the file.

4. It has to be run again and again.

[stecal]

Originally posted by skdas
3. Without giving full path, I just go to differnet directory, run the master script in which all the script names are there. There is no fullpath for each of the file.

No, you go to where the master SQL file is, and EVERY file has an absolute path. You update the master file with the SQL files to be run.

[pando]

look SQLPATH variable

[mla_ca520]

If you generally run these sql scripts form one machine, you could alter your registry for SQLPATH:

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\(your oracle home)
open the SQLPATH and enter the folder locations, separated by a semicolon:
example - "c:\new_directry1;c:\new_directry2;c:\new_directry3;c:\new_directry4"

after updating your SQLPATH key, you will be able to type "@(name of sql script).sql" from sqlplus prompt and it will iterate through the directories in the registry path and run the first script it finds with that name.

[Steve R Jones]

mla_ca520 - thank you for posting.... Please note that this thread is Eight Years old...