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.
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
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
SELECTstatement. 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'?
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.
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.