cx_Oracle issues

[huw_at1]

On Dec 18 2008, 10:34 am, huw_at1 <huwdjo... at gmail.com> wrote:
> 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 theseissuesis 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 usingcx_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
> typecx_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.

Hi there. Any suggestions? I'm still a bit stuck on this one?

Cheers