Solved

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

Posted on 2016-11-21
6
56 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 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 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 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
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 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c patching 1 80
File generation using utl_file 4 53
oracle 11g 23 73
Oracle Listener Not Starting 11 44
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

816 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

13 Experts available now in Live!

Get 1:1 Help Now