Oracle XMLtype data type

[Former Member]

Latest Powerbuilder 12.5.2

Oracle 11g

How can I update a XMLType column?

can select it, but errors on update.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Thank you Bruce for the help!

[Former Member]

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/BH...

[Former Member]

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

I'm out of ideas

[Former Member]

Hi David;

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

Regards .. Chris

[Former Member]

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

[Former Member]

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.

[Former Member]

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)

IS

   txml           XMLTYPE;

BEGIN

-- Convert Clob Received into an XML document

   txml := xmltype (xmlin);

.....

END;

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Does anyone still use Oracle?   LOL

[Former Member]

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

[Former Member]

I already tried that.

[Former Member]

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.

[Former Member]

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

[Former Member]

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)