I think I need another set of eyes. I'm not seeing the invalid character in this dynamically generated SQL statement:
INSERT INTO source_arc
select v.*, sysdate, 'archived'
from source v
where v.id IN ( SELECT *
FROM TABLE( SELECT
CAST(str2tbl( 1,2,4 )
AS myTableType
)
FROM
dual ));
Here is the full setup:
create or replace type myTableType as table of number;
/
create or replace function str2tbl( p_str in varchar2 ) return myTableType
as
l_str varchar2(32767) default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := trim(substr(l_str,1,l_n-1));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end str2tbl;
/
create table source
( id number
,txt varchar2(25)
)
/
insert into source
values(1,'aaa')
/
insert into source
values(2,'bbb')
/
insert into source
values(3,'ccc')
/
insert into source
values(4,'ddd')
/
insert into source
values(5,'eee')
/
commit ;
create table source_arc
( id number
,txt varchar2(25)
,dt date
,comments varchar2(512)
)
/
)
/
Here is result when I run this code:
DECLARE
i_owner_tbl_arc varchar2(61) := 'source_arc';
i_owner_tbl varchar2(61) := 'source' ;
v_qry varchar2(32767) := 'INSERT INTO ' || i_owner_tbl_arc ;
g_content_list varchar2(32767) := '1,2,4';
BEGIN
v_qry := v_qry || chr(10) ||
'select v.*, sysdate, ''archived'' ' || chr(10) ||
'from ' || i_owner_tbl || ' v ' || chr(10) ||
'where v.id IN ( SELECT * ' || chr(10) ||
' FROM TABLE( SELECT ' || chr(10) ||
' CAST(str2tbl( ' || g_content_list || ' ) ' || chr(10) ||
' AS myTableType ' || chr(10) ||
' ) ' || chr(10) ||
' FROM dual ));';
execute immediate v_qry;
END ;
/
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 17
Any help appreciated.