Thursday, 13 June 2013

ORA-01460


In my quest to save blog content in an Oracle database I discovered a somewhat tricky problem to solve. Hence a note to self for future reference.

Running my Python script it returned with the error
ORA-01460: unimplemented or unreasonable conversion requested

Or to be precise:
Traceback (most recent call last):
  File "C:\Users\klevstul\miscellaneous\Dropbox\Miscellaneous\projects\foreneNo\trunk\cgi\jsonRpc.py", line 212, in <module>
    main()
  File "C:\Users\klevstul\miscellaneous\Dropbox\Miscellaneous\projects\foreneNo\trunk\cgi\jsonRpc.py", line 207, in main
    , str(params['value11'])
  File "C:\Users\klevstul\miscellaneous\Dropbox\Miscellaneous\projects\foreneNo\trunk\cgi\jsonRpc.py", line 118, in outputJson
    , value11
DatabaseError: ORA-01460: unimplemented or unreasonable conversion requested

After some debugging I realised it was caused by sending text content longer than 32K from the Python RPC to the Oracle RPC.

The problematic code:
retCursor = cursor.callfunc(
 procedure
, cx_Oracle.CURSOR
, [
 module
, action
, username
, password
, ipAddress
, sessionKey
, pk
, value1
, value2
, value3
, value4
, value5
, value6
, value7
, value8
, value9
, value10
, value11
]
)

Longer than 32K should be no problem, as "value11" (the variable causing the headache) is defined as a CLOB in Oracle, and not VARCHAR2 (with the maxlength of 32767 bytes). The solution (found here) is to make clear to Oracle, through cx_Oracle, that value11 is a CLOB. Otherwise it is handled like a string, or a VARCHAR2 datatype. Below is what was done.

Define a new CLOB variable:
value11clob = cursor.var(cx_Oracle.CLOB)

Assign the value that can be longer than 32K to that variable:
value11clob.setvalue(0, value11)

Then it is only a matter of using "value11clob" in stead of "value11" in the "cursor.callfunc()" code above.


No comments:

Post a Comment

Allowed HTML tags:
<b>bold</b>
<strong>strong</strong>
<i>italics</i>
<em>emphasis</em>
<a href="">hyperlink</a>


Please, show the courtesy of identifying yourself when adding a comment. Anonymous comments will, most likely, be removed.