The IN_SESSION_CLEAR stored procedure is used for clearing the IN_DB_UTIL framework for a single identifier of the IN_CONTEXT namespace. It is responsible for decrementing the call stack and adjusting the context attributes to reflect the changes to the call stack.
This procedure should be executed whenever a procedure, that has also initialized the session with IN_SESSION_SET, has finished its unit of work and is tearing down the session.
The TAG provided when executing the procedure must match the existing TAG associated with the specified identifier or an error will be returned. This is to help prevent any other sessions, that are initialized with a different TAG, from inadvertently making any changes to the specified identifier.
If the procedure is the last entry in the call stack then the call stack will be decremented and the associated IN_CONTEXT namespace attributes will be reset to IDLE for the specified identifier, freeing up the identifier for other sessions to use.
If the procedure is not the last entry in the call stack then the call stack will be decremented and the IN_CONTEXT namespace attributes will be updated to reflect the current state of the call stack for the specified identifier without freeing up the identifier for other sessions to use.
The IN_SESSION_CLEAR procedure is responsible for the following framework de-initialization steps.
- Decrement the call stack for the specified identifer.
- IN_CALLSTACK_TRIM
- Clear the session attributes for the specified identifier.
- IN_CONTEXT_SET
- IN_CONTEXT_CLEAR
Any procedure leveraging the IN_DB_UTIL framework should have the following exceptions defined to properly handle the exceptions that could be raised by this procedure.
- FAILED_CHECK – Invalid IDENTIFIER specified. Checked using the CHECK_PARAMETER procedure.
- TAG_MISMATCH – Returned if a procedure attempts to clear a session but the specified TAG value does not match the TAG value that is currently occupying the specified identifier.
- OTHERS – Catch all other undefined exceptions.
Under normal circumstances, IN_SESSION_CLEAR procedure should never need to be manually executed. However, it can be used to manually clear the call stack and session attributes as needed due to an unhandled exception or after manually cancelling an operation before completion.
Important Do not manually execute this procedure if any operations are still in progress as it will clear the call stack and session attributes which will lead to undesired results and unhandled exceptions.
| Parameter | Description | |
|---|---|---|
| IDENTIFIER | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | IN_DB_UTIL | |
| Description | The identifier for which to clear the session attributes. | |
| Options |
IN_DB_UTIL SYNC UPGRADE. |
|
| TAG | Type: | INPUT |
| Datatype: | VARCHAR2(10) | |
| Default Value: | DBMS_RANDOM.STRING('X',10) | |
| Description: | The unique tag associated with the session currently occupying the specified identifier of the IN_CONTEXT namespace. | |
Exceptions
The following example demonstrates how this procedure is called from within another procedure after it has completed its work and is cleanup up.
IN_DB_UTIL.IN_SESSION_CLEAR(V_IDENTIFIER, V_TAG);
The following example demonstrates how to use this procedure to manually clear the call stack and session attributes for a specific identifier, IN_DB_UTIL in this case.
Note that the TAG must match the tag currently associated with the specified identifier.
Important Do not manually execute this procedure if any operations are still in progress.
EXEC IN_DB_UTIL.IN_SESSION_CLEAR('IN_DB_UTIL','WNQJAP4QWJ');
Example output
The following example demonstrates the clearing of the IN_DB_UTIL identifier by executing the IN_SESSION_CLEAR procedure to clear the call stack and session attributes after cancelling the IN_CLEANUP_AUDIT_DATA procedure prior to completion.
SQL> EXEC IN_DB_UTIL.IN_CONTEXT_PRINT('IN_DB_UTIL');
Current DB Time: 2022-08-04 15:17:34.391000
Current UTC Time: 2022-08-04 20:17:34.391000
Current Activity for the IN_DB_UTIL identifier:
IN_DB_UTIL TAG: FUF9OPDVK8
IN_DB_UTIL STATUS: EXECUTING
IN_DB_UTIL OPERATION: CLEANUP_AUDIT_DATA
IN_DB_UTIL CALLED BY: NULL
IN_DB_UTIL START TIME: 2022-08-04 14:05:33.312000
IN_DB_UTIL MAX TIME: 2022-08-04 15:07:41.157000
IN_DB_UTIL MAX MINUTES: 60
IN_DB_UTIL OBJECT: IN_AUDIT
IN_DB_UTIL SUBOBJECT: NULL
IN_DB_UTIL TABLESPACE: INDX
IN_DB_UTIL BATCH SIZE: 100000
IN_DB_UTIL BULK LIMIT: NULL
IN_DB_UTIL PARALLEL: 1
IN_DB_UTIL LOGGING: NULL
IN_DB_UTIL OS USER: LOCAL_DOMAIN\USER_NAME
IN_DB_UTIL DB_SESSION: 742,52540
PL/SQL procedure successfully completed.
SQL> EXEC IN_DB_UTIL.IN_SESSION_CLEAR('IN_DB_UTIL','FUF9OPDVK8');
PL/SQL procedure successfully completed.
SQL> EXEC IN_DB_UTIL.IN_CONTEXT_PRINT('IN_DB_UTIL');
Current DB Time: 2022-08-04 15:21:53.541000
Current UTC Time: 2022-08-04 20:21:53.541000
Current Activity for the IN_DB_UTIL identifier:
IN_DB_UTIL TAG: NULL
IN_DB_UTIL STATUS: IDLE
IN_DB_UTIL OPERATION: NULL
IN_DB_UTIL CALLED BY: NULL
IN_DB_UTIL START TIME: NULL
IN_DB_UTIL MAX TIME: NULL
IN_DB_UTIL MAX MINUTES: NULL
IN_DB_UTIL OBJECT: NULL
IN_DB_UTIL SUBOBJECT: NULL
IN_DB_UTIL TABLESPACE: NULL
IN_DB_UTIL BATCH SIZE: NULL
IN_DB_UTIL BULK LIMIT: NULL
IN_DB_UTIL PARALLEL: NULL
IN_DB_UTIL LOGGING: NULL
IN_DB_UTIL OS USER: NULL
IN_DB_UTIL DB_SESSION: NULL
PL/SQL procedure successfully completed.