cx_Oracle issues

[huw_at1]

On Dec 16, 12:17 pm, huw_at1 <huwdjo... at gmail.com> wrote:
> On Dec 15, 12:59 pm, "ron.re... at gmail.com" <ron.re... at gmail.com>
> wrote:
>
>
>
> > On Dec 15, 2:44 am, huw_at1 <huwdjo... at gmail.com> wrote:
>
> > > On Dec 11, 5:34 pm, "ron.re... at gmail.com" <ron.re... at gmail.com> wrote:
>
> > > > On Dec 10, 9:48 am, huw_at1 <huwdjo... at gmail.com> wrote:
>
> > > > > Hey all. When usingcx_Oracleto run a procedure like:
>
> > > > > cursor.execute("select (obj.function(value)) from table where
> > > > > id=blah")
>
> > > > > I am getting the following error:
>
> > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too
> > > > > small ORA-06512: at line 1
>
> > > > > Looking at cursor.description I get:
>
> > > > > [('(obj.function(value))', <type 'cx_Oracle.STRING'>, 4000, 4000, 0,
> > > > > 0, 1)]
>
> > > > > Any tips - i have never seen this error before but am guessing that
> > > > > the value being returned is too big for the buffer size set for the
> > > > > cursor. the procedure fetches data from a LOB.
>
> > > > > Any suggestions/confirmations?
>
> > > > > Many thanks
>
> > > > This error is a problem with the PL/SQL, notcx_Oracle.  You need to
> > > > debug obj.function to see what kind of data is being accessed and then
> > > > a data analysis of that data to understand why this error occurs.  I
> > > > can tell you the function is most likely expecting characters from a
> > > > column that are numeric [0 .. 9] and is getting alpha characters.
>
> > > > --
> > > > Ron Reidy
> > > > Sr. Oracle DBA
>
> > > Hi thanks for the responses. Unfortunately the procedure in question
> > > is from a third party vendor so I can't really debug it so I'd say I
> > > was fairly stumped. Just out of interest how do you increase the
> > > output buffer size withcx_Oracle?
>
> > > Many thanks- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi,
>
> > Sure you can.  You can see the PL/SQL source from the ditionary view
> > ALL_SOURCE:
> > select text from all_source where name = 'NAME_OF_FUNCTION';
>
> > From there, reverse engineeer which table(s) and column(s) are being
> > accesses and do the data analysis.
>
> > --
> > Ron Reidy
>
> Hi all,
>
> So I tried Rons query but unfortunately I got 0 records returned.
> However I can confirm that running the select query from a client does
> indeed generate the same error. Is there anything else I could try?
> Otherwise I'll just get in touch with the vendor I guess.

Hi again. A further update to these issues is that I found some java
executable which seemed to execute the SQL query without hitch. My
Java isn't great but from what I could make out it seems that the
query is broken down from:

select (obj.function(value)) from table where id=blah

to:

select value from table where id=blah

obj.function(value)

So make two queries. In the first retrieve the BLOB (value) and store
it in a java.sql.blob object. Then pass this back in to the stored
procedure. I'm a bit puzzled as to why this way would work over just
performing the straight select statement. Culd it be the jdbc
connector handles BLOBs better? Anyway I was wondering if I could
implement something similar using cx_Oracle. however I am a bit stuck
on how to pass a BLOB in to the second query - specifically:

cursor.execute(obj.function(value))

where value is the BLOB. I get an error:

cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data
type cx_Oracle.LOB

So I wonder if I need to set something for the input type but I do not
know how to do this.

Any suggestions?

Many thanks again.