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

    Unanswered: problem in providing concurrent access

    hello oracle developers..
    I got a problem..plz. help me out.
    I have a oracle pl/sql procedure which is called from coldfusion ..
    when the procedure is called from coldfusion..it works as follows..

    first it executes couple of queries and inserts the values returned into a table(say TEMP) inside a loop.

    TEMP is queried inside coldfusion to generate a report.

    when multiple users are calling the procedure at same time with different parameters...
    I could make values to be inserted into TEMP table by oneuser at a time..(I mean I cud syncronize the process)
    but I required to make it concurent in nature..
    like ...
    when multiple users r calling the procedure at a time
    multiple instances of TEMP table should be created.
    i.e. each session data should be unique..
    plz..... help me to solve this..
    thanks in advance..

    here Iam sending a sample procedure for clarity..
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: problem in providing concurrent access

    Create a global temporary table. Each session will get its own "version" of the table and you can insert, delete, update, and read the data to your hearts content without affection data from another session.

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: problem in providing concurrent access

    global temp table is the best choice.

    in case you have to use standard table as temp table, like you have to share or copy data across db sessions, you then have to add session structure into the temp table itself, e.g., add column session_id etc.

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

    Re: problem in providing concurrent access

    Originally posted by lynden.zhang
    global temp table is the best choice.

    in case you have to use standard table as temp table, like you have to share or copy data across db sessions, you then have to add session structure into the temp table itself, e.g., add column session_id etc.
    thank u ...
    actually I tried using global temporary table..and it gives me proper data from sql*plus but when I call the same procedure from coldfusion and query the global temporary table it gives no records..
    is it the problem with sessions in coldfusion?
    I could not understand?
    and could u please tellme in detail as to how to go about if I have to use
    standard table as temp table by adding a session id to it..
    plz. help me in this regard..
    thanks inadvance..

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

    Re: problem in providing concurrent access

    Does your Coldfusion application maintain an Oracle session continously, or does it disconnect/reconnect between calls to Oracle, like a typical web application? Global temporary tables only work where the Oracle session is maintained, because by definition they are emptied whenever you disconnect from Oracle.

    In that case, you would have to defined your own "session id" to identify your data. This could for example be a value generated from an Oracle sequence created like this:

    create sequence session_seq;

    Now your temp table needs an extra column like this:

    alter table temp add session_id integer;

    Also, your PL/SQL procedure needs an additional parameter (p_session_id IN INTEGER), which it must use as the temp.session_id value in all DML.

    In your CF app you will now do this:

    1) Get a new session id:

    select session_seq.nextval from dual;

    (or more likely, call a packaged function like session_pkg.get_session_id which does the above select and returns the new value).

    Remember this value in a variable (e.g. v_session_id).

    2) Call the procedure like this:

    the_procedure( v_session_id, ...(other params) );

    3) Query from temp using session_id:

    select ... from temp where session_id = v_session_id;

Posting Permissions

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