Skip to content

Commit

Permalink
Fixes #9 - remove column ORIGIN_CON_ID from the select clause, since it
Browse files Browse the repository at this point in the history 
is not part of any final resultset.
  • Loading branch information
@PhilippSalvisberg
PhilippSalvisberg committed Jul 13, 2017
1 parent 533f453 commit 7c61c4e
Show file tree
Hide file tree
Showing 2 changed files with 26 additions and 52 deletions.
48 changes: 16 additions & 32 deletions sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml
View file
Original file line number Diff line number Diff line change
Expand Up @@ -30,8 +30,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_identifiers
UNION ALL
SELECT owner,
Expand All @@ -44,8 +43,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_statements
),
ids AS (
Expand All @@ -69,8 +67,7 @@ WITH
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM base_ids
WHERE (owner = :OBJECT_OWNER OR upper(replace(:OBJECT_TYPE,'plscope-utils-')) = 'SYNONYM')
AND object_type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
Expand All @@ -89,8 +86,7 @@ WITH
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -171,8 +167,7 @@ WITH
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM sys.all_identifiers
WHERE (owner = :OBJECT_OWNER OR upper(replace(:OBJECT_TYPE,'plscope-utils-')) = 'SYNONYM')
AND object_type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
Expand All @@ -191,8 +186,7 @@ WITH
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -373,8 +367,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_identifiers
UNION ALL
SELECT owner,
Expand All @@ -387,8 +380,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_statements
),
ids AS (
Expand All @@ -412,8 +404,7 @@ WITH
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM base_ids
WHERE owner = :OBJECT_OWNER
AND object_type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
Expand All @@ -432,8 +423,7 @@ WITH
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -488,8 +478,7 @@ WITH
END AS parent_statement_signature,
tree.signature,
tree.usage_id,
tree.usage_context_id,
tree.origin_con_id
tree.usage_context_id
FROM tree
LEFT JOIN sys.all_identifiers refs
ON refs.signature = tree.signature
Expand Down Expand Up @@ -614,8 +603,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_identifiers
UNION ALL
SELECT owner,
Expand All @@ -628,8 +616,7 @@ WITH
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_statements
),
ids AS (
Expand All @@ -653,8 +640,7 @@ WITH
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM base_ids
WHERE owner = :OBJECT_OWNER
AND object_type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
Expand All @@ -673,8 +659,7 @@ WITH
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -719,8 +704,7 @@ WITH
END AS parent_statement_signature,
tree.signature,
tree.usage_id,
tree.usage_context_id,
tree.origin_con_id
tree.usage_context_id
FROM tree
LEFT JOIN sys.all_identifiers refs
ON refs.signature = tree.signature
Expand Down
30 changes: 10 additions & 20 deletions sqldev/src/main/resources/com/salvis/plscope/sqldev/report/plscope-utils-reports.xml
View file
Original file line number Diff line number Diff line change
Expand Up @@ -102,8 +102,7 @@ SELECT CASE
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_identifiers
UNION ALL
SELECT owner,
Expand All @@ -116,8 +115,7 @@ SELECT CASE
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_statements
),
ids AS (
Expand All @@ -141,8 +139,7 @@ SELECT CASE
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM base_ids
),
tree AS (
Expand All @@ -158,8 +155,7 @@ SELECT CASE
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -227,8 +223,7 @@ SELECT CASE
END AS parent_statement_path_len,
tree.signature,
tree.usage_id,
tree.usage_context_id,
tree.origin_con_id
tree.usage_context_id
FROM tree
LEFT JOIN sys.all_identifiers refs
ON refs.signature = tree.signature
Expand Down Expand Up @@ -461,8 +456,7 @@ SELECT CASE
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_identifiers
UNION ALL
SELECT owner,
Expand All @@ -475,8 +469,7 @@ SELECT CASE
usage_id,
line,
col,
usage_context_id,
origin_con_id
usage_context_id
FROM sys.all_statements
),
ids AS (
Expand All @@ -500,8 +493,7 @@ SELECT CASE
)
),
0
) AS usage_context_id, -- fix broken hierarchies
origin_con_id
) AS usage_context_id -- fix broken hierarchies
FROM base_ids
),
tree AS (
Expand All @@ -517,8 +509,7 @@ SELECT CASE
ids.usage,
ids.signature,
ids.usage_id,
ids.usage_context_id,
ids.origin_con_id
ids.usage_context_id
FROM ids
START WITH ids.usage_context_id = 0
CONNECT BY PRIOR ids.usage_id = ids.usage_context_id
Expand Down Expand Up @@ -585,8 +576,7 @@ SELECT CASE
END AS is_used, -- wrong result, if used in statements which do not register usage, such as a variable for dynamic_sql_stmt in EXECUTE IMMEDIATE. Bug?
tree.signature,
tree.usage_id,
tree.usage_context_id,
tree.origin_con_id
tree.usage_context_id
FROM tree
LEFT JOIN sys.all_identifiers refs
ON refs.signature = tree.signature
Expand Down

0 comments on commit 7c61c4e

Please sign in to comment.