Re: sqlplus prompt

On Thu, 01 May 1997 10:34:40 +0000, Chrysalis <cellis_at_iol.ie> wrote:

>Rich Headrick wrote:
>>
>> How does one change the default sqplus prompt to be = to current SID?
>>
>> Please email.
>>
>> rich
>
>The basic idea is to get the string you want into a substitution (&)
>variable and then:
> set sqlprompt "&string"
>
>For example:
>
>column sq new_value sqp;
>select userenv('sessionid')||'> ' sq from dual;
>set sqlprompt "&sqp" -- the quotes are necessary to preserve the
>trailing space
>
>Now put this in your login.sql command file to be executed each time you
>start a SQL*Plus session.
>
>All you now have to do is to work out a way of automatically
>re-executing the login.sql file whenever the user changes session with a
>"connect ..." statement.
>I have no idea how you might do this :-(
>
>Chrysalis.

You could create a couple of script files to be stored in a common area on the LAN in which you simply execute the scripts to do the reconnect. For instance, you could name the files cdev, cqa and cprod (for connect to dev, connect to qa and connect to prod) and these files could
1. Issue a connect to the new database. 2. Do the new_value thing and set the prompt.

If you didn't want to hard-code the passwords in these files, you could use a substitution variable which would prompt the user for the password and then proceed to connect.

Problem with this method is that it puts the sql statement select userenv('sessionid')||'> ' sq from dual in the sql buffer. Somtimes, when I switch databases, I prefer to keep the sql statement in the buffer. For instance, if I'm comparing the constraints from one database to another, I might issue a "select constraint_name,search_condition from user_constraints where table_name = 'mytable' " in the dev database, look at the results, then connect to the qa database and issue the same command by simply typing in a "/". You will lose this from the buffer with the above method. It just depends on what works best for

you...................Jean