Solved

Servername

Posted on 2002-04-03
8
1,992 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 80
oracle DR - data guard failover. 18 73
subtr returning incorrect value 8 93
return value based on substr 10 71
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

737 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