{"id":7547,"date":"2017-03-17T21:14:33","date_gmt":"2017-03-17T20:14:33","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=7547"},"modified":"2023-11-07T23:19:30","modified_gmt":"2023-11-07T22:19:30","slug":"plscope-utils-utilities-for-plscope-in-oracle-database-12-2","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/","title":{"rendered":"plscope-utils &#8211; Utilities for PL\/Scope"},"content":{"rendered":"\n<p>PL\/Scope was introduced with Oracle Database version 11.1 and covered PL\/SQL only. SQL statements such as SELECT, INSERT, UPDATE, DELETE and MERGE were simply ignored. Analysing PL\/SQL source code without covering SQL does not provide a lot of value. Hence, PL\/Scope was neglected by the Oracle community. But this\u00a0seems to change with version 12.2. PL\/Scope covers SQL statements, finally. This makes fine-grained dependency analysis possible. Fine-grained means on column level and package unit level (procedure\/function).<\/p>\n\n\n\n<p>PL\/Scope is something like&nbsp;a software development kit (SDK) for source code analysis. It consists basically of the following two&nbsp;components:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The compiler, which collects information when compiling source code (e.g.&nbsp;<a href=\"http:\/\/docs.oracle.com\/database\/122\/REFRN\/PLSCOPE_SETTINGS.htm#REFRN10271\">plscope_settings<\/a>=&#8217;identifiers:all&#8217;)<\/li>\n\n\n\n<li>The dictionary views, providing information about collected identifiers (<a href=\"http:\/\/docs.oracle.com\/database\/122\/REFRN\/ALL_IDENTIFIERS.htm#REFRN20599\">dba_identifiers<\/a>) and SQL statements (<a href=\"http:\/\/docs.oracle.com\/database\/122\/REFRN\/ALL_STATEMENTS.htm#REFRN-GUID-36FF82A6-DD4B-45E4-8B46-2ECD38C2AE47\">dba_statements<\/a>).<\/li>\n<\/ul>\n\n\n\n<p>The provided views are based on a recursive data structure which is not that easy to understand. Querying them will soon need recursive self-joins and joins to other Oracle data dictionary views. Everybody is going to build some tools (scripts, reports, views, etc.) for their analysis. Wouldn&#8217;t it make sense to have some open-source library\u00a0doing that once and for all? &#8211; Obviously, the answer is yes. This library exists. It is available as a <a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\">GitHub<\/a>\u00a0repository named <a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\">plscope-utils<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Compile<\/h2>\n\n\n\n<p>First, you have to compile the source code you&#8217;d like to analyse. All source code, in all relevant schemas. If you do not compile them, they cannot be referenced and therefore\u00a0they will be simply missing in your analysis. This step is completely independent of whether you are going to use <a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\">plscope-utils<\/a> or not.<\/p>\n\n\n\n<p>Here&#8217;s a simplistic ETL example using emp and dept as source data.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Enable PL\/Scope in the current session<\/span><span role=\"button\" tabindex=\"0\" data-code=\"ALTER SESSION SET plscope_settings='identifiers:all, statements:all';\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">ALTER<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SESSION<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SET<\/span><span style=\"color: #D4D4D4\"> plscope_settings=<\/span><span style=\"color: #CE9178\">&#39;identifiers:all, statements:all&#39;<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Create Procedure (compile with PL\/Scope)<\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE OR REPLACE PROCEDURE load_from_tab IS\nBEGIN\n   INSERT INTO deptsal (dept_no, dept_name, salary)\n   SELECT \/*+ordered *\/\n          d.deptno, d.dname, SUM(e.sal + NVL(e.comm, 0)) AS sal\n     FROM dept d\n     LEFT JOIN (SELECT * \n                  FROM emp \n                 WHERE hiredate &gt; DATE '1980-01-01') e\n       ON e.deptno = d.deptno\n    GROUP BY d.deptno, d.dname;\n   COMMIT;\nEND load_from_tab;\n\/\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">CREATE OR REPLACE<\/span><span style=\"color: #D4D4D4\"> PROCEDURE load_from_tab <\/span><span style=\"color: #569CD6\">IS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">INSERT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">INTO<\/span><span style=\"color: #D4D4D4\"> deptsal (dept_no, dept_name, salary)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #6A9955\">\/*+ordered *\/<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">          d.deptno, d.dname, <\/span><span style=\"color: #DCDCAA\">SUM<\/span><span style=\"color: #D4D4D4\">(e.sal + <\/span><span style=\"color: #DCDCAA\">NVL<\/span><span style=\"color: #D4D4D4\">(e.comm, <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)) <\/span><span style=\"color: #569CD6\">AS<\/span><span style=\"color: #D4D4D4\"> sal<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> dept d<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">     <\/span><span style=\"color: #569CD6\">LEFT JOIN<\/span><span style=\"color: #D4D4D4\"> (<\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> emp <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                 <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> hiredate &gt; <\/span><span style=\"color: #569CD6\">DATE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #CE9178\">&#39;1980-01-01&#39;<\/span><span style=\"color: #D4D4D4\">) e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       <\/span><span style=\"color: #569CD6\">ON<\/span><span style=\"color: #D4D4D4\"> e.deptno = d.deptno<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">GROUP BY<\/span><span style=\"color: #D4D4D4\"> d.deptno, d.dname;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #DCDCAA\">COMMIT<\/span><span style=\"color: #D4D4D4\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #569CD6\">END<\/span><span style=\"color: #D4D4D4\"> load_from_tab;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Views by plscope-utils<\/h2>\n\n\n\n<p>After installing plscope-utils, you have access to the following 5 views from any user within the database instance:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/database\/utils\/view\/plscope_identifiers.sql\">plscope_identifiers<\/a> &#8211; for PL\/Scope identifiers<\/li>\n\n\n\n<li><a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/database\/utils\/view\/plscope_statements.sql\">plscope_statements<\/a> &#8211; for SQL statements<\/li>\n\n\n\n<li><a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/database\/utils\/view\/plscope_tab_usage.sql\">plscope_tab_usage<\/a> &#8211; for table usage<\/li>\n\n\n\n<li><a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/database\/utils\/view\/plscope_col_usage.sql\">plscope_col_usage<\/a> &#8211; for column usage<\/li>\n\n\n\n<li><a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/database\/utils\/view\/plscope_ins_lineage.sql\">plscope_ins_lineage<\/a> &#8211; for where-lineage of insert statements<\/li>\n<\/ul>\n\n\n\n<p>For each of these views, you find an example query and result in the <a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\/blob\/main\/README.md\">readme<\/a> of plscope-utils.<\/p>\n\n\n\n<p>Nonetheless, I&#8217;m going to show some query results in this post as well.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">PLSCOPE_IDENTIFIERS<\/h2>\n\n\n\n<p>The view plscope_identifiers combines dba_identifiers and dba_statements and provides all columns from dba_identifiers plus&nbsp;procedure_name, name_path, path_len, ref_owner, ref_object_type and ref_object_name.<\/p>\n\n\n\n<p>Here&#8217;s an example query for the procedure defined above.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query PL\/SQL and SQL identifiers<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT procedure_name, line, col, name, name_path, path_len, type, usage, \n       ref_owner, ref_object_type, ref_object_name\n  FROM plscope_identifiers\n WHERE object_name = 'LOAD_FROM_TAB'\n   AND owner = USER\n ORDER BY line, col;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">procedure_name<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col, <\/span><span style=\"color: #569CD6\">name<\/span><span style=\"color: #D4D4D4\">, name_path, path_len, <\/span><span style=\"color: #569CD6\">type<\/span><span style=\"color: #D4D4D4\">, usage, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       ref_owner, ref_object_type, ref_object_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> plscope_identifiers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> object_name = <\/span><span style=\"color: #CE9178\">&#39;LOAD_FROM_TAB&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\"> = USER<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1269\" height=\"410\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result.png\" alt=\"\" class=\"wp-image-7565\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result.png 1269w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-300x97.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-768x248.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-1024x331.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-260x84.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-50x16.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_identifiers_result-150x48.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1269px) 100vw, 1269px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">PLSCOPE_STATEMENTS<\/h2>\n\n\n\n<p>This view is very similar to dba_statements. It just adds an is_duplicate column to easily identify duplicate SQL statements.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query Duplicate SQL Statements<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT owner, object_type, object_name, line, col, type, sql_id, is_duplicate, full_text\n  FROM plscope_statements S\n WHERE owner = USER \n   AND is_duplicate = 'YES'\n ORDER BY sql_id, owner, object_type, object_name, line, col;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\">, object_type, object_name, <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col, <\/span><span style=\"color: #569CD6\">type<\/span><span style=\"color: #D4D4D4\">, sql_id, is_duplicate, full_text<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> plscope_statements S<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\"> = USER <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> is_duplicate = <\/span><span style=\"color: #CE9178\">&#39;YES&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> sql_id, <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\">, object_type, object_name, <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1151\" height=\"112\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result.png\" alt=\"\" class=\"wp-image-7569\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result.png 1151w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-300x29.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-768x75.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-1024x100.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-260x25.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-50x5.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_statements_result-150x15.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1151px) 100vw, 1151px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">PLSCOPE_TAB_USAGE<\/h2>\n\n\n\n<p>This view reports synonym, view and table usages. The column direct_dependency is used to indicate whether the usage has been resolved by a synonym or view.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Query Synonym\/View\/Table Usages<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT * \n  FROM plscope_tab_usage\n WHERE object_name = 'LOAD_FROM_TAB'\n   AND owner = USER\n ORDER BY owner, object_type, object_name, line, col, direct_dependency;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> plscope_tab_usage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> object_name = <\/span><span style=\"color: #CE9178\">&#39;LOAD_FROM_TAB&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\"> = USER<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\">, object_type, object_name, <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col, direct_dependency;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1040\" height=\"93\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results.png\" alt=\"\" class=\"wp-image-7570\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results.png 1040w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-300x27.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-768x69.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-1024x92.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-260x23.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-50x4.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_tab_usage_results-150x13.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1040px) 100vw, 1040px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">PLSCOPE_COL_USAGE<\/h2>\n\n\n\n<p>This view reports view\/table column usages.&nbsp;Column-less accesses (e.g. insert statements without column-list or *) and accesses on synonyms and views are resolved. The value &#8216;NO&#8217; in the column direct_dependency indicates such an access.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Column usage<\/span><span role=\"button\" tabindex=\"0\" data-code=\" SELECT * \n  FROM plscope_col_usage\n WHERE object_name = 'LOAD_FROM_TAB'\n   AND owner = USER\n ORDER BY owner, object_type, object_name, line, col, direct_dependency;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> * <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> plscope_col_usage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> object_name = <\/span><span style=\"color: #CE9178\">&#39;LOAD_FROM_TAB&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">   <\/span><span style=\"color: #569CD6\">AND<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\"> = USER<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">owner<\/span><span style=\"color: #D4D4D4\">, object_type, object_name, <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col, direct_dependency;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1141\" height=\"269\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result.png\" alt=\"\" class=\"wp-image-7571\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result.png 1141w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-300x71.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-768x181.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-1024x241.png 1024w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-260x61.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-50x12.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_col_usage_result-150x35.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:1141px) 100vw, 1141px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">PLSCOPE_INS_LINEAGE<\/h2>\n\n\n\n<p>This view reports the where-lineage of &#8220;INSERT &#8230; SELECT&#8221; statements. I&#8217;ll have a talk named &#8220;SQL Lineage Made Easy with PL\/Scope 12.2?&#8221; at the Trivadis TechEvent tomorrow\u00a0and at <a href=\"https:\/\/www.doag.org\/konferenz\/konferenzplaner\/konferenzplaner_details.php?locS=1&amp;id=527880&amp;vid=534482\">APEX Connect 17<\/a>\u00a0on May 11 2017, where I&#8217;ll cover this topic in more detail. I&#8217;ll write a dedicated blog post about this topic soon.<\/p>\n\n\n\n<p>Here&#8217;s a query which shows that the target column salary is based on the source columns sal and comm in the table emp.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">Where-lineage of Insert Statements<\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT line, col, \n       from_owner, from_object_type, from_object_name, from_column_name,\n       to_owner, to_object_type, to_object_name, to_column_name\n  FROM plscope_ins_lineage\n WHERE object_name = 'LOAD_FROM_TAB'\n ORDER BY to_column_name;\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #569CD6\">SELECT<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">line<\/span><span style=\"color: #D4D4D4\">, col, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       from_owner, from_object_type, from_object_name, from_column_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">       to_owner, to_object_type, to_object_name, to_column_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">  <\/span><span style=\"color: #569CD6\">FROM<\/span><span style=\"color: #D4D4D4\"> plscope_ins_lineage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">WHERE<\/span><span style=\"color: #D4D4D4\"> object_name = <\/span><span style=\"color: #CE9178\">&#39;LOAD_FROM_TAB&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #569CD6\">ORDER BY<\/span><span style=\"color: #D4D4D4\"> to_column_name;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result.png\"><img loading=\"lazy\" decoding=\"async\" width=\"930\" height=\"113\" src=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result.png\" alt=\"\" class=\"wp-image-7572\" srcset=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result.png 930w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result-300x36.png 300w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result-768x93.png 768w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result-260x32.png 260w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result-50x6.png 50w, https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/03\/plscope_ins_lineage_result-150x18.png 150w\" sizes=\"auto, (max-width:767px) 480px, (max-width:930px) 100vw, 930px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<p>Dependency analysis within the Oracle Database 12.2 has become much easier, thanks to PL\/Scope. But using the views provided by <a href=\"https:\/\/github.com\/PhilippSalvisberg\/plscope-utils\">plscope-utils<\/a> makes it almost a trivial thing.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PL\/Scope was introduced with Oracle Database version 11.1 and covered PL\/SQL only. SQL statements such as SELECT, INSERT, UPDATE, DELETE and MERGE were simply ignored. Analysing PL\/SQL source code without covering SQL does not provide a lot of value. Hence, PL\/Scope was neglected by the Oracle community. But this\u00a0seems to change with<span class=\"excerpt-hellip\"> [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":7685,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[86,97,13,85],"class_list":["post-7547","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-code-analysis","tag-plscope","tag-plsql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>plscope-utils - Utilities for PL\/Scope - Philipp Salvisberg&#039;s Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"plscope-utils - Utilities for PL\/Scope - Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"og:description\" content=\"PL\/Scope was introduced with Oracle Database version 11.1 and covered PL\/SQL only. SQL statements such as SELECT, INSERT, UPDATE, DELETE and MERGE were simply ignored. Analysing PL\/SQL source code without covering SQL does not provide a lot of value. Hence, PL\/Scope was neglected by the Oracle community. But this\u00a0seems to change with [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg&#039;s Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-17T20:14:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-07T22:19:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1034\" \/>\n\t<meta property=\"og:image:height\" content=\"778\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"plscope-utils &#8211; Utilities for PL\\\/Scope\",\"datePublished\":\"2017-03-17T20:14:33+00:00\",\"dateModified\":\"2023-11-07T22:19:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/\"},\"wordCount\":658,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/plscope-utils-1-identifiers.png\",\"keywords\":[\"Code Analysis\",\"PL\\\/Scope\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/\",\"name\":\"plscope-utils - Utilities for PL\\\/Scope - Philipp Salvisberg&#039;s Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/plscope-utils-1-identifiers.png\",\"datePublished\":\"2017-03-17T20:14:33+00:00\",\"dateModified\":\"2023-11-07T22:19:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/plscope-utils-1-identifiers.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2017\\\/07\\\/plscope-utils-1-identifiers.png\",\"width\":1034,\"height\":778},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2017\\\/03\\\/17\\\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"plscope-utils &#8211; Utilities for PL\\\/Scope\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg&#039;s Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"plscope-utils - Utilities for PL\/Scope - Philipp Salvisberg&#039;s Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/","og_locale":"en_US","og_type":"article","og_title":"plscope-utils - Utilities for PL\/Scope - Philipp Salvisberg&#039;s Blog","og_description":"PL\/Scope was introduced with Oracle Database version 11.1 and covered PL\/SQL only. SQL statements such as SELECT, INSERT, UPDATE, DELETE and MERGE were simply ignored. Analysing PL\/SQL source code without covering SQL does not provide a lot of value. Hence, PL\/Scope was neglected by the Oracle community. But this\u00a0seems to change with [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/","og_site_name":"Philipp Salvisberg&#039;s Blog","article_published_time":"2017-03-17T20:14:33+00:00","article_modified_time":"2023-11-07T22:19:30+00:00","og_image":[{"width":1034,"height":778,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"plscope-utils &#8211; Utilities for PL\/Scope","datePublished":"2017-03-17T20:14:33+00:00","dateModified":"2023-11-07T22:19:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/"},"wordCount":658,"commentCount":3,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png","keywords":["Code Analysis","PL\/Scope","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/","url":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/","name":"plscope-utils - Utilities for PL\/Scope - Philipp Salvisberg&#039;s Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png","datePublished":"2017-03-17T20:14:33+00:00","dateModified":"2023-11-07T22:19:30+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2017\/07\/plscope-utils-1-identifiers.png","width":1034,"height":778},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2017\/03\/17\/plscope-utils-utilities-for-plscope-in-oracle-database-12-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"plscope-utils &#8211; Utilities for PL\/Scope"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg&#039;s Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/7547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=7547"}],"version-history":[{"count":30,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/7547\/revisions"}],"predecessor-version":[{"id":12606,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/7547\/revisions\/12606"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/7685"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=7547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=7547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=7547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}