Скачать или смотреть How to Determine if Your PL/SQL Procedure is Running in a Concurrent Program Environment

Discover a simple solution for your PL/SQL procedures to determine if they are executed in a concurrent program environment, logging appropriately whether standalone or from Oracle's concurrent manager.
---
This video is based on the question https://stackoverflow.com/q/69977258/ asked by the user 'Paul Moore' ( https://stackoverflow.com/u/34438/ ) and on the answer https://stackoverflow.com/a/69987784/ provided by the user 'Andy Haack' ( https://stackoverflow.com/u/6306430/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How can a PL/SQL procedure tell if it is being run from a concurrent program?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Determine if Your PL/SQL Procedure is Running in a Concurrent Program Environment

When developing PL/SQL procedures in Oracle, one common requirement is to understand whether the code is being executed in a concurrent program environment or running standalone. This distinction is crucial for logging mechanisms; we often want to log outputs in different ways depending on the execution context. In this guide, we’ll explore how to efficiently determine if your PL/SQL procedure is being run from a concurrent program and how to log outputs accordingly.

The Challenge

Imagine creating a PL/SQL procedure tasked with logging output information. If this procedure is executed from an Oracle concurrent manager, you want to log that output directly to the concurrent manager log. Conversely, when executed in standalone mode, the output should be directed to dbms_output. The challenge then becomes how to check the execution context efficiently.

Previously, one might resort to the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

However, as pointed out, this method can be slow and inefficient. So, what’s the more efficient solution available?

The Efficient Solution

To determine if the PL/SQL procedure is running within a concurrent program, we can utilize the fnd_global.conc_request_id. This function offers a straightforward approach to detect the context of execution without the overhead of complex queries. Here’s how you can implement this in your code.

Implementing the Solution

Below is a sample procedure that checks the concurrent request context and logs accordingly.

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Procedure Declaration: The write_log procedure takes two parameters - the text to log (p_text) and the log level (p_log_level), with a default value set to 1.

Checking Context: By checking if fnd_global.conc_request_id is greater than 0, the procedure determines if it’s running as part of a concurrent request.

If true, it uses fnd_file.put_line() to log to the concurrent manager log.

If false, it falls back to using dbms_output or another logging mechanism defined by the user.

Conclusion

Understanding the execution context of your PL/SQL procedures is essential for meaningful logging and output management. Using fnd_global.conc_request_id provides a quick and efficient method to establish whether your code runs in a concurrent program, allowing you to handle logging effectively according to the execution context.

By implementing this simple yet effective technique, you can enhance the maintainability and performance of your Oracle PL/SQL procedures.

In summary, next time you're developing a PL/SQL procedure, leverage this knowledge to make your logging strategies robust and efficient!