Inserting to a clob field using cx_Oracle via a stored procedure

615703

Hello,

Does anyone have experience using cx_Oracle to call a stored procedure which inserts to a clob field? I have found examples of doing this via straight SQL but I was hoping it was possible to do this via a stored procedure call. Thanks for any help you can provide.

Jason

484861

And cursor.callproc('insert_clob_proc', (clob,)) doesn't work for you?

Przemek

615703

And cursor.callproc('insert_clob_proc', (clob,))
doesn't work for you?

Przemek

Yes - I should have been more clear in my original post. The callproc function works until we have a value which is over 32K. At values over 32K, we get an error message "ORA-01460: unimplemented or unreasonable conversion requested". I believe this is because we are sending the value as a string and so we would need to figure out how to send as a CLOB in cx_Oracle? Here is some code to use to test if interested...

Oracle (Oracle Database 10g Release 10.1.0.4.0 - Production):
CREATE TABLE clob_test (CLOB_FIELD CLOB);
CREATE OR REPLACE PROCEDURE ins_clob_test (v_clob_field IN CLOB)
AS
BEGIN
INSERT INTO clob_test (clob_field) VALUES (v_clob_field);
END ins_clob_test;

Python (2.5):
conn = cx_Oracle.connect(xhash['oraclelogin'])
cursor = conn.cursor()
clob_var = 'Some test data' * 10000
cursor.callproc('ins_clob_test',(clob_var,))
conn.commit()
cursor.close()
conn.close()

I should also mention that I am the Oracle developer and not the Python programmer - my knowledge of Python is very limited. I would like the Python programmers to use the procedures (packages) I have created to do their inserts but this situation has caused them to put the SQL directly in their code.

Thanks again for any assistance you can provide.

Jason

484861

Ok, now we are clear - you just need to let Oracle know what you are up to.

Try declaring the input clob parameter as follows:

clob_var = cursor.var(cx_Oracle.CLOB)

clob_var.setvalue(0, 'Some test data' * 10000)

Przemek

615703

Ok, now we are clear - you just need to let Oracle
know what you are up to.

Try declaring the input clob parameter as follows:

clob_var = cursor.var(cx_Oracle.CLOB)

clob_var.setvalue(0, 'Some test data' * 10000)

Przemek

That is working - thank you so much for your help.

Jason

690155

I was wondering if sql statement rather than pl/sql can be used to update records with clob type.
For example, UPDATE table_name SET clob_field="clob_obj" where key=something.
Can I use clob_obj=clob.setvalue("long_string") to set up the value?

MX

705380

hi!

in python,how doing(select,insert,update) clob?

Thank you!