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.
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
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,
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.
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 !
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.
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...
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.
November 19, 2012 - 10:22 am UTC
did you actually read the original answer?
??????????? seriously?