ORACLE PL/Scope

I didn't find much data about the internals of PL/Scope.

I'd like to use it to analyze identifiers in PL/SQL scripts. Does it work only on Oracle 11g instances? Can I reference its dlls to use it on a machine with only ORACLE 9/10 installed?

In a related manner, do I have to ex开发者_运维问答ecute the script in order for its identifiers to be analyzed?

---

To answer the easy question first, we do not have to execute the program unit. We have to compile it. That is relatively simple:

SQL> alter session set plscope_settings='IDENTIFIERS:ALL'
  2  /

Session altered.

SQL> alter function str_to_number_tokens compile
  2  /

Function altered.

SQL> SELECT LPAD(' ', level*2, ' ') || name AS name, type, usage, usage_id, line, col
  2  FROM   user_identifiers
  3  START WITH usage_context_id = 0
  4  CONNECT BY PRIOR usage_id = usage_context_id;

NAME                           TYPE               USAGE         USAGE_ID       LINE        COL
------------------------------ ------------------ ----------- ---------- ---------- ----------
  STR_TO_NUMBER_TOKENS         FUNCTION           DECLARATION          1          1         10
    STR_TO_NUMBER_TOKENS       FUNCTION           DEFINITION           2          1         10
      P_STRING                 FORMAL IN          DECLARATION          3          2         10
      P_SEPARATOR              FORMAL IN          DECLARATION          4          3         13
        P_SEPARATOR            FORMAL IN          ASSIGNMENT           5          3         13
      RETURN_VALUE             VARIABLE           DECLARATION          6          6          5
      REGEX_STR                VARIABLE           DECLARATION          7          7          5
      REGEX_STR                VARIABLE           ASSIGNMENT           8         10          9
        P_SEPARATOR            FORMAL IN          REFERENCE            9         10         31
      REGEX_STR                VARIABLE           REFERENCE           10         17         46
      P_STRING                 FORMAL IN          REFERENCE           11         17         36
      REGEX_STR                VARIABLE           REFERENCE           12         16         47
      P_STRING                 FORMAL IN          REFERENCE           13         16         37
      REGEX_STR                VARIABLE           REFERENCE           14         12         57
      P_STRING                 FORMAL IN          REFERENCE           15         12         47
      RETURN_VALUE             VARIABLE           ASSIGNMENT          16         14         22
      RETURN_VALUE             VARIABLE           REFERENCE           17         19         16

17 rows selected.

SQL> 

(Credit where credit is due, I took that query from my mate Tim Hall's Oracle-Base site)

Incidentally, note that PL/Scope operates on named PL/SQL programs (procedures, functions, packages, etc). It doesn't work on anonymous PL/SQL blocks. I mention this because you talk of "scripts" rather than programs. It won't do anything with a SQL script containing some PL/SQL blocks.

The other thing to bear in mind is that PL/Scope populates tables on the SYSAUX tablespace, and can chew up a lot of storage. That's why it isn't run by default, and it's why we should use it judiciously.

As for backwards compatibility: it is a new feature in 11g, and it is a compiler feature at that. So I doubt whether it is something you could just crowbar into a 10g install.

---

Won't work on 10g, but you can copy the code to a scratch environment for analysis

You could get an OTN edition of 11g and copy the code in there just for the analysis. If you think it would get classed as production use, you can get Personal Edition on Windows for a few hundred dollars