Profiler Function shows no code in analysis window.
The problem of no code showing in the Profiler Analysis window is due to having executed PROFLOAD.SQL in both SYS and the user's schema.
In addition, not having the privilege for DBMS_PROFILE will cause similar results.
To set up the Profiler (NOTE: The minimum Oracle database version required for the PL/SQL Profiler is Oracle8i.)
1. Make sure you have the SYS.DBMS_PROFILE package or load the ORA8I\RDBMS\ADMIN\PROFLOAD.SQL script if you do not have the package. Make sure that GRANT EXECUTE on the DBMS_PROFILE package has been granted to PUBLIC or the users that will use the profiling feature. DBMS_PROFILER must be installed as SYS.
2. Run the TOAD Profiler.SQL script. You can find a copy of the script in the TOAD\temps folder. This script will create the tables, package, synonyms, and grants required to do profiling. We recommend installing these objects in the TOAD schema, but you can select whatever schema is appropriate to your environment.
And from the header in TOADProfiler.sql:
We suggest that you run this script in the TOAD schema. Alternatively, you could install these objects in the SYSTEM schema. If you elect to install these objects in a private user schema, do NOT run the GRANTS at the bottom of the script. Usage of these objects is not fully qualified, e.g., [insert into plsql_profiler_data ...] It relies on the objects to exist in the same user schema or be resolved via public synonyms to toad.plsql_profiler_data, system.plsql_profiler_data, etc. If you have plsql_profiler_data locally and publicly, the profiler will not operate properly.
1.) Make sure the user has not executed PROFLOAD.SQL in their own schema. It should be installed as SYS ONLY.
2.) Make sure GRANT EXECUTE ON DBMS_PROFILE has been granted to PUBLIC (optionally only to users using TOAD Profiler)
3.) Make sure TOADProfiler.sql was executed in TOAD or SYSTEM schema OR if TOADProfiler.sql was executed in 'private' schemas instead of as SYSTEM or TOAD then make sure they did not execute the grants at the end of the TOADPRofiler script, as noted in the header (Grants shown below)
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_data TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_units TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_profiler_runs TO PUBLIC;
GRANT execute ON toad_profiler TO PUBLIC;
GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;