OraFAQ Forum: Client Tools » Trigger

Home » Other » Client Tools » Trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger [message #291916]

Mon, 07 January 2008 04:03

saiphani723
Messages: 38
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

Report message to a moderator

Re: Trigger [message #291917 is a reply to message #291916]

Mon, 07 January 2008 04:16

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger [message #291938 is a reply to message #291917]

Mon, 07 January 2008 05:11

saiphani723
Messages: 38
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

Report message to a moderator

Re: Trigger [message #291944 is a reply to message #291938]

Mon, 07 January 2008 05:22

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

saiphani723
Messages: 38
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

Report message to a moderator

Re: Trigger [message #291958 is a reply to message #291956]

Mon, 07 January 2008 05:35

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger [message #291960 is a reply to message #291956]

Mon, 07 January 2008 05:39

saiphani723
Messages: 38
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

Report message to a moderator

Re: Trigger [message #291964 is a reply to message #291960]

Mon, 07 January 2008 05:42

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger [message #291974 is a reply to message #291964]

Mon, 07 January 2008 06:00

saiphani723
Messages: 38
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

Report message to a moderator

Re: Trigger [message #291977 is a reply to message #291974]

Mon, 07 January 2008 06:05

Frank Naude
Messages: 4587
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> "

Report message to a moderator

Re: Trigger [message #291978 is a reply to message #291974]

Mon, 07 January 2008 06:05

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger [message #291983 is a reply to message #291977]

Mon, 07 January 2008 06:12

saiphani723
Messages: 38
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.

Report message to a moderator

Re: Trigger [message #291987 is a reply to message #291983]

Mon, 07 January 2008 06:35

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

That's the spirit. We all make mistakes, but if we learn from them, we will continue to grow.

Report message to a moderator

Re: Trigger [message #291988 is a reply to message #291983]

Mon, 07 January 2008 06:35

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ok, did you now solve this issue?

Regards
Michel

Report message to a moderator

Re: Trigger [message #291989 is a reply to message #291983]

Mon, 07 January 2008 06:37

saiphani723
Messages: 38
Registered: July 2006
Location: Hyderabad

Member

yes i got the solution
Thanq very much

Report message to a moderator

Previous Topic:	USING THE VALUE &
Next Topic:	SQL16W95.DLL General Protection Fault on Win XP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 03 08:55:16 CST 2025