How can I change the default SQL*Plus directory? 4

[DeepDiverMom]

If the current path that SQL*Plus checks, as a default, to resolve scripts (as in "@<script>") is path "/x/y/z", what change can I make, for my SQL*Plus session only, to use as my new default path, "/a/b/c"?

 

[Thargy]

ddm,

you can't. It's a major bugaboo of sql plus as far as I'm concerned.

My work around is to invoke sqlplus several times in sequence from a batch file. I invoke it in the first folder and then quit at the end.

Then in the batch file I issue CD commands as needed to move to another folder, then do a sqlplus - s (to stop annoying the user) in the new folder to get at files.

Alternatively, if you're in folder a, with subfolder b, wanting to run the file DDM.SQL, inside sqlplus you can say

@B\DDM.SQL;

and by specifying the subfolder in the invocation of the file, you'll nail it.

This inability to elegantly change folders is yet another reason why I want to learn PERL - sign, er, no, I mean SIGH.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Actually, DeepDiver and Tharg, if you invoke SQL*Plus from the directory that you want to be the default, then guess what...That directory is now your default for both reading from, and writing to, for SQL*Plus, sans special commands.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

I obviously wasn't clear. I do massive amounts of sql plus scripting, and I gather functionally associated scripts into a single folder. I usually add some sort of hierarchical structure to make sub-scripts easier to find. One can either repeatedly start and stop sql plus in the desired folder, or do my trick of adding relative path information to each file invocation. However, this effectively hard-codes the file structure into the sql plus scripts, which is a right nuisance.

That's why I don't like the inability to alter the default folder on the fly.

Is that lot as clear as mud?

T

Grinding away at things Oracular

 

[SantaMufasa]

<grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Turkbear]

Hi,
There is a SqlPath entry in the Windows registry that allows you to specify which directory(ies) SqlPlus looks in for scripts..It is like any other PATH statement so multiple locations can be specified separated by semi-colons - SqlPLus checks them in order...

If it is not there ( usually in the base Oracle section of HKLM-Software)you can add it...



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DrD123]

Hi Mom,

You can create a different Unix .profile (still using Linux?) and that can contain the SQL path. Just source the one you want to use. before you run the scripts:

Here's an example, and take note of the SQLPPATH, which is a directory where SQL files and the login.sql reside.

example .profile

MAIL=/usr/mail/${LOGNAME:?}
ORACLE_HOME=/u01/oracle/products/9.2.0
export ORACLE_HOME
ORACLE_BASE=/u01/oracle/products
export ORACLE_BASE
ORACLE_SID=yourdb
export ORACLE_SID
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/ucb:/usr/perl5/bin:$ORACLE_HOME/bin:.
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH
SQLPATH=/export/home/your_name/dbscripts
export SQLPATH
TERM=vt100
export TERM

trap 2 3

bash

example of login.sql which resides in the SQLPATH directory:

define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 999
set pagesize 999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
¦SETTING UP YOUR ENVIRONMENT xxvii
select lower(user) || '@' || substr( global_name, 1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

Good Luck
DrD

 

[DeepDiverMom]

I should have been more explicit about my operating system (besides relying upon the direction of my path slashes..."/"...to signify my using a *nix environment).

All of the suggestions, above, are very clever methods of defining the default path for SQL*Plus to use. As a result, please accept a star for each suggestion, along with my gratitude for assisting me.

Mom