1 2 Previous Next 24 Replies Latest reply: Feb 11, 2013 2:38 AM by 930854 Go to original post RSS
      • 15. Re: DML operations
        Hemant K Chitale
        okay. I give up.

        Let's all assume that questions published by Oracle are complete and devoid of ambiguity and have no mistakes in them.


        Hemant K Chitale

        Edited by: Hemant K Chitale on Feb 8, 2013 5:28 PM
        • 16. Re: DML operations
          Nitesh.
          I have even written certification but Oracle server is fixed and I even noticed that in a exam, options for the question provided are wrong but then also Oracle server accepts, selected wrong options as Right and allots marks .. Its i have experienced ..

          Edited by: Niteshkhush on Feb 8, 2013 3:11 PM
          • 17. Re: DML operations
            930854
            Hi,

            This is not a wrong question. I tried in the oracle command prompt, it's showing a result. if it's a wrong question means why the result is showing?
            • 18. Re: DML operations
              APC
              Billy  Verreynne  wrote:
              The answer is, as it often is, it depends. As in the case of a motorcycle where doors are not applicable. :-)
              Unless the motorcycle has a sidecar which often have doors - or at least a door :)

              Cheers, APC
              • 19. Re: DML operations
                rp0428
                >
                This is not a wrong question. I tried in the oracle command prompt, it's showing a result. if it's a wrong question means why the result is showing?
                >
                That is where you need to be careful with the terminology that you use.

                'Oracle' does not have a 'command prompt'. The tool you are using is what would have a command prompt. Reread Karthick's answer about how the tool you are using can affect what result you get when the 'tool' ends the session.

                This question should have been two different questions.

                There is also often confusion about the term 'rollback'. When the 'ROLLBACK' statement is executed the entire transaction is undone.

                But Oracle, internally, can (and will) rollback a single statement. As those doc links show if a transaction has 2 successful update statements but the third one fails Oracle will rollback the third statement and transfer you to an exception handler. You, the user, can now issue a COMMIT in that handler and that will cause the first two update statements (which now comprise the entire TRANSACTION) to be committed even though the third statement was undone by Oracle.
                >
                Statement-Level Atomicity
                Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.

                A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update.

                If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:

                •A SQL statement that does not succeed causes the loss only of work it would have performed itself.

                The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction. For example, if the execution of the second UPDATE statement in Figure 10-1 causes an error and is rolled back, then the work performed by the first UPDATE statement is not rolled back. The first UPDATE statement can be committed or rolled back explicitly by the user.

                •The effect of the rollback is as if the statement had never been run.

                Any side effects of an atomic statement, for example, triggers invoked upon execution of the statement, are considered part of the atomic statement. Either all work generated as part of the atomic statement succeeds or none does.
                • 20. Re: DML operations
                  930854
                  Hi,

                  As karthick said, The basics is that oracle never issue COMMIT or ROLLBACK by itself for DML statements, its truly decided by the client.

                  can you please clarify me what client does in oracle.

                  Please advice.
                  • 21. Re: DML operations
                    Karthick_Arp
                    927851 wrote:
                    Hi,

                    As karthick said, The basics is that oracle never issue COMMIT or ROLLBACK by itself for DML statements, its truly decided by the client.

                    can you please clarify me what client does in oracle.

                    Please advice.
                    Client is just a tool that connects to a Oracle DB and give instructions to oracle which oracle executes. A client could be SQL Plus, Toad, SQL Developer, Java, Dot Net, Apex etc.

                    For example consider a simple banking system. Scenario is "+Debit account A and credit account B+"

                    Client instruction 1
                     
                    update account_balance 
                       set balance = balance - 100 
                    where account_no = '100'; 
                    Instruction completed successfully

                    Client instruction 2
                     
                    update account_balance 
                       set balance = balance + 100 
                    where account_no = '200'; 
                    Instruction fails, Oracle automatically ROLLBACKS the second instruction and return a failure status to client.

                    Now if client issue a COMMIT, instruction one is preserved, leading to inconsistent data in the transaction. And if client issues a ROLLBACK, instruction one is rollbacked taking the database to the initial state.
                    • 22. Re: DML operations
                      930854
                      Thanks Karthick,

                      one more doubt,

                      1) why client issue a commit statement when the second transaction got failed state. Is this oracle default?

                      2) if we faced issue in second transaction means, the oracle issue commit statement. when we give rollback statement the first instruction will be rollback or not?

                      Please clarify. if you think my question is wrong, Please advice me.
                      • 23. Re: DML operations
                        Karthick_Arp
                        927851 wrote:
                        Thanks Karthick,

                        one more doubt,

                        1) why client issue a commit statement when the second transaction got failed state. Is this oracle default?

                        2) if we faced issue in second transaction means, the oracle issue commit statement. when we give rollback statement the first instruction will be rollback or not?

                        Please clarify. if you think my question is wrong, Please advice me.
                        Read what i said again

                        "+Now *_if_* client issue a COMMIT, instruction one is preserved, leading to inconsistent data in the transaction. And if client issues a ROLLBACK, instruction one is rollbacked taking the database to the initial state.+ "

                        The COMMIT or ROLLBACK is decided by the client. Oracle has nothing to do with it. Oracle does not decide how and when a transaction ends, Its the client who makes that decision.
                        • 24. Re: DML operations
                          930854
                          Thanks a lot karthick.
                          1 2 Previous Next