5 Replies Latest reply: Oct 30, 2006 11:13 PM by 540593 RSS

    here i send the logic, which i have to use to develop a PL/SQL code

    540593
      hi

      here i send the logic, which i have to use to develop a PL/SQL code

      can u write a code for this in PL/SQL


      IMPLEMENTATION LOGIC

      1. if callers make a call
      then MIS_REPEAT_COUNT= MIS_REPEAT_COUNT+1
      start timer
      {
      if MIS_REPEAT_COUNT >PRARM.MIS_REPEAT_CALLER_THRESOLD or timer off then Summary tag should be updated (Repeat_caller_flag ='1' and 7days_flag='0')
      }
      2.when MIS_TIMESTAMP ='7days
      then Summary tag should be updated (Repeat_caller_flag='0' and 7days_flag='1')
      3.If MIS_REPEAT_COUNT=1 and MIS_TIMESTAMP= 7days
      then Summary tag should be updated (7days_flag='0'
      and 30days_flag='1')
      4. IF MIS_REPEAT_COUNT=1 and MIS_TIMESTAMP=23days
      then summary tag should be updated (30days_flag='0'
      and 90days_flag='1')
      5. IF MIS_REPEAT_COUNT=1 and MIS_TIMESTAMP= 60 days
      then summary tag should be updated (90days_flag='0')







      i m unable to understand how to start a timer, and how i will put condition on timer, also there is one summary tag field, it represend the status of customer.

      summary_tag varchar(60)

      eg. 0000000000000000102356875

      i have to use one of the field of summary tag as a flag , and want to update it

      what i was thinking is that i will use suppose 7 no field as a flag and whenever i have to set a flag , i will assign a perticular value to 7 no. field, suppose for 7 days flag value 2
      for 23 days value will be 3

      so on
      , so im unable to understand how could i extract the 7 no. filed and use it as a flag value & change the value according to condition
        • 1. Re: here i send the logic, which i have to use to develop a PL/SQL code
          475314
          One of the O'reilly books has a good sample below;

          DROP TYPE tmr_t FORCE;

          CREATE TYPE tmr_t AS OBJECT (
          startTime INTEGER,
          endTime INTEGER,
          repetitions INTEGER,
          name VARCHAR2(2000),
          MEMBER PROCEDURE go ,
          MEMBER PROCEDURE stop (show_timing IN BOOLEAN := TRUE),
          MEMBER FUNCTION timing RETURN INTEGER,
          MEMBER FUNCTION timing_desc RETURN VARCHAR2,
          MEMBER PROCEDURE reset (name IN VARCHAR2 := NULL),
          STATIC FUNCTION make (
          name IN VARCHAR2,
          repetitions IN INTEGER := 1
          )
          RETURN tmr_t
          );
          /

          CREATE OR REPLACE TYPE BODY tmr_t
          AS
          STATIC FUNCTION make (
          name IN VARCHAR2,
          repetitions IN INTEGER := 1
          )
          RETURN tmr_t
          IS
          BEGIN
          RETURN tmr_t (NULL, NULL, NVL (repetitions, 1), name);
          END;

          MEMBER PROCEDURE go IS
          BEGIN
          IF startTime IS NOT NULL
          THEN
          DBMS_OUTPUT.PUT_LINE (
          'You have already started timer "' || name || '"');
          ELSE
          startTime := DBMS_UTILITY.GET_TIME;
          END IF;
          END;

          MEMBER PROCEDURE stop (show_timing IN BOOLEAN := TRUE)
          IS
          BEGIN
          IF endTime IS NOT NULL
          THEN
          DBMS_OUTPUT.PUT_LINE (
          'You have already stopped timer "' || name || '"');
          ELSE
          endTime := DBMS_UTILITY.GET_TIME;

          IF show_timing
          THEN
          DBMS_OUTPUT.PUT_LINE (timing_desc);
          END IF;
          END IF;
          END;

          MEMBER PROCEDURE reset (name IN VARCHAR2 := NULL)IS
          BEGIN
          startTime := NULL;
          endTime := NULL;
          IF name IS NOT NULL
          THEN
          SELF.name := name;
          END IF;
          END;

          MEMBER FUNCTION timing RETURN INTEGER
          IS
          BEGIN
          IF endTime IS NULL
          THEN
          DBMS_OUTPUT.PUT_LINE (
          'You must stop timer "' || name || '"' ||
          ' before you can get timing information.');
          END IF;

          -- Avoid "epoch time" wrap around (thanks, Solomon Yakobson)
          -- RETURN endTime - startTime;
          RETURN (
          MOD (endTime - startTime + POWER(2,32), POWER(2,32)));
          END;

          MEMBER FUNCTION timing_desc RETURN VARCHAR2
          IS
          retval VARCHAR2(2000);
          BEGIN
          IF endTime IS NULL
          THEN
          DBMS_OUTPUT.PUT_LINE (
          'You must stop timer "' || name || '"' ||
          ' before you can get timing information.');
          END IF;

          retval :=
          'Elapsed time for "' || name || '" = ' ||
          TO_CHAR (timing / 100) || ' seconds.';

          IF NVL (repetitions, 1) > 1
          THEN
          retval := retval ||
          ' Per repetition timing = ' ||
          TO_CHAR ((timing / 100) / repetitions) || ' seconds.';
          END IF;

          RETURN retval;
          END;
          END;
          /
          • 2. Re: here i send the logic, which i have to use to develop a PL/SQL code
            540593
            but how can i put conditions on timer

            suppose i have to set a flag i caller make call more than 3 times ina 7 day period
            • 3. Re: here i send the logic, which i have to use to develop a PL/SQL code
              540593
              what should i do

              Message was edited by:
              user537590
              • 4. Re: here i send the logic, which i have to use to develop a PL/SQL code
                475314
                put conditions to MEMBER Procedures/Functions of the timer. Store that call count somewhere else and do whatever you want to do?

                isn't that sample above clear enough, is it?
                • 5. Re: here i send the logic, which i have to use to develop a PL/SQL code
                  540593
                  is necessary to use hat much amount of code if i use only below mention code it will work or not

                  dbms_scheduler.create_job(
                  job_name => 'seven_day_job',
                  job_type=>'plsql_block',
                  job_action=>'myprocedure();',(here i write the code which i want to execute)
                  start_date=>systimestamp+7,
                  repeat_interval =>freq=hourly;
                  enabled=>true);
                  end;