Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jeff.

Asked: October 19, 2000 - 6:17 pm UTC

Last updated: November 19, 2012 - 10:22 am UTC

Version: 8.1.5.0

Viewed 1000+ times

You Asked


I have a login.sql script that changes my sqlprompt whenever I start sqlplus. This script executes fine when I first go into sqlplus, but if I connect to another database while already in sqlplus, my prompt doesn't change. (I know, all I have to do is type @login again, but I'm a lazy DBA).

Is there any way to get this script (or something like it) to execute automatically whenever I connect to a database?

jeffh@dev42$ cat login.sql
set termout off
col user_name new_value user_name
col db_name new_value db_name
select lower(user) user_name,
ltrim(rtrim(lower(global_name))) db_name
from global_name;
set sqlprompt "&user_name@&db_name> "
set termout on
set trimspool on
set trimout on
set serveroutput on

jeffh@dev42$ sqlplus system@ibprod1.us
SQL*Plus: Release 8.1.5.0.0 - Production on Thu Oct 19 17:49:01 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password:

Connected to:

Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

system@thiprod.world> connect sys@ibprod1.us
Connected.

system@thiprod.world>






and Tom said...

Nope, I use a script @connect myself that looks like:


connect &1
@login


It is then a matter of teaching yourself to

SQL> @connect scott/tiger

instead of

SQL> connect scott/tiger



Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Elegant workaround

sanjay raj, October 31, 2001 - 1:50 pm UTC

Thanks. I have been using the same technique for the last few years and always thought that there was a better solution to this. But there U have it - IT WORKS :)

Brilliant, thanks

A reader, November 01, 2001 - 2:00 am UTC


When I issue connect..

Chandra S.Reddy, May 01, 2003 - 6:22 am UTC

Hi Tom,
This is a great solution.
for first time login it is working fine.
When I issue "connect" command from the existing SQL>, then it not changing to new prompt.
As you said,if I create a script with the name @connect, it will not be invoked if we are in some other directory.
So we need to copy the connect.sql file into current working directory.?
Or is there any other solution available for this.
Because, current working directory will be the project related directory and we should not have any junk files in it. 

Tom Kyte
May 01, 2003 - 12:15 pm UTC

set your SQLPATH to a directory (environment variable)....

put all of your scripts in that directory.

now you can run them from anywhere.

Thanks

SS, May 01, 2003 - 5:23 pm UTC

Thanks Tom... I had the same problem.

Service Names!!!!!

A reader, June 30, 2004 - 11:34 am UTC

Hi Tom,
Is there any data dictionary which holds the service names or the host strings.
For example when connecting to a remote host we have to use the connect string
conn user/pwd@host_string
This is the host string I want to be reflected in my SQL prompt. Is this is in any way possible?
Thanks as always.
P.S. I forgot the dictionary which shows the SID of the database.
Thanks again. :D

Tom Kyte
June 30, 2004 - 12:18 pm UTC

the connect string is an "outside of the database thing" -- in fact you don't even need one, it is purely a 'nicety' for end users. It is translated by the client into a string like:


sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dhcp-reston-gen-3-east-122-155.us.oracle.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ora9ir2)))'


your program could do the same if it likes. So, no there is no dictionary table since this is a translation that takes place way before we hit the network from the client side.

v$instance.

10g does it

A reader, July 09, 2004 - 2:09 pm UTC

In 10g, login.sql is re-executed for each connect.

Problem with connect.sql when trying to log on as SYS

Sharvil Desai, April 07, 2006 - 3:32 pm UTC

Tom,

The connect.sql script doesn't work as intended when I try to connect as the user sys, i.e.

scott@prod> @connect sys/password@prod as sysdba
ERROR:
ORA-28009: connection to sys should be as sysdba or sysoper

It works fine if I do the following:
scott@prod> @connect "sys/password@prod as sysdba"

Is there a way to have connect.sql work without the user having use double-quotes?

Thank you for the wealth of knowledge you provide for free.
It's true when they say, "Some of the best things in life are free!"

Regards,

Tom Kyte
April 08, 2006 - 9:37 am UTC

that is not connecting SYS

that is connect "as sysdba"

...


suggest if you don't want the double quotes (the solution) you just use


connect sys/.......
@login


instead

dangerous solution

Asim Naveed, May 04, 2006 - 7:06 am UTC

The @connect solution in 9i is good, but its
dangerous also, if some time i did
CONNECT instead of @connect and then
leave my desk, after some time I come and
beliving on what prompt shows (which is wrong), I
drop or delete something.

Is there any other better solution.

Tom Kyte
May 04, 2006 - 7:52 am UTC

well, I disagree that this is "dangerous". If this is "dangerous" then EVERYTHING is "dangerous".

case in point: rm is dangerous. rm is dangerous because I could type

$ rm -rf /tmp/ *

when I meant to type

$ rm -rf /tmp/*

and so on.

better solution - 10g, upgrade, sqlplus works differently in that release.

Could these connect time commands be run from the server instead ?

Rehan Yusuf, May 24, 2007 - 3:56 am UTC

Hello Tom,

I hope this is the correct place to put this question. I'm using certain third party tools to do some load testing. The query I'm collecting data on, returns rows only when certain session information is set ('CLIENT_INFO' in USERENV). Editor based client like SQLPlus allow me to run a procedure and set this info. but certain others don't provide that option. Is there a server (DB) option that would run a script or a bunch of statements on a session everytime a new session is created ?
We are on Oracle 10g (10.2.0.2.0)

Thanks !
Tom Kyte
May 26, 2007 - 11:00 am UTC

http://asktom.oracle.com/pls/ask/search?p_string=%22create+or+replace+trigger%22+%22after+logon%22


ops$tkyte%ORA10GR2> create or replace trigger logon_trigger
  2  after logon on schema
  3  begin
  4     dbms_application_info.set_client_info( 'hello world ' || systimestamp );
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA10GR2> select sys_context('userenv', 'client_info' ) from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------


ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select sys_context('userenv', 'client_info' ) from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------
hello world 26-MAY-07 11.03.13.018000000 AM -04:00

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select sys_context('userenv', 'client_info' ) from dual;

SYS_CONTEXT('USERENV','CLIENT_INFO')
-------------------------------------------------------------------------------
hello world 26-MAY-07 11.03.13.195954000 AM -04:00

ops$tkyte%ORA10GR2> drop trigger logon_trigger;

Trigger dropped.

how to change SQL prompt

sruthi, September 29, 2009 - 7:05 am UTC

hai friends,
we r using oracle 7.3 database in UNIX environment.
If i use the following statement 
$sqlplus username/password
it simply shows
SQL>
but i want
username> prompt permanently whenever i use to log in into that particular user.
please suggest me.

Tom Kyte
October 02, 2009 - 9:10 am UTC

"r" - what the heck is "r"? Is your keyboard breaking? The vowels are not working all of the time.


if you hit page up a couple of times and read the original question (which is identical to yours) and the original answer - you'll have everything you need.


I don't get it - why doesn't anyone *read* anymore?


note: the answer only applies to oracle 9i and BEFORE, in 10g and later, we run the login.sql every time you connect - you don't need @connect anymore in the 21st century.

Oleksandr Alesinskyy, October 03, 2009 - 1:32 pm UTC

The most fascinating part of the last question was the Oracle version in use - I have thought that 7.3 is dead as dead.

change of sqlprompt as connected username

prasad, January 06, 2012 - 11:42 am UTC

hi,
I tried as u said, i got it, but i got with this the below cmd too,
------------------------------
set sqlprompt '_user> '
------------------------------
on glogin.sql file.

let me know that, is it worth full or not.

thanQ...


Tom Kyte
January 10, 2012 - 9:41 pm UTC

that is the correct way to do it in current versions, yes, _user did not exist in the version this question was asked of.

Changing Prompt

Manohar, November 17, 2012 - 7:44 am UTC

hai friends,
we r using oracle 8i database in Windows XP environment.
If i use the following statement 
$sqlplus username/password
it simply shows
SQL>
but i want
username> prompt permanently whenever i use to log in into that particular user.
please suggest me.

Tom Kyte
November 19, 2012 - 10:22 am UTC

did you actually read the original answer?

??????????? seriously?

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.