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!

How to Manage simulteanous execution for creating dynamic temorary tables

Discussion in 'SQL PL/SQL' started by arunkumar546, May 1, 2013.

  1. arunkumar546

    arunkumar546 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    IN my procedure,i'm creating dynamic temporary tables(columns also creted dynamically based on the table columns).my problem is if two users execute procedure at the same time,the second user get the error " table already exist".for avoiding this confliction, what i need to do? the procedure have 1000 lines code.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No one can say what you should do since we don't know what you're doing now.

    Please post the code so someone can assist you.
     
  3. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Hero,

    What exactly you are trying to do things.

    Below are some of my Questions to you

    1. If the second user is trying to create table it shows error tats obvious, But incase second user creates table deleting the existing table will tat be ok for you ? or you need the records stored in the table by User1 also for the User2?

    2. What exactly your requirement and why you are creating dynamic table in procedure?

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  4. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Yeah agreed with Kamal !! ..

    First & foremost, 4get about 1st user/2nd user/3rd user/ ... nth user.
    The question is .. What'd happen when the procedure runs and the table already exists?
     
  5. arunkumar546

    arunkumar546 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    In my procedure, Im creating gobal temporary table.those i want create dynammically with columns.here columns also varible columns i.e. some time 2 columns need create or some time 10 columns need to create.

    for example, I have one source table table with 2 rows like

    rule name souurce_columns
    1. ABCD RULE EMPNO,EMPNAME
    2. XYZ RULE DNO,DNAME,LOCATION,MANAGER

    this is my source table.so,i'm getting the columns from source columns into cursor and i'm creating table like
    open c1;
    fetch ci into v_columns
    loop
    execute immediate' create table tempbox3('||v_columns||' varchar2(256)');
    v_columns:=v_columns+||chr(13);
    end loop;

    like this i'm crating table and with this table i'm going to manipulations with some other tables.here my problem is i don't know whether that table(tempbox3) has strictly no of columns.the no columns based on source columns from source columns.

    So, when the 1st user try execute sp and he is in middle,at the same time 2nd user tries execute the sp, the error is occured 'table already exists' avoiding this confliction,what will i do? hope u people understand my problem.

    One note to remember,that no user can be disturbed at the time of sp execution.

    Thanks to All,...
     
  6. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi ArunKumar,

    Thanks for the Reply.
    I got your point , can you answer me below question

    1. During Procedure run are you passing any parameter?

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  7. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    That's what answers my question ..
    So, the option of "drop & re-create" is eliminated. What u wanna have here is that ..
    1. Neither of your users should be disturbed.
    2. They'll all be using different data from the table.
    3. And also, the table structure will also be different w.r.t different users.

    **Conclusion: We can say that the tables 2 different users will be using will be different. They're 2 completely different tables.
    Now the problem remains that the table name is SAME. :rolleyes:

    Ok, i personally did a workaround (successful) in one of my projects.
    **But Note: B4 u go live with this concept, Plz consult with ur DBA as it may lead to significant run-time resource consumption and tablespace usage.

    Here r the steps:
    1. Take a COUNTER, initiate it from 1.
    2. Lets take the base table name as "tempbox"
    3. U gotto make use of the USER_TABLES table here. Check if there exists a table with the name "tempbox". If doesn't then, create with the name "tempbox" or else check if there exists a table with the name "tempbox||COUNTER", if no then create, else increment COUNTER .. again check the same with a iterator and so on ...

    4. Do make sure to delete and purge the table before ending the SP. (This is very very important or else it'll lead to excessive garbage tablespace allocated)
    5. To be on the safer side, keep a tolerance value for the COUNTER, if the COUNTER reaches the tolerance value then raise an exception to alarm your SYSADMIN about the same. And SYSADMIN may free the unwanted allocated space. (I kept it 20)

    HTH
     
  8. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi ArunKumar,

    While u running the Procedure are you passing any parameter values...
    Can i get a reply for this from your end..

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  9. arunkumar546

    arunkumar546 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Kamal,

    Ya, I'm passing parameters to my sp like source_table name,user id and formula.may i know why you are asked this question plz.
     
  10. arunkumar546

    arunkumar546 Active Member

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

    Thank you very much.i think it's very helpfull to me.i have another doubt,how i can i check table "tempbox||Counter"? for that counter i need maintain sequence or repeat the loop in code? how can i keep tolerance value for counter? if u dont mind can u post sample code plz.
     
  11. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Arun,

    Hope you got my point i think,
    But i will explain...

    If you are passing user_id as parameter then you can use that user_id with the table name during the dynamic table creation, so u wont hit with Duplicate.
    But if the same user tries to run the Procedure many times then once again u vl have issues.

    If you feel keeping max value for a counter. you can use sequence ...

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The problem you have is using the same name for every temp table users can create -- you should be creating temp tables with a bit more descriptive names that differ from each other:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE create_tmp_tbl(p_tname IN varchar2)
      2  IS
      3
      4  BEGIN
      5          EXECUTE immediate 'create table temp_'||substr(p_tname,1,25)||' as select * from '||p_tname||' where 0=1';
      6
      7  END;
      8  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> EXEC create_tmp_tbl('emp')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('dept')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('salgrade')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DESC temp_emp

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
     
    SQL> SELECT COUNT(*) FROM temp_emp;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_dept
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                             NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)
     
    SQL> SELECT COUNT(*) FROM temp_dept;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_salgrade
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     GRADE                                              NUMBER
     LOSAL                                              NUMBER
     HISAL                                              NUMBER
     
    SQL> SELECT COUNT(*) FROM temp_salgrade;
     
      COUNT(*)
    ----------
             0
    SQL>

    Also you're going through too much work to create these temp tables -- look at the statement in my procedure to simplify your code.
     
  13. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Honestly, i expected u to figure this one out yourself (the coding part).
    Use the following in your SP, to check the existence of the table.
    Code (SQL):

    SELECT  'Y'
    FROM    dual
    WHERE   EXISTS
            (SELECT ut.TABLE_NAME
             FROM   sys.user_tables ut
             WHERE  ut.TABLE_NAME   =   'TEMPBOX'||COUNTER
            );        
     
    You don't need any sequence just use a NUMBER type COUNTER variable and increment it inside a loop.

    If u're incrementing the COUNTER inside a loop by 1 .. and checking the table's existence simultaneously .. then there may be (just may be) an unlikely scenario when the COUNTER reaches your tolerance value (lets say 10).
    So, when u encounter the COUNTER has reached 10 (incrementing from 1) .. Just terminate the loop and raise an user-defined exception.

    *Now, i seriously don't like to spoon-feed. :p (I do in case of more complex issues, this is not)
    Come on !! Figure it out. :hurray
     
  14. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Ari,

    If you use counter also its an issue.
    Next time when next user creates table counter increments from 1 and then also it will say table already exists....

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  15. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Kamal,

    :D .. LOL .. :D
    What for are we checking USER_TABLES? ;)
     
  16. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Ari,

    I got it but the purpose of creating table is not happening na when you check USER_TABLES and if its already exisits , does the program creates new table if the table already exists also ?

    If not the dynamic way of Table creationg is not happening na..

    Cheers
    Kamal
    (kamal.love@gmail.com)
     
  17. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    OMG !!

    .. Can't i expect some smart replies .. Hell :mad:

    **NOTE: Plz go through my earlier posts THOROUGHLY

    Here's the Flow Diagram (somewhat):
    -------------------
    START ..

    1. Check if base table exists i.e tempbox. If doesn't then well n good (dynamically create the table with the base name only tempbox) or else initialize COUNTER=0.

    Loop
    2. Check if COUNTER has reached tolerance value (10 or 20 or xyz). For true RAISE EXCEPTION else continue.
    3. Increment COUNTER by 1
    4. Again Check if table name 'tempbox'||COUNTER exists. If doesn't then create (dynamically create table with this name 'tempbox'||COUNTER) keep the created table name somewhere (variable) and break the loop, or else table exists then continue in the loop.
    End Loop

    <do what u need to>
    5. DROP TABLE
    END
    ------------------------------------------------

    **This is not the code (plz don't copy paste) .. And guyz don't force me to write the script for u (coz it'd b irritating). Prepare the script urself (this is way to easy).. I never thought .. i'd have to post 3 replies to this thread. :mad:
     
  18. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Ari,

    Sorry if i am asking you again and again, from my end i feel the solution will once again give same error, after reaching max counter, if some other is trying to execute same procedure when it comes to the counter part once again only if table exists ur not creating the new table there what is the point here...

    if the count u specifying as 10 then table_name||counter will create till 10 after tat if some one run the same procedure then the count will start from 1 only again then it wont create any table.. what is the use then...

    Cheers
    Kamal

    Cheers
    Kamal
     
  19. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Uff !!

    I guess u ignored this ...
    Coz i believe i'm re-explaining it.

    Ok !! Plz see the 5th step which i mentioned in the flow diagram (which is DROP TABLE) .. so there remains very rarest of chances that all the tables b/w 1..10th/20th/nth will exist at the same time. So, in case it does, then i can strongly say that not [10/20/n] user's will be using simultaneously, but infact the DROP TABLE statement may (just may) have not worked.

    Therefore, this malfunctioned or exceptional scenario needs to be conveyed to the DBA or SYSADMIN. So that he clears out the garbage memory.

    **If u don't keep any tolerance value and go on creating tables without dropping them, sooner or later your tablespaces will get loaded and will hamper performance of the whole application.
     
  20. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE create_tmp_tbl(p_tname IN varchar2)
      2  IS
      3
      4          v_tabno NUMBER:=0;
      5
      6  BEGIN
      7          SELECT COUNT(*)
      8          INTO v_tabno
      9          FROM user_tables
     10          WHERE TABLE_NAME LIKE 'TEMP_'||UPPER(p_tname)||'%';
     11
     12          v_tabno:=v_tabno+1;
     13          EXECUTE immediate 'create table temp_'||substr(p_tname,1,25)||v_tabno||' as select * from '||p_tname||' where 0=1';
     14
     15  END;
     16  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> EXEC create_tmp_tbl('emp')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('dept')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('salgrade')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('emp')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('dept')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> EXEC create_tmp_tbl('salgrade')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DESC temp_emp1

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
     
    SQL> SELECT COUNT(*) FROM temp_emp1;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_dept1

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                             NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)
     
    SQL> SELECT COUNT(*) FROM temp_dept1;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_salgrade1

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     GRADE                                              NUMBER
     LOSAL                                              NUMBER
     HISAL                                              NUMBER
     
    SQL> SELECT COUNT(*) FROM temp_salgrade1;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_emp2

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
     
    SQL> SELECT COUNT(*) FROM temp_emp2;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_dept2

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     DEPTNO                                             NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)
     
    SQL> SELECT COUNT(*) FROM temp_dept2;
     
      COUNT(*)
    ----------
             0
     
    SQL> DESC temp_salgrade2

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     GRADE                                              NUMBER
     LOSAL                                              NUMBER
     HISAL                                              NUMBER
     
    SQL> SELECT COUNT(*) FROM temp_salgrade2;
     
      COUNT(*)
    ----------
             0
    SQL>