How can find the a string like ‘FD%’, ‘FD’, ‘FD with regardless of case sensitivity in oracle database objects source code like package spec, package body , table, view, Materialized view , procedure, function, trigger and etc
I tried the following but did not come all my needs
SELECT distinct name, type
FROM dba_source
WHERE instr (UPPER(text), UPPER(:search_text)) >0
--and owner like 'APPS'
and type in ('TRIGGER', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
--and NAME like 'EKG%'
To search for a string like ‘FD%’, ‘FD’, or ‘FD’ regardless of case sensitivity across various Oracle database objects such as package specifications, package bodies, tables, views, materialized views, procedures, functions, triggers, etc., you can use the DBA_SOURCE
view for source code objects like procedures, functions, packages, and triggers. For other object types like tables, views, and materialized views, you’ll need to use different views such as DBA_TABLES
, DBA_VIEWS
, and DBA_MVIEWS
respectively.
Since you’re looking to expand your search beyond just the source code objects, you’ll need to perform separate queries for each type of object and union them together for a comprehensive search. Note that for non-source code objects (like tables and views), you might be interested in searching their definitions or comments, as these objects don’t contain “source code” in the same way that procedures or functions do.
Here’s an approach to cover a broader range of objects:
- Search in DBA_SOURCE for procedures, functions, packages, and
triggers. - Search in DBA_TAB_COMMENTS for comments on tables and
views. - Search in DBA_COL_COMMENTS for comments on columns of tables
and views. - Search in DBA_MVIEWS for materialized views.
-- Search in DBA_SOURCE
SELECT DISTINCT OWNER, NAME, TYPE
FROM DBA_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%FD%')
AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
UNION
-- Search in DBA_TAB_COMMENTS for tables and views
SELECT OWNER, TABLE_NAME AS NAME, 'TABLE/VIEW COMMENT' AS TYPE
FROM DBA_TAB_COMMENTS
WHERE UPPER(COMMENTS) LIKE UPPER('%FD%')
UNION
-- Search in DBA_COL_COMMENTS for column comments
SELECT OWNER, TABLE_NAME AS NAME, 'COLUMN COMMENT' AS TYPE
FROM DBA_COL_COMMENTS
WHERE UPPER(COMMENTS) LIKE UPPER('%FD%')
UNION
-- Search in DBA_MVIEWS for materialized views
SELECT OWNER, MVIEW_NAME AS NAME, 'MATERIALIZED VIEW' AS TYPE
FROM DBA_MVIEWS
WHERE UPPER(QUERY) LIKE UPPER('%FD%');
This query combines results from different sources to find occurrences of the specified string in various database objects and their comments. Adjust the WHERE
clauses as needed to refine your search, such as including or excluding specific owners or object names.
1