11 Replies Latest reply on Jan 29, 2011 11:24 AM by 835262


      Please can you me the difference between SYS_REFCURSOR and CURSOR
        • 1. Re: SYS_REFCURSOR and CURSOR

          sys_refcursor is a new feature of Oracle 9i that can be used to pass cursors from and to a stored procedure.

          For details about Cursors, read the documentation.

          Ajay K. Garg
          • 2. Re: SYS_REFCURSOR and CURSOR
            Thanks man but I made a mistake I wanted the difference between SYS_REFCURSOR and REFCURSOR. I think REFCURSOR also can be passed through and fro the procedures.
            • 3. Re: SYS_REFCURSOR and CURSOR
              I wanted the difference between SYS_REFCURSOR and REFCURSOR
              SYS_REFCURSOR is merely a pre-declared weak ref cursor. In other words these two variables are equivalent...
                 ref1 SYS_REFCURSOR;
                 TYPE rc IS REF CURSOR;
                 ref2 rc;
              As opposed to a strong ref cursor declaration:
                  TYPE emp_rc IS REF CURSOR RETURN scott.emp%ROWTYPE;
                 emp_recs emp_rc;
              We can use both strong and weak ref cursors as parameters. However, if we are not using SYS_REFCURSOR then we must declare the type in an in-scope package spec.

              Cheers, APC
              • 4. privileges on tables using SYS_REFCURSOR and CURSOR

                Further to this message I am trying to define a procedure whereby I am trying to use an 'OPEN p_recordset FOR' and then define a SELECT statement on a table. This table (for the sake of argument, it's called EMP) is owned by somebody else, but I have privileges to SELECT FROM it.

                On trying to incorporate a SELECT * FROM EMP in my 'OPEN p_recordset FOR' Oracle will say when I try to install this procedure that the table or view does not exist, by means of an ORA-00942 error. What am I doing wrong?

                Any idea?


                • 5. Re: privileges on tables using SYS_REFCURSOR and CURSOR
                  If you can SELECT from it directly in SQL*Plus / TOAD etc. but not in a stored procedure then it is likely you have the privilege granted to you by way of ROLE rather than directly. Stored procedures created with definer rights do not honour roles.
                  • 6. Re: privileges on tables using SYS_REFCURSOR and CURSOR
                    Hi James,

                    Thanks ever so much for your response.

                    I can "see" what you are saying though could you point me to some documentation on the matter? I have been trying all sorts of documentation on technet.oracle.com, but am not getting any further. Also, what could be done so that in my quality of a programmer I could get rights to the relevant database object so that my code is generated properly?

                    Kind regards,

                    • 7. Re: privileges on tables using SYS_REFCURSOR and CURSOR

                      If you have just the SELECT privilege on that object then you need to prefix EMP with the "<schema_name>." IF EMP is in schema HR then it ahould be

                      select * from HR.EMP.

                      IF HR has create a public synonym on the EMP table and granted select privilege for you then you can just say SELELCT * FROM EMP.

                      Hope it helps.

                      Thanks and Regards,
                      • 8. Re: privileges on tables using SYS_REFCURSOR and CURSOR
                        Well there's a good idea. Why did you feel the need to dig up a thread that's over 2 years old?
                        • 9. Re: privileges on tables using SYS_REFCURSOR and CURSOR
                          I believe it would be because this thread currently comes up as the second result when you google sys_refcursor and cursor.

                          That would also explain the 4 thousand-odd views for this thread :)

                          Message was edited by:
                          Anthony Wilson

                          Or third when you just google sys_refcursor
                          • 10. Re: SYS_REFCURSOR and CURSOR
                            > Please can you me the difference between SYS_REFCURSOR and CURSOR

                            No difference. Big difference.

                            Depends on from where you look at the thing called a "cursor".

                            Inside Oracle itself (the SQL Shared Pool) - no difference at all. Every single SQL parsed becomes a cursor. Oracle's SQL engine has no concept of ref cursor, or any other type of cursor. These are all just cursors. Period.

                            From the client language (PL/SQL, Java, C#, etc) there are differences. They implement "interfaces" to that cursor in the SQL engine.

                            PL/SQL specifically provides a couple of such interfaces:
                            - an implicit cursor (you simply code SQL directly in PL, and it does the cursor "thing" for you)
                            - an explicit cursor (you code the cursor definition, and the fetches from the cursor)
                            - a DBMS_SQL cursor (the full SQL cursor interface - complex, but with a lot of additional goodies)
                            - a reference cursor (a reference/handle/pointer to a cursor that is intended to be passed to an external client for use)

                            Oh - just saw that this is a 3 year old thread... Just plain stupid (hello reksab!) to bump this thread. Oh well, ..
                            • 11. Re: SYS_REFCURSOR and CURSOR
                              Useful answer... Thanks for it...

                              Edited by: Khilendra on Jan 29, 2011 3:24 AM