SWITCH_CURRENT_CONSUMER_GROUP Procedure

This procedure changes the current resource consumer group of a user's current session.

This lets you switch to a consumer group if you have the switch privilege for that particular group. If the caller is another procedure, then this enables the user to switch to a consumer group for which the owner of that procedure has switch privilege.

Syntax

DBMS_SESSION.switch_current_consumer_group (
   new_consumer_group     IN  VARCHAR2, 
   old_consumer_group     OUT VARCHAR2, 
   initial_group_on_error IN  BOOLEAN); 

Parameters

Table 58-19 SWITCH_CURRENT_CONSUMER_GROUP Procedure Parameters

Parameter	Description
new_consumer_group	Name of consumer group to which you want to switch.
old_consumer_group	Name of the consumer group from which you just switched out.
initial_group_on_error	If TRUE, then sets the current consumer group of the caller to his/her initial consumer group in the event of an error.

Returns

This procedure outputs the old consumer group of the user in the parameter old_consumer_group.

Note: You can switch back to the old consumer group later using the value returned in old_consumer_group.

Exceptions

Table 58-20 SWITCH_CURRENT_CONSUMER_GROUP Procedure Exceptions

Exception	Description
29368	Non-existent consumer group.
1031	Insufficient privileges.
29396	Cannot switch to OTHER_GROUPS consumer group.

Usage Notes

The owner of a procedure must have privileges on the group from which a user was switched (old_consumer_group) in order to switch them back. There is one exception: The procedure can always switch the user back to his/her initial consumer group (skipping the privilege check).

By setting initial_group_on_error to TRUE, SWITCH_CURRENT_CONSUMER_GROUP puts the current session into the default group, if it can't put it into the group designated by new_consumer_group. The error associated with the attempt to move a session into new_consumer_group is raised, even though the current consumer group has been changed to the initial consumer group.

Example

CREATE OR REPLACE PROCEDURE high_priority_task is 
   old_group varchar2(30); 
   prev_group varchar2(30); 
   curr_user varchar2(30);  
BEGIN 
  -- switch invoker to privileged consumer group. If we fail to do so, an 
  -- error will be thrown, but the consumer group will not change 
  -- because 'initial_group_on_error' is set to FALSE 
 
  dbms_session.switch_current_consumer_group('tkrogrp1', old_group, FALSE); 
  -- set up exception handler (in the event of an error, we do not want to 
  -- return to caller while leaving the session still in the privileged 
  -- group) 
 
  BEGIN 
    -- perform some operations while under privileged group 
 
  EXCEPTION 
    WHEN OTHERS THEN 
     -- It is possible that the procedure owner does not have privileges 
     -- on old_group. 'initial_group_on_error' is set to TRUE to make sure 
     -- that the user is moved out of the privileged group in such a 
     -- situation 
 
     dbms_session.switch_current_consumer_group(old_group,prev_group,TRUE); 
     RAISE; 
    END; 
 
  -- we've succeeded. Now switch to old_group, or if cannot do so, switch 
  -- to caller's initial consumer group 
 
  dbms_session.switch_current_consumer_group(old_group,prev_group,TRUE); 
END high_priority_task; 
/