How To use Autotrace

You can control the report by setting the AUTOTRACE system variable. To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema.

SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.

Explain plan for statements

To explain a SQL statement, you can enter the following:
EXPLAIN PLAN FOR
<<SQL Statement>>
With multiple statements, you can specify a unique statement identifier and use that to identify your specific execution plan.
EXPLAIN PLAN
    SET STATEMENT_ID = <<identifier>> FOR
<<SQL Statement>>
After you have explained the plan, you can use UTLXPLS.SQL provided by Oracle to display the most recent plan table output.

Highlight current Tree Node

1. Create a regular tree region
2. Create a form region which gets synchronized with your tree
3. Change the tree query to contain the following CASE statement

SELECT EMPLOYEE_ID AS ID,
MANAGER_ID AS PID,
CASE
WHEN EMPLOYEE_ID = 4_EMPLOYEE_ID THEN
” || LAST_NAME || ”
ELSE
LAST_NAME
END AS NAME,
‘f?p=&APP_ID.:4:’ || :SESSION || ‘::NO::P4_EMPLOYEE_ID:’ ||
EMPLOYEE_ID AS LINK,
NULL AS A1,
NULL AS A2
FROM #OWNER#.EMPLOYEES

Adding Timing Information to a Report

To add a timer to a report,indicating how long it took for the database to get its results,

You  cann add this special substitution string. #TIMING# to region footer of your report.

#TIMING# Elapsed.

Using CONNECT BY to get a table of n rows

A handy way to get a table of n rows:

SELECT level FROM dual CONNECT BY level <= 10;

Automatically converting text to uppercase

Sometimes you need to get data typed uppercase or lowercase but you can’t trust user for this.You need to force them to enter text in uppercase/lowercase.

To achieve this you can add the following code to HTML Form Element Attributes of your item.

For uppercase

onChange="this.value=this.value.toUpperCase();

For lowercase

onChange="this.value=this.value.toLowerCase();