Solved

How to prepend a server name and db name before my sp call withing another stored procedure

Posted on 2016-11-21
6
31 Views
Last Modified: 2016-11-27
I am calling a stored procedure from within another stored procedure. In Oracle this can be done on line
8 below. However I have a requirement where I must precede the stored procedure name with the server name and
database name. I am trying to do this on line 29 and 30, however I will get an error when I try to compile
the stored procedure. How can I modify my stored procedure call on line 29 and 30 so that I can prepend the
server name and database name? Note that all of my code is not shown below. I am just showing a basic example.

1 procNameWithOtherInfo varchar2(100);
2 serverName varchar2(100);
3 DBName varchar2(100);

4 param1 varchar2(100);
5 param2 varchar2(100);
6 param3 varchar2(100);
7 param4 varchar2(100);



28  getTeamTotals(param1,param2,param3,param4);

29 procNameWithOtherInfo := serverName || DBName || 'getTeamTotals';

30   procNameWithOtherInfo(param1,param2,param3,param4);
0
Comment
Question by:brgdotnet
6 Comments
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 41896963
by using execute immediate and a varchar2
http://docs.oracle.com/database/121/LNPLS/executeimmediate_statement.htm#LNPLS01317

stmt := serverName || DBName || 'getTeamTotals(:param1, :param2, :param3, :param4')';
execute immediate stmt using in param1, param2, param3, param4;

Open in new window


that doesn't mean a procedure will be executed on server X in database Y
for that you need to setup a dblink from within this database to database Y
with an tns entry in the tnsnames of the oracle of this database
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41897362
Just out of curiosity, why?

When you move your code from development to test to production, every move is going to require renaming all the objects.  That really isn't a good change management plan.
1
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
ID: 41897780
This looks like a SQL Server idea in an Oracle question.  Did you intend to post this question in the SQL Server area of this site instead?

No, Oracle syntax does not allow you to include a server name and a database name directly in a call to another stored procedure.  You can get that effect in Oracle, if you need it, by creating a database link and synonym that points to a procedure via the database link.  Then, in your procedure you call the synonym.  This avoids the problem that johnsone mentioned, assuming that you create the database link and synonym differently in each system!
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 2

Author Comment

by:brgdotnet
ID: 41897884
Thank you Mark. I have been trying to use the execute immediate suggestion which geert said would work, but no go? So I will follow your advice instead. I am porting some SQL server code to Oracle. So yes originally it was SQL server. No
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 41897970
Oracle and SQL Server are both "SQL compliant" databases.  That means they both support the four basic SQL verbs: select, insert, update and delete.  Beyond those four verbs though, and especially in their proprietary procedural languages, they are very different systems!  Do not assume that the way some things are done in SQL Server is a good way (or even legal or possible) in Oracle to get the same task done.

There have been many questions (and answers) posted on this site discussing some of those differences in the past.
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 41903585
Actuallly Greer is correct. It can be done using Execute immediate. I just wrap it in a string with a begin at the start and end at the end of the string.
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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

758 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

22 Experts available now in Live!

Get 1:1 Help Now