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 <goks...@gmail.com>
>To: solr-user@lucene.apache.org; Niran Fajemisin <afa...@yahoo.com>
>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 <afa...@yahoo.com> 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 <michael.della.bi...@appinions.com>
>>>To: solr-user@lucene.apache.org
>>>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 <afa...@yahoo.com> 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
>goks...@gmail.com
>
>
>
