asked on

Oracle cursors

Hey,
In MSSQL if you have a cursor defined for a result set e.g.

DECLARE cur CURSOR FOR
SELECT lObjClaID, lObjID FROM #RESULTS /* Temporary table*/

and if the data gets added to the table #results then that data will be
picked up by the cursor. i.e. the cursor changes as your going through it.

I cannot seem to repeat this in Oracle.

DECLARE CURSOR cur IS
SELECT lObjClaID,lObjID FROM sSession /* sSession is a Global
Temporary Table*/

Now if while looping through the cursor, I add a couple of rows to
sSession....shouldn't the cursor pick up on these rows???

is there some sort of update command, can't find anything in my books.

any help appreciated

james

andrewst

No, Oracle works totally differently from MSSQL and most other DBMSs. Once you open a cursor, the result set it will return is fixed. Any updates, inserts or deletes that occur after the cursor is opened are not visible to the cursor. You cannot "update" the cursor, all you can do is close and re-open it. This could mean a re-design of code ported from MSSQL (e.g.) that relies on cursors getting "updated".

obrienj

ASKER

Is there some other way to do the same thing in oracle.
Can I loop through a table adding rows as I go and this loop will continue to the end, including the new rows.

andrewst

It depends on what exactly you are trying to do, and how much data you want to do it with. For example, you can hold data in a "collection" (varray, PL/SQL table, etc) in memory - but that wouldn't be appropriate for large volumes of data.

Perhaps you can describe the requirement in more detail, then someone can suggest an Oracle approach to it. Currently I am not at all clear on what you are trying to do. Even if the cursor could see the new rows (it can't), it would only see them if they were logically "after" the currently fetched row according to the ORDER BY clause of the cursor. I suspect you are assuming that newly added records will be "at the end" if you don't use ORDER BY. That is not necessarily true.

tamas_urban

Try to use MATERIALIZED VIEW, and make a cursor on it.
I don't wanna write down the syntax of mat.view, u can find in oracle documentation.

CREATE

U can set the refresh FAST,COMPLETE,FORCE,ON COMMIT,ON DEMAND.
U can set the periodic refresh of mat.view.

http://download-east.oracle.com/docs/cd/A87861_01/NT817EE/server.817/a85397/state14c.htm#2063795

Don't forget about privileges...
in your schema:
-You must have been granted either the CREATE MATERIALIZED VIEW or CREATE SNAPSHOT system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege

-You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

in another user's schema:
-You must have the CREATE ANY MATERIALIZED VIEW or CREATE ANY SNAPSHOT system privilege and access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

-The owner or the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

andrewst

Materialized views do not violate Oracle's multi-versioning model, so the same issue would apply: once you open a cursor that selects from an MV, the result is fixed at that point in time. Any subsequent inserts made to the MV will not be seen by the cursor.

obrienj

ASKER

Basically what I am trying to do is recursively go through a table of results. -> a hierarchial result set

In the first insert I add the first layer of items. Been the children of the single item that I start with (take it that @lClaID and @lID are the ID's of this item)

I then declare a curser for the table I just put the results into.

I then loop through this table and repeat the first insert for each item in the results table (using the changing IDs @lClaID and @lID);constantly adding to the same results table; what this means is that I start with a parent item, get its first row of children, then in turn get those items children and so forth....

the curser will keep going until no more items are added, i.e. when the full hierarchy is now contained within the results table.

BEGIN
INSERT INTO #RESULTS
SELECT
*
FROM
fHIE, fOBJ
WHERE
(lObj1ClaID = @lClaID) AND (lObj1ID = @lID)......
ORDER BY lSeqNo

IF @bLoadAllFlag = 1
BEGIN
DECLARE cur CURSOR LOCAL FOR
SELECT lObjClaID, lObjID FROM #RESULTS
OPEN cur
FETCH NEXT FROM cur INTO @lClaID, @lID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #RESULTS
SELECT
*
FROM
fHIE, fOBJ
WHERE
(lObj1ClaID = @lClaID) AND (lObj1ID = @lID)..... ORDER BY lSeqNo
END
FETCH NEXT FROM cur INTO @lClaID, @lID
END
CLOSE cur
DEALLOCATE cur
END

SELECT * FROM #RESULTS

And now I need to be able to do the same thing in oracle.
Do you see what I'm attempting and why?
Any ideas??

ASKER CERTIFIED SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

obrienj

ASKER

Thanks alot, didn't even know it existed. Have just started with oracle.
I think that this should do the trick okay.
thanks again,
james

Mark Geerlings

The "hierarchical" query in Oracle (using "connect by" and "start with") is still subject to the same data concurrency model that Oracle uses consistently. That is, any changes made after a query started to the data that the query could select will not be visible in that query. If you want to see the changes, you will need to close, then re-open the cursor.

obrienj

ASKER

yes, but I wont need to use a cursor etc, I can do it all in the query itself.