Join a cursor or record set in oracle

Question

I have good experience in sybase and have started looking into oracle in free time. Most of the sybase procedures that i have worked with have temp tables and it makes sense to join two or more temp tables get a result set.

Question: Is there a way to join two or more cursors like a logical table.

Something like:

SELECT c1.id, 
       c2.name 
  FROM cursorEmp c1, 
       CursorDept c2 
 WHERE c1.DeptId = c2.DeptId

Answer 1

You cannot join two cursors, no.

You could, of course, combine the two underlying queries, i.e.

SELECT c1.id,
       c2.name
  FROM (SELECT * FROM emp WHERE ename = 'KING') c1,
       (SELECT * FROM dept WHERE dname = 'ACCOUNTING') c2
 WHERE c1.DeptID = c2.DeptID

In Oracle, since readers do not block writers (and vice versa), it is very rarely necessary to use temporary tables. You would normally just query the underlying tables using views as appropriate to provide appropriate levels of abstraction.