6 Replies Latest reply: Jun 30, 2014 3:26 AM by BluShadow RSS

    Ref Cursor , Cursor variable and cusror object

    SShubhangi

      Hello Experts,

      I am bit confused with cursor object concept

       

      For curosr variable we can assign any select statement

       

      For example

      Suppose i have assigned 2 select staemnt to same cursor variable Like Open c1 for select * from dept then i m assigning new query without closing the c1 Open c1 for select * from emp what will happen ? what is the role of cursor object here ? I am really confued with this as cursor variable points to the result set ? Can you please explain how same varible is working with differnet queries ?? means how same varible is pointing to the differnet result sets Thanks a lot in advance , SShubhangi

        • 1. Re: Ref Cursor , Cursor variable and cusror object
          rp0428
          I am bit confused with cursor object concept

           

          Post a link to the Oracle documentation you are using and a quote from the section you are confused about.

          For curosr variable we can assign any select statement

          Yes - just like you can put a little yellow 'sticky note' on your refrigerator, or you file cabinet or on your stove. Stick it on whatever you like - but you can only stick it on ONE item at a time.

          For example

          Suppose i have assigned 2 select staemnt to same cursor variable Like Open c1 for select * from dept then i m assigning new query without closing the c1 Open c1 for select * from emp what will happen ?

          What happened when you tried it?

           

          Don't be afraid of breaking Oracle by actually trying things. That is the best way to learn.

           

          Try what you said and post the results.

          I am really confued with this as cursor variable points to the result set ? Can you please explain how same varible is working with differnet queries ?? means how same varible is pointing to the differnet result sets

          Reread what I just said above.

           

          Then explain how you can 'point' that yellow 'sitcky note' to any object you want. You can put it on one object. Then you can take it off that object and put it on another object.

           

          But no matter how many times you do that it can only be sticking to ONE object at a time.

           

          What is confusing about that?

           

          See the PL/SQL Language doc for the OPEN statement

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/open_statement.htm#i35173

          Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement. Although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.

          Did you notice that line that starts: 'Although you must close a cursor before you can reopen it'?

          • 2. Re: Ref Cursor , Cursor variable and cusror object
            Billy~Verreynne

            A cursor is not a result set. A cursor is in fact like an executable program (compiled from SQL source code) that produces output.

             

            A cursor resides in the Shared Pool - kind of like a pool of programs. When you parse a SQL, either an existing program is used, or a new program created, and a "pointer" (reference handle) returned to you to reference and execute that program.

             

            How do you pass data to that program? Via bind variables.

             

            So you and I can have cursor variables (references) to the same cursor in the Shared Pool. You can bind values to it and execute it. I can bind values to it and execute it. Same cursor in use. Executed with different input data (bind variables). Producing different output data.

             

            There are a number of ways in PL/SQL to create cursor variables (reference handles to cursors in the Shared Pool). One such way is via the sys_refcursor data type in PL/SQL. It can be used to point to cursor "select count(*) from emp", execute that cursor, get the data output from that cursor, and then be reused to point to another cursor such as "select * from all_objects order by 1".

             

            Consider sys_refcursor as of a pointer type. It can be used to point to any cursor program. However, before changing the existing pointer to point at something else, first release the client resources used by pointing to a cursor.

             

            If you are not using sys_refcursor to pass the pointer to an external client (Java/C#/etc), then you are responsible for cleaning up the resources used by that pointer (private cursor area data) by closing the cursor, prior to reusing the same PL/SQL sys_refcursor variable to point at another/new cursor.

             

            You also need to ask yourself why your PL/SQL code is using a sys_refcursor as that is usually the wrong choice of cursor variable for PL/SQL only code.

            • 3. Re: Ref Cursor , Cursor variable and cusror object
              BluShadow

              And to add to the existing correct reponses, please take a read of...

               

              PL/SQL 101 : Understanding Ref Cursors

               

              which will help demonstrate how cursors (not just ref cursors) are not result sets.

              • 4. Re: Ref Cursor , Cursor variable and cusror object
                SShubhangi

                rp0428,BillyVerreynne,BluShadow...

                 

                You guys are great....!!

                 

                Thanks a lot

                • 5. Re: Ref Cursor , Cursor variable and cusror object
                  SShubhangi

                  Thanks a lot... 

                   

                  cursor variable always point to the one and only result set at a time

                  • 6. Re: Ref Cursor , Cursor variable and cusror object
                    BluShadow

                    Not quite... it doesn't point to any result set.  No results have been fetched when you open a cursor, they're only fetched when a fetch is issued (implicitly or explicitly).

                    Oracle identifies the rows internally when the query is executed, but before any data is fetched, by using the SCN (Transactions) which means that any other activity on those rows is not picked up.  It's like taking a snapshot of the data at the point the query starts but without having the fetch any data into memory or a 'result set' or anything like that.