Anyone know how to do this in Oracle?

[kxramse]

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.

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

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.

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

 

[SantaMufasa]

kxramse,

If I understand correctly your need, then this code should do what you want:

CREATE OR REPLACE PROCEDURE IMSV7.Template_LinkedServer
        (ORACLE_DB_ENVIRONMENT IN  varchar2
        ,p_recordset1          OUT SYS_REFCURSOR)
AS
  hold_query varchar2(2000);
BEGIN
  hold_query := 'select acctkey,acctno from imsv7.account@'
                oracle_db_environment||'.world';
  OPEN p_recordset1 FOR hold_query;
END Template_LinkedServer;
/

Let us know how close this is to resolving your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]