oracle - Pl/Sql array inside a statement -

i'm trying prepare function, i've started sql sketch figure out how manage situation:

 declare   x xmltype;   begin x := xmltype('<?xml version="1.0"?> <rowset>  <row>    <start_datetime>29/05/2015 14:23:00</start_datetime>   </row>  <row>    <start_datetime>29/05/2015 17:09:00</start_datetime>   </row> </rowset>');     r in (     select extractvalue(value(p),'/row/start_datetime/text()') deleted        table(xmlsequence(extract(x,'/rowset/row'))) p     ) loop     -- whatever want r.name, r.state, r.city     -- dbms_output.put_line( 'to_date('''|| r.deleted ||''', '''|| 'dd/mm/yyyy hh24:mi:ss'')');        dbms_output.put_line( ''''|| r.deleted ||'''');     delete  mytable a.start_datetime not in (''''|| r.deleted || '''');    end loop;   end; 

i've tried different ways perform query after loop has filled variable gaves me conversion error:

1858. 00000 - "a non-numeric character found numeric expected" *cause: input data converted using date format model incorrect. input data did not contain number number required format model. *action: fix input data or date format model make sure elements match in number , type. retry operation.

can me?

thanks!

you're wrapping string in explicit single quotes; making quotes part of string itself, don't want.

you need convert string data type, sort of doing in commented-out section - in case need quotes dbms_output() make text literal, , end valid to_date() call; end output that:

to_date('29/05/2015 14:23:00', 'dd/mm/yyyy hh24:mi:ss') 

but delete though need do:

delete mytable a.start_datetime not in (to_date(r.deleted, 'dd/mm/yyyy hh24:mi:ss')); 

the reference r.deleted string, refer directly, no additional quotes.

you have single value though, @ point in loop using not in not necessary , can use != instead:

delete mytable start_datetime != to_date(r.deleted, 'dd/mm/yyyy hh24:mi:ss'); 

your title mentions array, perhaps intend put values xml (schema-level type) table collection , use in not in clause, removes except dates in xml. doing individually delete in table if there more 1 date in xml, suggests either want use array, and/or meant in or = only remove those.

---

incidentally, extractvalue() deprecated, better use xmlquery or xmltable, e.g.:

  r in (     select *     xmltable('/rowset/row/start_datetime'       passing x columns deleted varchar2(19) path '.')     ) loop