lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Niran Fajemisin <afa...@yahoo.com>
Subject Re: Using Data Import Handler to invoke a stored procedure with output (cursor) parameter
Date Sat, 02 Jun 2012 00:01:11 GMT
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 <goksron@gmail.com>
>To: solr-user@lucene.apache.org; Niran Fajemisin <afajem@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 <afajem@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.bitta@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 <afajem@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
>goksron@gmail.com
>
>
>
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message