Syntax error

[pankajit09]

Hello,


A syntax error is coming in the following oracle code:

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

Please help.

[pankajit09]

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=search_in_table.table_name
                                    and all_tab_columns.owner=search_in_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(count(*),null,null) from "'||owner||'"."'||table_name||'"';
        execute immediate stmt into col;
            return col;
end;

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

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;

the follwing error comes :

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