Unable to Parse Oracle Stored Proc Parameters in transform Data Task

Question

Post reply

Unable to Parse Oracle Stored Proc Parameters in transform Data Task

data_czar

Ten Centuries

Points: 1147
More actions
January 20, 2005 at 11:10 am

#76221

I'm trying to use an Oracle package/proc with input parameters as the source in a Transform Data Task. When I type the following it parses:
exec package_name.procedure_name ?, ?, ?
But when I click the Parameters... button I receive the following error message:
Error Source: OraOleDB
Error Description: Provider cannot derive parameter information ans SetParameterInfo has not been called
I've upgraded my oledb driver to 9.2.0.4 but still receive this error. Any ideas?

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

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

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 1

When you create your Oracle connection, use the Oracle ODBC driver, not the OLEDB provider.

[font="Courier New"]ZenDada[/font]

data_czar Ten Centuries Points: 1147 More actions · Answer 2

I tried that and recevied this error:

[Microsoft][ ODBC Driver for Oracle]Invalid Parameter Number

I am passing the correct number of parameters (3), but still receive this error.

Any other thoughts?

win SSCommitted Points: 1569 More actions · Answer 3

Have you tried to enclose the parameters in brackets?

exec pkg.spname(par1,par2,par3)

Cheers,

Win

data_czar Ten Centuries Points: 1147 More actions · Answer 4

I receive the same error indicated above when I enclose in parens. Thanks for the suggestion though.

Has anyone gotten this to work in the past? If so, maybe I have a bad driver??

win SSCommitted Points: 1569 More actions · Answer 5

Is your statement a pass-thru statment? If so, have you tried to embed your statement in an Oracle pl/sql block:

begin pkg.spname(par1,pa2,par3); end;

Exec is a SQL*PLUS commmand, not a pl/slq command if I remember correctly.

Cheers,

Win

data_czar Ten Centuries Points: 1147 More actions · Answer 6

I tried this and I receive an error that say I have the wrong number of paramters. It wants a paramter for the Ref Cursor that is being returned.

Any ideas on what to do with this? I tried using a variable but I didn't set this up right and received syntax errors.

win SSCommitted Points: 1569 More actions · Answer 7

Without knowing what exactly is going on inside the oracle procedure it is hard to guess. For starters the error message could just be plain misleading.

If it is a procedure with a global variable as output parameter ("in out" in oracle as opposed to output) you'll have to declare a variable in your calling code that can handle a record set.

Alternatively, if this procedure is a function returning a cursor based result set you'll need to call it appropriately:

rowset-variable=pkg.funcname(...);

Cheers,

Win

data_czar Ten Centuries Points: 1147 More actions · Answer 8

The procedure I'm calling is supposed to return a recordset.

I pass in 3 input parameters and want to receive the recordset and use it for the source of my Transform Data Task. The Oracle procedure is nested inside an Oracle Package.

Do you know how to pass the output variable when calling the Oracle stored proc?

win SSCommitted Points: 1569 More actions · Answer 9

One of your 3 parameters is a global (in out) parameter of a reference type - the equivalence of a cursor variable in sql svr I don't know if you can pass a sql svr cursor variable as parameter to an oracle procedure and how it is done. You are trying to pass a pointer (to a memory structure) from sql svr to oracle.

The alternative is to modify the oracle procedure and write the recordset of the cursor into a table which you can pick up from sql svr.

Cheers,

Win