6 Replies Latest reply on Sep 21, 2013 8:58 PM by rp0428

    Cursor for loop and  forall

    surendra4y


      Hi All,

       

      can  I  use cursor  for loop  in  forall if we can the  what is  the  way .

      as I know  cursor  loop is  faster  the  normal cursor  and  for  all also faster  instant  of for  loop

      so can  I combine both in my procedure.

       

      Thanks,

        • 1. Re: Cursor for loop and  forall
          sb92075

          Never do in PL/SQL that which  can be done in plain SQL

           

           

           

          How do I ask a question on the forums?

          https://forums.oracle.com/message/9362002#9362002

          • 2. Re: Cursor for loop and  forall
            Billy~Verreynne

            surendra4y wrote:

             

            as I know  cursor  loop is  faster  the  normal cursor  and  for  all also faster  instant  of for  loop

            Your statement does not make sense.

             

            All SQLs are parsed and executed as SQL cursors on the server.

             

            All SQL cursors are equal. Some are not created via special magic to be faster than another, for the same SQL source code.

             

            SQL cursors provide a fetch interface - allowing the client (the language using the cursor, such as Java or PL/SQL) different means to consume the cursor's output. Bulk fetching is usually preferred.

             

            However, using a cursor output (e.g. SELECT cursor) as input to another cursor (e.g. INSERT cursor), is almost always a fundamentally flawed idea. Irrespective of bulk processing  (such as using a FORALL loop).

            • 3. Re: Cursor for loop and  forall
              Oracle Maniac

              Hi Billy ,

               

              What about delete,update & inserts ? Is the concept still true for them ?  How do we fetch a insert or delete statement ?If it sounds silly then please brief with the cursor handling in case of dmls. Please throw some light  .:)

              • 4. Re: Cursor for loop and  forall
                sb92075

                >How do we fetch a insert or delete statement ?

                what exactly does above mean?

                 

                Consider to Read The Fine Manual below.

                 

                Contents

                • 5. Re: Cursor for loop and  forall
                  Billy~Verreynne

                  Which concept exactly?

                   

                  DMLs are also cursors - and have the same interface as any other cursor. See this image chart (from Using SQL Statements in OCI) for a basic overview of how the call interface looks.

                   

                  Not all interface call are relevant. So for a DML call, the fetch is not relevant. Likewise, the bind call is optional for all types of calls, and not applicable at all to cursors executing DDLs.

                   

                  A PL/SQL anonymous block for example that is used to return data (such as a ref cursor), will make use of the bind, execute and define variable call.

                   

                  Inside the PL/SQL language, the DBMS_SQL is the language's "manual" wrapper to the cursor interface interface. This allows one to use cursors in PL/SQL the same way as external languages like C/C++ and Java and so on use cursors (though in Java you would seldom use the cursor interface directly as abstraction layers in the form of classes are provided).

                   

                  PL/SQL's real strength though is dealing with the cursor interface itself, so that you do not have to. You code SQL and mix PL/SQL variables and so on into it. The PL/SQL engine recognises that, and creates all the cursor interface calls needed for binding, executing, and so on. So do not confuse the way PL/SQL treats cursors as the SQL cursor interface. Just like Java and C# classes provides an abstraction layer and internally use the SQL cursor interface, so too PL/SQL provides an abstraction layer (implicit cursors, explicit cursors, ref cursors, execute immediate cursors) - which internally uses the SQL cursor interface.

                  • 6. Re: Cursor for loop and  forall
                    rp0428
                    can  I  use cursor  for loop  in  forall if we can the  what is  the  way .

                    No - a 'forall' is a single executable statement.