How do you pass SQL stored procedure parm to linked Oracle query within that same stored procedure

I am trying tp use the parm passed to the sql  stored procedure in the WHERE clause in the linked oracle openquery within that sql stored procedure but keep getting errors or no records are witten to the table.
I have called that parm   :param1 in the where clause but get errors. The way the where clause is now runs but gives me no records.     Man, what am I missing, it can't be that hard......

USE [RMReports]
GO
/****** Object:  StoredProcedure [dbo].[usp_rmagegroup]    Script Date: 11/08/2008 17:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_rmagegroup]

 @PARAM1 varchar(75) = '3M COMPANY'


      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

IF OBJECT_ID('[dbo].[tbl_rmagegroup]') IS NOT NULL
    drop TABLE [dbo].[tbl_rmagegroup]

CREATE TABLE [dbo].[tbl_rmagegroup]
(
[company] [varchar](75) NULL,      
[salesmn] [varchar](30) NULL,      
[custno] [varchar](60) NULL,
[trantype] [varchar](1) NULL,
[invdate] [varchar](20) NULL,
[balance] [decimal](11,2) NULL,
[npest] [decimal](5,0) NULL,
[custnocode] [varchar](1) NULL

       
) ON [PRIMARY]
      
INSERT INTO [dbo].[tbl_rmagegroup]

SELECT  * from openquery

 
([GETPAIDE.CN.CA],

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = '' @PARAM1 '')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')

end
thayduckProgrammer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:

take the @param1 variable out of the string like the one below...

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = ' + @PARAM1 +')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')
0
tigin44Commented:
sory I missed the quotations... since your parameter is string value it must be like this one

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = ''' + @PARAM1 +''')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')
0
Mark GeerlingsDatabase AdministratorCommented:
It looks like you posted this both the SQL Server zone and the Oracle zone on this site and you use SQL Server syntax, but you mention Oracle in your question, so I'm puzzled.  Is this a SQL Server question, or an Oracle question?  The "create table..." syntax is not directly supported in Oracle stored procedures.  

Oracle stored procedure syntax supports only the four basic SQL verbs (select, insert, update and delete) directly.  Other SQL verbs in Oracle procedures are only supported in the "execute immediate" command.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

thayduckProgrammer AnalystAuthor Commented:
tigin44:
Got this message when trying your suggestion
Msg 102, Level 15, State 1, Procedure usp_rmagegroup, Line 42
Incorrect syntax near '+'.

markqeer:

In this sql stored procedure, I am trying to fill a sql work table with data from 2 oracle tables by using a openquery to access those 2 oracle tables. That is why I pointed this question to SQL and Oracle.
The problem I am having is that in the openquery it will not accept the parm that was fed to the sql stored procedure. The sql stored procedure parm starts with a @ but the openquery to oracle doesnt like the @.
0
thayduckProgrammer AnalystAuthor Commented:
= '' + @PARAM1 + '' works in the WHERE clause  with no error but returns no records.

Remember, the openquery part of this sql stored procedure is pointing to and reading from a Oracle server.  That is why the @param1 in the WHERE clause of the openquery is not working. Oracle does not like '' + @param1 + ''. Also tried  '' + :param1 + '' and that did not work either.


 
0
tigin44Commented:
are you building the sql sytax dynamically and passing it to the oracle... or is this an sql statement in oracle...
0
thayduckProgrammer AnalystAuthor Commented:
No.

If I run this SQL Stored procedure without any parms then:

It will buld a work table in sql database RMREPORTS.
It will run a openquery that accesses oracle tables (within this sql stored procedure) and puts that data into my sql work table, thats it.
This works fine without the introduction of a parm.

The problem is that I only want certain records from the oracle tables.
So i changed this sql stored procedure to accept a parm (@param1).
Now, the parm is accepted by the sql stored procedure but I cannot figure out a way to use that parm in a WHERE clause in my openquery part of my sql stored procedure that is accessing the Oracle tables.

I can get the sql stored procedure to run, but it doesn't retrieve any records from the oracle tables.

From what I read, there is a trick to get the the openquery to oracle tables to use that parm in a WHERE clause.

This below code in the sql stored procedure points that openquery to the oracle server via our tnsnames.ora table.

SELECT  * from openquery
 
([GETPAIDE.CN.CA],

 
0
thayduckProgrammer AnalystAuthor Commented:
After about 18 hours of testing and searching internet I found this blog that explained how to pass a parm within a openquery that is accessing Oracle tables via linked oracle servers. Or maybe just passing a parm within a openquery to. Anyways, it took me a couple more hours but I finally got it to work.


 http://blogs.conchango.com/jamespipe/archive/2007/06/28/SQL-Server-2005_3A00_-Passing-variables-into-an-OPENQUERY-argument.aspx

My original query (linking to Oracle Tables via openquery that no matter what I did would not retrieve any records because of parm problems in WHERE clause:

--INSERT INTO [dbo].[tbl_rmagegroup]

--SELECT  * from openquery

 
--([GETPAIDE.CN.CA],


--'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
 --           SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
--FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
--WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND GPCOMP1.GPCUST.COMPANY = @company
--ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')

 What I did after finding Blog:     AND IT WORKS...        

Mke sure to add                         @MyString    varchar(max)


set     @MyString =  'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL                
                   
                     WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND GPCOMP1.GPCUST.COMPANY = (''' + convert(varchar(75),@company,120) + ''')
                     ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO'


set     @MyString =  N'INSERT INTO [dbo].[tbl_rmagegroup]

SELECT  * from openquery

 
([GETPAIDE.CN.CA],    ''' + REPLACE(@MyString, '''', '''''') + ''')'


--print   @MyString

 

EXEC    (@MyString)





0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.