I have a need to connect to a database that serves as a redirector of sorts to different database environments.
In this linked database you can run the following command and get data from each of the databases that are set up as a linked server.
The only difference in each of those commands is the string right before .world
The input values would be HVPRD or HVTST or HVDEV or HVT10G
Here IS my template for a SP with a cursur that is returned. This template is what has best worked for me to call this stored procedure from reporting services. Now I want to make it dynamic.
Here is my code for a SP in SQL server that does what I want to do in Oracle. The only difference is that I don't use a cursor in SQL Server because Reporting services seems to work better this way with SQL Server.
Can anyone show me how to change the oracle stored procedure to return a cursor and be dynamic like SQL Server?
We have all of our linked servers working. The simple select statements work that I posted above.
Thanks,
Keith
In this linked database you can run the following command and get data from each of the databases that are set up as a linked server.
Code:
select acctkey,acctno from imsv7.account@hvtst.world
select acctkey,acctno from imsv7.account@hvPRD.world
select acctkey,acctno from imsv7.account@hvt10G.world
select acctkey,acctno from imsv7.account@hvDEV.world
The input values would be HVPRD or HVTST or HVDEV or HVT10G
Here IS my template for a SP with a cursur that is returned. This template is what has best worked for me to call this stored procedure from reporting services. Now I want to make it dynamic.
Code:
CREATE OR REPLACE PROCEDURE IMSV7.Template_LinkedServer (ORACLE_DB_ENVIRONMENT IN varchar2, p_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset1 FOR
SELECT * from acctcons;
END Template_LinkedServer;
/
Here is my code for a SP in SQL server that does what I want to do in Oracle. The only difference is that I don't use a cursor in SQL Server because Reporting services seems to work better this way with SQL Server.
Code:
CREATE PROCEDURE [dbo].[SPNAME]
@ORACLE_ENVIRONMENT VARCHAR (5)
AS
BEGIN
DECLARE @ORACLE_VIEWNAME VARCHAR (45)
DECLARE @SQLSTRING VARCHAR (1000)
DECLARE @S2 NVARCHAR(1000)
Set @Oracle_VIEWNAME = '..IMSV7.VIEW_NAME'
SET @SQLSTRING = 'select field1, field2
FROM '
SET @SQLSTRING = @SQLSTRING + @ORACLE_ENVIRONMENT + @ORACLE_VIEWNAME -- This combines everything into one string.
SELECT @S2 = CAST(@SQLSTRING AS NVARCHAR(1000))
execute sp_executesql @S2
END
Can anyone show me how to change the oracle stored procedure to return a cursor and be dynamic like SQL Server?
We have all of our linked servers working. The simple select statements work that I posted above.
Thanks,
Keith