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,param
29 procNameWithOtherInfo := serverName || DBName || 'getTeamTotals';
30 procNameWithOtherInfo(para
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,param
29 procNameWithOtherInfo := serverName || DBName || 'getTeamTotals';
30 procNameWithOtherInfo(para
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
There have been many questions (and answers) posted on this site discussing some of those differences in the past.
ASKER
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.
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.