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