Syntax error

[pankajit09]

Hello,


A syntax error is coming in the following oracle code:


[CODE=oracle] stmt := stmt || 'max(case when "' || c.column_name ||
'" REGEXP_LIKE(' || c.column_name || ',''' || search_string || ''') then ''' || c.column_name
|| ' ''' || ' else null end) || ';
[/CODE]


Please help.

[pankajit09]

[CODE=oracle]create or replace function search_in_table (
owner varchar2,
table_name varchar2,
search_string varchar2)
return varchar2 is
stmt varchar2(32700) ;
col varchar2(32700) ;
begin
stmt := 'select ';
for c in (
select column_name
from all_tab_columns
where all_tab_columns .table_name=sea rch_in_table.ta ble_name
and all_tab_columns .owner=search_i n_table.owner
and data_type like '%CHAR%' )
loop
stmt := stmt || 'max(case when "' || c.column_name ||'" REGEXP_LIKE(''' || c.column_name || ''',"' || search_string || '") then ''' || c.column_name|| ' ''' || ' else null end) || ';
end loop;
stmt:= stmt||'nvl2(cou nt(*),null,null ) from "'||owner||'"." '||table_name|| '"';
execute immediate stmt into col;
return col;
end;[/CODE]


and when I call the above function using the below code :

[CODE=oracle]select table_name,cols from (
select rownum,
table_name,
search_in_table ('SCOTT',table_ name,'[[:digit:]]') cols
from all_tables where owner='SCOTT')
where cols is not null;[/CODE]



the follwing error comes :

[CODE=oracle]search_in_table ('SCOTT',table_ name,'[[:digit:]]') cols
*
ERROR at line 4:
ORA-00920: invalid relational operator
ORA-06512: at "SYS.SEARCH_IN_ TABLE", line 20[/CODE]