Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Execute immediate will auto commit sql statement before

Posted on 2007-11-25
1
Medium Priority
?
12,663 Views
Last Modified: 2013-12-07
Is Execute immediate will commit the update statement execute before?
In the example below when error happen when execute procedure Proc_1 at update table2, i notice that the update statement for table1 has been committed. If i uncomment the execute immediate statement, update table1 will not commit, anyone knows why this happen?

CREATE OR REPLACE Procedure Proc_1 As
 Begin
        -- UPDATE Table  1      
        Update table1 Set col1 = 'Col1';
       
      -- Execute immediate
       execute immediate ('Alter trigger trigger1 disable');
       
        -- Update Table 2      
        Update Table2 Set col2 = 'Col2';
        Commit;
     End If;
  Exception
     When others Then
      Rollback;
       Raise_application_error(-20747, Sqlcode || ' ' || Sqlerrm);
End;
0
Comment
Question by:yuching
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 13

Accepted Solution

by:
sonicefu earned 500 total points
ID: 20348524
No, execute immediate does not commit automatically. In your example update in table1 was commited due to DDL statement (Alter trigger trigger1 disable). DDL statements commit previously executed DML statements whether DDL statement successfully executed or not.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question