Oracle PL/SQL Toad for Oracle-How to make editor generate a script for tables,views,packages so that the script can be searched
Hello, I am using "Toad for Oracle" and i am trying to find a way to search for certain words and numbers in the comments in scripts. I know you could use the create a script button and then control f to search for the words in the script. But is their a way to do it using a query or to create a function that does it. Or relatively anyway to do that besides manually.
Software/Hardware used:
Software/Hardware used:
ASKED:
June 10, 2009 7:40 PM
UPDATED:
July 21, 2009 3:42 PM
Answer Wiki:
All the (non-obfuscated) source code for stored procedures and functions can be accessed in the DBA_SOURCE view.
If you just want to find all the code objects that contain a particular word, that is a simple query:
select *
from dba_source
where upper(text) like upper('%<word>%')
However, when you ask for words contained in comments, that is a bit trickier, since comments can be either single line (with "--" preceding the comment) or block (with "/*" and "*/" bracketing multiple lines of text.
If you don't care about /*...*/ blocks, finding words in single line comments isn't hard:
select *
from dba_source
where upper(text) like upper('%--%<word>%')
To find words in block comments, you really need to write a function that uses a cursor to parse the source code.
Obviously, the same thing applies to finding words in non-comment statements. You can find *most* such words with:
select *
from dba_source ds1
where upper(text) like upper('%<word>%')
and not exists (select 1 from dba_source ds2
where ds2.name = ds1.name and ds2.line = ds1.line and upper(ds2.text) like upper('%--%<word>%'))
This query will return all the occurrences of a word in the code, ignoring words occurring after a single-line comment start. It will also of course return the words that occur in a block comment.
NOTE the need to use "upper" (or "lower" - your preference). Code is stored as typed, in mixed case, and Oracle requires an exact case match. If you don't fold the text to the same case, you will miss occurrences that don't use exactly the same capitalization.
Also - as noted in the 1st line, this only works for non-obfuscated code.
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you, I was looking everywhere for how to deal with comments. I was just wondering what is the diferance between dba_source and from dba_source ds1? Also whats the differance between “upper” and “lower”. I’m used to dealing with C so I’m new to this.
Simple question first – “upper()” and “lower()” are built-in Oracle functions that “fold” a text string to all upper-case or lower-case, respectively. These are necessary because Oracle normally does exact matching of text strings. “Abc” is not equal to “abc” or “ABC”. Thus, the only “safe” way to match strings is to force both strings to the same case, i.e.:
if upper(<string1>) = upper(<string2>)
or
if lower(<string1>) = lower(<string2>)
Both these are functionally identical – they will convert the argument strings to the same case and then compare them.
Your question about dba_source is important. There are two things here.
One is that I am assigning each reference to the table a different “alias”. When you put a “name” after a table name, that establishes the new “name” as an “alias” for the table, and it can be used to uniquely identify the table wherever the table name could be used. This is useful for two purposes – obviously, I can use a short alias to avoid having to type the whole name of the table, as in the following example:
select … from dba_source
where dba_source.text like ‘%123%’
or
select … from dba_source ds1
where ds1.text like ‘%123%’
The more important part is that in order to find data that is NOT in a comment, I need to first find the data and then see if that occurrence is actually after a “–” string.
I could have done it with a single statement:
select * from dba_source where upper(text) like ‘%HELP%’ and upper(text) not like ‘%–%HELP%’
However, I personally don’t like expressions like that as I don’t always trust the query optimizer to handle that efficiently, especially when the number of occurrences of the target string are relatively few. Instead, I prefer to have the main body of the query find each occurrence, and then, when an occurrence is found, use a subquery to see if that particular record has the preceding “–”.
To do this, I need to join the main table reference to the same table again. But then how do I tell the database engine which table reference is which? The answer is to give each table reference a unique alias and then use the alias for the reference, ergo:
select * from dba_source ds1
where uppert(text) like ‘%HELP%’
and not exists (select 1 from dba_source ds2
where ds2.name = ds1.name and ds2.line = ds1.line and upper(ds2.text) like ‘%–%HELP%’)
This will only execute the subquery when the first part finds a candidate line, and it will be pretty efficient by querying against the name and line and then only applying the Upper function on the text of that particular line.
One other item of interest…
Note the use of “Select 1 from …” in the subquery. Many people code these as “Select * from …”, but since all I am looking for is the existence of a record, the syntax “select <literal> from …” is much more efficient than actually retrieving any data from the record. Regardless of how the query optimizer decides to execute the rest of the expression, when a record exists, the engine only has to return the literal “1″ – it doesn’t need to actually go out and touch the physical data record.
The dba_source works for everything except tables and views. Any idea of what i could use to search for the comments in the others?
DBA_TAB_COMMENTS and DBA_COL_COMMENTS.
Also look in DBA_VIEWS.text