Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • 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 GruwezOracle 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

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.

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