Question Re: Oracle XMLtype data type in Technology Q&A

Oracle XMLtype data type

Former Member — Thu, 22 May 2014 13:38:43 GMT

Latest Powerbuilder 12.5.2

Oracle 11g

How can I update a XMLType column?

can select it, but errors on update.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 13:43:19 GMT

Hi Robert;

I have not personally done this with Oracle .. but, my guess is that you may need to use the UpdateBlob command.

BTW: What error(s) are you getting?

Regards ... Chris

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 13:51:39 GMT

I already tried that.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 13:54:54 GMT

If data string size is under 4k it works fine. If data is over 4k I get...

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

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:08:44 GMT

I suspect that you will need to treat it as a clob, on your select you use the .getCLOBVal() syntax to convert the XMLType to a string.

Likewise on your update you use the XMLTYPE function to convert the string back to XML.

Something like this:

UPDATE purchaseorder

SET OBJECT_VALUE = XMLType(<xmlstring>), nls_charset_id('AL32UTF8')) where ....

Not tried this but I hope it helps.

David

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:10:48 GMT

How are you connecting to Oracle ... via ODBC?

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:18:57 GMT

The syntax is not correct. I am not sure how to test that. I tried

set xml_setting = xmltype(convert(:ls_xmlData , 'UTF8'))

this also had the same error.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:25:12 GMT

No, ODBC will not let me even select the column. I am using ORA Oracle. It has the Metadata Type XMLType.

BTW UpdateBlob errors with....

SELECTBLOB/UPDATEBLOB is valid only for LONG, RAW and LONG RAW datatypes. (108)

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:29:08 GMT

Sorry about that, I got the information from this link:

XMLType Operations

I guess we need to get the right syntax in sqlplus (or equivalent) then map that to PB.

The other alternative is to create a procedure to do the update for you, pass in a clob and convert that. The 4k limit you are seeing is the limit on a varchar2. All the xml work I have done has involved endless conversions.

CREATE OR REPLACE PROCEDURE xmlupdate (xmlin IN CLOB, xmlout OUT CLOB)

txml XMLTYPE;

BEGIN

-- Convert Clob Received into an XML document

txml := xmltype (xmlin);

.....

END;

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 14:35:04 GMT

I am guessing that Powerbuilder is not binding the variable correctly. I have this working in Java.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 15:10:02 GMT

After trying all different ways I cannot get it to work. Looking at the PB documentation the XMLTYPE is handled as a string, and oracle limits you to 4000 characters. If I go over that I get string literal too long.

So I conclude the procedure method is the only way to update large XML strings.

This is the syntax I used, as you can see I tried it with two strings less that 4000 just in case, but once the string went over 4000 I got the error.

update timesheets.test set xmlstuff = xmltype('<XX>'||:xmlss1||:xmlss1||'</XX>') where keyx = '1

Message was edited by: David Peace

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 15:37:39 GMT

FWIW: I have this working with ASE and SS - but, I use the TEXT data type and the Slect/Update Blog commands.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 15:54:03 GMT

I also have it working with MS SQL Server and SQL ANywhere, but that's not Oracle.

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 16:33:01 GMT

Does anyone still use Oracle? LOL

Re: Oracle XMLtype data type

Former Member — Thu, 22 May 2014 17:39:26 GMT

In the PowerBuilder help under datatypes > Oracle it shows the following for the XMLType:

XMLType (partial support, ORA driver only)

The ORA driver adds support for the XMLType datatype that was introduced with Oracle 9i. However, you cannot use this datatype with embedded SQL statements or in a DataWindow object.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc37776.1252/html/connpb/BHCEGJDB.htm

Re: Oracle XMLtype data type

Former Member — Fri, 23 May 2014 09:32:26 GMT

I read that too, so it is limited support i.e. 4000 characters. Very limited for XML data.

I'm out of ideas

Re: Oracle XMLtype data type

Former Member — Fri, 23 May 2014 11:15:42 GMT

Hi David;

Why not just use a Long Raw column and the Select/Update Blob commands?

Regards .. Chris

Re: Oracle XMLtype data type

Former Member — Fri, 23 May 2014 11:37:50 GMT

I am looking into the PBNI interface to see if the XML is > 4K then I will update the column via a C dll. As long as the database connection is available, I should be able to get it to work.

First time looking at the PBNI.

Re: Oracle XMLtype data type

Former Member — Fri, 23 May 2014 13:00:58 GMT

As long as you're going to be playing with C++ you might just create a simple DLL. You can pass in the handle of the PowerBuilder database connection to the DLL so you can do the work in the same connection.

I took the same approach to hiding the SET ROLE statement from the DB Trace log, by creating a DLL that issues that and calling that from PB right after the initial connect. The last part of the following article explains that:

http://oracle.sys-con.com/node/927244

And the source code for the DLL is on CodeXchange (the article has the link). Look for the "DBHandle demo" file.

Re: Oracle XMLtype data type

Former Member — Fri, 23 May 2014 15:39:16 GMT

Thank you Bruce for the help!