9 Replies Latest reply: Aug 12, 2014 9:13 AM by leinadjan RSS

    Any why my function cause an no data found exception ?

    leinadjan


      Hello everybody,

       

      I got a weird problem with one search report using a function that return the custom SQL query.  I wasn't the first time I  did this kind of stuff, but it seems this time I can't seen why it's not working.

       

       

      I have a function with 10 parameters that will return an sql string that will be executed in a report region :

      create or replace
      function SEARCH(p_param1 varchar2,
                               p_param2 varchar2,
                               p_param3 varchar2,
                               p_param4 varchar2,
                               p_param5 varchar2,
                               p_param6 varchar2,
                               p_param7 varchar2,
                               p_param8 varchar2,
                               p_param9 varchar2,
                               p_param10 varchar2)
      return varchar2 as

        v_sql   varchar2(32767 ) := '';
        v_index number := 0;
      begin

       

        v_sql := 'SELECT col1, col2, col3, col4 FROM ( SELECT M.col1, M.col2, M.col3,';
        v_sql := v_sql || ' replace((select stragg(o.x) from tablex  o xwhere o.col1 = m.col1),'';'',''<br/>'') col4,';
        v_sql := v_sql || ' DECODE(M.col5,''Y'',''Yes'',''N'',''No'') col5, ';
        v_sql := v_sql || ' M.col6, M.col7, M.col8, M.col9, M.col10, ';
        v_sql := v_sql || ' (select stragg(d.col11) from tabley d where d.col1 = m.col1) col11, ';
        v_sql := v_sql || ' (select stragg(d.col12) from tabley d where d.col1 = m.col1) col12, ';
        v_sql := v_sql || ' (select stragg(d.col13) from tabley d where d.col1 = m.col1) col13 ';
        v_sql := v_sql || ' FROM  tablea M) ';


           if (p_param1 is not null and p_param1 <> 'NULL') or (p_param2 is not null and p_param2 <> 'NULL') then
          
           if v_index = 0 then
             v_sql := v_sql || ' WHERE ';
             v_index := v_index + 1;
           end if;
          
           if p_param1 <> 'NULL' and p_param2 <> 'NULL' then
              v_sql := v_sql || ' col6 = ''' || p_param1 || ''' and col7 = ''' || p_param2 || ''' '; 
           else
              if p_param1 <> 'NULL' then
                 v_sql := v_sql || ' col6 = ''' || p_param1 || ''' ';
              end if;
              if p_param2 <> 'NULL' then
                 v_sql := v_sql || ' col7 = ''' || p_param2 || ''' ';
              end if;
           end if;
        
        end if;
       
         if p_param3 is not null and p_param3<> 'NULL' then
       
           if v_index = 0 then
             v_sql := v_sql || ' WHERE ';
             v_index := v_index + 1;
           else
             v_sql := v_sql || ' AND ';
           end if;
          
           v_sql := v_sql || ' col8 = ''' || p_param3 || ''' ';
       
        end if;
       
       
        if (p_param4 is not null and p_param4 <> 'NULL') or (p_param5 is not null and p_param5 <> 'NULL') or (p_param6 is not null and p_param6 <> 'NULL') then  

           if v_index = 0 then
             v_sql := v_sql || ' WHERE ';
             v_index := v_index + 1;
           else
             v_sql := v_sql || ' AND ';
           end if;
          
           if p_param4 <> 'NULL' then
             v_sql := v_sql || ' col9 = ''' || p_param4  || ''' ';
           end if;
          
           if p_param5 <> 'NULL' then
             v_sql := v_sql || ' AND col10 = ''' || p_param5  || ''' ';
           end if;
          
           if p_param6 <> 'NULL' then
             v_sql := v_sql || ' AND col11 = ''' || p_param6  || ''' ';
           end if;

        end if;
       

       
        if (p_param7 is not null and p_param7 <> 'NULL') or (p_param8 is not null and p_param8 <> 'NULL') or (p_param9 is not null and p_param9 <> 'NULL') then  

           if v_index = 0 then
             v_sql := v_sql || ' WHERE ';
             v_index := v_index + 1;
           else
             v_sql := v_sql || ' AND ';
           end if;
          
           if p_param7 <> 'NULL' then
             v_sql := v_sql || ' INSTR(col12,''' || p_param7  || ''') > 0 ';
           end if;
          
           if p_param8 <> 'NULL' then
             v_sql := v_sql || ' AND INSTR(col13,''' || p_param8  || ''') > 0 ';
           end if;
          
           if p_des_ville <> 'NULL' then
             v_sql := v_sql || ' AND INSTR(col14,''' || p_param9  || ''') > 0 ';
           end if;

        end if;
       
       
        if (p_param10 is not null) then  

           if v_index = 0 then
             v_sql := v_sql || ' WHERE ';
             v_index := v_index + 1;
           else
             v_sql := v_sql || ' AND ';
           end if;
          
           v_sql := v_sql || ' INSTR(UPPER(col2 || '' '' || col3|| '' '' || col4),NVL(UPPER(''' || p_param10 || '''),UPPER(col2 || '' '' || col3 || '' '' || col4))) > 0';
          
        end if;
       
        return v_sql;
      end;

       

       

          And I call it correctly in my source region :  return  SEARCH(:P1_1, :P1_2,:P1_3,:P1_4,:P1_5,:P1_6, :P1_7, :P1_8, :P1_9, :P1_10);

       

        When I run the function in SQL Developper, the result is always correct and return exactly what I need to see.

       

      Plus I initiate the default values of my fields on entering the page.

       

      I tried to remove all the cariage return characters so that the result should be on one line.

       

      Any idea of what would my problem be ?

        • 1. Re: Any why my function cause an no data found exception ?
          HarryF

          This could be a problem:

           

          select stragg(o.x) from tablex  o xwhere o.col1 = m.col1

          • 2. Re: Any why my function cause an no data found exception ?
            eomolina

            tray to add this line just before return the sql:

             

            htp.p(v_sql);

             

            This way you can see if the query is correct or it have some error.

            • 3. Re: Any why my function cause an no data found exception ?
              fac586

              leinadjan wrote:

               


              Hello everybody,

               

              I got a weird problem with one search report using a function that return the custom SQL query.  I wasn't the first time I  did this kind of stuff, but it seems this time I can't seen why it's not working.

               

               

              I have a function with 10 parameters that will return an sql string that will be executed in a report region :

              create or replace
              function SEARCH(p_param1 varchar2,
                                      p_param2 varchar2,
                                      p_param3 varchar2,
                                      p_param4 varchar2,
                                      p_param5 varchar2,
                                      p_param6 varchar2,
                                      p_param7 varchar2,
                                      p_param8 varchar2,
                                      p_param9 varchar2,
                                      p_param10 varchar2)
              return varchar2 as

                v_sql  varchar2(32767 ) := '';
                v_index number := 0;
              begin

               

                v_sql := 'SELECT col1, col2, col3, col4 FROM ( SELECT M.col1, M.col2, M.col3,';
                v_sql := v_sql || ' replace((select stragg(o.x) from tablex  o xwhere o.col1 = m.col1),'';'',''<br/>'') col4,';
                v_sql := v_sql || ' DECODE(M.col5,''Y'',''Yes'',''N'',''No'') col5, ';
                v_sql := v_sql || ' M.col6, M.col7, M.col8, M.col9, M.col10, ';
                v_sql := v_sql || ' (select stragg(d.col11) from tabley d where d.col1 = m.col1) col11, ';
                v_sql := v_sql || ' (select stragg(d.col12) from tabley d where d.col1 = m.col1) col12, ';
                v_sql := v_sql || ' (select stragg(d.col13) from tabley d where d.col1 = m.col1) col13 ';
                v_sql := v_sql || ' FROM  tablea M) ';


                  if (p_param1 is not null and p_param1 <> 'NULL') or (p_param2 is not null and p_param2 <> 'NULL') then
                 
                  if v_index = 0 then
                    v_sql := v_sql || ' WHERE ';
                    v_index := v_index + 1;
                  end if;
                 
                  if p_param1 <> 'NULL' and p_param2 <> 'NULL' then
                      v_sql := v_sql || ' col6 = ''' || p_param1 || ''' and col7 = ''' || p_param2 || ''' '; 
                  else
                      if p_param1 <> 'NULL' then
                        v_sql := v_sql || ' col6 = ''' || p_param1 || ''' ';
                      end if;
                      if p_param2 <> 'NULL' then
                        v_sql := v_sql || ' col7 = ''' || p_param2 || ''' ';
                      end if;
                  end if;
               
                end if;
               
                if p_param3 is not null and p_param3<> 'NULL' then
               
                  if v_index = 0 then
                    v_sql := v_sql || ' WHERE ';
                    v_index := v_index + 1;
                  else
                    v_sql := v_sql || ' AND ';
                  end if;
                 
                  v_sql := v_sql || ' col8 = ''' || p_param3 || ''' ';
               
                end if;
               
               
                if (p_param4 is not null and p_param4 <> 'NULL') or (p_param5 is not null and p_param5 <> 'NULL') or (p_param6 is not null and p_param6 <> 'NULL') then

                  if v_index = 0 then
                    v_sql := v_sql || ' WHERE ';
                    v_index := v_index + 1;
                  else
                    v_sql := v_sql || ' AND ';
                  end if;
                 
                  if p_param4 <> 'NULL' then
                    v_sql := v_sql || ' col9 = ''' || p_param4  || ''' ';
                  end if;
                 
                  if p_param5 <> 'NULL' then
                    v_sql := v_sql || ' AND col10 = ''' || p_param5  || ''' ';
                  end if;
                 
                  if p_param6 <> 'NULL' then
                    v_sql := v_sql || ' AND col11 = ''' || p_param6  || ''' ';
                  end if;

                end if;
               

               
                if (p_param7 is not null and p_param7 <> 'NULL') or (p_param8 is not null and p_param8 <> 'NULL') or (p_param9 is not null and p_param9 <> 'NULL') then

                  if v_index = 0 then
                    v_sql := v_sql || ' WHERE ';
                    v_index := v_index + 1;
                  else
                    v_sql := v_sql || ' AND ';
                  end if;
                 
                  if p_param7 <> 'NULL' then
                    v_sql := v_sql || ' INSTR(col12,''' || p_param7  || ''') > 0 ';
                  end if;
                 
                  if p_param8 <> 'NULL' then
                    v_sql := v_sql || ' AND INSTR(col13,''' || p_param8  || ''') > 0 ';
                  end if;
                 
                  if p_des_ville <> 'NULL' then
                    v_sql := v_sql || ' AND INSTR(col14,''' || p_param9  || ''') > 0 ';
                  end if;

                end if;
               
               
                if (p_param10 is not null) then

                  if v_index = 0 then
                    v_sql := v_sql || ' WHERE ';
                    v_index := v_index + 1;
                  else
                    v_sql := v_sql || ' AND ';
                  end if;
                 
                  v_sql := v_sql || ' INSTR(UPPER(col2 || '' '' || col3|| '' '' || col4),NVL(UPPER(''' || p_param10 || '''),UPPER(col2 || '' '' || col3 || '' '' || col4))) > 0';
                 
                end if;
               
                return v_sql;
              end;

               

               

                  And I call it correctly in my source region :  return  SEARCH(:P1_1, :P1_2,:P1_3,:P1_4,:P1_5,:P1_6, :P1_7, :P1_8, :P1_9, :P1_10);

               

                When I run the function in SQL Developper, the result is always correct and return exactly what I need to see.

               

              Plus I initiate the default values of my fields on entering the page.

               

              I tried to remove all the cariage return characters so that the result should be on one line.

               

              Any idea of what would my problem be ?

              The code as posted does not compile:

               

              FUNCTION SEARCH compiled

              Errors: check compiler log

              98/9          PLS-00201: identifier 'P_DES_VILLE' must be declared

              98/6          PL/SQL: Statement ignored

               

              So this statement "When I run the function in SQL Developper, the result is always correct and return exactly what I need to see" is false.

               

              If you want help, post valid code. Better still, reproduce the problem on apex.oracle.com with all of the dependent objects, then we can see it at first hand.

               

              Also state what the correct result "exactly what I need to see" actually is, and explain exactly when and where the error occurs, and the parameters used when it fails.

               

              Most importantly, this implementation is over complex (and hence error prone) and inefficient (because it doesn't use bind variables and generates lots of unique SQL). See this AskTom question and linked article for a much better flexible filtering technique. For APEX use, don't use a ref cursor, simply generate and return the query string, referencing the APEX items as the bind variables.

              • 4. Re: Any why my function cause an no data found exception ?
                fac586

                eomolina wrote:

                 

                tray to add this line just before return the sql:

                 

                htp.p(v_sql);

                 

                This way you can see if the query is correct or it have some error.

                Better still, use the apex_debug package to instrument the function to log what it's doing and the SQL it generates to the debug log.

                • 5. Re: Any why my function cause an no data found exception ?
                  leinadjan


                  Hi,

                   

                  thank you for your reply.  I didn't post my real code, I had to mask it.  There is no error in my code because the function is compiling correctly.  As I said, when I run it from SQL Developper, the returned query is working.  I have no choice to use a stragg function.

                  • 6. Re: Any why my function cause an no data found exception ?
                    leinadjan


                    Hi,

                     

                    well I did create a debug table to logs all the operations and it seems to work perfectly inside the function.  The problem seems to be when running the returned query.

                     

                    Thank you !

                    • 7. Re: Any why my function cause an no data found exception ?
                      leinadjan

                      Hi,

                       

                      of course my code does not compile, I  had to change the variable names because I can't share my source code with you.  Anyway, it's using many tables you don't have in you schemas and I may have left errors while I was renaming my variables.

                       

                      Yes, I should use apex.oracle.com

                       

                      Thank you !

                      • 8. Re: Any why my function cause an no data found exception ?
                        leinadjan


                        Ok, but I must try it inside my report's source because htp.p doesn't do anything outside of APEX.

                        • 9. Re: Any why my function cause an no data found exception ?
                          leinadjan

                          Hi HarryF,

                           

                          Since I did not understand the real problem, I decided to do it the old way, and this is working well.  Anyway, the query was not too big to use it as a simple query.

                           

                          Thank you all for your time !