Excuting oraclle procdure from sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Excuting oraclle procdure from sql server

Author

Topic

Mathew Simon
Starting Member

4 Posts

Posted - 2009-06-06 : 03:16:28

I have an Oracele databse OHDEV and I have SQL server.I have to execute an Oracle Package from SQL server.I have linked these servers and it works for the following query:
SELECT * FROM OPENQUERY(OHDEV,'Select * from tab')
But for queries 1),2),3) it does not work.
XXOH_INV_INTERFACE_PKG.INSERT_TRX is the a package in Oracle and
'MAS','GN00001',10 and 3 other parameters I am passing.These are not fixed parameters which is dependent on certain conditions.
1)SELECT * FROM OPENQUERY(OHDEV,'{call XXOH_INV_INTERFACE_PKG.INSERT_TRX(sysdate,''MAS'',''GN00001'',10)}')

2)SELECT * FROM OPENQUERY(OHDEV,'EXEC XXOH_INV_INTERFACE_PKG.INSERT_TRX(sysdate,''MAS'',''GN00001'',10)')

3)SELECT * FROM OPENQUERY(APPS,'declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate,null,null,10); End;')

The error I get is as follows :
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate,null,null,10); End;". The OLE DB provider "MSDAORA" for linked server "OHDEV" indicates that either the object has no columns or the current user does not have permissions on that object.

CAN SOMEONE HELP ME Please.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-06 : 16:07:33

Not real familiar with Oracle, but if you setup a linked server, you can try something like:

exec MyOracleLink...OHDEV.XXOH_INV_INTERFACE_PKG.INSERT_TRX '06/06/2009','Mas','GN0001',10

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Mathew Simon
Starting Member

4 Posts

Posted - 2009-06-06 : 23:44:47

vinnie,
i have managed with this
EXEC ('declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate,''MAS'',"GN000111',10); End;') AT OHDEV
but now my real issue is MAS,GN00111,10 these comes from a table in SQL SERVER.
Can you/some help me with this please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:27:25

just use variables to get values from sql server table and pass it to your staement. like

DECLARE @val1 varchar(20),@val2 varchar(50),@val3 int

SELECT @val1 =field1,@val2=field2,@val3=field3
FROM YourSQLTable
WHERE conditions...

and make final string dynamic

'EXEC (''declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate, ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'') AT OHDEV'

Mathew Simon
Starting Member

4 Posts

Posted - 2009-06-07 : 07:08:34

Hi Visakh I am not sure what you mean with final string dynamic.Could you explain?
What I did is as follows :

DECLARE @val1 varchar(20),@val2 varchar(50),@val3 int
SELECT @val1 ='SQLSRV',@val2=[Item_Code],@val3=[Quantity]
FROM [ReorderS]
EXECUTE('declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate, ''@val1'' , ''@val2'',''+@val3''); End;') AT OHDEV

When I run this I get the following error -

OLE DB provider "MSDAORA" for linked server "OHDEV" returned message "ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
".
Msg 7215, Level 17, State 1, Line 6
Could not execute statement on remote server 'OHDEV'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 07:12:23

something like:-


DECLARE @val1 varchar(20),@val2 varchar(50),@val3 int,@Sql varchar(8000)
SELECT @val1 ='SQLSRV',@val2=[Item_Code],@val3=[Quantity]
FROM [ReorderS]
SET @Sql='EXEC (''declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate, ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'') AT OHDEV'
EXEC (@Sql)

Mathew Simon
Starting Member

4 Posts

Posted - 2009-06-07 : 08:28:48

Visakh,I tried that.it gives error as follows
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'EXEC ('declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate, 'SQLSRV' , 'GN0011',' to data type int.

I thought it is bcoause of the sinlge quotes and so I removed it and ran that

DECLARE @val1 varchar(20),@val2 varchar(50),@val3 int,@Sql varchar(8000)
SELECT @val1 ='SQLSRV',@val2=[Item_Code],@val3=[Quantity]
FROM [ReorderS]
SET @Sql='EXECUTE (''declare Begin XXOH_INV_INTERFACE_PKG.insert_trx(sysdate, '' + @val1 + '' , ''+@val2 + '',''+@val3+''); End;'') AT OHDEV'
EXEC (@Sql)

This gives error as follows -

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@val1".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources