Calling and Oracle procedure from SQL and getting back values

Question

Post reply

Calling and Oracle procedure from SQL and getting back values

Schadenfreude-Mei

SSChampion

Points: 14083
More actions
June 25, 2008 at 5:47 am

#189608

Hi All,
I have an SQL 2005 64-bit server. I am trying to call a procedure on an Oracle server and pass it params and then get some back.
The developer says that its working on the Oracle side but SQL is not allowing result params through. SQL error is:
Start USP_TestOracleCall
OLE DB provider "OraOLEDB.Oracle" for linked server "ICLPRDA" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1". Ids DEAL01 1-111
OLE DB provider "OraOLEDB.Oracle" for linked server "ICLPRDA" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1". Ids DEAL02 2-111
Msg 7215, Level 17, State 1, Procedure USP_TestOracleCall, Line 50
Could not execute statement on remote server 'ICLPRDA'.
Msg 7215, Level 17, State 1, Procedure USP_TestOracleCall, Line 50
Could not execute statement on remote server 'ICLPRDA'.
The code for the procedure in SQL is:
BEGIN
DECLARE@Deal_Id nvarchar(50)
DECLARE@Id nvarchar(50)
DECLARE @Out_Deal_Id int
DECLARE Deal_Ids CURSOR FOR
SELECTId , Deal_Id
FROMT_Test_Transaction
OPENDeal_Ids
FETCH NEXT FROM Deal_Ids INTO @Id, @Deal_Id
PRINT N'Start USP_TestOracleCall'
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ( 'BEGIN test_transaction(pi_in_recnum => ?,
pi_in_deal_id => ? , po_out_deal_id => ?
); END;',@Id, @Deal_Id, @Out_Deal_Id = '11')
AT [ICLPRDA];
PRINT N' Ids ' + @Deal_Id + @Id + ISNULL(LTRIM(RTRIM(@Out_Deal_Id)),'-111')
FETCH NEXT FROM Deal_Ids INTO @Id, @Deal_Id
END
CLOSEDeal_Ids
DEALLOCATE Deal_Ids
END
On the linked server I have set RPC and RPC_OUT to be true. To start with I couldn’t send out the params and setting RPC_OUT to true solved this (that’s why i hoped RPC would let value back through)
Appreciate any ideas people have as this is head-scratchily annoying.
Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

hmaharaj Right there with Babe Points: 781 More actions · Answer 1

try using openquery instead or EXEC. i know EXEC is much faster but it will work with openquery.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

Hey Grasshopper,

Thanks for your reply. I actually started off with 'SELECT * FROM OPENQUERY(......)' but found that it didn’t really work.

It works fine to query a table or as a nested query, but in this case I don’t want to start with a query as I am just calling a sp in Oracle.

Or am I explaining wrong? The original attempt with OPENQUERY failed to execute because there was nothing to display (from the outer query) and OPENQUERY wont work on its own.

I was sure that OPENQUERY was the key, but couldn’t find a way to make it work. Any ideas?

Adam Zacks-------------------------------------------Be Nice, Or Leave

hmaharaj Right there with Babe Points: 781 More actions · Answer 3

hmaharaj

Right there with Babe

Points: 781

June 26, 2008 at 5:27 am

#834534

how about dynamic sql using openquery

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 4

Sorry, you've lost me. 'Dynamic SQL'?

Adam Zacks-------------------------------------------Be Nice, Or Leave

hmaharaj Right there with Babe Points: 781 More actions · Answer 5

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000)

SET @SQL =

'SELECT ACCT, USERNAME FROM OPENQUERY(CUSTOMER,''SELECT==lcl.ACCT, lcl.USERNAME FROM LOCAL_LOG lcl WHERE lcl.VALUE= '''''+ LTRIM(RTRIM(@VALUE)) +''''' ORDER BY lcl.PROCESS_DATE DESC'')'

--PRINT @SQL

EXEC(@SQL)

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 6

Ok, I understand now. This sort of query doesn’t seem to work because you have to do 'Select.... Openquery(....)'. That’s not working cause there is nothing to output from the 'Select' part.

That’s the reason behind the 'Exec'. All we are trying to do is pass values to Oracle sp via a cursor. Are we using this completely wrong? Please see code at start of thread, that’s what we have and it works on Oracle side, but is not passing values back.

Is it even possible. Appreciate all you help.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 7

Hi

Check out the below link

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1123229,00.html

This will slove yuor problem.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

samiam914 SSCommitted Points: 1886 More actions · Answer 8

Here is a link from another post that might help you or at least give you clues as to how to solve your problem

http://p2p.wrox.com/topic.asp?TOPIC_ID=15542

Please note this was posted by Chris Büttner in another part of the forum

in http://www.sqlservercentral.com/Forums/Topic532487-1042-1.aspx