I assume this is a follow-up to your earlier post: rollback after insert fail
It depends. SQL Developer connects to the database and processes SQL statements using JDBC. In general, JDBC connections default to using auto-commit mode: Using Transactions (The Java&trade; Tutorials &gt; JDBC(TM) Database Access &gt; JDBC Bas…
However, the default user setting in SQL Developer is exactly the opposite, more common use case where auto-commit is unchecked:
Tools > Preferences > Database > Advanced > Autocommit
A discussion of the equivalent for SQL*Plus (even though you did not ask) is here: Autocommit
And keep in mind DDL (data definition language) differs from DML (data manipulation language): it always assumes auto-commit, although there can be some subtleties: Auto commit DDL: Difference between SQL developer and SQL*Plus?
In oracle sql developer if I manually click run next to a stored procedure, then it will generates code like:
My question is : will above command will automatically commit all transactions in the stored procedure even I don't have a commit statement below the execute my sproc statement?
and will it also rollback all the transactions in the stored procedure if any errors happened -even I don't explicitly have a roll back statement?
How could anyone but you possibly know?
You didn't even bother to tell anyone what database and what DB version you are even using. Your question is like asking us if your car has a radio - we don't know since we have know way of looking into your car to see.
What result did you get when you tested it to see what would happen?
Don't be afraid of breadking your database by actually TRYING things.
The best way to learn is BY DOING - not by reading or just asking questions.
Conduct your own tests.
1. Create a procedure
2. add a transaction to the procedure
3. do your 'manually click run next to' the procedure
4. sit back and WATCH
What happened? THAT is your answer. You don't need anyone elses help to answer your question.