| Author |
Message
|
| Goon Bottle |
 Posted: Tue Jan 17, 2006 7:37 am Post subject: ESQL to Oracle 9i XMLType Column |
 |
|
Newbie
Joined: 17 Jan 2006
Posts: 3
Location: London
|
Does anyone have a solution for inserting XML into an Oracle 9 XMLType column. Despite various different ESQL attempts I either get NULL or have to use a CLOB column. There are processes on the database that will use Oracle XML extensions so I really need this XMLType column populated.
Broker is v5.3 on Solaris 9
Database is Ora 9i (9.2)
Thanks in advance. |
|
| Back to top |
|
 |
| Tibor |
| Posted: Tue Jan 17, 2006 8:03 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Use the PASSTHRU() for non-standard database functions.
HTH,
Tibor |
|
| Back to top |
|
|
| Goon Bottle |
| Posted: Tue Jan 17, 2006 8:21 am Post subject: |
|
|
Newbie
Joined: 17 Jan 2006
Posts: 3
Location: London
|
Tried PASSTHRU() and it didn't do anything for me. I was expecting something as simple as;
INSERT INTO Database.mySchema.myTable(textcol, xmlcol) VALUES ("Hello World", InputRoot.XML.Message);
to do the trick. That just gives me "Hello World" and NULL. So I have been trying the various things with ASBITSTREAM(). However when in an interactive database session, SQL like;
SELECT e.xmlcol.getstringval() from myTable e;
just return NULL. |
|
| Back to top |
|
|
| Ian |
| Posted: Wed Jan 18, 2006 8:17 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
My understanding of this would be that the Oracle 9i datatype XMLTYPE is not supported.
This assertion is based on the following :
1. WBIMB v5 topic "ak05730_" covers the "Data types of values from external sources" and it does not include this datatype under Oracle.
2. WBIMB v5 uses the DataDirect 4.1 driver which was certified to connect to Oracle 9i and to work with Oracle 8 functionality. The 4.1 driver does not provide support for new 9i features (including the Oracle datatype TIMESTAMP).
One (untested) suggestion would be to pass the data to an Oracle stored procedure and let the stored procedure insert the data into this datatype.
Note : WBIMB v5 topic "ak04970_" covers "CREATE PROCEDURE statement" and under section "All external procedures have the following restrictions:" it states that parameters cannot map onto the database LOB types (for example, BLOB and CLOB).
This implies that stored procedures can not be called with LOB datatypes. However, I think that the restriction for this was removed under APAR IY52570 which was implemented in v5 FP4. The fix under APAR IY52570 was to get the correct datatype and precision for LOB datatypes.
_________________
Regards, Ian |
|
| Back to top |
|
|
| Goon Bottle |
| Posted: Thu Jan 19, 2006 2:05 am Post subject: |
|
|
Newbie
Joined: 17 Jan 2006
Posts: 3
Location: London
|
| Ian many thanks for this. I shall have to try and invent a work-around. |
|
| Back to top |
|
|
|
|
