Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Anyone know how to do this in Oracle?

Status
Not open for further replies.

kxramse

Programmer
Jul 28, 2006
75
US
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.
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 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.

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
 
kxramse,

If I understand correctly your need, then this code should do what you want:
Code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top