Re: inserting null in XMLType

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 30 Nov 2006 03:12:05 -0800
Message-ID: <1164885125.522016.240960@j72g2000cwa.googlegroups.com>

Aakash ha escrito:

> Hi, my table has 2 xml columns and am trying to insert values using
> host variables.
> insert into xmltab values(XMLType(:H0), XMLType(:H1))
> Unfortunately one of these host variables (or both) is NULL and when
> its NULL, oracle complains.
>
> What is the way to insert NULL in such situations?
>
> Thanks
> Aakash

Quick and dirty:

sql> create table xml_tab_example (id_n integer not null, data xmltype);

Tabla creada.

sql> insert into xml_tab_example values (3, xmltype(NULL)) ; insert into xml_tab_example values (3, xmltype(NULL))

                                                *

ERROR en línea 1:
ORA-06553: PLS-307: demasiadas declaraciones de 'XMLTYPE' coinciden con esta llamada

sql> variable dummy varchar2(100);
sql> exec :dummy:=NULL;

sql> insert into xml_tab_example values (3, xmltype(:dummy)); insert into xml_tab_example values (3, xmltype(:dummy))

                                                *

ERROR en línea 1:

ORA-19032: Se esperaba la etiqueta XML , y se ha obtenido no content
ORA-06512: en "SYS.XMLTYPE", línea 0
ORA-06512: en línea 1

sql> insert into xml_tab_example values (4, decode(:dummy, NULL, NULL, xmltype(:dummy)));

1 fila creada.

sql> exec :dummy:= '<DATA><ID>4</ID><DESC>CUATRO</DESC></DATA>';

Procedimiento PL/SQL terminado correctamente.

sql> insert into xml_tab_example values (4, decode(:dummy, NULL, NULL, xmltype(:dummy)));

1 fila creada.

HTH Cheers.

Carlos. Received on Thu Nov 30 2006 - 05:12:05 CST