2 Replies Latest reply: Feb 6, 2013 4:06 AM by Dird RSS

    PL/SQL to return to the beginning of a table/reset value

    Dird
      Hi,

      My latest task involves having a page display 1 set of data every n seconds, e.g. data on each database. The table contains a PK which defines the order. Now if I have 15 databases (number unkown) how can I reset it to 1 when I've reached the end? I should also reset the DB name item. This is what I'm attempting with a PL/SQL Process (Before regions):
      begin
      IF (select 1 from monitor_status 
      where DB_MONITOR_PK = :P2_HIDDEN_DB_NM) = 0 then
      :P2_HIDDEN_DB_NM := 1;
      --select db_name into :P2_DBNAME from monitor_status
      -- where DB_MONITOR_PK = 1;
      end if;
      end;
      The idea is if there's no row with that PK then return to the first but it complains about the select in the IF statement :(

      Mike
        • 1. Re: PL/SQL to return to the beginning of a table/reset value
          fac586
          Dird wrote:
          Hi,

          My latest task involves having a page display 1 set of data every n seconds, e.g. data on each database. The table contains a PK which defines the order. Now if I have 15 databases (number unkown) how can I reset it to 1 when I've reached the end? I should also reset the DB name item. This is what I'm attempting with a PL/SQL Process (Before regions):
          begin
          IF (select 1 from monitor_status 
          where DB_MONITOR_PK = :P2_HIDDEN_DB_NM) = 0 then
          :P2_HIDDEN_DB_NM := 1;
          --select db_name into :P2_DBNAME from monitor_status
          -- where DB_MONITOR_PK = 1;
          end if;
          end;
          The idea is if there's no row with that PK then return to the first but it complains about the select in the IF statement :(
          Yes, that's not valid in PL/SQL.

          Assuming <tt>P2_HIDDEN_DB_NM</tt> is being incremented (from the "when I've reached the end" comment), where and when is this done? Can't it be reset there?

          Use a <tt>no_data_found</tt> exception handler:
          begin
          
            select
                :p2_hidden_db_nm
            into
                :p2_hidden_db_nm
            from
                monitor_status
            where
                db_monitor_PK = :p2_hidden_db_nm;
          
          exception
          
            when no_data_found
            then
              :p2_hidden_db_nm := 1;
          
              select
                  db_name
              into
                  :p2_dbname
              from
                  monitor_status
              where
                  db_monitor_pk = :p2_hidden_db_nm;
          
          end;
          • 2. Re: PL/SQL to return to the beginning of a table/reset value
            Dird
            >
            Assuming <tt>P2_HIDDEN_DB_NM</tt> is being incremented (from the "when I've reached the end" comment), where and when is this done? Can't it be reset there?
            Great fac586, works perfectly. Thanks :) Yeah my PL/SQL is a bit dodgy at the minute (was just translating rownum%2=0 ? 'A':'B' from JS into PL/SQL :))

            The request was for the one page to display a new databases data every 30 seconds. The way I plan to implement this is to have a JS timer reload the page but declaring the hidden P2_HIDDEN_DB_NUM as the current value+1, because this approach doesn't interact with the database I wanted this process check before page load.

            Thanks for the help :)

            Mike