1 2 Previous Next 22 Replies Latest reply: May 14, 2010 4:22 PM by AmmadAhmed RSS

    Insert data from another table

    404152
      My application has a data block than display information from another table. From this data block, the user has the option to select the records for upload. The information in the data block is used to insert data into 5 different tables. So if any exception occurs while inserting, the whole process should be rollback, but before this happen a record is inserting in a failed table indicated the reason of the failed.
      For this process I use the following commands:

      1.     sp_name := Get_Application_Property(SAVEPOINT_NAME);

      2.     INSERT DDL
      3.     INSERT DDL
      4.     INSERT DDL
      5.     INSERT DDL
      6.     INSERT DDL

      7.     If Other Exception then ISSUE_ROLLBACK(sp_name) Otherwise FORMS_DDL ('commit')

      The problem the previous insert(s) rollback

      Forms [32 Bit] Version 10.1.2.0.2 (Production)
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

      Thanks,
        • 1. Re: Insert data from another table
          49905
          Not sure how do you call your code, which triggers do you use?
          ISSUE_ROLLBACK may give unpredictable results when used outside an On-Rollback trigger
          You said:
          if any exception occurs while inserting, the whole process should be rollback, but before this happen a record is inserting in a failed table indicated the reason of the failed
          As I understand you have to loop trough the block and process all "selected" records.
          On exception you may call DB procedure with PRAGMA Autonomous_Transaction to insert and commit failed records.
          Maybe something like code below. (you can use WHEN-BUTTON-PRESSED trigger to call this)
           
          go_block ('your_block'); 
          first_record; 
          LOOP 
              IF :your_block.record_selected ='Y' THEN 
                BEGIN 
                   INSERT INTO tab1.. 
                   INSERT INTO tab2.. 
                   INSERT INTO tab3... 
                   forms_ddl('commit'); 
                EXCEPTION 
                   WHEN OTHERS THEN 
                      Insert_log (); -- this is call to DB procedure with PRAGMA Autonomous_Transaction to insert and commit errored record 
                      forms_ddl('rollback'); 
                END; 
               END IF; 
             IF :SYSTEM.last_record <> 'TRUE' THEN 
                 next_record; 
             ELSE 
                 exit; 
             END IF; 
          END LOOP; 
          • 2. Re: Insert data from another table
            404152
            Thanks for you answer
            Here is some additional information

            1.     My code is called when the user press a push button (when-button-pressed trigger)
            2.     The records selected by the user are moved to a temporary table (I don’t know if it is good idea) and from the temporary table I create a cursor / loop. The reason than I created the cursor is because the table from the data block has a lot of field and will be so tedious to display all on the screen. Beside any new field added later to the data block table need to be added on the screen

            Any suggestion?
            Thanks,
            • 3. Re: Insert data from another table
              49905
              401149 wrote:
              Any suggestion?
              Yes. Two.
              Do not create duplicate threads.
              Look at the code in my previous post.
              • 4. Re: Insert data from another table
                AmmadAhmed
                Why are you trying with INSERT DDL. Might be this one as you said from cursor and loop stuff...
                DECLARE
                   CURSOR
                     SELECT ...
                BEGIN
                   FOR GET_REC IN CURSOR_NAME LOOP
                   BEGIN
                     INSERT INTO TABLE1
                     VALUES (...);
                     INSERT INTO TABLE2
                     VALUES (...);
                     INSERT INTO TABLE3
                     VALUES (...);
                     INSERT INTO TABLE4
                     VALUES (...);
                     INSERT INTO TABLE5
                     VALUES (...);
                  EXCEPTION
                    WHEN OTHERS THEN
                       FORMS_DDL('ROLLBACK');
                       INSERT INTO ERR_LOG_TABLE  -- Here insertion log in case of failure in any one of above 5 insert statements.
                       VALUES (...);
                      EXIT;
                  END;
                  END LOOP;
                  FORMS_DDL('COMMIT');
                END;
                -Ammad

                Edited by: Ammad Ahmed on May 14, 2010 10:34 PM
                • 5. Re: Insert data from another table
                  404152
                  I already look into your previous code and my process is almost identical to your process, but the different is that I do a loop from the cursor than is created from the information of the temporary table.
                  Could you be more specific, please?
                  Thanks,
                  • 6. Re: Insert data from another table
                    404152
                    I already look into your previous code and my process is almost identical to your process, but the different is that I do a loop from the cursor than is created from the information of the temporary table.
                    Could you be more specific, please?
                    Thanks,
                    • 7. Re: Insert data from another table
                      404152
                      Ammad:
                      I modified your code to make more similar to my code


                      DECLARE
                      CURSOR
                      SELECT ...

                      **sp_name VARCHAR2(80);**
                      BEGIN
                      FOR GET_REC IN CURSOR_NAME LOOP
                      BEGIN
                      **sp_name := Get_Application_Property(SAVEPOINT_NAME);**

                      INSERT INTO TABLE1
                      VALUES (...);
                      INSERT INTO TABLE2
                      VALUES (...);
                      INSERT INTO TABLE3
                      VALUES (...);
                      INSERT INTO TABLE4
                      VALUES (...);
                      INSERT INTO TABLE5
                      VALUES (...);
                      EXCEPTION
                      WHEN OTHERS THEN
                      **Issue_Rollback(sp_name);**

                      INSERT INTO ERR_LOG_TABLE  -- Here insertion log in case of failure in any one of above 5 insert statements.
                      VALUES (...);
                      END;
                      FORMS_DDL('COMMIT');
                      END;


                      Everything work fine, but when the transaction failed the only than doesn't work is the rollback

                      Thanks,
                      • 8. Re: Insert data from another table
                        49905
                        Just replace loop trough block with loop with cursor from temp table:
                        FOR rec IN (select from temp table) LOOP 
                              BEGIN 
                                 INSERT INTO tab1.. 
                                 INSERT INTO tab2.. 
                                 INSERT INTO tab3... 
                                 forms_ddl('commit'); 
                              EXCEPTION 
                                 WHEN OTHERS THEN 
                                    Insert_log (); -- this is call to DB procedure with PRAGMA Autonomous_Transaction to insert and commit errored record 
                                    forms_ddl('rollback'); 
                              END; 
                        END LOOP; 
                        Ammad, in your example,lets say 10 records in cursor from temp table. Rcords #5 and record#8 are failed.
                        You will end with records #9 and #10 inserted and only record #8 in err_log_table.

                        Sorry, didn't notice EXIT from the loop in your code.

                        Edited by: Slava Natapov on May 14, 2010 2:37 PM
                        • 9. Re: Insert data from another table
                          AmmadAhmed
                          @Slava
                          yeah right i edited my old post and i added EXIT in exception. Because he wants to rollback the whole process.

                          Try this...
                          DECLARE
                          CURSOR
                          SELECT ...
                          BEGIN
                          FOR GET_REC IN CURSOR_NAME LOOP
                          BEGIN
                          INSERT INTO TABLE1
                          VALUES (...);
                          INSERT INTO TABLE2
                          VALUES (...);
                          INSERT INTO TABLE3
                          VALUES (...);
                          INSERT INTO TABLE4
                          VALUES (...);
                          INSERT INTO TABLE5
                          VALUES (...);
                          EXCEPTION
                          WHEN OTHERS THEN
                           FORMS_DDL('ROLLBACK');
                          INSERT INTO ERR_LOG_TABLE -- Here insertion log in case of failure in any one of above 5 insert statements.
                          VALUES (...);
                          EXIT;
                          END;
                          END LOOP;
                          FORMS_DDL('COMMIT');
                          END;
                          You did not waited my post ;) i just edited.
                          -Ammad
                          • 10. Re: Insert data from another table
                            404152
                            Ammad
                            EXIT will be rollback the whole process, but not the Loop?
                            • 11. Re: Insert data from another table
                              AmmadAhmed
                              401149 wrote:
                              Ammad
                              EXIT will be rollback the whole process, but not the Loop?
                              I could not understand what you are trying to say. Did you try that code. I added ROLLBACK in exception. It will ROLLBACK all insertion which happened in LOOP. And will create one record in LOG table then will EXIT the loop and it will COMMIT that one record. If you see in the last after LOOP i added COMMIT.
                              • 12. Re: Insert data from another table
                                404152
                                Ammad / Slava:

                                Do I need to create a save point as my code (sp_name:= Get_Application_Property(SAVEPOINT_NAME)and Issue_Rollback(sp_name)) of is enough the command FORMS_DDL('ROLLBACK') as your example?
                                Thanks,

                                Edited by: 401149 on May 14, 2010 3:50 PM
                                • 13. Re: Insert data from another table
                                  AmmadAhmed
                                  No need. This should work.
                                  • 14. Re: Insert data from another table
                                    49905
                                    Dear Julia :)
                                    If you need to rollback all records in case at least one failed - use Ammad code.
                                    If you want to insert all correct records into 5 tables and keep all failed in a log table - use my example.
                                    Do not use Issue_Rollback.
                                    One more question about your temporary table.
                                    Is it real Oracle Global Temporary table?
                                    If it is - be aware that commit or rollback may delete all records from that table.
                                    If not - think what will happen if more then one user run your form simultaneously.
                                    1 2 Previous Next