How to Set Variables in Oracle OTBI for Enhanced Report Control
Oracle Transactional Business Intelligence (OTBI) supports setting variables directly within SQL queries to dynamically influence how reports are generated, debugged, or filtered. Variables can control logging levels, cache behavior, or parameterize report execution on the fly—offering powerful control without modifying the underlying report design.
Setting Multiple Variables at Once
You can set multiple variables using the SET VARIABLE statement at the beginning of your query or in the Advanced SQL Clauses > Prefix section of OTBI Answers.
Example:
SET VARIABLE LOGLEVEL=7,DISABLE_CACHE_HIT=1,DISABLE_PLAN_CACHE_HIT=1;
LOGLEVEL=7: Enables detailed logging of query execution, useful for troubleshooting.DISABLE_CACHE_HIT=1: Disables cache retrieval, forcing fresh data retrieval.DISABLE_PLAN_CACHE_HIT=1: Disables cached query plans to ensure the optimizer re-evaluates each query.
Why Use Variables?
Troubleshooting: Increase log verbosity temporarily to diagnose slow or failing reports.
Data Freshness: Bypass caching during critical reporting periods where real-time accuracy is vital.
Dynamic Behavior: Change report filtering or calculations based on variable values.
How to Use in OTBI
Variables can be set in the Advanced tab of the analysis editor as a prefix to the SQL query. This ensures the variables apply to that specific run without permanent system changes.
SET VARIABLE PARAM_EFFECTIVE_DATE='2025-07-07';
SELECT ...
Other dynamic variables can be used in filters or display logic like:
WHERE "Calendar"."Date" <= VALUEOF(PARAM_EFFECTIVE_DATE)
Best Practices
Use variable settings sparingly in production to avoid unnecessary performance impacts.
Always remove or reset diagnostic variables like
LOGLEVELafter problem resolution.Document variable usage clearly in report metadata or user instructions.
Summary
Setting variables in OTBI empowers report developers and analysts to tailor report execution dynamically, improving flexibility and ease of troubleshooting while maintaining the robustness of Oracle OTBI.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
