[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Servername

Posted on 2002-04-03
8
Medium Priority
?
2,001 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 150 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

864 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