Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Delete from table where row not in PL/SQL XMLtype variable  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
Доброго времени суток всем.
Вот уже полдня бьюсь не могу ослилить, прошу помогите.
Дано: XML в котором хранятся ID записей не подлежащие удалению из таблицы сохранен в PL/SQL's XMLType - переменной
пытаюсь:
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

declare
  v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
begin
delete from a
  where
   a.a not in (select ExtractValue(value(t),'a')
           from table(XMLSequence(v_XML.Extract('b/a'))) t);
end;

ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at line 4
оданако же, тем не менее:
begin
delete from a
  where
   a.a not in (select ExtractValue(value(t),'a')
           from table(XMLSequence(XMLType('<b><a>2</a><a>3</a></b>').Extract('b/a'))) t);
  :cnt := sql%rowcount;
end;
/

PL/SQL procedure successfully completed
cnt
---------
1
rollback to savepoint hello;

Rollback complete
declare
  v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
begin
  select count(*)
  into :cnt
   from a
    where
     a.a not in (select ExtractValue(value(t),'a')
           from table(XMLSequence(v_XML.Extract('b/a'))) t);
end;
/

PL/SQL procedure successfully completed
cnt
---------
1
24 авг 06, 16:37    [3049370]     Ответить | Цитировать Сообщить модератору
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
Очень жаль что тема не получила обсуждения.
К вышесказанному добавлю лишь что ситуация, описанная для delete повторяется для update.
В данный момент закостылился следующим образом:
declare
  v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
  c     sys_refcursor;
  type trows is table of number;
  rows trows;
begin
  open c for
    select a
      from a
     where a.a not in
           (select ExtractValue(value(t), 'a')
              from table(XMLSequence(v_XML.Extract('b/a'))) t);
  fetch c bulk collect
    into rows;
  forall i in rows.first .. rows.last
    delete a
     where a = rows(i);
  :cnt := sql%rowcount;
end;
/

PL/SQL procedure successfully completed
cnt
---------
1

не верх, конечно, изящества, но изящество в данном случае критерий лишь только для меня.
25 авг 06, 13:10    [3053372]     Ответить | Цитировать Сообщить модератору
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Andrew IF
Member

Откуда: Москва
Сообщений: 741
ИМХО, это какие-то баги версии.

SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod                
PL/SQL Release 10.2.0.2.0 - Production                                          
CORE	10.2.0.2.0	Production                                                      
TNS for Linux: Version 10.2.0.2.0 - Production                                  
NLSRTL Version 10.2.0.2.0 - Production                                          

SQL> declare
  2    v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
  3  begin
  4  delete from testtab a
  5    where
  6     a.a not in (select ExtractValue(value(t),'a')
  7             from table(XMLSequence(v_XML.Extract('b/a'))) t);
  8  end;
  9  /

PL/SQL procedure successfully completed.
25 авг 06, 13:57    [3053715]     Ответить | Цитировать Сообщить модератору
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10042
Zloxa
Очень жаль что тема не получила обсуждения.


ORA-22905 using PLSQL variable with TABLE(xmlsequence()...) Bug 4187886 solved in Oracle 10g

SY.
25 авг 06, 17:33    [3055435]     Ответить | Цитировать Сообщить модератору
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Zloxa
Member

Откуда: СССР ☭
Сообщений: 1033
SY
Bug 4187886 solved in Oracle 10g

Спасибо большое, мне стало намного легче :) Неудобства доставленные мной моему самолюбию, обязуюсь компенсировать сегодня изрядным количеством пыва.
25 авг 06, 17:50    [3055534]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Alex Nik
Member

Откуда: Moscow city
Сообщений: 40
выход есть вот такой, проверил, работает и меньше букв
declare
  v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
begin
  delete from a
    where
      v_XML.existsNode('b[a='||a.a||']')=0;
end;
8 ноя 07, 13:42    [4890783]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Евгений_25
Member

Откуда: Харьков
Сообщений: 460
а мы побороли ее обернув запрос через execute immediate...
21 июл 09, 16:29    [7441370]     Ответить | Цитировать Сообщить модератору
 Re: Delete from table where row not in PL/SQL XMLtype variable  [new]
Elic
Member

Откуда:
Сообщений: 29979
Есть ещё cast-трюк для скалярных переменных:
declare
  v_XML XMLType := XMLType('<b><a>2</a><a>3</a></b>');
begin
delete from a
  where
   a.a not in (select ExtractValue(column_value,'a')
           from table(XMLSequence(XMLType(cast(v_XML.getStringVal() as varchar2(4000))).Extract('b/a'))) t);
end;
/

PL/SQL procedure successfully completed.
+
declare
  s_XML varchar2(100) := '<b><a>2</a><a>3</a></b>';
begin
delete from a
  where
   a.a not in (select ExtractValue(column_value,'a')
           from table(XMLSequence(XMLType(s_XML).Extract('b/a'))) t);
end;
/
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item
ORA-06512: at line 4


declare
  s_XML varchar2(100) := '<b><a>2</a><a>3</a></b>';
begin
delete from a
  where
   a.a not in (select ExtractValue(column_value,'a')
           from table(XMLSequence(XMLType(cast(s_XML as varchar2(4000))).Extract('b/a'))) t);
end;
/

PL/SQL procedure successfully completed.
21 июл 09, 17:24    [7441653]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить