cx_Oracle issues

[huw_at1]

Hey all. When using cx_Oracle to 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

 

[James Mills]

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?

Could you not increase the buffer size ?
I think you can do this with cx_Oracle.

cheers
James

 

[ron.reidy]

Hey all. When using cx_Oracle to 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, not cx_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.

 

[huw_at1]

Hey all. When using cx_Oracle to 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, not cx_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.

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 with cx_Oracle?

Many thanks

 

[Hrvoje Niksic]

ORA-06502: PL/SQL: numeric or value error: character string buffer too
small ORA-06512: at line 1

This error is a problem with the PL/SQL, not cx_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.

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 with cx_Oracle?

Ron's point was that you cannot fix this problem on the side of
cx_Oracle because the exception occurs before cx_Oracle ever sees the
result, during the execution of PL/SQL code.

This is easy to verify: simply run the function the same way in
sqlplus. If the problem persists, it's a bug in the function (or in
the way you're calling it, or setting up the data, etc.) and you
should complain to your vendor, or somehow work around the problem.
Otherwise it's a cx_Oracle related problem.

 

[ron.reidy]

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.

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.

 

[huw_at1]

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.

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.

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.

 

[huw_at1]

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.

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.

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.

 

[huw_at1]

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.

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.

 

[huw_at1]

On Dec 15, 12:59 pm, "(e-mail address removed)" <[email protected]>
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.

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