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
> >
> >
> >
