Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: handling concurrent users

    hi

    i am inserting data into a table then returning data into refcursor after returning data i m deleting the entire table.if multiple users acess this procedure at the same time it will cause a problem?if yes can we get an alternative soluition for handling multiple users like in sql they create temporary table.here i tried transaction specific global temporary table but it doesnt work.


    cursor c1(CADDRESSTYPE varchar2, CFROMDATE DATE, CTODATE DATE) is
    select MODIFIEDDATE, SERREQID, USERID
    from (select H_ps_serreq_log.MODIFIEDDATE,
    SERREQID,
    USERID,
    SERREQTYPEID
    from H_ps_serreq_log
    where ACTIVITYSTATUS = 'CC'
    UNION
    select T_ps_serreq_log.MODIFIEDDATE,
    SERREQID,
    USERID,
    SERREQTYPEID
    from T_ps_serreq_log
    where ACTIVITYSTATUS = 'CC') W
    WHERE W.SERREQID IS NOT NULL and
    W.SERREQTYPEID =
    DECODE(CADDRESSTYPE, '*', W.SERREQTYPEID, CADDRESSTYPE) AND
    trunc(W.MODIFIEDDATE) BETWEEN
    TRUNC(DECODE(CFROMDATE, NULL, W.MODIFIEDDATE, CFROMDATE)) AND
    TRUNC(DECODE(CTODATE, NULL, W.MODIFIEDDATE, CTODATE));
    begin

    for i in c1(VADDRESSTYPE, VFROMDATE, VTODATE)

    LOOP

    IF i.MODIFIEDDATE IS NOT NULL THEN

    SELECT MAX(MODIFIEDDATE)
    INTO vardate
    FROM (select MODIFIEDDATE, SERREQID
    from H_ps_serreq_log
    where H_ps_serreq_log.ACTIVITYSTATUS = 'MA'

    UNION

    select MODIFIEDDATE, SERREQID
    from T_ps_serreq_log
    where T_ps_serreq_log.ACTIVITYSTATUS = 'MA') A
    WHERE A.SERREQID = i.SERREQID and
    A.MODIFIEDDATE < i.MODIFIEDDATE;

    select to_char(vardate, 'DD/MM/YYYY HH:MIS')
    into var_date
    from dual;

    if vardate is not null then

    select C.USERID
    INTO vardummy.checkerid
    FROM (select H_ps_serreq_log.MODIFIEDDATE, SERREQID, USERID
    from H_ps_serreq_log
    where ACTIVITYSTATUS = 'CC'
    UNION
    select T_ps_serreq_log.MODIFIEDDATE, SERREQID, USERID
    from T_ps_serreq_log
    where ACTIVITYSTATUS = 'CC') C
    WHERE C.MODIFIEDDATE = i.MODIFIEDDATE;

    select M.userid,
    M.SERREQID,
    M.SERREQNAME,
    M.POLICYID,
    M.HEATCOLID,
    MODIFIEDDATE
    into vardummy.makerid,
    vserreqid,
    vserreqtypeid,
    vPolicyid,
    vHeadcolid,
    vDate
    FROM (select userid,
    MODIFIEDDATE,
    SERREQID,
    SERREQNAME,
    POLICYID,
    HEATCOLID
    from H_ps_serreq_log, M_PS_SERREQ_TYPE
    where H_ps_serreq_log.SERREQTYPEID =
    M_PS_SERREQ_TYPE.SERREQTYPEID and
    H_ps_serreq_log.ACTIVITYSTATUS = 'MA'
    union
    select userid,
    MODIFIEDDATE,
    SERREQID,
    SERREQNAME,
    POLICYID,
    HEATCOLID
    from t_ps_serreq_log, M_PS_SERREQ_TYPE
    where T_ps_serreq_log.SERREQTYPEID =
    M_PS_SERREQ_TYPE.SERREQTYPEID and
    t_ps_serreq_log.ACTIVITYSTATUS = 'MA') M
    WHERE M.SERREQID = i.SERREQID and M.MODIFIEDDATE = vardate;

    select min(t_ps_serreq_remarks.REMARKDATE)
    into rem_date
    from t_ps_serreq_remarks
    where SERREQID = i.SERREQID and USERID = i.USERID and
    REMARKDATE >= i.MODIFIEDDATE;

    if rem_date is not null then
    select to_char(rem_date, 'DD/MM/YYYY HH:MIS')
    into remdate
    from dual;
    select t_ps_serreq_remarks.REMARKS
    into VREMARKS
    from t_ps_serreq_remarks
    where SERREQID = i.SERREQID and USERID = i.USERID and
    TO_CHAR(REMARKDATE, 'DD/MM/YYYY HH:MIS') =
    TO_CHAR(rem_date, 'DD/MM/YYYY HH:MIS');

    else
    VREMARKS := null;
    end if;
    end if;

    insert into m_ps_temp
    (SERREQID,
    SERREQNAME,
    POLICYID,

    HEATCOLID,
    MAKER_ID,
    CHECKER_ID,
    REMARKS,
    MODIFIEDDATE)
    values
    (vserreqid,
    vserreqtypeid,
    vPolicyid,
    vHeadcolid,
    vardummy.makerid,
    vardummy.checkerid,
    VREMARKS,
    vDate);

    END IF;

    end loop;

    open v_cursor for
    select SERREQID AS RequestNo,
    SERREQNAME as RequestType,
    POLICYID as PolicyNumber,
    HEATCOLID as HeatColid,
    MAKER_ID as MakerName,
    CHECKER_ID as CheckerName,
    REMARKS as Remarks,
    MODIFIEDDATE as Modifieddate
    from m_ps_temp;
    io_cursor := v_cursor;

    delete from m_ps_temp

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Create m_ps_temp as GLOBAL TEMPORARY table, then each user will have an independent copy of the table to work on, there will be no concurrency issues.

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    i tried global temporary table transaction specific .but as my front end is asp.net it is not allowing the use of global temporary table it is giving some error.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh, the old "some" error again - if only Oracle gave something more explicit!

    But GTTs would not work in a stateless environment like .NET, so better forget about that idea.

    In that case, you really need to add a "session ID" column to your table so that you only insert/delete rows for the user's .NET session. The "session ID" value could be something .NET gives you to identify your session, or it could be a number you obtain from an Oracle sequence when a new session is created.

Posting Permissions

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