You Asked

I am working on some triggers to maintain summary information of parent records in a self-referencing table.

To get around the mutating table problem I do the following:
1) Stage the parent record id in a pl/sql table within a package in a "for each row" trigger.
2) In an "after statement" trigger call a procedure within the same package to loop through and process the values in the pl/sql table. This may cause an additional parent record update to be triggered. Only one level of this is possible.

An initial problem with this is that the same record is getting processed multiple times. And I get an
ORA-00036: maximum number of recursive SQL levels (50) exceeded

I have three workarounds
1) swap the pl/sql table into a local version, replace with an empty table, and process the local version.
2) add a status column to the pl/sql table and logically omit records which have been processed.
3) use a real table to stage the information and remove a row after processing.

Is one of these a better solution? Do either of the pl/sql table solutions have issues if different records (in the same or different hierarchal branches) are updated concurrently?

Last could you clairify the scope of pl/sql tables (global, transactional, session,...)

and Tom said...

Variables of type "PLSQL tables" have the same scope as any other variable in PLSQL.

If you put them in a package outside of a procedure or function -- they "live" for the life of the session.

If you put them in a procedure or function -- they "live" for the life of that procedure or function. Return from the procedure or function and they "go away".

The problem you are getting, ora-00036, is not due to the same record getting processed but rather more likely due to infinite recursion (the table is updated -- you add a record to be processed into the plsql table, after the initial update that fired you processes -- YOU yourself start updating the table which in turn fires your trigger, adding more rows which are then processed in your after trigger which in turn updates your table and so on and so on)

You need to avoid the recursion. see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:469621337269 <code>

for example.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the clear explanations as usual.

David, November 16, 2004 - 11:43 am UTC

I'm getting the forrolowing error, when i execute the cursor for all my records in a table inside my procedure.
ORA-00036: maximum number of recursive SQL levels (50) exceeded
for example i have "mast" table with 10,000 records.
and i execute the foll command.
...
Declare
cursor cur is seelct * from mast;
Begin
for c in cur
loop

end loop;
end ;
...
Im trying to add security where clause thru VPD .
If the colA = 1, ,2,3 then
user group admin in hr division can access it .
or
If the colA = 1, ,2,3 then
user group admin in hr division
and
user group admin in acct divsison can access it .

How do a write a where clause for this situation, to avoid the recursive cursor.

Thanks

November 16, 2004 - 1:01 pm UTC

sounds like you have an accidently infinte loop. without seeing code -- cannot "help"

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks for the clear explanations as usual.

More to Explore

PL/SQL demos

PL/SQL docs

what is the scope of pl/sql tables

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks for the clear explanations as usual.

More to Explore

PL/SQL demos

PL/SQL docs