• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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

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
brgdotnet
Asked:
brgdotnet
2 Solutions
 
Geert GOracle dbaCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
brgdotnetcontractorAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
brgdotnetcontractorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now