Home » SQL & PL/SQL » Client Tools » Trigger
Trigger [message #291916] Mon, 07 January 2008 04:03 Go to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
Hi can u anybody tell me how to use the set operator inside a logon trigger

i am writing the code in the below way

create or replace trigger trg_logon_db1
after logon on database
Declare
LvHostName Varchar2(100);

begin
Select Substr(Global_Name,1,5) DBase Into LvHostName From Global_Name;
Set sqlprompt lvhostname;
End;
/

Above trigger is giving the below error

5/2 PL/SQL: SQL Statement ignored
5/6 PL/SQL: ORA-00922: missing or invalid option

Actually what my idea was i want to set the sql prompt to server name on logon to the sql


Can any body suggest me
Re: Trigger [message #291917 is a reply to message #291916] Mon, 07 January 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trigger is executed inside the server.
SET is a SQL*Plus command that executes inside the client and has nothing to do with SQL or DBMS.
You can't do this.

But as you are in 10g, you can put your statement in login.sql script that is executed each time that you connect.

Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

Re: Trigger [message #291938 is a reply to message #291917] Mon, 07 January 2008 05:11 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
thanq Michel but my problem was not solved. I have three servers here. so i want to set the sqlprompt as server host name when i logon to sql. Is there any way to do this. If there is any way plese tell me
Re: Trigger [message #291944 is a reply to message #291938] Mon, 07 January 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to put your query and set sqlprompt in login.sql as I said?

Regards
Michel

[Updated on: Mon, 07 January 2008 05:23]

Report message to a moderator

Re: Trigger [message #291956 is a reply to message #291944] Mon, 07 January 2008 05:32 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
yes i tried that in login.sql is it possible to write the select statements and assign those output in set operator
i tried
Set Sqlprompt (select host_name from v$instance);
Set Sqlprompt Host_Name
but these are not giving solution to my problem.
pls advise me
Re: Trigger [message #291958 is a reply to message #291956] Mon, 07 January 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you post, copy and paste, what you tried instead of describing it?

Quote:

but these are not giving solution to my problem.

What does this give?

Regards
Michel
Re: Trigger [message #291960 is a reply to message #291956] Mon, 07 January 2008 05:39 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
i copy paste the code what i tried
Set Sqlprompt (select host_name from v$instance);
Set Sqlprompt Host_Name
above two statements i used
for the first i got the prompt as select
and for the select i got as Host_Name as prompt
Re: Trigger [message #291964 is a reply to message #291960] Mon, 07 January 2008 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a copy and paste of your session.
A copy and paste is:
SQL> select sysdate from dual;
SYSDATE
-------------------
07/01/2008 12:42:11

1 row selected.

Regards
Michel
Re: Trigger [message #291974 is a reply to message #291964] Mon, 07 January 2008 06:00 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Input truncated to 48 characters
unknown SET option "host_name"
(select

Above i am getting. Please advise me
Re: Trigger [message #291977 is a reply to message #291974] Mon, 07 January 2008 06:05 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try this (login.sql):

COL host NEW_VALUE v_host
SELECT SYS_CONTEXT('USERENV','HOST') AS host FROM dual;
SET SQLPROMPT "&&v_host> "
Re: Trigger [message #291978 is a reply to message #291974] Mon, 07 January 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Then, read http://www.orafaq.com/forum/t/95107/102589/.

Regards
Michel
Re: Trigger [message #291983 is a reply to message #291977] Mon, 07 January 2008 06:12 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
really very sorry for my mistakes.
i am using this forum first time. sorry for the inconvenience.Next time i will take care of the posts.
Re: Trigger [message #291987 is a reply to message #291983] Mon, 07 January 2008 06:35 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
That's the spirit. We all make mistakes, but if we learn from them, we will continue to grow.
Re: Trigger [message #291988 is a reply to message #291983] Mon, 07 January 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, did you now solve this issue?

Regards
Michel
Re: Trigger [message #291989 is a reply to message #291983] Mon, 07 January 2008 06:37 Go to previous message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
yes i got the solution
Thanq very much
Previous Topic: USING THE VALUE &
Next Topic: SQL16W95.DLL General Protection Fault on Win XP
Goto Forum:
  


Current Time: Sat Dec 03 08:23:30 CST 2016

Total time taken to generate the page: 0.25075 seconds