This content has been marked as final. Show 11 replies
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
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.
SYS_REFCURSOR is merely a pre-declared weak ref cursor. In other words these two variables are equivalent...
I wanted the difference between SYS_REFCURSOR and REFCURSOR
DECLAREAs opposed to a strong ref cursor declaration:
TYPE rc IS REF CURSOR;
DECLAREWe 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.
TYPE emp_rc IS REF CURSOR RETURN scott.emp%ROWTYPE;
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?
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.
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?
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,
Well there's a good idea. Why did you feel the need to dig up a thread that's over 2 years old?
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:
Or third when you just google sys_refcursor
> 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, ..
Useful answer... Thanks for it...
Edited by: Khilendra on Jan 29, 2011 3:24 AM