DB2 Stored Procedure

[pankajit09]

The below is an Oracle Stored procedure -->

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 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;

The below Query uses the above function -->

select table_name,cols from (
 
            select rownum,
 
            table_name,
 
            search_in_table('SYSMAN',table_name,'[[:digit:]]') cols
 
            from all_tables where owner='SYSMAN' and tablespace_name='SYSTEM')
 
            where cols is not null;

Please convert the function and the Query to DB2 Stored Procedure and Query respectively.

[docdiesel]

Hi,

in case you didn't recognize so far: This is not an online forum to roll off your tasks to others. If you try yourself and come to problems, then post the code, the error message etc. and we'll see if we can help.

Regards,

Bernd