Solved

Servername

Posted on 2002-04-03
8
1,982 Views
Last Modified: 2007-11-27
I have some old Procedures converting from Sybase to Oracle.

One issue that has arisen is the use of @@servername.  If there an equivalent value in Oracle.  I ahve looked but can't seem to locate the instance.

The procs execute differently depending on the server they are runnin on (Test or Production).

Any insights would be great.

Thanks
0
Comment
Question by:valiant_at_mci
8 Comments
 
LVL 4

Accepted Solution

by:
asimkovsky earned 50 total points
ID: 6915573
I'm not sure of how the syntax works in Sybase, but if you have a stored procedure that has to perform a specific way based on the server name (I assume you mean host name of the box), then you can pull host_name from V$INSTANCE and perform some action based on that.

DECLARE
vHost VARCHAR2(64);
BEGIN
SELECT host_name INTO vHost
FROM V$INSTANCE;

IF (host_name = 'PROD01') THEN
   do something here...
ELSE IF (host_name = 'DEV01') THEN
   do something else here
END IF;

END
/

Andrew
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6915583
valiant_at_mci,
   There's no pseudo column for the servername, although you can assign it in your script.  The value is in global_name.  Try:

select global_name from global_name;

to assign it to a variable in SQL*Plus:

column global_name new_value servername
select global_name from global_name;

then you can use "servername"

Good luck!
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 6915696
select global_name from global_name;

this will give you the name of the database is that what @@servername means in sybase?

0
 

Author Comment

by:valiant_at_mci
ID: 6915849
Pretty much.

@@Servername is a global variable that exists within the DB environment.

Thus if I were running a proc on DBTest01 and there were conditional statements within the proc (Psuedo):

if @@servername = 'DBTest01' Then
   Else
      @@servername = 'DBProd01' Then
   ELSE
      ERROR
End IF
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
ID: 6915918
valiant_at_mci,
  You could add this to the beginning of each script (in sql*plus):

def okserver = '''DBTest01'''

select case when global_name = &&okserver then 'SHOW ALL'
       else 'EXIT' end

set head off
set verify off
spool error_exit.sql
/
spool off
start error_exit
set head on
set verify off


Which would cause your scripts to exit if they're being run on the wrong server.  Obviously, you could switch this to have the case statement specifically check for multiple server instead of having only one that is "ok".

just thought I'd throw that out there.

Good luck!
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6916018
DrSQL,

I think you're missing a FROM clause in your SELECT CASE... statement.


Andrew
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6916047
Andrew,

    Thanks, the Oracle parser has trouble reading my mind, too.


valiant_at_mci,

I meant:

select case when global_name = &&okserver then 'SHOW ALL'
      else 'EXIT' end
  from global_name;

Good luck!
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6916053
valiant_at_mci,
  Could you please post a comment explaining why Andrew's answer was a "B"?  It helps those reviewing this question in the knowledge base.  It seemed to me that his answer was certainly complete enough, and others offered useful info - but most people searching the knowledge base won't even bother to look at an answer with a "B" grade.  If this was just an accidental, default grade, a moderator can fix it for you.  Just go to the Customer Support arean and post a 0 point question referencing the URL for this question.

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now