Toad World® Forums

Export Data Issue (XML type)


#1

HI Guys,

  1. Create Table:

CREATE TABLE xml_data
(id NUMBER,
name VARCHAR2(60),
status VARCHAR2(20),
created_date DATE DEFAULT SYSDATE,
content XMLTYPE)
/

  1. Insert record as

INSERT INTO xml_data VALUES(1,‘Quest Test’,‘NEW’,sysdate,
xmltype(’
<breakfast_menu>

Belgian Waffles
$5.95
two of our famous Belgian Waffles with plenty of real maple syrup
650


Strawberry Belgian Waffles
$7.95
light Belgian waffles covered with strawberries and whipped cream
900


Berry-Berry Belgian Waffles
$8.95
light Belgian waffles covered with an assortment of fresh berries and whipped cream
900


French Toast
$4.50
thick slices made from our homemade sourdough bread
600


Homestyle Breakfast
$6.95
two eggs, bacon or sausage, toast, and our ever-popular hash browns
950

</breakfast_menu>
'));

  1. Export Data as ‘Table INSERT’

Result: SQL Nav exports INSERT statement with incorrect syntax as

INSERT INTO xml_dataVALUES(1,'Quest Test','NEW','FRIDAY, 18 AUGUST 2006',)/

This INSERT will not run.

Expected result: see original INSERT where the contents of XMLTYPE column is enclosed in xmltype(’’) function or even better to give users option in Export Properties as a check box to include xmltype(’) or omit it entirely (in which case you need to construct the column list in your INSERT statement something like:

INSERT INTO xml_data (id, name, status, created_date)
VALUES (1,‘Quest Test’,‘NEW’,sysdate)
/

The approach you are going to take is up to you but it needs to be fixed eventually because that dangling comma at the end of INSERT statement NAV generates currently makes INSERT statement syntactically incorrect

Regards,

Vadim


#2

Hi Vadim,

This issue is part of a more general problem: SQL Editor doesn’t export object types or LOBs. We need to attend this in the future. Thanks for picking this up - now we know that this functionality is actually needed.

Roman