{"id":7852,"date":"2017-10-14T17:23:42","date_gmt":"2017-10-14T15:23:42","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=7852"},"modified":"2023-11-07T23:58:04","modified_gmt":"2023-11-07T22:58:04","slug":"limitations-of-plscope-and-how-to-deal-with-them","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2017\/10\/14\/limitations-of-plscope-and-how-to-deal-with-them\/","title":{"rendered":"Limitations of PL\/Scope and How to Deal with Them"},"content":{"rendered":"\n

My first car was a Renault R5 TX. The motor cooling of this car was really bad. On a hot summer day, it was simply not possible to drive slowly in high traffic without overheating the engine. To cool the engine you could either stop the car, open the front lid and hope for the best or turn on the heating. I decided on the latter. It was impressive how well the heating system worked on hot days. Not very pleasant\u00a0to drive uphill behind a slow Dutch caravan in the Alps, but a funny experience in retrospect. My R5 was a reliable companion for years and I loved it.<\/p>\n\n\n\n

When you are aware of the limitations of PL\/Scope and know how to deal with them, you will find PL\/Scope a very useful tool. This post is supposed to enable you to use PL\/Scope more effectively. I’m fond of PL\/Scope, because it may provide very reliable insights of your static PL\/SQL code. I hope you are going to use PL\/Scope, even if it is required sometimes “to turn on the heating”.<\/p>\n\n\n\n

PL\/Scope gathers at compile-time metadata about your PL\/SQL code and stores it in dedicated tables. These metadata are accessible for analysis via the views dba\/all\/user_identfiers (since 11.1) and in dba\/all\/user_statements (since 12.2). If you are not familiar with PL\/Scope I recommend reading Steven Feuerstein’s article\u00a0Powerful Impact Analysis\u00a0or having a look a the introduction chapters of the documentation<\/a>. If you are fluent in German, I can recommend also Sabine Heimsath’s article\u00a0Sch\u00f6ner Coden \u2013 PL\/SQL analysieren mit PL\/Scope<\/a>.<\/p>\n\n\n\n

The limitations covered in this post are based on Oracle Database version 12.2.0.1.170814. Most of the limitations are bugs. You may track the progress on MOS with the provided bug numbers.<\/p>\n\n\n\n

1. Missing Results After NULL Statement<\/h2>\n\n\n\n

In the following example, we analyse the procedure p2<\/code>. Look at the result of the user_identifiers<\/code> query on lines 27-29. All three calls of the procedure p1<\/code> have been detected. That’s good and correct.<\/p>\n\n\n\n

Example 1a – Complete Result<\/span><\/path><\/path><\/svg><\/span>
ALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:all, statements:all'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p1 (in_p <\/span>IN<\/span> <\/span>INTEGER<\/span>) <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   sys.<\/span>dbms_output.<\/span>put_line<\/span>(<\/span>sqrt<\/span>(in_p));<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p2 <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   p1(<\/span>4<\/span>);<\/span><\/span>\n   p1(<\/span>9<\/span>);<\/span><\/span>\n   p1(<\/span>16<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P2'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col;<\/span><\/span>\n<\/span>\nUSAGE       <\/span>TYPE<\/span>               <\/span>NAME<\/span>             LINE        COL<\/span><\/span>\n----------- ------------------ ---------- ---------- ----------<\/span><\/span>\nDEFINITION  PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span>\nDECLARATION PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>3<\/span>          <\/span>4<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>4<\/span>          <\/span>4<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>5<\/span>          <\/span>4<\/span>  <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
But when we add a NULL<\/code> statement before the first call of p1<\/code>, the calls after the NULL<\/code> the statement are not reported anymore.<\/p>\n\n\n\n
Example 1b – Missing Calls in Result<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> PROCEDURE p2 <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>NULL<\/span>;<\/span><\/span>\n   p1(<\/span>4<\/span>);<\/span><\/span>\n   p1(<\/span>9<\/span>);<\/span><\/span>\n   p1(<\/span>16<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P2'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col;<\/span><\/span>\n<\/span>\nUSAGE       <\/span>TYPE<\/span>               <\/span>NAME<\/span>             LINE        COL<\/span><\/span>\n----------- ------------------ ---------- ---------- ----------<\/span><\/span>\nDECLARATION PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span>\nDEFINITION  PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
I am not aware of a simple workaround. This means you have to change the code. In this case, it is easy, just remove the NULL<\/code> statement.<\/p>\n\n\n\n
The good news is, that it affects just the basic block of the NULL<\/code> statement. Other basic blocks are not affected. Here’s an example of a complete result, even if a NULL<\/code> statement is used. The term “basic block” has been introduced with PL\/SQL Basic Block Coverage<\/a> in version 12.2. However, the definition is valid for all versions of PL\/SQL. I like Chris Saxon’s definition: “It’s a piece of code that either runs completely or not at all”.<\/p>\n\n\n\n
Example 1c – Complete Result With NULL Statement<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> PROCEDURE p2 <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>IF<\/span> <\/span>FALSE<\/span> <\/span>THEN<\/span><\/span>\n      <\/span>NULL<\/span>;<\/span><\/span>\n   <\/span>END IF<\/span>;<\/span><\/span>\n   p1(<\/span>4<\/span>);<\/span><\/span>\n   p1(<\/span>9<\/span>);<\/span><\/span>\n   p1(<\/span>16<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P2'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE       <\/span>TYPE<\/span>               <\/span>NAME<\/span>             LINE        COL<\/span><\/span>\n----------- ------------------ ---------- ---------- ----------<\/span><\/span>\nDEFINITION  PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span>\nDECLARATION PROCEDURE          P2                  <\/span>1<\/span>         <\/span>11<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>6<\/span>          <\/span>4<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>7<\/span>          <\/span>4<\/span><\/span>\nCALL        PROCEDURE          P1                  <\/span>8<\/span>          <\/span>4<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
In cases where PL\/SQL requires at least one statement and the NULL<\/code> statement is the only one, you should not have a problem. Just unnecessary usages of  NULL<\/code> statements might cause problems. So, make sure that you do not use unnecessary NULL<\/code> statements. They are noise and may lead to incomplete code analysis results.<\/p>\n\n\n\n
2. Broken Usage Hierarchy<\/h2>\n\n\n\n
Let’s look at the usage hierarchy of the procedure p2<\/code> in example 1c. The hierarchy level is represented in the result column usage<\/code>. Three leading spaces for each sub-level. The usage_id<\/code> identifies a row for an object, p2<\/code> in this case. The usage_id<\/code> starts with 1 and ends with 5. There are no gaps. The column usage_context_id is part of the foreign key to the parent usage_id<\/code>. Oracle decided to start the hierarchy with the non-existing usage_id<\/code> 0<\/code> (zero). That’s what we use in the start_with clause. The recursive query produces the same number of result rows as the non-recursive query in example 1c. That’s important, and that’s how it should be. Always.<\/p>\n\n\n\n
Example 2a – Intact Usage Hiearchy<\/span><\/path><\/path><\/svg><\/span>
WITH<\/span> ids <\/span>AS<\/span> (<\/span><\/span>\n   <\/span>SELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id<\/span><\/span>\n     <\/span>FROM<\/span> user_identifiers ids<\/span><\/span>\n    <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span> <\/span><\/span>\n      <\/span>AND<\/span> object_name = <\/span>'P2'<\/span><\/span>\n)<\/span><\/span>\n <\/span>SELECT<\/span> <\/span>lpad<\/span>(<\/span>' '<\/span>, <\/span>3<\/span> * (<\/span>level<\/span> - <\/span>1<\/span>)) || ids.usage <\/span>AS<\/span> usage,<\/span><\/span>\n        ids.<\/span>type<\/span>,<\/span><\/span>\n        ids.<\/span>name<\/span>,<\/span><\/span>\n        ids.line,<\/span><\/span>\n        ids.col,<\/span><\/span>\n        ids.usage_id,<\/span><\/span>\n        ids.usage_context_id<\/span><\/span>\n   <\/span>FROM<\/span> ids<\/span><\/span>\n  <\/span>START WITH<\/span> ids.usage_context_id = <\/span>0<\/span><\/span>\nCONNECT BY PRIOR<\/span> ids.usage_id = ids.usage_context_id<\/span><\/span>\n  <\/span>ORDER BY<\/span> ids.line, ids.col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE          <\/span>TYPE<\/span>       <\/span>NAME<\/span>  LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n-------------- ---------- ----- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION    PROCEDURE  P2       <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\n   DEFINITION  PROCEDURE  P2       <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\n      CALL     PROCEDURE  P1       <\/span>6<\/span>    <\/span>4<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\n      CALL     PROCEDURE  P1       <\/span>7<\/span>    <\/span>4<\/span>        <\/span>4<\/span>                <\/span>2<\/span><\/span>\n      CALL     PROCEDURE  P1       <\/span>8<\/span>    <\/span>4<\/span>        <\/span>5<\/span>                <\/span>2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
There are two reasons for broken usage hierarchies.<\/p>\n\n\n\n
    \n
  1. Static SQL statements (expected behaviour)<\/a><\/li>\n\n\n\n
  2. References to uncompiled synonyms (bug)<\/a><\/li>\n<\/ol>\n\n\n\n
    <\/a>2.1. Static SQL Statements<\/h3>\n\n\n\n
    Since version 12.2 PL\/Scope covers static SQL statements in the user_statements<\/code> view. Static SQL statements are missing in the user_identifiers<\/code> view, probably for compatibility reasons. To get the full usage hierarchy you have to combine the views using UNION ALL<\/code> like in line 49 of the next example 2.1a. Please note that the usages on line 25-29 are referring to the parent on line 41. A recursive query on user_identifiers<\/code> only, would return just the lines 69-70 – a quite incomplete result set. Therefore you should think twice before applying your “old” PL\/Scope version 11.1 scripts against an Oracle 12.2 database.<\/p>\n\n\n\n
    Example 2.1a – Combine Identifiers and Statements<\/span><\/path><\/path><\/svg><\/span>
    ALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:all, statements:all'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> FUNCTION f1 (in_val <\/span>NUMBER<\/span>) <\/span>RETURN<\/span> <\/span>NUMBER<\/span> <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>RETURN<\/span> <\/span>SQRT<\/span>(in_val);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p3 <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>UPDATE<\/span> emp <\/span>SET<\/span> sal = sal + f1(comm);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P3'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE          <\/span>TYPE<\/span>       <\/span>NAME<\/span>  LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n-------------- ---------- ----- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION    PROCEDURE  P3       <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\nDEFINITION     PROCEDURE  P3       <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\nREFERENCE      <\/span>TABLE<\/span>      EMP      <\/span>3<\/span>   <\/span>11<\/span>        <\/span>4<\/span>                <\/span>3<\/span><\/span>\nREFERENCE      <\/span>COLUMN<\/span>     SAL      <\/span>3<\/span>   <\/span>19<\/span>        <\/span>6<\/span>                <\/span>3<\/span><\/span>\nREFERENCE      <\/span>COLUMN<\/span>     SAL      <\/span>3<\/span>   <\/span>25<\/span>        <\/span>5<\/span>                <\/span>3<\/span><\/span>\nCALL           FUNCTION   F1       <\/span>3<\/span>   <\/span>31<\/span>        <\/span>7<\/span>                <\/span>3<\/span><\/span>\nREFERENCE      <\/span>COLUMN<\/span>     COMM     <\/span>3<\/span>   <\/span>34<\/span>        <\/span>8<\/span>                <\/span>7<\/span><\/span>\n<\/span>\n7<\/span> rows selected. <\/span><\/span>\n <\/span><\/span>\nSELECT<\/span> text, <\/span>type<\/span>, sql_id, line, col, usage_id, usage_context_id <\/span><\/span>\n  <\/span>FROM<\/span> user_statements<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P3'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col;<\/span><\/span>\n<\/span>\nTEXT                                <\/span>TYPE<\/span>   SQL_ID        LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n----------------------------------- ------ ------------- ---- ---- -------- ----------------<\/span><\/span>\nUPDATE<\/span> EMP <\/span>SET<\/span> SAL = SAL + F1(COMM) <\/span>UPDATE<\/span> 8kyysdc8m75ag    <\/span>3<\/span>    <\/span>4<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\n <\/span><\/span>\nWITH<\/span> <\/span><\/span>\n   ids <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id <\/span><\/span>\n        <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n       <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span> <\/span><\/span>\n         <\/span>AND<\/span> object_name = <\/span>'P3'<\/span><\/span>\n    <\/span>UNION<\/span> <\/span>ALL<\/span><\/span>\n      <\/span>SELECT<\/span> <\/span>'EXECUTE'<\/span> <\/span>AS<\/span> usage, <\/span>type<\/span>, sql_id <\/span>AS<\/span> <\/span>name<\/span>, line, col, usage_id, usage_context_id <\/span><\/span>\n       <\/span>FROM<\/span> user_statements<\/span><\/span>\n       <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span> <\/span><\/span>\n         <\/span>AND<\/span> object_name = <\/span>'P3'<\/span><\/span>\n   )<\/span><\/span>\n <\/span>SELECT<\/span> <\/span>lpad<\/span>(<\/span>' '<\/span>, <\/span>3<\/span> * (<\/span>level<\/span> - <\/span>1<\/span>)) || usage <\/span>AS<\/span> usage,<\/span><\/span>\n        <\/span>type<\/span>,<\/span><\/span>\n        <\/span>name<\/span>,<\/span><\/span>\n        line,<\/span><\/span>\n        col,<\/span><\/span>\n        usage_id,<\/span><\/span>\n        usage_context_id<\/span><\/span>\n   <\/span>FROM<\/span> ids<\/span><\/span>\n  <\/span>START WITH<\/span> usage_context_id = <\/span>0<\/span><\/span>\nCONNECT BY PRIOR<\/span> usage_id = usage_context_id<\/span><\/span>\n  <\/span>ORDER BY<\/span> line, col, usage_id; <\/span><\/span>\n<\/span>\nUSAGE                 <\/span>TYPE<\/span>       <\/span>NAME<\/span>           LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n--------------------- ---------- -------------- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION           PROCEDURE  P3                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\n   DEFINITION         PROCEDURE  P3                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\n      EXECUTE         <\/span>UPDATE<\/span>     8kyysdc8m75ag     <\/span>3<\/span>    <\/span>4<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\n         REFERENCE    <\/span>TABLE<\/span>      EMP               <\/span>3<\/span>   <\/span>11<\/span>        <\/span>4<\/span>                <\/span>3<\/span><\/span>\n         REFERENCE    <\/span>COLUMN<\/span>     SAL               <\/span>3<\/span>   <\/span>19<\/span>        <\/span>6<\/span>                <\/span>3<\/span><\/span>\n         REFERENCE    <\/span>COLUMN<\/span>     SAL               <\/span>3<\/span>   <\/span>25<\/span>        <\/span>5<\/span>                <\/span>3<\/span><\/span>\n         CALL         FUNCTION   F1                <\/span>3<\/span>   <\/span>31<\/span>        <\/span>7<\/span>                <\/span>3<\/span><\/span>\n            REFERENCE <\/span>COLUMN<\/span>     COMM              <\/span>3<\/span>   <\/span>34<\/span>        <\/span>8<\/span>                <\/span>7<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    <\/a>2.2 References to Uncompiled Synonyms<\/h3>\n\n\n\n
    In example 2.2a the procedure p4<\/code> calls procedure p1<\/code>, but p1<\/code> is not compiled with PL\/Scope. The usage hierarchy is intact. Please note that the result set contains also all usages of the parameter in_p<\/code>.<\/p>\n\n\n\n
    Example 2.2a – Intact Usage Hierarchy<\/span><\/path><\/path><\/svg><\/span>
    ALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:none, statements:none'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p1 (in_p <\/span>IN<\/span> <\/span>INTEGER<\/span>) <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   sys.<\/span>dbms_output.<\/span>put_line<\/span>(<\/span>sqrt<\/span>(in_p));<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:all, statements:all'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p4 (in_p <\/span>IN<\/span> <\/span>INTEGER<\/span>) <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   p1(in_p => in_p);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P4'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE                 <\/span>TYPE<\/span>       <\/span>NAME<\/span>           LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n--------------------- ---------- -------------- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION           PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\nDEFINITION            PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\nDECLARATION           FORMAL <\/span>IN<\/span>  IN_P              <\/span>1<\/span>   <\/span>15<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\nREFERENCE             <\/span>SUBTYPE<\/span>    <\/span>INTEGER<\/span>           <\/span>1<\/span>   <\/span>23<\/span>        <\/span>4<\/span>                <\/span>3<\/span><\/span>\nREFERENCE             FORMAL <\/span>IN<\/span>  IN_P              <\/span>3<\/span>   <\/span>15<\/span>        <\/span>5<\/span>                <\/span>2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    But the usage hierarchy becomes broken when we call procedure p1<\/code>via an uncompiled synonym as in example 2.2b. The highlighted result row references a non-existing usage_id. In a recursive query, this result row will get lost.<\/p>\n\n\n\n
    Example 2.2b – Broken Usage Hierarchy by Uncompiled Synonym<\/span><\/path><\/path><\/svg><\/span>
    ALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:none, statements:none'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> SYNONYM s1 <\/span>FOR<\/span> p1;<\/span><\/span>\n<\/span>\nALTER SESSION<\/span> <\/span>SET<\/span> plscope_settings=<\/span>'identifiers:all, statements:all'<\/span>;<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> PROCEDURE p4 (in_p <\/span>IN<\/span> <\/span>INTEGER<\/span>) <\/span>IS<\/span><\/span>\nBEGIN<\/span><\/span>\n   s1(in_p => in_p);<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id <\/span><\/span>\n  <\/span>FROM<\/span> user_identifiers<\/span><\/span>\n <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span><\/span>\n   <\/span>AND<\/span> object_name = <\/span>'P4'<\/span><\/span>\n <\/span>ORDER BY<\/span> line, col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE                 <\/span>TYPE<\/span>       <\/span>NAME<\/span>           LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n--------------------- ---------- -------------- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION           PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\nDEFINITION            PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\nDECLARATION           FORMAL <\/span>IN<\/span>  IN_P              <\/span>1<\/span>   <\/span>15<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\nREFERENCE             <\/span>SUBTYPE<\/span>    <\/span>INTEGER<\/span>           <\/span>1<\/span>   <\/span>23<\/span>        <\/span>4<\/span>                <\/span>3<\/span><\/span>\nREFERENCE             FORMAL <\/span>IN<\/span>  IN_P              <\/span>3<\/span>   <\/span>15<\/span>        <\/span>6<\/span>                <\/span>5<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Example 2.2c – Fix Broken Usage Hierarchies<\/span><\/path><\/path><\/svg><\/span>
    WITH<\/span> <\/span><\/span>\n   filtered_ids <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id, usage_context_id<\/span><\/span>\n        <\/span>FROM<\/span> user_identifiers ids<\/span><\/span>\n       <\/span>WHERE<\/span> object_type = <\/span>'PROCEDURE'<\/span> <\/span><\/span>\n         <\/span>AND<\/span> object_name = <\/span>'P4'<\/span><\/span>\n   ),<\/span><\/span>\n   sanitized_ids <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> fids.usage, fids.<\/span>type<\/span>, fids.<\/span>name<\/span>, fids.line, fids.col, <\/span><\/span>\n             fids.usage_id, fids.usage_context_id,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> fk.usage_id <\/span>IS NOT NULL<\/span> <\/span>OR<\/span> fids.usage_context_id = <\/span>0<\/span> <\/span>THEN<\/span><\/span>\n                   <\/span>'YES'<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   <\/span>'NO'<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> sane_fk<\/span><\/span>\n        <\/span>FROM<\/span> filtered_ids fids<\/span><\/span>\n        <\/span>LEFT JOIN<\/span> filtered_ids fk<\/span><\/span>\n          <\/span>ON<\/span> fk.usage_id = fids.usage_context_id<\/span><\/span>\n   ),<\/span><\/span>\n   ids <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> usage, <\/span>type<\/span>, <\/span>name<\/span>, line, col, usage_id,<\/span><\/span>\n             <\/span>CASE<\/span><\/span>\n                <\/span>WHEN<\/span> sane_fk = <\/span>'YES'<\/span> <\/span>THEN<\/span><\/span>\n                   usage_context_id<\/span><\/span>\n                <\/span>ELSE<\/span><\/span>\n                   last_value(<\/span>CASE<\/span> <\/span>WHEN<\/span> sane_fk = <\/span>'YES'<\/span> <\/span>THEN<\/span> usage_id <\/span>END<\/span>) <\/span><\/span>\n                      IGNORE NULLS <\/span>OVER<\/span> (<\/span><\/span>\n                         <\/span>ORDER BY<\/span> line, col<\/span><\/span>\n                         <\/span>ROWS BETWEEN<\/span> UNBOUNDED PRECEDING <\/span>AND<\/span> <\/span>1<\/span> PRECEDING<\/span><\/span>\n                      )<\/span><\/span>\n             <\/span>END<\/span> <\/span>AS<\/span> usage_context_id <\/span>-- fix broken hierarchies<\/span><\/span>\n        <\/span>FROM<\/span> sanitized_ids<\/span><\/span>\n   )<\/span><\/span>\n <\/span>SELECT<\/span> <\/span>lpad<\/span>(<\/span>' '<\/span>, <\/span>3<\/span> * (<\/span>level<\/span> - <\/span>1<\/span>)) || usage <\/span>AS<\/span> usage,<\/span><\/span>\n        <\/span>type<\/span>,<\/span><\/span>\n        <\/span>name<\/span>,<\/span><\/span>\n        line,<\/span><\/span>\n        col,<\/span><\/span>\n        usage_id,<\/span><\/span>\n        usage_context_id<\/span><\/span>\n   <\/span>FROM<\/span> ids<\/span><\/span>\n  <\/span>START WITH<\/span> usage_context_id = <\/span>0<\/span><\/span>\nCONNECT BY PRIOR<\/span> usage_id = usage_context_id<\/span><\/span>\n  <\/span>ORDER BY<\/span> line, col, usage_id;<\/span><\/span>\n<\/span>\nUSAGE                 <\/span>TYPE<\/span>       <\/span>NAME<\/span>           LINE  COL USAGE_ID USAGE_CONTEXT_ID<\/span><\/span>\n--------------------- ---------- -------------- ---- ---- -------- ----------------<\/span><\/span>\nDECLARATION           PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>1<\/span>                <\/span>0<\/span><\/span>\n   DEFINITION         PROCEDURE  P4                <\/span>1<\/span>   <\/span>11<\/span>        <\/span>2<\/span>                <\/span>1<\/span><\/span>\n      DECLARATION     FORMAL <\/span>IN<\/span>  IN_P              <\/span>1<\/span>   <\/span>15<\/span>        <\/span>3<\/span>                <\/span>2<\/span><\/span>\n         REFERENCE    <\/span>SUBTYPE<\/span>    <\/span>INTEGER<\/span>           <\/span>1<\/span>   <\/span>23<\/span>        <\/span>4<\/span>                <\/span>3<\/span><\/span>\n            REFERENCE FORMAL <\/span>IN<\/span>  IN_P              <\/span>3<\/span>   <\/span>15<\/span>        <\/span>6<\/span>                <\/span>4<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    3 Missing Usages of Objects in CDB$ROOT<\/h3>\n\n\n\n
    PL\/Scope stores by default the metadata for the following SYS<\/code> packages:<\/p>\n\n\n\n
    You can fix such a broken usage hierarchy on the fly. Here’s simplified version of  the query based on the plscope_identifiers<\/a> view of the plscope-utils project. The analytic function in line 27-31 fixes invalid foreign keys. The highest preceding usage_id<\/code> might not always be the best choice, but it is usually not that bad either. In this case the non-existing ‘5’ was replaced with a ‘4’ as you can see on line 53.<\/p>\n\n\n\n
    This is a bug. See bug 26363026<\/a> on MOS.<\/p>\n\n\n\n
This is a bug. See bug 24916492<\/a> on MOS.<\/p>\n\n\n\n