2 Replies Latest reply: Feb 3, 2013 11:45 PM by kikolus RSS

    Oracle Reports Function Problem

    988585
      Hi All,
      i've a serious problem with oracle reports.
      i've written a function with if else statement. But this select statement returns more than one value. In this case my if-else and the report does not work at all.
      If the return value is one row the statement is OK, but the statement returns 3 rows!
      the SQL statement is below; any help will be much appreciated;

      function CF_6 return char  is
      l_value  varchar2(1000);
      begin
      select distinct dd.SUPP3_3 into l_value
      from xxdeniz.XXDP_VESSEL_DGOODS dg, xxdeniz.XXDP_DGOODS_DETAIL dd
      where dd.CARGO_DESCRIPTION = 'Cargo Holds'
      and dg.VESSEL_ID= :CF_VESSEL_ID
      and dg.DGOODS_ID = '46'
      and dg.DGOODS_DETAIL_ID = dd.DGOODS_DETAIL_ID
      and dd.SUPP3_3 is not null;



      if l_value = '1'
      then l_value :=
      *'*
      *1.CHOPPED RUBBER AND PLASTIC INSULATION, COARSE CHOPPED TYRES and GRANULATE TYRE RUBBER are not permitted to be loaded when the planned interval between the commencement of loading and the completion of discharge of the cargoes exceeds 5 days.' ;*
      elsif l_value = '2'
      then l_value :=
      *'*
      *2.AMMONIUM NITRATE BASED FERTILIZER (non-hazardous) is to be stowed out of direct contact with a metal engine room boundary.' ;*
      elsif l_value = '3'
      then l_value :=
      *'*
      *3.Only natural ventilation is required for the transportation of corresponding cargo, mechanical ventilation is prohibited. And the electrical appliances need not have a type of protection if they can be isolated from the electrical supply and safeguarded against uninternational reconnection.' ;*
      then l_value := null;
      end IF;
      return l_value ;
      end;
        • 1. Re: Oracle Reports Function Problem
          HamidHelal
          Hi 985582,
          Welcome to the Oracle Forums. Please take a few minutes to review the following:

          <ul>
          <li>Oracle Forums FAQ
          <li>Before posting on this forum please read
          <li>10 Commandments for the OTN Forums Member
          <li>How to ask questions the smart way
          </ul>

          Following these simple guidelines will ensure you have a positive experience in any forum; not just this one!

          Now come to u'r point.
          It's an idea.

          Declare a cursor in the declare section with your SQL

          now open and read your cursor and do your IF ELSE END IF condition.


          Hope it helps

          Hamid

          Mark correct/helpful to help others to get right answer(s).*
          • 2. Re: Oracle Reports Function Problem
            kikolus
            985582 wrote:
            select distinct dd.SUPP3_3 into l_value
            from xxdeniz.XXDP_VESSEL_DGOODS dg, xxdeniz.XXDP_DGOODS_DETAIL dd
            where dd.CARGO_DESCRIPTION = 'Cargo Holds'
            and dg.VESSEL_ID= :CF_VESSEL_ID
            and dg.DGOODS_ID = '46'
            and dg.DGOODS_DETAIL_ID = dd.DGOODS_DETAIL_ID
            and dd.SUPP3_3 is not null;
            Hi,
            If your select returns more than one row at time and you try to assign result to the variables then you always get TOO_MANY_ROWS exception. To prevent this you should do one of the following:
            * write your query properly (best solution)
            * catch exception and handle it (good solution)
            * change your query to cursor and iterate through result or exist after firs row (poor solution, but still solution :) )
            Cause I'm not abe to fix your query I can only provide some guidelines for last two proposal:
            - To catch exception you should do something like this:
             
            begin 
              select distinct dd.SUPP3_3 into l_value 
              from xxdeniz.XXDP_VESSEL_DGOODS dg, xxdeniz.XXDP_DGOODS_DETAIL dd 
              where dd.CARGO_DESCRIPTION = 'Cargo Holds' 
              and dg.VESSEL_ID= :CF_VESSEL_ID 
              and dg.DGOODS_ID = '46' 
              and dg.DGOODS_DETAIL_ID = dd.DGOODS_DETAIL_ID 
              and dd.SUPP3_3 is not null; 
            exception then TOO_MANY_ROWS then 
              -- your logic here or null statement; 
            end; 
            - to change query to cursor :
             
            for r in (select distinct dd.SUPP3_3 into l_value 
                      from xxdeniz.XXDP_VESSEL_DGOODS dg, xxdeniz.XXDP_DGOODS_DETAIL dd 
                      where dd.CARGO_DESCRIPTION = 'Cargo Holds' 
                      and dg.VESSEL_ID= :CF_VESSEL_ID 
                      and dg.DGOODS_ID = '46' 
                      and dg.DGOODS_DETAIL_ID = dd.DGOODS_DETAIL_ID 
                      and dd.SUPP3_3 is not null) 
            loop 
            -- your logic here or exit statement 
            end loop;   
            Hope this helps