Forum Stats

  • 3,816,106 Users
  • 2,259,142 Discussions
  • 7,893,390 Comments

Discussions

Inserting to a clob field using cx_Oracle via a stored procedure

615703
615703 Member Posts: 9
edited Jun 5, 2009 10:29AM in Python
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

Comments

  • 484861
    484861 Member Posts: 271
    And cursor.callproc('insert_clob_proc', (clob,)) doesn't work for you?

    Przemek
  • 615703
    615703 Member Posts: 9
    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
    484861 Member Posts: 271
    edited Jan 3, 2008 4:33AM
    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
    615703 Member Posts: 9
    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
    690155 Member Posts: 1
    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
    705380 Member Posts: 1
    hi!

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

    Thank you!
This discussion has been closed.