complex sql search

Declare

  sqlStr_part1 varchar2(4000);

  sqlStr_part2 varchar2(4000);

  sqlStr_part3 varchar2(4000);

  sqlStr_part4 varchar2(4000);

  out_string_in_part1 varchar2(4000);

  out_string_in_part2 varchar2(4000);

  out_string_in_part3 varchar2(4000);

  out_string_in_part4 varchar2(4000);

  TYPE   cur_type   IS   REF   CURSOR;  

  d_cursor_tab_part1   cur_type;

  d_cursor_tab_part2   cur_type;

  d_cursor_tab_part3   cur_type;

  d_cursor_tab_part4   cur_type;

  idNum_part1 NUMBER;

  idNum_part2 NUMBER;

  idNum_part3 NUMBER;

  idNum_part4 NUMBER;

  v_rows NUMBER;

  str_len_part1 NUMBER;

  str_len_part2 NUMBER;

  str_len_part3 NUMBER;

  str_len_part4 NUMBER;

  loop_count_part1 NUMBER;

  loop_count_part2 NUMBER;

  loop_count_part3 NUMBER;

  loop_count_part4 NUMBER;

  cursor searchSQL_Cursor_part1 is

         select 'select count(*) from "'|| t.TABLE_NAME || '" t where 

             '|| 'upper("' ||COLUMN_NAME || '") like ''%PFINGOI%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%R538LA%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%R519LA%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%R532LA%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%4249B%''   or '|| 'upper("' || COLUMN_NAME ||'") like ''%4977B01A%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%3437A%''   or '|| 'upper("' || COLUMN_NAME||'") like ''%3437C%''    or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A-LOOP-UTM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A-LOOP-MIM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R614A%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613A%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R455A%''or '|| 'upper("' || COLUMN_NAME||'") like ''%DFDRLGDM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R613-LOOP-TESTS%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%AAAA-LOOP-TESTS%''  or '|| 'upper("' || COLUMN_NAME||'") like ''%R143%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R123%'' 

          or '|| 'upper("' || COLUMN_NAME||'") like ''%R162%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%1870%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3221%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3624%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3131%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3037%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%3698%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R184%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%2900%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4270%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4035%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4450%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%4675%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R473%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R342%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%R358%'' '

          as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

         

  cursor searchSQL_Cursor_part2 is

         select 'select count(*) from "'|| t.TABLE_NAME || '" t where  

             '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG-LOOP-GOI%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG57L1PBMAC1%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG47L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG45L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P65NLG38L1P8M7C1%'' 

          or '|| 'upper("' || COLUMN_NAME||'") like ''%P55NLG35L1P8M7C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P11ULG32L1P7M6C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P11ULG32L1P7M6C1%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG-LOOP-UTM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG-LOOP-MIM%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C3%''or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C2%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09ULG54L1PAM9C1%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09UCI-LOOP-TESTS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%PDDF_LGDMY%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P09UCI-LOOP-TESTS%'' '

          as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

          

  cursor searchSQL_Cursor_part3 is

         select 'select count(*) from "'|| t.TABLE_NAME || '" t where  

             '|| 'upper("' ||COLUMN_NAME || '") like ''%0920POX0116%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%0760SIN1250%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%1000LOX0106%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%1100ANN120M%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%0650SIN0300%''   or '|| 'upper("' || COLUMN_NAME ||'") like ''%0800WGO0051%''  or '|| 'upper("' || COLUMN_NAME ||'") like ''%0850GOX0032-65LL%''   or '|| 'upper("' || COLUMN_NAME||'") like ''%0620UPY1000-65LL%''    or '|| 'upper("' || COLUMN_NAME||'") like ''%0410ALY030M-65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800SOX0029%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800SOX0028%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%0800ANN060M-90G%''  or '|| 'upper("' || COLUMN_NAME||'") like ''%0650SIN0206B%''or '|| 'upper("' || COLUMN_NAME||'") like ''%0650SIN0124%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%B1AL10N700-C124%'' 

          or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-12%''  or '|| 'upper("' || COLUMN_NAME||'") like ''%C65%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-42%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C090N200-43%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%C90%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E30C65N200-D43%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_16KAV2E1A1-13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_7KAV1E2A1-123%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_7KAV1E2A1-2%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%P_8KAV1E2A1-23%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C12%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C13%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%E35T05N50-C42%'' '

          as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';

          

  cursor searchSQL_Cursor_part4 is

         select 'select count(*) from "'|| t.TABLE_NAME || '" t where '|| 'upper("' ||COLUMN_NAME || '") like ''%LOGIC%'' or '|| 'upper("' || COLUMN_NAME ||'") like ''%LG%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%65LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%55LL%'' or '|| 'upper("' || COLUMN_NAME||'") like ''%90RF%'' or '

         || 'upper("' || COLUMN_NAME||'") like ''%CIS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%CCD%''or '|| 'upper("' || COLUMN_NAME||'") like ''%FSI%''or '|| 'upper("' || COLUMN_NAME||'") like ''%BSI%''or '|| 'upper("' || COLUMN_NAME||'") like ''%APS%''or '|| 'upper("' || COLUMN_NAME||'") like ''%R614%''or '|| 'upper("' || COLUMN_NAME||'") like ''%R613%''or ' || 'upper("' || COLUMN_NAME||'") like ''%IMAGE SENSOR%'' '

         as sqlStrr, TABLE_NAME from user_tab_columns t where data_type = 'VARCHAR2' or data_type = 'CHAR';          

          

begin 

  

   --part1 loop

   for searchSQL_Cursor_Point_part1 in searchSQL_Cursor_part1 loop

     DBMS_OUTPUT.enable(1000000);

     sqlStr_part1 := searchSQL_Cursor_Point_part1.sqlStrr;

     out_string_in_part1:=sqlStr_part1;

     str_len_part1 := length(sqlStr_part1);

     loop_count_part1 := 0;

 

     execute immediate sqlStr_part1;

 

     

     

     OPEN   d_cursor_tab_part1   FOR   sqlStr_part1;

     LOOP  

              FETCH   d_cursor_tab_part1   INTO   idNum_part1;  

              EXIT   WHEN   d_cursor_tab_part1%NOTFOUND;

              

              if (idNum_part1>0)

                 then

                    WHILE loop_count_part1<str_len_part1 loop       

                          dbms_output.put_line( substr( out_string_in_part1, loop_count_part1 +1, 255 ) );       

                          loop_count_part1 := loop_count_part1 +255;    

                    end loop;

                    DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for prodID find table:'||searchSQL_Cursor_Point_part1.TABLE_NAME||'Found:='||idNum_part1||'=exists!');

              end if;

              --   process   record  

     END   LOOP; 

    end loop; 

    

    --part2 loop

    for searchSQL_Cursor_Point_part2 in searchSQL_Cursor_part2 loop

     DBMS_OUTPUT.enable(1000000);

     sqlStr_part2 := searchSQL_Cursor_Point_part2.sqlStrr;

     out_string_in_part2:=sqlStr_part2;

     str_len_part2 := length(sqlStr_part2);

     loop_count_part2 := 0;

 

     execute immediate sqlStr_part2;

 

     

     

     OPEN   d_cursor_tab_part2   FOR   sqlStr_part2;

     LOOP  

              FETCH   d_cursor_tab_part2   INTO   idNum_part2;  

              EXIT   WHEN   d_cursor_tab_part2%NOTFOUND;

              

              if (idNum_part2>0)

                 then

                    WHILE loop_count_part2<str_len_part2 loop       

                          dbms_output.put_line( substr( out_string_in_part2, loop_count_part2 +1, 255 ) );       

                          loop_count_part2 := loop_count_part2 +255;    

                    end loop;

                    DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for planID find table:'||searchSQL_Cursor_Point_part2.TABLE_NAME||'Found:='||idNum_part2||'=exists!');

              end if;

              --   process   record  

     END   LOOP; 

    end loop;

    

    --part3 loop

    for searchSQL_Cursor_Point_part3 in searchSQL_Cursor_part3 loop

     DBMS_OUTPUT.enable(1000000);

     sqlStr_part3 := searchSQL_Cursor_Point_part3.sqlStrr;

     out_string_in_part3:=sqlStr_part3;

     str_len_part3 := length(sqlStr_part3);

     loop_count_part3 := 0;

 

     execute immediate sqlStr_part3;

 

     

     

     OPEN   d_cursor_tab_part3   FOR   sqlStr_part3;

     LOOP  

              FETCH   d_cursor_tab_part3   INTO   idNum_part3;  

              EXIT   WHEN   d_cursor_tab_part3%NOTFOUND;

              

              if (idNum_part3>0)

                 then

                    WHILE loop_count_part3<str_len_part3 loop       

                          dbms_output.put_line( substr( out_string_in_part3, loop_count_part3 +1, 255 ) );       

                          loop_count_part3 := loop_count_part3 +255;    

                    end loop;

                    DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for recipeID find table:'||searchSQL_Cursor_Point_part3.TABLE_NAME||'Found:='||idNum_part3||'=exists!');

              end if;

              --   process   record  

     END   LOOP; 

    end loop;

    

    --part4 loop

    for searchSQL_Cursor_Point_part4 in searchSQL_Cursor_part4 loop

     DBMS_OUTPUT.enable(1000000);

     sqlStr_part4 := searchSQL_Cursor_Point_part4.sqlStrr;

     out_string_in_part4:=sqlStr_part4;

     str_len_part4 := length(sqlStr_part4);

     loop_count_part4 := 0;

 

     execute immediate sqlStr_part4;

 

     

     

     OPEN   d_cursor_tab_part4   FOR   sqlStr_part4;

     LOOP  

              FETCH   d_cursor_tab_part4   INTO   idNum_part4;  

              EXIT   WHEN   d_cursor_tab_part4%NOTFOUND;

              

              if (idNum_part4>0)

                 then

                    WHILE loop_count_part4<str_len_part4 loop       

                          dbms_output.put_line( substr( out_string_in_part4, loop_count_part4 +1, 255 ) );       

                          loop_count_part4 := loop_count_part4 +255;    

                    end loop;

                    DBMS_OUTPUT.PUT_LINE('--------------------------------------Search for Logic or CIS keyword find table:'||searchSQL_Cursor_Point_part4.TABLE_NAME||'Found:='||idNum_part4||'=exists!');

              end if;

              --   process   record  

     END   LOOP; 

    end loop;

    

end;