Solved

Servername

Posted on 2002-04-03
8
1,990 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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