cx-oracle-users

[cx-oracle-users] using bind variables to insert into xmltype colums

[Charl P. B. <cp...@gm...>]

Dear list,

I'm trying to insert xml into xmltype columns.  My xml is longer than
4000 characters, so I get "ORA-01704: string literal too long" if I
try and insert my xml as a string literal:
cursor.execute("insert into table xmltable values(xmltype('<xml>very
long string...</xml>'))")

As far as I could see, it seems one is supposed to use bind variables
in cases such as these.  So I tried something similar to the
following:

xml_clob = cx_Oracle.CLOB
cursor.execute("insert into table xmltable values(xmltype(:xml_clob))",
                      xml_clob = '<xml>very long string...</xml>')

This yields a: "ORA-01461: can bind a LONG value only for insert into
a LONG column".

How else can I insert long XML strings into xmltype columns from
cx_Oracle?  A temporary column of sorts?  Any tips on this would be
greatly appreciated!

Thanks,
Charl

-- 
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Anthony T. <ant...@gm...>]

The following code works for me:

connection = cx_Oracle.Connection("user/pw@tns")
cursor = connection.cursor()
cursor.setinputsizes(value = cx_Oracle.CLOB)
cursor.execute("insert into xmltable values (:value)",
        value = "A very long XML string")

where in my case the xml was about 50 KB. Hope this helps! If not, can
you post the exact code you are using so I can see if anything obvious
shows up. Thanks.

On Apr 7, 2005 5:33 PM, Charl P. Botha <cp...@gm...> wrote:
> Dear list,
> 
> I'm trying to insert xml into xmltype columns.  My xml is longer than
> 4000 characters, so I get "ORA-01704: string literal too long" if I
> try and insert my xml as a string literal:
> cursor.execute("insert into table xmltable values(xmltype('<xml>very
> long string...</xml>'))")
> 
> As far as I could see, it seems one is supposed to use bind variables
> in cases such as these.  So I tried something similar to the
> following:
> 
> xml_clob = cx_Oracle.CLOB
> cursor.execute("insert into table xmltable values(xmltype(:xml_clob))",
>                       xml_clob = '<xml>very long string...</xml>')
> 
> This yields a: "ORA-01461: can bind a LONG value only for insert into
> a LONG column".
> 
> How else can I insert long XML strings into xmltype columns from
> cx_Oracle?  A temporary column of sorts?  Any tips on this would be
> greatly appreciated!
> 
> Thanks,
> Charl
> 
> --
> charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Charl P. B. <cp...@gm...>]

On Apr 8, 2005 6:10 PM, Anthony Tuininga <ant...@gm...> wrote:
> connection = cx_Oracle.Connection("user/pw@tns")
> cursor = connection.cursor()
> cursor.setinputsizes(value = cx_Oracle.CLOB)
> cursor.execute("insert into xmltable values (:value)",
>         value = "A very long XML string")
> 
> where in my case the xml was about 50 KB. Hope this helps! If not, can
> you post the exact code you are using so I can see if anything obvious
> shows up. Thanks.

Thank you very much for the quick answer Anthony!

It seems in my example I forgot the "setinputsizes()"; I've now added
this and I don't see the ORA-01461 message anymore, but rather a
different error message: "ORA-00932: inconsistent datatypes: expected
- got CLOB"

I've attached a complete and simple example that illustrates this, at
least on my setup.  It also creates the necessary table called
"xmltable".  It drops the old "xmltable", so be careful.  After
changing the username/password, you have to do "import simple_ex;
simple_ex.main()" from the python interpreter.

Thanks for looking into this!

Regards,
Charl

-- 
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Charl P. B. <cp...@gm...>]

On Apr 8, 2005 6:39 PM, Charl P. Botha <cp...@gm...> wrote:
> I've attached a complete and simple example that illustrates this, at
> least on my setup.  It also creates the necessary table called
> "xmltable".  It drops the old "xmltable", so be careful.  After
> changing the username/password, you have to do "import simple_ex;
> simple_ex.main()" from the python interpreter.

Changing the execute line to:
cursor.execute("insert into xmltable values (xmltype(:text_clob))",
                        text_clob = very_long_xml_text)
i.e. adding the xmltype() fixed this!

Thanks very much!

-- 
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Anthony T. <ant...@gm...>]

Glad to see you got it working. Apologies for dropping the
xmltype(:value) from the example I gave you. It was in my code that
was working, of course. :-)

On Apr 8, 2005 2:58 PM, Charl P. Botha <cp...@gm...> wrote:
> On Apr 8, 2005 6:39 PM, Charl P. Botha <cp...@gm...> wrote:
> > I've attached a complete and simple example that illustrates this, at
> > least on my setup.  It also creates the necessary table called
> > "xmltable".  It drops the old "xmltable", so be careful.  After
> > changing the username/password, you have to do "import simple_ex;
> > simple_ex.main()" from the python interpreter.
> 
> Changing the execute line to:
> cursor.execute("insert into xmltable values (xmltype(:text_clob))",
>                         text_clob = very_long_xml_text)
> i.e. adding the xmltype() fixed this!
> 
> Thanks very much!
> 
> --
> charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>