1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Global temporary tables Session issue

Discussion in 'SQL PL/SQL' started by krylor, Apr 27, 2009.

  1. krylor

    krylor Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    Am a developer with more Sql Server experience than Oracle.
    I Am working on Web application developed in C#,for which I need to use Global temporary tables in pl/sql procedure.
    (
    I use the temp tables to store the unique ids of the transaction table I am trying to fetch data from.
    I need to use a temp table as there are various conditions to be filtered.
    )

    The only issue here is that Oracle says GTT can be either session specific or transaction specific.
    I want want the data in the temp table to exist only for the scope of the PL/SQL procedure and not persist beyond that.

    What I found was that each time I refreshed the page ( for every refresh the PL/SQL procedure is invoked once ) the no of records being displayed kept increasing as the Oracle session does not get terminated due to connection pooling
    and it keeps adding records to the temp table.
    .
    Under such circumstances what can be done to make the data persist only for the current PL/SQL scope?.

    Currently am using a rough hack,inserting a sequence value ( which is unique everytime the PL/SQL procedure is invoked ) into the temporary table.
    It does do the job, but am not very happy with this.

    Incidentally this is effortlessly done in sql server using inline tables.

    Can somebody suggest a better approach.

    ps:
    PL/SQL tables wont work for me as I need to delete data from the temp table using sql syntax

    regards
    Krylor
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi krylor, while I am not too much experienced with GTT, from the description of your problem it seems that you would do just well with WITH CLAUSE (Subquery factoring). You can assign a name to a subquery block and this name can be referenced in multiple places in the main query. I suggest you take a look on this article by Rajavu

    http://www.club-oracle.com/forums/with-clause-subquery-factoring-t136/
     
    krylor likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am not an expert in SQL Server. But I know GTT is widely used in Sql Server than in Oracle. It is also observed user having SQL Server who is working in Oracle tend to use them repeatedly.

    GTT concept in SQL server is also accepted in Oracle. Even can use with the same functionality as n SQL Server. But in Oracle inline views or WITH clause are more accepted.


    Anyway here is piece of brilliant comment from TOM

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48812348054

     
    krylor likes this.
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Why not use pl/sql table and reset it at the begining of the pl/sql proc?
     
    krylor likes this.
  5. krylor

    krylor Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi People,

    Perhaps I should have posted my sample code.
    That would have made things clearer.

    Below is a sample stored proc.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE ptsdbuser.sp_searchfieldtest (
       psearchfield   IN       VARCHAR2,
       pageno         IN       INTEGER,
       pagesize       IN       INTEGER,
       oresult1       OUT      TYPES.o_cursor,
       oresult2       OUT      TYPES.o_cursor,
       oresult3       OUT      TYPES.o_cursor,
       oresult4       OUT      TYPES.o_cursor
    )
    IS
       tmpseq         NUMBER;
       startrec       INTEGER;
       endrec         INTEGER;
       totalrecords   INTEGER;
    BEGIN
       startrec := ((pageno - 1) * pagesize) + 1;
       endrec := startrec + pagesize - 1;
       totalrecords := 0;

       SELECT userrow_seq.NEXTVAL
         INTO tmpseq
         FROM DUAL;

       -- USE A LOCK ID IN ORDER TO PREVENT 2 DIFFERENT PL/SQL CALLS FROM CLASHING.
       IF LENGTH (psearchfield) > 0
       THEN
          INSERT INTO userrow
                      (lock_id, row_no, user_id)
             SELECT   tmpseq, ROWNUM AS row_no, user_id
                 FROM login_master
                WHERE (user_id LIKE psearchfield || '%')
                   OR (UPPER (   NVL (user_first_name, '')
                              || ' '
                              || NVL (user_last_name, '')
                             ) LIKE UPPER (psearchfield) || '%'
                      )
                   OR (UPPER (NVL (user_email, '')) LIKE
                                                        UPPER (psearchfield)
                                                        || '%'
                      )
                   OR (UPPER (NVL (user_last_name, '')) LIKE
                                                        UPPER (psearchfield)
                                                        || '%'
                      )
                   OR (UPPER (NVL (user_first_name, '')) LIKE
                                                        UPPER (psearchfield)
                                                        || '%'
                      )
             ORDER BY user_id DESC;
       ELSE
          INSERT INTO userrow
                      (lock_id, row_no, user_id)
             SELECT tmpseq, ROWNUM, user_id
               FROM login_master;
       END IF;

       OPEN oresult1 FOR
          SELECT   a.ID, a.user_id, NVL (a.user_first_name,
                                         '') AS user_first_name,
                   NVL (a.user_last_name, '') AS user_last_name,
                   NVL (a.user_email, '') AS user_email, a.user_expiry_date,
                   NVL (a.location_type, '') AS location_type,
                   NVL (a.user_status, '0') AS user_status,
                   NVL (a.user_password, '') AS user_password, a.user_expiry_date
              FROM login_master a INNER JOIN userrow b ON a.user_id = b.user_id
             WHERE b.lock_id = tmpseq
               AND b.row_no >= startrec
               AND b.row_no <= endrec
          ORDER BY b.row_no;

       OPEN oresult2 FOR
          SELECT a.location_code, a.location_type, a.user_id, b.location_name
            FROM user_location a INNER JOIN location_master_vw b
                 ON a.location_code = b.location_code
                 INNER JOIN userrow b ON a.user_id = b.user_id
           WHERE b.lock_id = tmpseq AND b.row_no >= startrec
                 AND b.row_no <= endrec;

       OPEN oresult3 FOR
          SELECT a.user_id, a.role_code, b.role_name
            FROM user_roles a INNER JOIN role_master b ON a.role_code =
                                                                       b.role_code
                 INNER JOIN userrow b ON a.user_id = b.user_id
           WHERE b.lock_id = tmpseq AND b.row_no >= startrec
                 AND b.row_no <= endrec;

       SELECT COUNT (*)
         INTO totalrecords
         FROM userrow b
        WHERE b.lock_id = tmpseq;

       OPEN oresult4 FOR
          SELECT 1 AS returncode, NVL (totalrecords, 0) AS returnmessage
            FROM DUAL;

       -- Clean up
       DELETE FROM userrow
             WHERE lock_id = tmpseq;
    END sp_searchfieldtest;
    The reason I need to use a temp table is because.

    I need to build a lot more functionality into this like
    more filter conditions, conditional sorting etc.