?
Solved

Servername

Posted on 2002-04-03
8
Medium Priority
?
1,998 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

765 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