Working directory in SQL*Plus.

[Paul]

Hi all,


I am working my way through "Beginning Oracle Programming" by Dillon,
Beck, Kyte & al.

Now, around p. 139, they start to introduce the idea of running scripts,
which is all fine and dandy, so they give an example of a script and say
that if you place this *_IN YOUR WORKING DIRECTORY_*, it will run thus

SQL> connect hr/hr
SQL> @myscript

so, I tried putting the file into C:\Documents and Settings\Paul and
also C:\Documents and Settings\Paul\My Documents, which I thought were
the two standard windows working dirs. But it can't find them. If I
include @'C:\Documents and Settings\Paul\myscript.sql' it works fine,
but I don't want to have to specify the path all the time.

Could someone tell me where I have to put the script so that it will
work with simply a @myscript? Using W2000 Pro, Oracle 9.2.0.1.0.


TIA.


Paul...


--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com

[Ana C. Dent]

Paul wrote:
Hi all, I am working my way through "Beginning Oracle Programming" by Dillon, Beck, Kyte & al. Now, around p. 139, they start to introduce the idea of running scripts, which is all fine and dandy, so they give an example of a script and say that if you place this *_IN YOUR WORKING DIRECTORY_*, it will run thus SQL> connect hr/hr SQL> @myscript so, I tried putting the file into C:\Documents and Settings\Paul and also C:\Documents and Settings\Paul\My Documents, which I thought were the two standard windows working dirs. But it can't find them. If I include @'C:\Documents and Settings\Paul\myscript.sql' it works fine, but I don't want to have to specify the path all the time. Could someone tell me where I have to put the script so that it will work with simply a @myscript? Using W2000 Pro, Oracle 9.2.0.1.0. TIA. Paul...

IIRC, the concept of "working directory" comes from *nix.
You can see your current directory by issuing the "pwd" command;
which Prints Working Directory; which is the directory into
which you last cd'ed into.

Also if setup correctly SQL*Plus will search the folders
which are enumerated by the SQL_PATH environmental variable.

[Paul]

anacedent@hotmail.com says...
IIRC, the concept of "working directory" comes from *nix.


Yes, and that's fine, but as I said, I'm using windows. I do know a bit
of Unix/Linux, but I'm not working under that at the moment. I plan to
later.

You can see your current directory by issuing the "pwd" command; which Prints Working Directory; which is the directory into which you last cd'ed into.


But, under windows at least, if I do 'pwd' under SQL*Plus, there is no
pwd parameter. 'show pwd' doesn't work either.

Also if setup correctly SQL*Plus will search the folders which are enumerated by the SQL_PATH environmental variable.

I couldn't find that either under windows. I issued the 'show
parameters;' command which gave me lots of stuff, but nothing of any
help from what you've offered.

Thanks anyway for your input.


Paul...

--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com

[Sybrand Bakker]

On Sun, 6 Jun 2004 23:01:26 +0100, Paul <paul@not.a.chance.ie> wrote:
anacedent@hotmail.com says... IIRC, the concept of "working directory" comes from *nix.Yes, and that's fine, but as I said, I'm using windows. I do know a bitof Unix/Linux, but I'm not working under that at the moment. I plan tolater. You can see your current directory by issuing the "pwd" command; which Prints Working Directory; which is the directory into which you last cd'ed into.But, under windows at least, if I do 'pwd' under SQL*Plus, there is nopwd parameter. 'show pwd' doesn't work either.

is a command line tool, and doesn't exist in Windows. Just type

host cd
Also if setup correctly SQL*Plus will search the folders which are enumerated by the SQL_PATH environmental variable.I couldn't find that either under windows. I issued the 'showparameters;' command which gave me lots of stuff, but nothing of anyhelp from what you've offered.

either create this as a string value in the registry under
hkey\local_machine\software\oracle\<yourhome>
or set this up as an environment variable on the My computer
properties (right click my computer, click properties, click the
advanced tab, choose environment variables)
Alternatively, you may want to create a shortcut on your desktop, with
the correct working directory as the working directory of the
shortcut.Thanks anyway for your input.Paul...

It looks like you may want to improve on your Windows skills


--
Sybrand Bakker, Senior Oracle DBA

[Paul]

gooiditweg@nospam.demon.nl says...

But, under windows at least, if I do 'pwd' under SQL*Plus, there is nopwd parameter. 'show pwd' doesn't work either.
is a command line tool, and doesn't exist in Windows. Just type host cd


This shows 'C:\' if I type it while running sqlplus in a dos window, but
under the GUI, the screen just flashes (presumably, the dos window
springs up and is closed again immediately). Doing just 'host' on its
own, puts you into a dos window where you can issue host commands and
see the output (however, see below).


The script does indeed work if I put it in 'C:\', but as you can
imagine, that's not something that I want to be doing.

I couldn't find that either under windows. I issued the 'showparameters;' command which gave me lots of stuff, but nothing of anyhelp from what you've offered.
either create this as a string value in the registry under hkey\local_machine\software\oracle\<yourhome>


Set up what exactly in there? I tried putting in a variable 'HOME' and
gave it the value 'G:\Paul', and put the script in there, but it didn't
work - even after rebooting.

or set this up as an environment variable on the My computer properties


I already have a variable 'HOME' = 'C:\Documents and Settings\Paul' in
there - which is why I was surprised that the script didn't work when I
put it in there (see original post). What should it be called?

Alternatively, you may want to create a shortcut on your desktop, with the correct working directory as the working directory of the shortcut.


OK - that worked. I now have a shortcut on my desktop which will run the
script automatically from G:\Paul.

That's great, and thank you for your input.


Now, I have one final question - I like to use many rather than few
directories to organise my scripts &c., and would like to organise them,
say in Paul\C1, Paul:\C2 &c. for chapters 1, 2 &c...

How do I tell SQL*Plus to move between directories? I've tried 'host cd
g:\Paul\c1' (to where I removed the script), but when I exit the dos
window and go back to the gui tool, it appears that it is still stuck in
g:\Paul. host dir still gives me g:\Paul and the script fails. It also
fails if I use sqlplus from the dos window.

It looks like you may want to improve on your Windows skills


I don't know if my Windows skills are that bad 8-). I'm on my own here
and, as I said, going through this book. Now, I can always reinstall if
I mess up catastrophically, but that takes a long time. For example, I
assumed that my 'HOME' variable under Windows would be the default one
for the tool, and was surprised to learn that it was 'C:\', which I
can't for the life of me understand.


Thanks for your help so far.


Paul...


--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com

[Frank van Bortel]

Paul wrote:
gooiditweg@nospam.demon.nl says...But, under windows at least, if I do 'pwd' under SQL*Plus, there is nopwd parameter. 'show pwd' doesn't work either.is a command line tool, and doesn't exist in Windows. Just typehost cd This shows 'C:\' if I type it while running sqlplus in a dos window, but under the GUI, the screen just flashes (presumably, the dos window springs up and is closed again immediately). Doing just 'host' on its own, puts you into a dos window where you can issue host commands and see the output (however, see below). The script does indeed work if I put it in 'C:\', but as you can imagine, that's not something that I want to be doing.I couldn't find that either under windows. I issued the 'showparameters;' command which gave me lots of stuff, but nothing of anyhelp from what you've offered.either create this as a string value in the registry underhkey\local_machine\software\oracle\<yourhome> Set up what exactly in there? I tried putting in a variable 'HOME' and gave it the value 'G:\Paul', and put the script in there, but it didn't work - even after rebooting.or set this up as an environment variable on the My computerproperties I already have a variable 'HOME' = 'C:\Documents and Settings\Paul' in there - which is why I was surprised that the script didn't work when I put it in there (see original post). What should it be called?Alternatively, you may want to create a shortcut on your desktop, withthe correct working directory as the working directory of theshortcut. OK - that worked. I now have a shortcut on my desktop which will run the script automatically from G:\Paul. That's great, and thank you for your input. Now, I have one final question - I like to use many rather than few directories to organise my scripts &c., and would like to organise them, say in Paul\C1, Paul:\C2 &c. for chapters 1, 2 &c... How do I tell SQL*Plus to move between directories? I've tried 'host cd g:\Paul\c1' (to where I removed the script), but when I exit the dos window and go back to the gui tool, it appears that it is still stuck in g:\Paul. host dir still gives me g:\Paul and the script fails. It also fails if I use sqlplus from the dos window.It looks like you may want to improve on your Windows skills I don't know if my Windows skills are that bad 8-). I'm on my own here and, as I said, going through this book. Now, I can always reinstall if I mess up catastrophically, but that takes a long time. For example, I assumed that my 'HOME' variable under Windows would be the default one for the tool, and was surprised to learn that it was 'C:\', which I can't for the life of me understand. Thanks for your help so far. Paul...
Bottom line:
SQL*Plus (GUI and cl version) looks for scripts in:
1) the current directory ("Start in" property of a
GUI tool, or the directory the command line (cl)
version was started in.
2) Any directory, mentioned in the SQLPATH variable.
On Windows, this is a registry entry - you may
set it in your environment (e.g. C:\>set sqlpath=c:\paul\c1)
which takes precedence over the registry value, or
change your registry
Concatenate the std Windows way, using ";":
set sqlpath=c:\paul\c1;c:\paul\c2;c:\work\sql etc

The above is ordered, that is look in 1) first, when
not found, then 2)
--

Regards,
Frank van Bortel

[Paul]

fvanbortel@netscape.net says...

How do I tell SQL*Plus to move between directories? I've tried 'host cd g:\Paul\c1' (to where I removed the script), but when I exit the dos window and go back to the gui tool, it appears that it is still stuck in g:\Paul. host dir still gives me g:\Paul and the script fails. It also fails if I use sqlplus from the dos window.

Bottom line:
Concatenate the std Windows way, using ";": set sqlpath=c:\paul\c1;c:\paul\c2;c:\work\sql etc
The above is ordered, that is look in 1) first, when not found, then 2)


OK, so (within SQL*Plus, either by issuing a 'host cd MyNewWorkingDir'
or some other method) it is not possible to change directories, and have
'.', as your default working directory, and then be able to go down the
line of directories in your SQLPATH variable?

If this is the case, I think that it should be changed.

Paul...

--
plinehan y_a_h_o_o and d_o_t com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.

"XML avoids the fundamental question of what we should do,
by focusing entirely on how we should do it."

quote from http://www.metatorial.com

[Frank van Bortel]

Paul wrote:
fvanbortel@netscape.net says...How do I tell SQL*Plus to move between directories? I've tried 'host cdg:\Paul\c1' (to where I removed the script), but when I exit the doswindow and go back to the gui tool, it appears that it is still stuck ing:\Paul. host dir still gives me g:\Paul and the script fails. It alsofails if I use sqlplus from the dos window.Bottom line:Concatenate the std Windows way, using ";":set sqlpath=c:\paul\c1;c:\paul\c2;c:\work\sql etcThe above is ordered, that is look in 1) first, whennot found, then 2) OK, so (within SQL*Plus, either by issuing a 'host cd MyNewWorkingDir' or some other method) it is not possible to change directories, and have '.', as your default working directory, and then be able to go down the line of directories in your SQLPATH variable? If this is the case, I think that it should be changed. Paul...

Don't quite understand what you're getting at, but:
<quote> either by issuing a 'host cd MyNewWorkingDir' or some other method </quote>
so you changed your current working directory... Yes,
you can do that.

<quote> and then be able to go down the line of directories </quote>
errh... if you try to run my_proc (@myproc), and
myproc.sql is NOT in the current directory,
then SQLPATH will be traversed.
If that's what you mean

--

Regards,
Frank van Bortel

[Igor Racic]

> IIRC, the concept of "working directory" comes from *nix. You can see your current directory by issuing the "pwd" command; which Prints Working Directory; which is the directory into which you last cd'ed into. Also if setup correctly SQL*Plus will search the folders which are enumerated by the SQL_PATH environmental variable.


I think it should be SQLPATH

Igor

[Tony Dare]

Igor is correct: See Metalink Note 199925.1 for confirmation.

TD

Igor Racic wrote: IIRC, the concept of "working directory" comes from *nix. You can see your current directory by issuing the "pwd" command; which Prints Working Directory; which is the directory into which you last cd'ed into. Also if setup correctly SQL*Plus will search the folders which are enumerated by the SQL_PATH environmental variable. I think it should be SQLPATH Igor