Hi all,

I've seen a few questions asked around invoking stored procedures from within Data Import Handler but none of them seem to indicate what type of output parameters were being used.

I have a stored procedure created in Oracle database that takes a couple input parameters and has an output parameter that is a reference cursor. The cursor is expected to be used as a way of iterating through the returned table rows. I'm using the following format to invoke my stored procedure in the Data Import Handler's data config XML:

<entity name="entity_name" ... query="{call my_stored_proc(inParam1, inParam2)}"> ...</entity>

I have tested that this query works prior to attempting to use it from within the DIH. But when I attempt to invoke this stored procedure, it naturally complains that the output parameter is not specified (essentially a mismatch in the number of parameters).

I don't know of anyway to pass in a cursor parameter (or any output parameter for that matter) to the stored procedure invocation from within the <entity> definition.  I would greatly appreciate if anyone could provide any pointers or hints on how to proceed.

Thanks so much for your time

I could be wrong about this, but Oracle has a table() function that I
believe turns the output of a function as a table. So possibly you
could wrap your procedure in a function that returns the cursor, or
convert the procedure to a function.

Michael Della Bitta

------------------------------------------------
Appinions, Inc. -- Where Influence Isn’t a Game.
http://www.appinions.com
On Thu, May 31, 2012 at 8:00 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I've seen a few questions asked around invoking stored procedures from within Data Import Handler but none of them seem to indicate what type of output parameters were being used.
>
> I have a stored procedure created in Oracle database that takes a couple input parameters and has an output parameter that is a reference cursor. The cursor is expected to be used as a way of iterating through the returned table rows. I'm using the following format to invoke my stored procedure in the Data Import Handler's data config XML:
>
> <entity name="entity_name" ... query="{call my_stored_proc(inParam1, inParam2)}"> ...</entity>
>
> I have tested that this query works prior to attempting to use it from within the DIH. But when I attempt to invoke this stored procedure, it naturally complains that the output parameter is not specified (essentially a mismatch in the number of parameters).
>
> I don't know of anyway to pass in a cursor parameter (or any output parameter for that matter) to the stored procedure invocation from within the <entity> definition.  I would greatly appreciate if anyone could provide any pointers or hints on how to proceed.
>
> Thanks so much for your time
>

Thanks for your response, Michael. Unfortunately changing the stored procedure is not really an option here. 

From what I'm seeing, it would appear that there's really no way of somehow instructing the Data Import Handler to get a handle on the output parameter from the stored procedure. It's a bit surprising though that no one has ran into this scenario but I suppose most people just work around it.

Anyone else care to shed some more light on alternative approaches? Thanks again.

>________________________________
> From: Michael Della Bitta <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Sent: Thursday, May 31, 2012 9:40 AM
>Subject: Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
>
>I could be wrong about this, but Oracle has a table() function that I
>believe turns the output of a function as a table. So possibly you
>could wrap your procedure in a function that returns the cursor, or
>convert the procedure to a function.
>
>Michael Della Bitta
>
>------------------------------------------------
>Appinions, Inc. -- Where Influence Isn’t a Game.
>http://www.appinions.com
>
>
>On Thu, May 31, 2012 at 8:00 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
>> Hi all,
>>
>> I've seen a few questions asked around invoking stored procedures from within Data Import Handler but none of them seem to indicate what type of output parameters were being used.
>>
>> I have a stored procedure created in Oracle database that takes a couple input parameters and has an output parameter that is a reference cursor. The cursor is expected to be used as a way of iterating through the returned table rows. I'm using the following format to invoke my stored procedure in the Data Import Handler's data config XML:
>>
>> <entity name="entity_name" ... query="{call my_stored_proc(inParam1, inParam2)}"> ...</entity>
>>
>> I have tested that this query works prior to attempting to use it from within the DIH. But when I attempt to invoke this stored procedure, it naturally complains that the output parameter is not specified (essentially a mismatch in the number of parameters).
>>
>> I don't know of anyway to pass in a cursor parameter (or any output parameter for that matter) to the stored procedure invocation from within the <entity> definition.  I would greatly appreciate if anyone could provide any pointers or hints on how to proceed.
>>
>> Thanks so much for your time
>>
>
>
>

Can you add a new stored procedure that uses your current one? It
would operate like the DIH expects.

I don't remember if DB cursors are a standard part of JDBC. If they
are, it would be a great addition to the DIH if they work right.

On Thu, May 31, 2012 at 10:44 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
> Thanks for your response, Michael. Unfortunately changing the stored procedure is not really an option here.
>
> From what I'm seeing, it would appear that there's really no way of somehow instructing the Data Import Handler to get a handle on the output parameter from the stored procedure. It's a bit surprising though that no one has ran into this scenario but I suppose most people just work around it.
>
> Anyone else care to shed some more light on alternative approaches? Thanks again.
>
>
>
>>________________________________
>> From: Michael Della Bitta <[EMAIL PROTECTED]>
>>To: [EMAIL PROTECTED]
>>Sent: Thursday, May 31, 2012 9:40 AM
>>Subject: Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
>>
>>I could be wrong about this, but Oracle has a table() function that I
>>believe turns the output of a function as a table. So possibly you
>>could wrap your procedure in a function that returns the cursor, or
>>convert the procedure to a function.
>>
>>Michael Della Bitta
>>
>>------------------------------------------------
>>Appinions, Inc. -- Where Influence Isn’t a Game.
>>http://www.appinions.com
>>
>>
>>On Thu, May 31, 2012 at 8:00 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
>>> Hi all,
>>>
>>> I've seen a few questions asked around invoking stored procedures from within Data Import Handler but none of them seem to indicate what type of output parameters were being used.
>>>
>>> I have a stored procedure created in Oracle database that takes a couple input parameters and has an output parameter that is a reference cursor. The cursor is expected to be used as a way of iterating through the returned table rows. I'm using the following format to invoke my stored procedure in the Data Import Handler's data config XML:
>>>
>>> <entity name="entity_name" ... query="{call my_stored_proc(inParam1, inParam2)}"> ...</entity>
>>>
>>> I have tested that this query works prior to attempting to use it from within the DIH. But when I attempt to invoke this stored procedure, it naturally complains that the output parameter is not specified (essentially a mismatch in the number of parameters).
>>>
>>> I don't know of anyway to pass in a cursor parameter (or any output parameter for that matter) to the stored procedure invocation from within the <entity> definition.  I would greatly appreciate if anyone could provide any pointers or hints on how to proceed.
>>>
>>> Thanks so much for your time
>>>
>>
>>
>>

--
Lance Norskog
[EMAIL PROTECTED]

So I was able to run some additional tests today on this. I tried to use a stored function instead of a stored procedure. The hope was that the Stored Function would simply be a wrapper for the Store Procedure and would simply return the cursor as the return value. This unfortunately did not work.

My test attempted to call the function from the query attribute of the <entity> tag as such:  
{call my_stored_func()}

It raised an error stating that: 'my_stored_func' is not a procedure or is undefined.  This makes sense because the invocation format above is customarily reserved for a stored procedure.

So then I tried the typical approach for invoking a function which would be:
{call ? := my_stored_function()}

And as expected this resulted in an error stating that: not all variables bound . Again, this is expected as the "?" notation would be the placeholder parameter that would be bound to the OracleTypes.CURSOR constant in a typical JDBC program.

Note that this function has been tested outside of DIH and it works when properly invoked.

I think the bottom-line here is that there is no proper support for stored procedures (or functions for that matter) in DIH. This is really unfortunate because anyone thinking of doing any significant processing in the source RDBMS prior to data export would have to look elsewhere. Short of adding this functionality to the JdbcDataSource class of the DIH, I think I'm at a dead end.

If anyone knows of any alternatives I would greatly appreciate hearing them.

Thanks for the responses as usual.

Cheers.
>________________________________
> From: Lance Norskog <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]; Niran Fajemisin <[EMAIL PROTECTED]>
>Sent: Thursday, May 31, 2012 3:09 PM
>Subject: Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
>
>Can you add a new stored procedure that uses your current one? It
>would operate like the DIH expects.
>
>I don't remember if DB cursors are a standard part of JDBC. If they
>are, it would be a great addition to the DIH if they work right.
>
>On Thu, May 31, 2012 at 10:44 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
>> Thanks for your response, Michael. Unfortunately changing the stored procedure is not really an option here.
>>
>> From what I'm seeing, it would appear that there's really no way of somehow instructing the Data Import Handler to get a handle on the output parameter from the stored procedure. It's a bit surprising though that no one has ran into this scenario but I suppose most people just work around it.
>>
>> Anyone else care to shed some more light on alternative approaches? Thanks again.
>>
>>
>>
>>>________________________________
>>> From: Michael Della Bitta <[EMAIL PROTECTED]>
>>>To: [EMAIL PROTECTED]
>>>Sent: Thursday, May 31, 2012 9:40 AM
>>>Subject: Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
>>>
>>>I could be wrong about this, but Oracle has a table() function that I
>>>believe turns the output of a function as a table. So possibly you
>>>could wrap your procedure in a function that returns the cursor, or
>>>convert the procedure to a function.
>>>
>>>Michael Della Bitta
>>>
>>>------------------------------------------------
>>>Appinions, Inc. -- Where Influence Isn’t a Game.
>>>http://www.appinions.com
>>>
>>>
>>>On Thu, May 31, 2012 at 8:00 AM, Niran Fajemisin <[EMAIL PROTECTED]> wrote:
>>>> Hi all,
>>>>
>>>> I've seen a few questions asked around invoking stored procedures from within Data Import Handler but none of them seem to indicate what type of output parameters were being used.
>>>>
>>>> I have a stored procedure created in Oracle database that takes a couple input parameters and has an output parameter that is a reference cursor. The cursor is expected to be used as a way of iterating through the returned table rows. I'm using the following format to invoke my stored procedure in the Data Import Handler's data config XML:

Apologies for the terseness of this reply, as I'm on my mobile.

To treat the result of a function call as a table in Oracle SQL, use the
table() function, like this:

select * from table(my_stored_func())

HTH,

Michael
On Jun 1, 2012 8:01 PM, "Niran Fajemisin" <[EMAIL PROTECTED]> wrote:

> So I was able to run some additional tests today on this. I tried to use a
> stored function instead of a stored procedure. The hope was that the Stored
> Function would simply be a wrapper for the Store Procedure and would simply
> return the cursor as the return value. This unfortunately did not work.
>
> My test attempted to call the function from the query attribute of the
> <entity> tag as such:
> {call my_stored_func()}
>
> It raised an error stating that: 'my_stored_func' is not a procedure or is
> undefined.  This makes sense because the invocation format above is
> customarily reserved for a stored procedure.
>
> So then I tried the typical approach for invoking a function which would
> be:
> {call ? := my_stored_function()}
>
> And as expected this resulted in an error stating that: not all variables
> bound . Again, this is expected as the "?" notation would be the
> placeholder parameter that would be bound to the OracleTypes.CURSOR
> constant in a typical JDBC program.
>
> Note that this function has been tested outside of DIH and it works when
> properly invoked.
>
> I think the bottom-line here is that there is no proper support for stored
> procedures (or functions for that matter) in DIH. This is really
> unfortunate because anyone thinking of doing any significant processing in
> the source RDBMS prior to data export would have to look elsewhere. Short
> of adding this functionality to the JdbcDataSource class of the DIH, I
> think I'm at a dead end.
>
> If anyone knows of any alternatives I would greatly appreciate hearing
> them.
>
> Thanks for the responses as usual.
>
> Cheers.
>
>
>
>
> >________________________________
> > From: Lance Norskog <[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED]; Niran Fajemisin <[EMAIL PROTECTED]>
> >Sent: Thursday, May 31, 2012 3:09 PM
> >Subject: Re: Using Data Import Handler to invoke a stored procedure with
> output (cursor) parameter
> >
> >Can you add a new stored procedure that uses your current one? It
> >would operate like the DIH expects.
> >
> >I don't remember if DB cursors are a standard part of JDBC. If they
> >are, it would be a great addition to the DIH if they work right.
> >
> >On Thu, May 31, 2012 at 10:44 AM, Niran Fajemisin <[EMAIL PROTECTED]>
> wrote:
> >> Thanks for your response, Michael. Unfortunately changing the stored
> procedure is not really an option here.
> >>
> >> From what I'm seeing, it would appear that there's really no way of
> somehow instructing the Data Import Handler to get a handle on the output
> parameter from the stored procedure. It's a bit surprising though that no
> one has ran into this scenario but I suppose most people just work around
> it.
> >>
> >> Anyone else care to shed some more light on alternative approaches?
> Thanks again.
> >>
> >>
> >>
> >>>________________________________
> >>> From: Michael Della Bitta <[EMAIL PROTECTED]>
> >>>To: [EMAIL PROTECTED]
> >>>Sent: Thursday, May 31, 2012 9:40 AM
> >>>Subject: Re: Using Data Import Handler to invoke a stored procedure
> with output (cursor) parameter
> >>>
> >>>I could be wrong about this, but Oracle has a table() function that I
> >>>believe turns the output of a function as a table. So possibly you
> >>>could wrap your procedure in a function that returns the cursor, or
> >>>convert the procedure to a function.
> >>>
> >>>Michael Della Bitta
> >>>
> >>>------------------------------------------------
> >>>Appinions, Inc. -- Where Influence Isn’t a Game.
> >>>http://www.appinions.com
> >>>
> >>>
> >>>On Thu, May 31, 2012 at 8:00 AM, Niran Fajemisin <[EMAIL PROTECTED]>
> wrote:
> >>>> Hi all,
> >>>>
> >>>> I've seen a few questions asked around invoking stored procedures

Right, or create a view.

On Fri, Jun 1, 2012 at 8:11 PM, Michael Della Bitta
<[EMAIL PROTECTED]> wrote:
> Apologies for the terseness of this reply, as I'm on my mobile.
>
> To treat the result of a function call as a table in Oracle SQL, use the
> table() function, like this:
>
> select * from table(my_stored_func())
>
> HTH,
>
> Michael
> On Jun 1, 2012 8:01 PM, "Niran Fajemisin" <[EMAIL PROTECTED]> wrote:
>
>> So I was able to run some additional tests today on this. I tried to use a
>> stored function instead of a stored procedure. The hope was that the Stored
>> Function would simply be a wrapper for the Store Procedure and would simply
>> return the cursor as the return value. This unfortunately did not work.
>>
>> My test attempted to call the function from the query attribute of the
>> <entity> tag as such:
>> {call my_stored_func()}
>>
>> It raised an error stating that: 'my_stored_func' is not a procedure or is
>> undefined.  This makes sense because the invocation format above is
>> customarily reserved for a stored procedure.
>>
>> So then I tried the typical approach for invoking a function which would
>> be:
>> {call ? := my_stored_function()}
>>
>> And as expected this resulted in an error stating that: not all variables
>> bound . Again, this is expected as the "?" notation would be the
>> placeholder parameter that would be bound to the OracleTypes.CURSOR
>> constant in a typical JDBC program.
>>
>> Note that this function has been tested outside of DIH and it works when
>> properly invoked.
>>
>> I think the bottom-line here is that there is no proper support for stored
>> procedures (or functions for that matter) in DIH. This is really
>> unfortunate because anyone thinking of doing any significant processing in
>> the source RDBMS prior to data export would have to look elsewhere. Short
>> of adding this functionality to the JdbcDataSource class of the DIH, I
>> think I'm at a dead end.
>>
>> If anyone knows of any alternatives I would greatly appreciate hearing
>> them.
>>
>> Thanks for the responses as usual.
>>
>> Cheers.
>>
>>
>>
>>
>> >________________________________
>> > From: Lance Norskog <[EMAIL PROTECTED]>
>> >To: [EMAIL PROTECTED]; Niran Fajemisin <[EMAIL PROTECTED]>
>> >Sent: Thursday, May 31, 2012 3:09 PM
>> >Subject: Re: Using Data Import Handler to invoke a stored procedure with
>> output (cursor) parameter
>> >
>> >Can you add a new stored procedure that uses your current one? It
>> >would operate like the DIH expects.
>> >
>> >I don't remember if DB cursors are a standard part of JDBC. If they
>> >are, it would be a great addition to the DIH if they work right.
>> >
>> >On Thu, May 31, 2012 at 10:44 AM, Niran Fajemisin <[EMAIL PROTECTED]>
>> wrote:
>> >> Thanks for your response, Michael. Unfortunately changing the stored
>> procedure is not really an option here.
>> >>
>> >> From what I'm seeing, it would appear that there's really no way of
>> somehow instructing the Data Import Handler to get a handle on the output
>> parameter from the stored procedure. It's a bit surprising though that no
>> one has ran into this scenario but I suppose most people just work around
>> it.
>> >>
>> >> Anyone else care to shed some more light on alternative approaches?
>> Thanks again.
>> >>
>> >>
>> >>
>> >>>________________________________
>> >>> From: Michael Della Bitta <[EMAIL PROTECTED]>
>> >>>To: [EMAIL PROTECTED]
>> >>>Sent: Thursday, May 31, 2012 9:40 AM
>> >>>Subject: Re: Using Data Import Handler to invoke a stored procedure
>> with output (cursor) parameter
>> >>>
>> >>>I could be wrong about this, but Oracle has a table() function that I
>> >>>believe turns the output of a function as a table. So possibly you
>> >>>could wrap your procedure in a function that returns the cursor, or
>> >>>convert the procedure to a function.
>> >>>
>> >>>Michael Della Bitta
>> >>>
>> >>>------------------------------------------------
>> >>>Appinions, Inc. -- Where Influence Isn’t a Game.

Lance Norskog
[EMAIL PROTECTED]

Thanks Michael and Lance! 

I decided to go with an Oracle Pipelined Table function and that took care of it. I think that's what Michael was referring to below. This enabled us to be able to make a simple SQL call.

Thanks again.
>________________________________
> From: Lance Norskog <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Sent: Sunday, June 3, 2012 12:28 AM
>Subject: Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
>
>Right, or create a view.
>
>On Fri, Jun 1, 2012 at 8:11 PM, Michael Della Bitta
><[EMAIL PROTECTED]> wrote:
>> Apologies for the terseness of this reply, as I'm on my mobile.
>>
>> To treat the result of a function call as a table in Oracle SQL, use the
>> table() function, like this:
>>
>> select * from table(my_stored_func())
>>
>> HTH,
>>
>> Michael
>> On Jun 1, 2012 8:01 PM, "Niran Fajemisin" <[EMAIL PROTECTED]> wrote:
>>
>>> So I was able to run some additional tests today on this. I tried to use a
>>> stored function instead of a stored procedure. The hope was that the Stored
>>> Function would simply be a wrapper for the Store Procedure and would simply
>>> return the cursor as the return value. This unfortunately did not work.
>>>
>>> My test attempted to call the function from the query attribute of the
>>> <entity> tag as such:
>>> {call my_stored_func()}
>>>
>>> It raised an error stating that: 'my_stored_func' is not a procedure or is
>>> undefined.  This makes sense because the invocation format above is
>>> customarily reserved for a stored procedure.
>>>
>>> So then I tried the typical approach for invoking a function which would
>>> be:
>>> {call ? := my_stored_function()}
>>>
>>> And as expected this resulted in an error stating that: not all variables
>>> bound . Again, this is expected as the "?" notation would be the
>>> placeholder parameter that would be bound to the OracleTypes.CURSOR
>>> constant in a typical JDBC program.
>>>
>>> Note that this function has been tested outside of DIH and it works when
>>> properly invoked.
>>>
>>> I think the bottom-line here is that there is no proper support for stored
>>> procedures (or functions for that matter) in DIH. This is really
>>> unfortunate because anyone thinking of doing any significant processing in
>>> the source RDBMS prior to data export would have to look elsewhere. Short
>>> of adding this functionality to the JdbcDataSource class of the DIH, I
>>> think I'm at a dead end.
>>>
>>> If anyone knows of any alternatives I would greatly appreciate hearing
>>> them.
>>>
>>> Thanks for the responses as usual.
>>>
>>> Cheers.
>>>
>>>
>>>
>>>
>>> >________________________________
>>> > From: Lance Norskog <[EMAIL PROTECTED]>
>>> >To: [EMAIL PROTECTED]; Niran Fajemisin <[EMAIL PROTECTED]>
>>> >Sent: Thursday, May 31, 2012 3:09 PM
>>> >Subject: Re: Using Data Import Handler to invoke a stored procedure with
>>> output (cursor) parameter
>>> >
>>> >Can you add a new stored procedure that uses your current one? It
>>> >would operate like the DIH expects.
>>> >
>>> >I don't remember if DB cursors are a standard part of JDBC. If they
>>> >are, it would be a great addition to the DIH if they work right.
>>> >
>>> >On Thu, May 31, 2012 at 10:44 AM, Niran Fajemisin <[EMAIL PROTECTED]>
>>> wrote:
>>> >> Thanks for your response, Michael. Unfortunately changing the stored
>>> procedure is not really an option here.
>>> >>
>>> >> From what I'm seeing, it would appear that there's really no way of
>>> somehow instructing the Data Import Handler to get a handle on the output
>>> parameter from the stored procedure. It's a bit surprising though that no
>>> one has ran into this scenario but I suppose most people just work around
>>> it.
>>> >>
>>> >> Anyone else care to shed some more light on alternative approaches?
>>> Thanks again.
>>> >>
>>> >>
>>> >>
>>> >>>________________________________
>>> >>> From: Michael Della Bitta <[EMAIL PROTECTED]>
>>> >>>To: [EMAIL PROTECTED]