Menu
SELECT * from ALL_OBJECTS returns the names of various procedures/packages/tables/other db objects. I want to look inside the PL/SQL code for a matching string. How do I do this?
Something like: (pseudocode) SELECT * FROM all_code WHERE line_of_code like '%mytext%'
674
The PLSQL INSTR function is used for returning the location of a substring in a string. The PLSQL INSTR function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a specified occurrence of the substring.
To enter an Oracle Text query, use the SQL SELECT statement. Depending on the type of index, you use either the CONTAINS or CATSEARCH operator in the WHERE clause. You can use these operators programatically wherever you can use the SELECT statement, such as in PL/SQL cursors.
The Oracle INSTR function is used to search string for substring and find the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring.
SELECT TABLE_NAME FROM USER_TABLES will provide you with listing of tables in a particular schema. SELECT TABLE_NAME, OWNER FROM ALL_TABLES will provide you with listing of all tables for all schemas in the DB for which you have at least select privilege.
Use something like:
SELECT *
FROM USER_SOURCE
WHERE type='PACKAGE'
AND NAME='PACKAGE_NAME'
ORDER BY type, name, line;
There are many options, check out the USER_SOURCE table.
To search ALL code for a String:
SELECT *
FROM ALL_SOURCE
WHERE UPPER(text) LIKE UPPER('%what I am searching for%')
ORDER BY type, name, line
Note that view code is not included in the _SOURCE tables. View code is stored in [USER|ALL|DBA]_VIEWS.TEXT which is a LONG column and difficult to query.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
