Solved

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

Posted on 2016-11-21
6
62 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
[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
6 Comments
 
LVL 37

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 35

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 35

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
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!

 
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 35

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

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!

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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