Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    9

    Unanswered: Stored procedure with job

    Hi,
    I want to use temporal db with oracle. Firstly I create table POSTMAN and it has id,name,surname,latitude,longitude,starttime,finis htime.After that I create job that has
    HTML Code:
    repeat_interval =>'FREQ=SECONDLY;INTERVAL=1;
    and it use stored procedure for update latitude.
    I update every second change latitude value of postman.But I need to time value for STARTTIME and FINISHTIME.When I changed the latitute I have to save old value and changed time for set STARTTIME and FINISHTIME . I want to copy row to another new row before updating process.After that I update the latitude value.So I kept the value of latitude.Is it possible with stored procedure on Oracle. How to solve problem with stored procedure or any other methods?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by fred_dogan View Post
    Hi,
    I want to use temporal db with oracle. .. Etc .. . I want to copy row to another new row before updating process.After that I update the latitude value.So I kept the value of latitude.Is it possible with stored procedure on Oracle. How to solve problem with stored procedure or any other methods?
    1) Temporal db? what is temporal db?

    2) Almost anything in Oracle can be done using procedures -- but perhaps to solve your requirement you may want to think about changing the schema design:

    Recommended tables:

    PHP Code:

    postman  (idnamesurname

    location (idlatitudelongitudetime_value
    This way you record each location and corresponding time value.
    Then all you need to do to get the start/finish time is to query the location table.

    Good luck!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2012
    Posts
    9
    For example db postman tracking system
    HTML Code:
    id lat lon starttime     finish time    name
    1  3   4   13             14               Tom
    2  5   6   14             15               Jim
    after update I changed latitude value of this table.

    HTML Code:
    id lat lon starttime     finish time    name
    1  4   4   13             14               Tom
    2  5   6   14             15               Jim
    Latitude value increased to 4.So I missed the old value.But I need to every changed value.AFAIK Also called temporal db that related to time value.
    For this reason I need to

    HTML Code:
    id lat lon starttime     finish time     name
    1  3   4   13             14                Tom
    2  5   6   14             15                Jim
    3  4   4   15             16                Tom
    4  5   4   17             18                Tom
    5  6   4   19             20                Tom
    I want to track every latitude longitude value of Tom.I made to increment oracle scheduled job.How I solve this problem with trigger or stored procedure?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by fred_dogan View Post
    For example
    . . . Etc . . .
    after update I changed latitude value of this table.

    I want to track every latitude longitude value of Tom.I made to increment oracle scheduled job.How I solve this problem with trigger or stored procedure?
    Instead of UPDATE, just INSERT new values:

    Code:
    SQL> CREATE TABLE postman
      2  (
      3    id            NUMBER (3)
      4  , lat           NUMBER (3)
      5  , lon           NUMBER (3)
      6  , start_time       DATE
      7  , finish_time      DATE
      8  , name          VARCHAR2 (10)
      9  );
    
    Table created.
    
    SQL> INSERT INTO postman
      2       VALUES ( 1, 3, 4
      3            , TO_DATE ( TRUNC (SYSDATE) + 13 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      4            , TO_DATE ( TRUNC (SYSDATE) + 14 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      5            , 'Tom');
    
    1 row created.
    
    SQL> INSERT INTO postman
      2       VALUES ( 2, 5, 6
      3            , TO_DATE ( TRUNC (SYSDATE) + 14 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      4            , TO_DATE ( TRUNC (SYSDATE) + 15 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      5            , 'Jim');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
    SQL> SELECT * FROM postman;
    
      ID  LAT  LON START_TIME                    FINISH_TIME                   NAME
    ---- ---- ---- ----------------------------- ----------------------------- ------------------------------
       1    3    4 19-Dec-2012 13:00:00          19-Dec-2012 14:00:00          Tom
       2    5    6 19-Dec-2012 14:00:00          19-Dec-2012 15:00:00          Jim
    
    SQL>
    SQL> INSERT INTO postman
      2       VALUES ( 3, 4, 4
      3            , TO_DATE ( TRUNC (SYSDATE) + 15 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      4            , TO_DATE ( TRUNC (SYSDATE) + 16 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      5            , 'Tom');
    
    1 row created.
    
    SQL> INSERT INTO postman
      2       VALUES ( 4, 5, 4
      3            , TO_DATE ( TRUNC (SYSDATE) + 17 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      4            , TO_DATE ( TRUNC (SYSDATE) + 18 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      5            , 'Tom');
    
    1 row created.
    
    SQL> INSERT INTO postman
      2       VALUES ( 5, 6, 4
      3            , TO_DATE ( TRUNC (SYSDATE) + 19 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      4            , TO_DATE ( TRUNC (SYSDATE) + 20 / 24, 'DD-Mon-YYYY HH24:MI:SS')
      5            , 'Tom');
    
    1 row created.
    
    SQL>
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>
    SQL>   SELECT *
      2      FROM postman
      3  ORDER BY name, id;
    
      ID  LAT  LON START_TIME                    FINISH_TIME                   NAME
    ---- ---- ---- ----------------------------- ----------------------------- ------------------------------
       2    5    6 19-Dec-2012 14:00:00          19-Dec-2012 15:00:00          Jim
       1    3    4 19-Dec-2012 13:00:00          19-Dec-2012 14:00:00          Tom
       3    4    4 19-Dec-2012 15:00:00          19-Dec-2012 16:00:00          Tom
       4    5    4 19-Dec-2012 17:00:00          19-Dec-2012 18:00:00          Tom
       5    6    4 19-Dec-2012 19:00:00          19-Dec-2012 20:00:00          Tom
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2012
    Posts
    9
    Basically you right.You reached the target db.
    But this way doesn't change latitude value dynamically.If I increment latitude value ,I have to write new insert into command.But instead of this way
    I used scheduled job and connected stored procedure (for update latitude )
    HTML Code:
    repeat_interval =>'FREQ=SECONDLY;INTERVAL=1;
    so latitude changed dynamically.I execute the job once. Every 1 second increment the latitude value of postman.But it doesnt keep old value.How can I do a dynamic?

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by fred_dogan View Post
    Basically you right...Etc
    ....How can I do a dynamic?
    To keep old value you need to record it either in the same table or another table. If you choose to do it, you still need to "INSERT" the data. If you insert into another table, you can use a before update trigger to do it.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •