January 14, 2008 at 2:06 pm
Hi , I need call a package Oracle from store procedure , I have this script example :
/* example : callfromSqlserverPAckage
Create table #test (indicator int identity(1,1),
Oracle_Result varchar(500))
/*Define la consulta que quieres ejecutar
nota el uso de los parametros
claro que si los parametros no son numericos
deberas usar ''.
*/
declare @sql nvarchar(1024)
set @sql = '{CALL BACKOFFICE.CallFromSqlServerPackage(''''@PR1'''',''''@PR2'''',''''@PR3'''',''''@PR4'''' }'
set @sql = @sql + ',{resultset 25, ReturnVal})}'')'
-- set @sql = 'SELECT * FROM BACKOFFICE.TERMINALES WHERE CODCOMERCIO=''''@PR1'''' AND CODSUCURSAL=''''@PR1'''' '
/*Define los parametros que vas a usar
*/
declare @pr1 nvarchar(1024) set @pr1 = '1'
declare @pr2 nvarchar(1024) set @pr2 = '100.2'
declare @pr3 nvarchar(1024) set @pr3 = 'This is info-for Oracle test'
declare @pr4 nvarchar(1024) set @pr4 = 'Dec 12 2004'
/*Define el linked server donde ejecutaras la consulta
*/
declare @lnk nvarchar(1024) set @lnk = 'BACKOFFICE_E1'
/*Reemplaza los parametros por los valores
*/
set @sql = replace(@sql, '@pr1', @pr1)
set @sql = replace(@sql, '@pr2', @pr2)
set @sql = replace(@sql, '@pr3', @pr3)
set @sql = replace(@sql, '@pr4', @pr4)
/*Aqui es donde terminas ejecutando la consulta en el linked server
*/
declare @qry nvarchar(1024)
set @qry = 'Insert into #test(Oracle_Result) select * from openquery(@lnk,''@sql'')'
set @qry = replace(@qry,'@lnk', @lnk)
set @qry = replace(@qry,'@sql', @sql)
print @qry
execute(@qry)
give me the error :
OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: The provider did not give any information about the error.].
calling a statement select it's work but calling a package don't work , what's wrong in my example ?
Someone have a example working in your office or house ?
P.D.: I don't speak English , just read English
January 18, 2008 at 9:06 am
I don't have experience calling Oracle packages from SQL but I do with calling packages via ADO. Some things to look into, the error you list is comming from MSDAORA which is the Oracle provider from Microsoft. To complete the conversation to Oracle the Oracle ODBC driver provided from Oracle also needs to be installed - this is an element of the Oracle client installation. I found this info on the Microsoft MSDN site and was instrumental in fixing some of the road blocks I ran into.
Whether or not the SQL installation is on your local PC or you're connecting to a remote SQL instance, running code to call the Oracle PKG, you need to be certain all the proper connections are there. The machine hosting SQL needs to have this connection to Oracle before you can successfully proceed.
February 1, 2008 at 2:23 pm
I think you need to use OPENROWSET to connect to Oracle to exec the package.
February 21, 2008 at 4:22 pm
Hi,
You can go for linked servers.
Thanks -- Vj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply