philippsalvisberg / plscope-utils Goto Github PK
View Code? Open in Web Editor NEWUtilities for PL/Scope in Oracle Database
License: Apache License 2.0
Utilities for PL/Scope in Oracle Database
License: Apache License 2.0
The following error message is shown when running install.sql
via SQL*Plus 21.0.0.0.0:
====================================================================
Views
====================================================================
View created.
No errors.
View created.
No errors.
View created.
No errors.
-- plscope_context.set_attr('GLOBAL_VARIABLE_REGEX', '^(g|m)_.*')
*
ERROR at line 9:
ORA-00903: invalid table name
SP2-0042: unknown command "src as (" - rest of line ignored.
),
*
ERROR at line 11:
ORA-00933: SQL command not properly ended
No errors.
====================================================================
Grants
====================================================================
Filtering is easy. E.g. where owner = USER
but in views such as PLSCOPE_IDENTIFIERS the response time is bad, since the optimizer cannot push the predicate to the appropriate place.
As a solution context attributes should be used to filter the user within the view. By default USER should be used. To change the user
BEGIN
plscope_context.set_attr('OWNER', 'SCOTT');
END;
/
As in the SQL Developer reports a LIKE predicate should be used to allow also querying all owners by setting the context attribute as follows:
BEGIN
plscope_context.set_attr('OWNER', '%');
END;
/
Every PLSCOPE view will contain a predicate such the following:
WHERE owner LIKE nvl(sys_context('PLSCOPE', 'OWNER'), USER)
Run the following to restore default behaviour of the views (filter by current user):
BEGIN
plscope_context.remove_attr('OWNER');
END;
/
Enhance the plscope_identifiers view to allow analysis as shown by @stevenfeuerstein's find-function-calls-in-sql.pls script. The following query will retrieve all SQL statements calling a function:
SELECT owner, object_type, object_name, parent_statement_type, line, col, text
FROM plscope_identifiers
WHERE parent_statement_type IN ('SELECT', 'INSERT', 'UPDATE', 'DETETE', 'MERGE')
AND type = 'FUNCTION'
AND usage = 'CALL'
AND owner = USER
ORDER BY owner, object_type, object_name, line, col;
The following result is expected when @stevenfeuerstein's my_procedure
is installed in schema plscope
:
OWNER OBJECT_TYPE OBJECT_NAME PARENT_ LINE COL TEXT
------- ------------ ------------ ------- ---- ---- -----------------------------------------------------
PLSCOPE PACKAGE BODY ETL INSERT 139 33 SELECT deptno, dname, etl.sal_of_dept(deptno)
PLSCOPE PROCEDURE MY_PROCEDURE SELECT 11 11 SELECT my_function1 ()
PLSCOPE PROCEDURE MY_PROCEDURE SELECT 15 15 AND my_function2 () = 0
PLSCOPE PROCEDURE MY_PROCEDURE SELECT 16 27 AND n = (SELECT my_function1 () FROM DUAL);
PLSCOPE PROCEDURE MY_PROCEDURE UPDATE 24 15 SET n = my_function2 ()
As of v1.0.0, the PL/SQL code of the "Compile with PL/Scope" action always uses ALL_xxx views.
If using the action with DBA privileges, I'd expect DBA_xxx views to be used, yet SQL Developer does not automatically changes references to ALL_xxx views into references to matching DBA_xxx views in the code of actions, as it does in the queries of editors.
In general, when the DBA role is enabled, ALL_xxx views should return the same results as matching DBA_xxx views 1. Yet exceptions to that principle—bugs?—do exist 2, and because of these it might be safer to use DBA_xxx views if possible. 3
Remark: this is more of a general design principle, for actions intended to be used by users with the DBA role, than an actual issue, as far as v1.0.0 is concerned.
On the other hand, the "Compile with PL/Scope" action should still work for users without SELECT / READ privileges on required DBA_xxx views, by falling back gracefully on matching ALL_xxx views. A simple solution for this would consist in adding a prompt for choosing between using DBA_xxx or ALL_xxx views, with the default choice adequately pre-defined by counting how many of the required DBA_xxx views are listed in ALL_VIEWS. In practice, the user would see the prompt (I'm not aware of an option for an invisible prompt), and could change it if she wishes to, but in practice that would not be necessary.
ALL_xxx views are documented using the phrase "describes xxx accessible to the current user", which means that xxx is listed if and only if the current user has enough permissions on it. Therefore, ALL_xxx views include security access checks in their WHERE-clause conditions, and in general system privileges (EXECUTE ANY..., CREATE ANY...) granted through the DBA role are taken into account in these checks. ↩
Per Oracle 12.2, even with the DBA role enabled, ALL_DEPENDENCIES may omit some rows that DBA_DEPENDENCIES returns. And in 19c, the catprc.sql file (under $ORACLE_HOME/rdbms/admin) mentions the following fix in its heading comments: "Bug 25096570: include Table and Synonym dependencies in ALL_DEPENDENCIES". ↩
Querying DBA_xxx views guarantees: (1) that what is returned is subject to far simpler security access checks than when querying matching ALL_xxx views; consequently: (2) DBA_xxx views are inherently immune from bugs in the security access checks that may (or may not, depending on version) be present in matching ALL_xxx views. ↩
Add a new viewer "Uses" for the following object types:
The viewer lists REFERENCE
and CALL
usages of other objects. Calls of SYS.STANDARD units are not listed.
Hi,
Trying to build on Oracle 11g Enterprise Edition Release 11.2.0.4.0
I had Plscope installed and recompiled all objects so there is existing data for plscope.
When running @database/utils/user/plscope.sql I get:
GRANT SELECT ON sys.dba_statements TO PLSCOPE WITH GRANT OPTION * ERROR at line 1: ORA-00942: table or view does not exist
Though dba_identifiers does exist.
When running @database/install.sql I get:
Also, there appears to be a dependency on Oracle Real Application Security packages, which I do not have installed.
ace => sys.xs$ace_type( * ERROR at line 4: ORA-06550: line 4, column 14: PLS-00201: identifier 'SYS.XS$ACE_TYPE' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored
There also appears to be a dependency on SYS.DBA_STATEMENTS, which also appears to be an Oracle 12 object at:
`Errors for PACKAGE BODY LINEAGE_UTIL:
LINE/COL ERROR
188/14 PLS-00341: declaration of cursor 'C_INSERT' is incomplete or
malformed
189/10 PL/SQL: SQL Statement ignored
190/17 PL/SQL: ORA-00942: table or view does not exist`
There are other errors that I suspect are noise due to previous errors.
Is there a version of this utility that will work with 11g?
Thanks!
Installation of plsql-utils fails in Oracle Database 18c with
Errors for PACKAGE BODY PLSCOPE.PARSE_UTIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
33/10 PL/SQL: Statement ignored
33/10 PLS-00306: wrong number or types of arguments in call to 'PARSEQUERY'
The utl_xml.parseQuery
procedure has the following new signature:
PROCEDURE parseQuery (currUid IN NUMBER,
schema IN VARCHAR2,
sqltext IN CLOB,
lobloc IN OUT NOCOPY CLOB
)
ACCESSIBLE BY (PACKAGE SYS.DBMS_METADATA);
The following things are different to Oracle Database 12.2:
currUid
is new, this is not a problem to identify the user_id
of the current user and pass it to the procedure.SYS.DBMS_METADATA
As a consequence of these changes the package parse_util
cannot be compiled in Oracle Database 18c. Alternatives have to be elaborated.
Running install_test.sql
produces the following failures/errors when running on an PDB of an Oracle 19c instance. It works without problem in an non-multitenant environment.
====================================================================
Run tests with 12.2 code coverage
====================================================================
plscope
test
test_type_util
test_dedup_t_obj [1.35 sec]
test_dedup_t_col [.052 sec]
test_dedup_t_col_lineage [.054 sec]
test_plscope_identifiers
user_identifiers [2.234 sec]
user_statements [.727 sec]
test_plscope_context
test_set_attr [.025 sec]
test_remove_attr [.015 sec]
test_remove_all [.104 sec]
test_parse_util
test_parse_query [.103 sec]
test_get_insert_targets [.13 sec]
test_get_insert_subquery [.052 sec]
test_get_dep_cols [.064 sec]
test_lineage_util
test_set_get_recursive [.026 sec]
test_get_dep_cols_from_query [.742 sec]
test_get_dep_cols_from_view [.059 sec]
test_get_dep_cols_from_insert [2.142 sec] (FAILED - 1)
test_get_target_cols_from_insert [.699 sec] (FAILED - 2)
test_dd_util
test_resolve_synonym [.042 sec] (FAILED - 3)
test_get_object [.007 sec]
test_get_objects [.048 sec]
test_get_column_id [.011 sec]
test_get_view_source [.119 sec]
demo
test_etl
test_load_from_tab [.204 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from table).
test_load_from_view [.034 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from view).
test_load_from_syn [.021 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from view via synonym).
test_load_from_syn_wild [.019 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from view via synonym without explicit column references).
test_load_from_syn_log [.023 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (with log errors).
test_load_multi_table [.026 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from via multi-table-insert).
test_load_from_implicit_cursor [.022 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from implicit cursor).
test_load_from_explicit_cursor [.019 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from explicit cursor).
test_load_from_dyn_sql [.02 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from dynamic SQL).
test_sal_of_dept [.01 sec]
test_load_from_app_join [.033 sec]
deptsal an deptsal_err deleted.
deptsal loaded and commited (from application join).
Failures:
1) test_get_dep_cols_from_insert
Actual: plscope.t_col_lineage_type [ count = 3 ] was expected to equal: plscope.t_col_lineage_type [ count = 3 ]
Diff:
Rows: [ 3 differences ]
Row No. 1 - Actual: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
Row No. 1 - Expected: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 2 - Actual: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 2 - Expected: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 3 - Actual: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 3 - Expected: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
at "PLSCOPE.TEST_LINEAGE_UTIL.TEST_GET_DEP_COLS_FROM_INSERT", line 121 ut.expect(anydata.convertCollection(l_sorted))
Actual: plscope.t_col_lineage_type [ count = 7 ] was expected to equal: plscope.t_col_lineage_type [ count = 7 ]
Diff:
Rows: [ 7 differences ]
Row No. 1 - Actual: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 1 - Expected: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 2 - Actual: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
Row No. 2 - Expected: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 3 - Actual: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 3 - Expected: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
Row No. 4 - Actual: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 4 - Expected: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
Row No. 5 - Actual: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
Row No. 5 - Expected: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 6 - Actual: <TO_COLUMN_NAME>DEPT_NAME</TO_COLUMN_NAME>
Row No. 6 - Expected: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 7 - Actual: <TO_COLUMN_NAME>DEPT_NO</TO_COLUMN_NAME>
Row No. 7 - Expected: <TO_COLUMN_NAME>SALARY</TO_COLUMN_NAME>
at "PLSCOPE.TEST_LINEAGE_UTIL.TEST_GET_DEP_COLS_FROM_INSERT", line 149 ut.expect(anydata.convertCollection(l_sorted))
2) test_get_target_cols_from_insert
Actual: plscope.t_col_type [ count = 3 ] was expected to equal: plscope.t_col_type [ count = 3 ]
Diff:
Rows: [ 3 differences ]
Row No. 1 - Actual: <COLUMN_NAME>DEPT_NAME</COLUMN_NAME>
Row No. 1 - Expected: <COLUMN_NAME>DEPT_NO</COLUMN_NAME>
Row No. 2 - Actual: <COLUMN_NAME>SALARY</COLUMN_NAME>
Row No. 2 - Expected: <COLUMN_NAME>DEPT_NAME</COLUMN_NAME>
Row No. 3 - Actual: <COLUMN_NAME>DEPT_NO</COLUMN_NAME>
Row No. 3 - Expected: <COLUMN_NAME>SALARY</COLUMN_NAME>
at "PLSCOPE.TEST_LINEAGE_UTIL.TEST_GET_TARGET_COLS_FROM_INSERT", line 173 ut.expect(anydata.convertCollection(l_actual))
3) test_resolve_synonym
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for function goto if loop mod null
package pragma procedure raise return select type update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
table continue call close current define delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge library assembly operator pipe purge compound
json_exists json_value json_q
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for function goto if loop mod null
package pragma procedure raise return select type update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
table continue call close current define delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge library assembly operator pipe purge compound
json_exists
ORA-06512: at "PLSCOPE.DD_UTIL", line 12
ORA-06512: at "PLSCOPE.DD_UTIL", line 26
ORA-06512: at "PLSCOPE.TEST_DD_UTIL", line 35
ORA-06512: at "PLSCOPE.DD_UTIL", line 12
ORA-06512: at "PLSCOPE.DD_UTIL", line 26
ORA-06512: at "PLSCOPE.TEST_DD_UTIL", line 35
ORA-06512: at line 6
Finished in 9.592997 seconds
33 tests, 2 failed, 1 errored, 0 disabled, 0 warning(s)
Hi,
Just going through your cool stuff and tries to play around . I didnt see any option to track code commenting pattern context or any custom pattern standards context setting (plscope_context.set_attr) provision inside procedure or function. Is this doable if yes than can you give me some guidance.
Thanks
Rajiv
Format all files according the Trivadis PL/SQL & SQL guidelines. Configure a pre-commit hook in the repo which can be installed easily by every user cloning this repo.
Failure when installing plscope-utils in the OracleCloud. parse_util
package body is invalid due to missing grant.
If using an account with DBA privileges, I'd expect to be able to use the "Compile with PL/Scope" action in order to recompile any schema of my own choosing1. This is implied by the context menu entry being available on every PL/Scope node in the object tree2.
As of v1.0.0 that doesn't work, however, because the target of the action is—obviously, in the PL/SQL code—the value of the user
function, which always equates to the session user.
Solution: per the documentation (link) the target schema of an action is identified by the #OBJECT_OWNER#
substitution value, so that's what should be used in place of user
, in order to enable the action to act on any schema, not just that of the session user.
Additionally, the execute immediate statement in the body of the <<synonyms>>
for-loop specifies the synonym to be recompiled without using a schema-qualified name, hence it implicitly targets a synonym in the current schema, whatever it may be—and in general it may be different from #OBJECT_OWNER#
.
Solution: obvious.
To reproduce
OGDEMO
with version 0.5.0EXTENDED_VIEW
packageFindings
View | Non-PDB Time (seconds) | PDB Time (seconds) | Result Rows |
---|---|---|---|
Identifiers | 0.293 | 64.198 | 274 |
Statements | 0.027 | 0.098 | 2 |
Uses | 0.479 | 30.465 | 94 |
Used by | 0.142 | 0.392 | 0 |
Table Usages | 0.440 | 49.105 | 0 |
Column Usages | 0.595 | 3.562 | 6 |
As of v1.0.0, the "Compile Synonyms?" prompt controls recompilation of both private and public synonyms. It should be possible to distinguish between the two, by splitting this choice into 2 separate prompts, respectively labeled:
Reason: private synonyms are part of the target schema, whereas public synonyms are not; recompiling the latter is more likely to cause invalidations in other schemas (distinct from the target of the action), whereas recompiling private synonyms could result in invalidations in other schemas, but only indirectly. Therefore, handling the 2 separately would provide a greater degree of control.
Additionally, the default value of both prompts should be "No".
Reason; because of the potential for invalidations in other schemas, recompiling public synonyms should be left to users who know what they are doing, hence "No" by default. Using the same default for private synonyms would then be necessary, otherwise the dialog box of the action would seem inconsistent.
In the commits ec01236 and d34b6ee the use of the pseudo column
user
was replaced with
sys_context('userenv', 'authenticated_identity')
Using this context variable is a good idea for end users connecting to the database. It can be used for database users and for enterprise users managed outside of the database, e.g. via Oracle Enterprise User Security. However, it's a bad idea to use for schemas. The following example shows why:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> column user format a10
SQL> column auth_id format a10
SQL> column owner format a10
SQL> column table_name format a10
SQL> connect bond@pdbea51
Enter password:
Connected.
SQL> select user, sys_context('userenv', 'authenticated_identity') as auth_id from dual;
USER AUTH_ID
---------- ----------
EUS_USER BOND
SQL>
SQL> create table t (c1 number);
Table created.
SQL>
SQL> select owner, table_name from all_tables where table_name = 'T';
OWNER TABLE_NAME
---------- ----------
EUS_USER T
SQL> grant select on t to public;
Grant succeeded.
SQL> connect m@pdbea51
Enter password:
Connected.
SQL> select user, sys_context('userenv', 'authenticated_identity') as auth_id from dual;
USER AUTH_ID
---------- ----------
EUS_USER M
SQL> create table t (c1 number);
create table t (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
The enterprise users BOND
and M
are authenticated via LDAP, but they are connected to the Oracle Database user EUS_USER
. This user has the create table
privilege, which usually makes no sense. In this case, it shows that both end users share the same Oracle Database user. All objects are owned by this database user. It's not possible that objects are owned by sys_context('userenv', 'authenticated_identity').
Based on this information, the pseudo column user
should be used instead of sys_context('userenv', 'authenticated_identity')
and the changes made by the commits mentioned above should be reverted.
Thanks to @rvo-cs for pointing this out in another channel.
As of v1.0.0, the "Compile with PL/Scope" action recompiles PL/SQL code by calling dbms_utility.compile_schema
1. Replacing that procedure should be considered, for the following reasons:
And above all:
For the above reasons, getting through the trouble of providing an ad-hoc recompilation routine would seem a better option, though more ambitious 2.
How to check naming convention for constraint,PK,FK etc ..? Is their any provision ?
For some analysis it is helpful the see the referenced source code line.
Synonyms compiled with PL/Scope can be found in sys.plscope_identifier$, with the following identifying properties:
While such internal implementation details are not publicly disclosed by Oracle, hence there's obviously no commitment that they may not change in the future, in the meantime they make it possible to create a report for showing synonyms compiled with/without PL/Scope, along with relevant information (e.g. status of the synonym object, existence and type of the target object, creation date and last_ddl_time of the synonym object).
Because that report would query sys.plscope_identifier$, only users having the DBA role, or the SELECT ANY DICTIONARY system privilege, or an ad-hoc grant of SELECT / READ on sys.plscope_identifier$, would be able to use the new report. This privilege requirement should be made as obvious as possible, otherwise insufficiently-privileged users could be frustrated by trying the report, and receiving the ORA-00942 ("table or view does not exist") exception in response.
As of v1.0.0, existing reports only use ALL_xxx views, hence in principle they can be run by unprivileged users without raising exceptions—though the actual results will vary greatly depending on user privileges. The new report would be different in terms of privilege requirements and exception behaviour, therefore, in order to make this clearer for users I'd suggest to put it clearly aside, perhaps by using sub-folders under the main "plscope-utils Reports" folder:
Must not use dba_ views in reports. Replace them by sys_all_* views. SQL Developer will automatically change them to "sys.Dba_" when the user has access to the dba_* views.
"ORA-00942: table or view does not exist" is thrown when the "Identifiers" view is opened in a database connection against an Oracle Database version < 12.2.
Unknown prep_ids
is used in the named query fids
. sys.all_identifiers
should be used instead.
For some objects following error occurs on the Identifiers
, Table Usages
and Column Usages
tab:
The obvious reason is that the sys_connect_by_path
function produces a result with more than 4000 bytes. This can happen only with more or less 130 levels (assuming a max of 30 characters per name). In a customer's case there are more than 700 levels. Most levels were artificial. Produced by the fix of inconsistent PL/Scope data (missing levels). In such a case the node was connected to the last previous consistent node. In case of a series of calls to a unit which is not compiled with PL/Scope this leads to a lot of nested levels where no nesting would be expected.
Add a column named procedure_scope
to plscope_identifiers
, with the following possible values:
Value | Meaning | Notes |
---|---|---|
NULL |
n/a | Identifier is not part of a PL/SQL package function or procedure. |
PRIVATE |
Private scope | Locally declared and defined function or procedure. It is visible only within the package body. |
PUBLIC |
Public scope | Function or procedure is declared in the package specification and therefore visible outside of the package body. |
SELECT line, col, name, type, procedure_scope, text
FROM plscope_identifiers
WHERE owner = USER
AND object_name = 'ETL'
AND usage = 'DEFINITION'
ORDER BY line, col;
LINE COL NAME TYPE PROCEDURE_SCOPE TEXT
---- ---- ------------------------- ---------- --------------- -----------------------------------------------------------------------------------
1 14 ETL PACKAGE PACKAGE BODY etl AS
4 14 CLEAR_DEPTSAL PROCEDURE PRIVATE PROCEDURE clear_deptsal IS
11 14 LOAD_FROM_TAB PROCEDURE PUBLIC PROCEDURE load_from_tab IS
24 14 LOAD_FROM_VIEW PROCEDURE PUBLIC PROCEDURE load_from_view IS
34 14 LOAD_FROM_SYN PROCEDURE PUBLIC PROCEDURE load_from_syn IS
44 14 LOAD_FROM_SYN_WILD PROCEDURE PUBLIC PROCEDURE load_from_syn_wild IS
55 14 LOAD_FROM_SYN_LOG PROCEDURE PUBLIC PROCEDURE load_from_syn_log IS
66 14 LOAD_MULTI_TABLE PROCEDURE PUBLIC PROCEDURE load_multi_table IS
80 14 LOAD_FROM_IMPLICIT_CURSOR PROCEDURE PUBLIC PROCEDURE load_from_implicit_cursor IS
94 14 LOAD_FROM_EXPLICIT_CURSOR PROCEDURE PUBLIC PROCEDURE load_from_explicit_cursor IS
108 14 LOAD_FROM_DYN_SQL PROCEDURE PUBLIC PROCEDURE load_from_dyn_sql IS
124 13 SAL_OF_DEPT FUNCTION PUBLIC FUNCTION sal_of_dept (in_deptno dept.deptno%TYPE) RETURN deptsal.salary%TYPE IS
135 14 LOAD_FROM_APP_JOIN PROCEDURE PUBLIC PROCEDURE load_from_app_join IS
13 rows selected.
Sven Weller shows on in https://svenweller.wordpress.com/2018/05/17/plscope-fetch-module-name-from-line-number/ how to calculate submodule names (e.g. functions within functions within functions) using a dot notation.
This information should be included in plscope_identifiers
view as an additional column (it is different to the name_path
column which provides the full name path including all identifier types).
Usage
, Type
, Name
into a single column Name (Usage)
as in plscope_identifiers viewUsage
, Type
, Name
, Used?
to the end (to allow filtering, sorting)The query based on alexandria-plsql-utils
SELECT name, usage, line, col
FROM user_identifiers
WHERE object_name = 'AMAZON_AWS_AUTH_PKG'
AND object_type = 'PACKAGE BODY'
AND name = 'L_KEY_BYTES_RAW';
produces
NAME USAGE LINE COL
------------------------------ ----------- ---------- ----------
L_KEY_BYTES_RAW DECLARATION 28 2
L_KEY_BYTES_RAW REFERENCE 46 99
L_KEY_BYTES_RAW ASSIGNMENT 43 3
but the query
SELECT name, usage, line, col, is_used
FROM plscope_identifiers
WHERE object_name = 'AMAZON_AWS_AUTH_PKG'
AND object_type = 'PACKAGE BODY'
AND name = 'L_KEY_BYTES_RAW';
produces
NAME USAGE LINE COL IS_USED
------------------------------ ----------- ---------- ---------- -------
L_KEY_BYTES_RAW ASSIGNMENT 43 3
L_KEY_BYTES_RAW DECLARATION 28 2 NO
The missing reference leads to a wrong result in the IS_USED column.
Test setup:
alter session set current_schema = "SCOTT";
alter session set plscope_settings = "IDENTIFIERS:ALL, STATEMENTS:ALL";
create table t1 (c1 number);
create synonym s_t1 for t1;
create view vw_t1 as select * from s_t1;
create synonym s_vw_t1 for vw_t1;
create table t2 (c1 number);
create synonym sa_t2 for t2;
create synonym sb_t2 for sa_t2;
create view vw_t2 as select * from sb_t2;
create synonym sa_vw_t2 for vw_t2;
create synonym sb_vw_t2 for sa_vw_t2;
create package pkg_tab_usage_tst as
procedure proc;
end pkg_tab_usage_tst;
/
create package body pkg_tab_usage_tst as
procedure proc
is
begin
insert into s_t1(c1)
select * from s_vw_t1;
insert into sb_t2(c1)
select * from sb_vw_t2;
end proc;
end pkg_tab_usage_tst;
/
We have created a table t1, a matching view vw_t1, and private synonyms for both objects; the chain of dependencies is as follows: s_vw_t1 (synonym) --> vw_t1 (view) --> s_t1 (synonym) --> t1
Likewise, we have created a table t2, a matching view vw_t2, and private synonyms for both, but this time we have not used a single synonym for each object, but a chain of 2 synonyms: 1 synonym for the base object, and 1 synonym for the synonym. The resulting chain of dependencies is as follows: sb_vw_t2 (synonym) --> sa_vw_t2 (synonym) --> vw_t2 (view) --> sb_t2 (synonym) --> sa_t2 (synonym) --> t2
Remark: this is arguably a made-up test case: chains of synonyms are rarely seen in the wild, but they may exist nonetheless.
Test query:
exec plscope_context.set_attr('OWNER', 'SCOTT');
exec plscope_context.set_attr('OBJECT_NAME', 'PKG_TAB_USAGE_TST');
select line,
col,
procedure_name,
operation,
ref_object_type,
ref_object_name,
direct_dependency,
text
from plscope.plscope_tab_usage
order by line,
col,
ref_object_type,
ref_object_name;
Results:
LINE COL PROCEDURE_NAME OPERATION REF_OBJECT_TYPE REF_OBJECT_NAME DIRECT_DEPENDENCY TEXT
5 19 PROC INSERT SYNONYM S_T1 YES insert into s_t1(c1)
5 19 PROC INSERT TABLE T1 NO insert into s_t1(c1)
6 21 PROC INSERT SYNONYM S_VW_T1 YES select * from s_vw_t1;
6 21 PROC INSERT TABLE T1 NO select * from s_vw_t1;
6 21 PROC INSERT VIEW VW_T1 NO select * from s_vw_t1;
7 19 PROC INSERT TABLE T2 NO insert into sb_t2(c1)
8 21 PROC INSERT TABLE T2 NO select * from sb_vw_t2;
8 21 PROC INSERT VIEW VW_T2 NO select * from sb_vw_t2;
Comment: all usages of tables and views are found as expected. However, there's no mention of any of the intervening synonyms at lines 7 and 8, and this is not consistent with usages of synonyms S_T1 and S_VW_T1 being reported, at lines 5 and 6 respectively.
Expected results: it's hard to know exactly what should be expected without a formal specification of the plscope_tab_usage view in the first place. Meanwhile, it would make sense that every synonym directly referenced in the source code (DIRECT_DEPENDENCY is YES) and leading to a table or a view, either directly or through a chain of synonyms, be returned.
E.g.:
LINE COL PROCEDURE_NAME OPERATION REF_OBJECT_TYPE REF_OBJECT_NAME DIRECT_DEPENDENCY TEXT
5 19 PROC INSERT SYNONYM S_T1 YES insert into s_t1(c1)
5 19 PROC INSERT TABLE T1 NO insert into s_t1(c1)
6 21 PROC INSERT SYNONYM S_VW_T1 YES select * from s_vw_t1;
6 21 PROC INSERT TABLE T1 NO select * from s_vw_t1;
6 21 PROC INSERT VIEW VW_T1 NO select * from s_vw_t1;
7 19 PROC INSERT SYNONYM SB_T2 YES insert into sb_t2(c1)
7 19 PROC INSERT TABLE T2 NO insert into sb_t2(c1)
8 21 PROC INSERT SYNONYM SB_VW_T2 YES select * from sb_vw_t2;
8 21 PROC INSERT TABLE T2 NO select * from sb_vw_t2;
8 21 PROC INSERT VIEW VW_T2 NO select * from sb_vw_t2;
The intermediary synonyms sa_t2 and sa_vw_t2 would not be returned, as they are neither directly referenced in the PKG_TAB_USAGE_TST package body, nor are they "final" table or view objects that we are primarily interested in.
As of v1.0.0, the "Compile with PL/Scope" action changes the plscope_settings
parameter in the session, then recompiles objects in the schema (unless the user has opted out of that), enforcing whatever PL/SQL compilation parameters are effective in the session when doing so.
The expectation is that it would preserve prior PL/SQL compilation settings for all affected objects, with the single exception of updating the plscope_settings
parameter.
This is important because some modules could have specific requirements as regards compilation settings (especially, but not limited to, the value of the plsql_ccflags
parameter), and these settings should not be lost when running the action.
see https://sonarcloud.io/project/issues?resolved=false&id=philippsalvisberg_plscope
Options:
Create JUnit tests for SQL Developer Extension.
Cannot use use utPLSQL since the code to be tested is in XML files. Easier to extract relevant SQL and PL/SQL Code and run it in JUnit and integrate it in existing Maven build.
Add the project to the CI server.
Create unit tests using utPLSQL v3 for Core Database Objects.
Add the project to the CI server. See utPLSQL-sql-cli
In the Oracle Database Development Guide are more root identifier types mentioned which are not part of the PL/Scope tree:
Add these secondary objects to the PL/Scope tree.
Additionally, check relevance of
Object and opaque represent data types, but I've never seen alias.
This view is part of core database objects. The report should work without installing objects in the database.
"Slow" is highly subjective, and it depends vastly on the underlying hardware, OS load, etc. But the point is, it's consistently slow, no matter how narrow the scope set by plscope_context.set_attr
.
Typical figures from a sample execution on my test PDB are as follows:
Buffers | Reads | Writes |
---|---|---|
1723K | 18262 | 18742 |
And even on fast hardware these numbers would be on the slow side.
Reason: the main contributors to the above figures are as follows:
Their respective contributions are as follows:
Contributor | Buffers | Reads | Writes |
---|---|---|---|
dep_graph CTE | 1619K | 11367 | 11367 |
Base views used in PLSCOPE_IDENTIFIERS | 94060 | 6355 | 6355 |
So we can see that the dep_graph CTE causes more than 90% of the logical reads, and roughly 2/3 of the physical I/Os.
There's not much that can be done about the base views, so I'll leave that aside.
On the other hand, the primary reason for the high cost of the dep_graph CTE (regardless of scope, as said above) is that the hierarchy of dependencies is built "from the bottom", beginning with every table from DBA_TABLES, and every dependency in DBA_DEPENDENCIES for any object of type view, materialized view, or synonym; and then going up the dependency chains within that subset of all database dependencies. But that subset is independent from the scope set by plscope_context.set_attr
, and it is rather large: on my test PDB, dep_graph_base returns 258 K rows, which reduce to 93 K distinct rows out of the dep_graph CTE. Unless the scope of the analysis is very broad, this could mean a lot of unnecessary work.
Solution: refactor the query in order to build the dependency chains "from the top", beginning with dependent objects referenced in PLSCOPE_IDENTIFIERS, and going down the dependency hierarchy from dependent object to referenced objects. This way, the dependency chains would be built only for objects within the scope set by plscope_context.set_attr
.
It its known that if not all components are compiled with PL/Scope, the analysis might be incomplete. However, in certain cases the hierarchical data structure of dba_identifiers
is inconsistent. The column usage_context_id
can hold values which do not exist in the column usage_id
for a chosen object. If your analysis relies on the hierarchical structure such as the view plscope_identifiers
then the result will not contain all available data.
To reproduce do the following:
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
f
CREATE OR REPLACE FUNCTION f(in_string IN VARCHAR2) RETURN NUMBER IS
l_result NUMBER;
BEGIN
l_result := dbms_random.value(
0,
dbms_utility.get_hash_value(
in_string,
0,
2048
)
);
RETURN l_result;
END f;
/
dba_identifiers
SELECT name, type, usage, line, col, usage_id, usage_context_id
FROM dba_identifiers
WHERE owner = USER
AND object_type = 'FUNCTION'
AND object_name = 'F'
ORDER by line, col;
NAME TYPE USAGE LINE COL USAGE_ID USAGE_CONTEXT_ID
---------- ------------------ ----------- ---------- ---------- ---------- ----------------
F FUNCTION DEFINITION 1 10 2 1
F FUNCTION DECLARATION 1 10 1 0
IN_STRING FORMAL IN DECLARATION 1 12 3 2
VARCHAR2 CHARACTER DATATYPE REFERENCE 1 25 4 3
NUMBER NUMBER DATATYPE REFERENCE 1 42 5 2
L_RESULT VARIABLE DECLARATION 2 4 6 2
NUMBER NUMBER DATATYPE REFERENCE 2 13 7 6
L_RESULT VARIABLE ASSIGNMENT 4 4 8 2
IN_STRING FORMAL IN REFERENCE 7 23 11 10
L_RESULT VARIABLE REFERENCE 12 12 12 2
10 rows selected.
The in_string
reference with usage_id 11
has a usage_context_id 10
. But there is no usage_id
with the value 10
. That's the inconsistency.
plscope_identifiers
(v0.2.0)SELECT name, type, usage, line, col, usage_id, usage_context_id
FROM plscope_identifiers
WHERE owner = USER
AND object_type = 'FUNCTION'
AND object_name = 'F'
ORDER by line, col;
NAME TYPE USAGE LINE COL USAGE_ID USAGE_CONTEXT_ID
---------- ------------------ ----------- ---------- ---------- ---------- ----------------
F FUNCTION DECLARATION 1 10 1 0
F FUNCTION DEFINITION 1 10 2 1
IN_STRING FORMAL IN DECLARATION 1 12 3 2
VARCHAR2 CHARACTER DATATYPE REFERENCE 1 25 4 3
NUMBER NUMBER DATATYPE REFERENCE 1 42 5 2
L_RESULT VARIABLE DECLARATION 2 4 6 2
NUMBER NUMBER DATATYPE REFERENCE 2 13 7 6
L_RESULT VARIABLE ASSIGNMENT 4 4 8 2
L_RESULT VARIABLE REFERENCE 12 12 12 2
9 rows selected.
The result contains only 9 rows. The in_string
reference with usage_id 11
is missing. That's the wrong result.
To fix the issue either an Oracle Support Request has to be opened (chances are that this will be accepted as a bug) or the usage_context_id
needs to be fixed on the fly within the plscope_identifiers
view. I consider doing both.
As of v1.0.0, the scope of the action is limited to the schema (plus public synonyms) of the current session user. This limitation could be a bug—see issue #55.
Now let's suppose, as a mental experiment, that that issue is fixed, and now a user with DBA privileges may use the action to recompile code with PL/Scope in any schema. That's cool. What about applying it to the SYS schema? After all, the idea of recompiling SYS packages with PL/Scope has been advertised before (e.g. in this article; see example 3b), and it sure sounds promising. And this nice "Compile with PL/Scope" action was created to do just that, wasn't it?
As a result, inexperienced users having DBA privileges1 may not resist the temptation of applying the action to the SYS schema, resulting in possible disasters due to object invalidations—both in the SYS schema and in related Oracle-maintained schemas, e.g. LBACSYS—which could be difficult to repair.
Therefore, as a safety precaution, the scope of the "Compile with PL/Scope" action should be narrowed in order to prevent from it being applied to Oracle-maintained schemas. Recompiling the SYS schema with PL/Scope may indeed be possible, but it seems that doing it requires a lot more than what can be provided in a simple context-menu action2, so it should be made clear that Oracle-maintained schemas are beyond the scope of the action, and that restriction should be enforced.
Production databases are generally in the hands of experienced DBAs, or at least extremely careful people. On the contrary, it is not infrequent on some sites that developers, experienced or not, may have full DBA privileges on development databases. They are the ones who will find the highest interest in PL/Scope, and they may not always be as cautious as production DBAs. ↩
Recompiling all objects in the SYS schema with PL/Scope is far from a trivial task: it requires putting the database in UPGRADE mode, running utlirp.sql with a specially modified version of utlip.sql, and then recompiling everything using utlrp.sql—all this through catcon.pl if on multitenant. And after that you still have to do the recompilation of public synonyms, this time with containers started in RESTRICTED mode... This operation can be done for fun, or out of curiosity, but of course it is absolutely not supported by Oracle, and what happens through subsequent patching/upgrade sessions remains to be seen—chances are plscope_settings will revert to the default for affected objects. ↩
Implement the Naming Conventions for PL/SQL according the chapter 2.2 of the PL/SQL & SQL Coding Guidelines V3.2
Identifier | (P)refix / (S)uffix | Example |
---|---|---|
Global Variable | P: g | g_version |
Local Variable | P: l | l_version |
Cursor | P: c | c_employees |
Record | P: r | r_employee |
Array / Table | P: t | t_employees |
Object | P: o | o_employee |
Cursor Parameter | P: p | p_empno |
In Parameter | P: in | in_empno |
Out Parameter | P: out | out_ename |
In/Out Parameter | P: io | io_employee |
Record Type Definitions | P: r / S: type | r_employee_type |
Array/Table Type Definitions | P: t / S: type | t_employees_type |
Exception | P: e | e_employee_exists |
Constants | P: co | co_empno |
Subtypes | S: type | big_string_type |
Make check patterns configurable (reporting parameters).
see #46
The report "Unused Local Identifiers" could be implemented for Oracle Database version < 12.2 as well.
Same issue as in #12. OWNER should be filtered earlier.
Installation works, but after a restart the extension is not loaded and also not listed as installed. Retry does not help.
It has been properly registred in cfu_bundles.extras
and cfu_bundles.info
. The jar file has been also been copied into the extensions directory.
No errors found in logs after enabling sqldeveloper-debug.conf
.
If I had to guess then maybe one or more of the dependent bundles do not exist anymore. List is to exhaustive anyway.
Add a column named is_used
to plscope_identifiers
, with the following possible values:
Value | Meaning | Notes |
---|---|---|
NULL |
n/a | Identifiers is not declared within a package body, procedure, function or type body |
NO |
not used | Identifier is declared and optionally assigned a value, but not referenced within the PL/SQL unit |
YES |
used | Identifiers is declared and referenced within the PL/SQL unit |
See original script by @stevenfeuerstein at https://github.com/oracle/oracle-db-examples/blob/master/plsql/code-analysis/find-function-calls-in-sql.pls
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
CREATE TABLE my_data (n NUMBER);
CREATE OR REPLACE FUNCTION my_function1
RETURN NUMBER
AUTHID DEFINER
IS
BEGIN
RETURN 1;
END;
/
CREATE OR REPLACE FUNCTION my_function2
RETURN NUMBER
AUTHID DEFINER
IS
BEGIN
RETURN 1;
END;
/
CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
AUTHID DEFINER
IS
l_my_data my_data%ROWTYPE;
--
PROCEDURE do (x_in IN NUMBER) IS
BEGIN
NULL;
END do;
BEGIN
SELECT my_function1 ()
INTO l_my_data
FROM my_data
WHERE n = n_in
AND my_function2 () = 0
AND n = (SELECT my_function1 () FROM DUAL);
SELECT COUNT (*)
INTO l_my_data
FROM my_data
WHERE n = n_in;
UPDATE my_data
SET n = my_function2 ()
WHERE n = n_in;
END;
/
plscope_identifiers
SELECT line, col, name, usage, type, text
FROM plscope_identifiers
WHERE owner = USER
AND object_name = 'MY_PROCEDURE'
AND is_used = 'NO'
ORDER BY line, col;
LINE COL NAME USAGE TYPE TEXT
---- ---- ---------- ----------- ---------- ----------------------------------------
4 4 L_MY_DATA DECLARATION VARIABLE l_my_data my_data%ROWTYPE;
6 18 X_IN DECLARATION FORMAL IN procedure do (x_in IN NUMBER) IS
Report is slow even if the current schema does not have a lot of objects compiled with PL/Scope.
Reason: OWNER is filtered too late.
Provide code in this repo to automatically format SQL and PL/SQL files before committing changes. This could be done by using SQLcl and tvdformat.js or the standalone CLI provided in this repo.
Include views/reports to report potential SQL injection risks based on the queries in https://www.salvis.com/blog/2017/12/17/how-to-prove-that-your-smartdb-app-is-secured-against-sql-injection-attacks/
Similar as in plscope_identifiers
this might help for analysis, especially for derived dependencies.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.