Home » SQL & PL/SQL » SQL & PL/SQL » setting sql prompt using trigger
setting sql prompt using trigger [message #269738] Mon, 24 September 2007 06:32 Go to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
I want view my sql prompt as
user_name@database_name:sql>
instead of sql.

I already did it using sql scripts
define Prompt = "SQL> "
column prompt new_value Prompt

set termout off
select
  user || ' @ ' || instance_name || ':' || 'SQL> '  prompt
from
  sys.v_$instance
/
set termout on
set sqlprompt "&Prompt"


But everytimes i need to execute that and if i switch user from same sql window user_name do not change.
I want to write a trigger after user login oracle using sql*plus and will show my customize prompt.
Can anybody help about that trigger?
Re: setting sql prompt using trigger [message #269740 is a reply to message #269738] Mon, 24 September 2007 06:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Tell us: where does a trigger run?
And where do you want your prompt?

Now tell us, would your request be possible?

Use the "autoexec.bat" of sql: the (g)login.sql
Re: setting sql prompt using trigger [message #269741 is a reply to message #269738] Mon, 24 September 2007 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does it in 10g and up.
Before you have to create a "connect.sql" script to connect and use it to connect instead of directly using the "connect" statement.
The SQL*Plus command to set the prompt is "sqlprompt"...

Regards
Michel
Re: setting sql prompt using trigger [message #269742 is a reply to message #269738] Mon, 24 September 2007 06:47 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

Tell us: where does a trigger run?


Trigger will run after any user login via sql*plus

Quote:

And where do you want your prompt?


I want prompt at SQL*PLus

Quote:

Now tell us, would your request be possible?


It is possible. See the following link
http://www.orafaq.com/forum/m/241201/94420/?srch=dropping+all+the+tables#msg_241201
Quote:

system@DBASE-SQL>>create user g00gle identified by isg00glebroken;

User created.
system@DBASE-SQL>>grant connect, resource to g00gle;

Grant succeeded.

system@DBASE-SQL>>grant all on scott.emp to g00gle;

Grant succeeded.

system@DBASE-SQL>>conn g00gle/isg00glebroken
Connected.
g00gle@DBASE-SQL>>create table nitpicking as select * from scott.emp;

Table created.




Quote:

Use the "autoexec.bat" of sql: the (g)login.sql


then every user need to manually execute it.
Re: setting sql prompt using trigger [message #269757 is a reply to message #269742] Mon, 24 September 2007 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read my answer?

Regards
Michel
Re: setting sql prompt using trigger [message #269775 is a reply to message #269742] Mon, 24 September 2007 08:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Mon, 24 September 2007 13:47
Quote:

Tell us: where does a trigger run?


Trigger will run after any user login via sql*plus

WHERE, not when.

Quote:

Quote:

Use the "autoexec.bat" of sql: the (g)login.sql


then every user need to manually execute it.
No, it is executed automatically. Pre-10g on start of sqlplus, 10g (and after) on each login

[Updated on: Mon, 24 September 2007 08:51]

Report message to a moderator

Previous Topic: count duplicate
Next Topic: Password and Username
Goto Forum:
  


Current Time: Fri Dec 09 19:06:47 CST 2016

Total time taken to generate the page: 0.15488 seconds