4 Replies Latest reply: Oct 10, 2011 11:21 AM by riedelme RSS

    Insert into another table through Dynamic sequel

    819404
      Hi,


      I have to insert data from one table to another through dynamic sequel .The reason we are using because the where condition can vary in the select statement .Can any one tell me the logic to use that


      the sql statement is like this

      INSERT INTO table1

      SELECT * FROM table2 WHERE condition


      the condition can vary as because it has to come from the front end


      Any ideas will be highly appreciated


      Thanks,
      Prafulla
        • 1. Re: Insert into another table through Dynamic sequel
          sybrand_b
          My idea comes from

          Prafulla     
               
               
          Handle:     Prafulla
          Status Level:     Newbie
          Registered:     Nov 29, 2010
          Total Posts:     50
          Total Questions:     27 (24 unresolved)
          Name     Prafulla
          Location     Bangalore(India)
          Occupation     Senior Software Engg
          Biography     I am a software engineer working in the data base technolgies including Data warehousing and Business Intelligence products

          It is hard to believe you could become 'Senior Software Engg' by being so lazy, specializing in doc questions, and being so rude, never marking a question as resolved.

          -----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Insert into another table through Dynamic sequel
            riedelme
            Prafulla wrote:
            Hi,


            I have to insert data from one table to another through dynamic sequel .The reason we are using because the where condition can vary in the select statement .Can any one tell me the logic to use that


            the sql statement is like this

            INSERT INTO table1

            SELECT * FROM table2 WHERE condition


            the condition can vary as because it has to come from the front end


            Any ideas will be highly appreciated


            Thanks,
            Prafulla
            I usually advise aganst using dynamic SQL because it is hard to develop, hard to debug, hard to maintain, and very hard to tune. It may make sense in the situation you described but think about other possibilities first. If you only have two or three variations it will probably be easier to use IF logic and hard-coded INSERTs.

            If you must use dynamic SQL build the SQL as text and use EXECUTE IMMEDIATE to execute the INSERTS and IF logic to concatenate the WHERE clauses as needed, something like
              v_text_c := 'insert into my_table(col1)'||chr(13)||
                'select col1'||chr(13)||
                '  from my_table2'||chr(13)'||
              ' where 1 = 1';
              if (whatever) then
                v_text_c := v_text_c||'  and col2 = 'whatever');
            end if;
            'WHERE 1 = 1' is a placeholder for any or no other predicates. CHR(13) is a newline to make the generated SQL readable if displayed.

            I also find it helpful to create an error handling table with a CLOB column to put the generated SQL into for later reference.

            Good luck!
            • 3. Re: Insert into another table through Dynamic sequel
              819404
              Thanks a lot and Could you please tell me wheather I can use the bulk insertion option in my code or can you please some tuning methods for the below code

              DECLARE
              v_str_slct VARCHAR2(1000):=' INSERT INTO work_folder_inter SELECT folder_id,''E'' FROM work_folder';
              v_str_cond VARCHAR2(1000):=NULL;
              v_str_full VARCHAR2(4000);
              TYPE Code_cur_type IS REF CURSOR;
              V_Code_cur_type Code_cur_type;
              code_record CODE%ROWTYPE;
              v_count NUMBER;
              BEGIN
              SELECT condition into v_str_cond FROM arch_criteria;
              v_str_full :=v_str_slct||' WHERE '||v_str_cond ;
              --v_str_full  :=v_str_slct;
              EXECUTE IMMEDIATE v_str_full;
              v_count :=SQL%ROWCOUNT;
              --DBMS_OUTPUT.PUT_LINE('v_str_full :'||v_str_full);
              DBMS_OUTPUT.PUT_LINE('v_count :'||v_count);
              EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('v_str_full :'||v_str_full);
              DBMS_OUTPUT.PUT_LINE('There might be some errors in the dynamic sequel'||substr(sqlerrm,215));

              END;
              • 4. Re: Insert into another table through Dynamic sequel
                riedelme
                Prafulla wrote:
                Thanks a lot and Could you please tell me wheather I can use the bulk insertion option in my code or can you please some tuning methods for the below code

                DECLARE
                v_str_slct VARCHAR2(1000):=' INSERT INTO work_folder_inter SELECT folder_id,''E'' FROM work_folder';
                v_str_cond VARCHAR2(1000):=NULL;
                v_str_full VARCHAR2(4000);
                TYPE Code_cur_type IS REF CURSOR;
                V_Code_cur_type Code_cur_type;
                code_record CODE%ROWTYPE;
                v_count NUMBER;
                BEGIN
                SELECT condition into v_str_cond FROM arch_criteria;
                v_str_full :=v_str_slct||' WHERE '||v_str_cond ;
                --v_str_full  :=v_str_slct;
                EXECUTE IMMEDIATE v_str_full;
                v_count :=SQL%ROWCOUNT;
                --DBMS_OUTPUT.PUT_LINE('v_str_full :'||v_str_full);
                DBMS_OUTPUT.PUT_LINE('v_count :'||v_count);
                EXCEPTION
                WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('v_str_full :'||v_str_full);
                DBMS_OUTPUT.PUT_LINE('There might be some errors in the dynamic sequel'||substr(sqlerrm,215));

                END;
                You should be able to use a bulk insert when you get the genererated SQL syntactically correct :). I do not see a need for a reference cursor.

                Tuning dynamic SQL is difficult - you will have to save the SQL in a table, use the saved SQL to get an execution plan, and make decisions on what the plan says. You might be able to find the SQL in V$SQL and link to V$SQL_PLAN if you have access to the V$ views or Oracle Enterprise Manager.