Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: concurency problem in oracle

    hello oracle developers..
    I have a situation like this..
    when run a procedure ..it extracts data from multiple tables based on given parameters.. and pumps the same into a temporary table(say x)...then procedure returns data from the table x..if another user run the same procedure , same time with diff. parameters....then one of the user is getting wrong values...how to overcome this problem..
    plz..help me..
    thanks in advance..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: concurency problem in oracle

    When you say "temporary table", do you mean a Global Temporary Table (GTT)? It sounds like you are not using a GTT and that the users are walking over each other's data.

  3. #3
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: concurency problem in oracle

    Originally posted by andrewst
    When you say "temporary table", do you mean a Global Temporary Table (GTT)? It sounds like you are not using a GTT and that the users are walking over each other's data.
    thank u Mr.andrewst..
    this is sridhar..
    Actually...its a normal database table but the data will be inserted only when the procedure is called..

    plz..allow me to be more clear..

    when the procedure is called with some parameters..inside the procedure
    1)a query extracts some data using given parameters.
    2) and this data is inserted into a table say TEMP.
    3)another query is executed with same parameters as in step 1.
    4)and data is inserted into table TEMP.
    commit and end of procedure.
    5) Existing data in TEMP table is deleted .. every time this procedure is called..

    and the TEMP table is querid to generate a report using a program in cfml (coldfusion markup language)
    this works fine for a single user..but when multiple users
    are calling the procedure ..
    it gives me improper data..may be becoz.. of asyncronous nature..
    plz.. help me..
    thanks in advance..
    Last edited by sridharreddy_d; 02-11-04 at 04:21.

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    In such a case, it wud be a good idea to have user id as primary key in table TEMP. Procedure should delete data only for that user.. and report to show data only for that user...
    Oracle can do wonders !

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Eevry user is accessing proc together, but every user will have his/her uniqu identification - the user id.

    Keep this user id in the TEMP table as one of the columns in primary key.

    Procedure should now delete only those records which have been inserted by the connected user. Similarly, the report SQL will also need modification to display records only for the logged on user.

    Does it make sense? If no, let me know, i'll give u a sample example.

    [i]
    thanks mr. sharma.. the problem is when multiple users access the procedure..at a time..how to make it syncronize..
    Oracle can do wonders !

  6. #6
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    thanks

    thank you sharma.. cud u be more clear.. plz..

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: thanks

    U can have column say userName and you should insert userName
    for the user who is inserting data in the temp table.
    for example
    temp
    ColName, ColName, UserName
    1
    Pagnint
    (No need to search web before posting new question)

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Re: thanks

    U can have column say userName and should insert userName
    for the user who is inserting data in the temp table.
    for example
    temp
    ColName, ColName, UserName
    1 a sanjay
    2 b sanjay
    3 c vijay

    Now you delete data from temp for the user who is exceuting the procedure.

    Hope this will solve your problem.
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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