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
