This content has been marked as final. Show 11 replies
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?
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,
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, ..