set serverout on FORMAT WRAPPED set lines 250 set verify off PROMPT PROMPT **************************************************************************** PROMPT * Enter an empty string (i.e. '') if you want to search all schemas. * PROMPT **************************************************************************** PROMPT ACCEPT SCHEMA PROMPT ' Schema to Search: ' PROMPT ACCEPT PATTERN PROMPT ' Pattern to match: ' declare -- -- Replacement variables: -- SCHEMA - allows you to set a single schema -- - if null, will search all schemas. -- - All Schemas may take a bit, and may -- - also blow out the dbms_ouput buffer -- - if you get a lot of hits. -- PATTERN - what you are looking for (case insensitive). -- -- TOAD USERS: strip off the sql*plus prompts before and -- after the script, and then replace the two -- VARIABLES below with TOAD's prefered :VARIABLE -- naming. Nice to have in your Named or Saved SQLs -- -- The fact that I am a devoted TOAD user is the reason why all the -- blank lines are commented. This allows this code to run as a single -- statement in the TOAD editor while still remaining some vestige -- of formatting for readability. -- -- STANDARD DISCLAIMER: -- This code written in a hurry by me, for my own personal -- use with all of the implied incompetence that that implies. -- Author takes no responsability for it whatsoever no matter how -- severe the side-effects. Worst case - printouts of this code -- may prove helpfull next time you are wrapping fish...... -- s_owner varchar2(30) := '&SCHEMA'; s_pattern varchar2(100) := '&PATTERN'; -- procname varchar2(4000); -- holder for line containing subproc name ctr number := 0; -- counter for hits -- -- Cursor that gets the matches in all_source -- originally I just used "nvl(:owner,owner) -- but upgraded to the decode for some extra -- saftey when I'm bashing around with the variable -- replacement dialog box in TOAD. -- cursor sourceMatch is select OWNER, NAME, TYPE, LINE from all_source where owner = decode(ltrim(rtrim(s_owner)), null, owner, '' , owner, upper(s_owner) ) and upper(text) like upper('%'||s_pattern||'%'); -- -- Cursor to try and get the package subprogram name when applicable. -- cursor subProc ( sOwner all_source.owner%type, sName all_source.name%type, sType all_source.type%type, nLine all_source.line%type) is select ltrim(text) from all_source where name = sName and owner = sOwner and type = sType and line <= nLine and ( upper(ltrim(text)) like 'FUNCTION%' or upper(ltrim(text)) like 'PROCEDURE%') order by line desc; -- -- -- Get the surrounding source for a hit to show it's context. -- The line with the hit is highlighted with a "==> " -- -- It gets at least the five before and the five after, but -- will extend further before to the first found semicolon to -- try and ensure that at least the whole statement that the -- match was found in is diplayed. -- cursor sourceDetails (sOwner all_source.owner%type, sName all_source.name%type, sType all_source.type%type, nLine all_source.line%type) is select rpad(to_char(line),5)||decode(line,nline,'==> '||text, ' '||text) text from all_source where name = sName and owner = sOwner and type = sType and line >= (select least(nLine - 5, max(line) +1) from all_source where name = sName and owner = sOwner and type = sType and line < nLine and text like ('%;%') ) and line <= nLine + 5 order by line; -- -- cursor to get active trigger bodies for parsing. -- cursor getTrigs is select distinct owner, trigger_name from all_triggers where status = 'ENABLED' -- I don't check code not in use (you may want to) and owner = decode(rtrim(s_owner), null, owner, '' , owner, upper(s_owner) ) order by owner, trigger_name; -- -- cursor to get view definitions for parsing. -- cursor getViews is select distinct owner, view_name from all_views where owner = decode(rtrim(s_owner), null, owner, '' , owner, upper(s_owner) ) order by owner, view_name; -- -- DBMS_OUTPUT procedure. -- procedure printLine (ntext varchar2 default null) is -- Procedure to break long lines in semi-intelligent -- places for chunks that dbms_output can handle. -- cstart number := 1; -- start index of current chunk cend number := 0; -- end index of current chunk multi number := 0; -- 'on subsequent line chunk' indicator currentline varchar2(300); -- this chunk to print -- function breakpoint(intext varchar2, brkchar varchar2, startpoint integer) return integer is dummy number; begin -- This is really just an instr() wrapper that returns the -- end of the line instead of zero if a match is not found. -- -- Don't let this funtion lose out in the real world without -- adding some null checking in here for brkchar and intext. dummy := instr(intext,brkchar,startpoint); if dummy = 0 then dummy := length(intext); else dummy := dummy + length(brkchar) - 1; end if; return dummy; end; -- begin if ntext is null then -- this'll force a blank line even if you forgot to FORMAT WRAPPED sys.dbms_output.put_line(chr(0)); else while cend <= length(ntext) loop -- Is the remaining text small enough to print in entirety? if length(ntext) - cstart <= 245 then -- 245 is my max linesize to account for "CONT==>" indicator -- cend := length(ntext)+1; else -- if too big then look for a good place to break the line -- instr returns zero if it can't find a match which -- would bugger up the least() call, so we check and correct. -- By choosing to break at the shortest of these options, -- we make the arbitrary cStart+245 the least likely option. -- the +200 startpoint gives a 45 character window in which -- to find a better breakpoint option. -- -- feel free to add more break characters used in your code. -- I don't include '.' because I prefer not to break up a -- table.column or package.subproc string. cend := least(breakpoint(ntext,',',cstart + 200), breakpoint(ntext,' ',cstart + 200), breakpoint(ntext,'-',cstart + 200), breakpoint(ntext,'=',cstart + 200), breakpoint(ntext,'+',cstart + 200), breakpoint(ntext,')',cstart + 200), breakpoint(ntext,'||',cstart + 200), cstart + 245); end if; -- so we pull out our chunk to print. currentline := substr(ntext,cstart,cend - cstart + 1); -- -- If we are printing any but the first chunk from this string, -- we indicate subsequent lines with "Cont" if multi != 0 then currentline := ' CONT.==> '||currentline; end if; -- -- all_source stores the linefeeds for the plsql, so we strip them' -- off to avoid extra lines between each output line sys.dbms_output.put_line(rtrim(currentline,chr(10))); -- -- Now we move our chunk startpoint cstart := cend + 1; -- set multi=1 as any time after getting through the loop once -- we must be in a subsequent chunk. multi := 1; end loop; end if; end; -- -- Since Trigger bodies and View definitions are held in fields in -- system tables of type long, We need a different function to -- search and print out any found matches. Returned value is the -- number of matches found. -- function search_longs (column_name varchar2, table_name varchar2, where_clause varchar2, msg varchar2, pattern varchar2 ) return integer is cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rc NUMBER; long_piece VARCHAR2(256); -- Holds each extracted chunk long_len INTEGER := 0; -- Offset pointer in the column piece_len INTEGER := 0; -- Length of returned chunk match_ctr INTEGER := 0; -- Counter of pattern matchs temp_string VARCHAR2(100) := ''; -- Holder to avoid missing a -- match if chunking splits -- in the middle of a hit for -- our pattern. lines_ctr INTEGER := 0; -- count line breaks in the -- long to help find the code. sqlstatement varchar2(2000); begin -- if the pattern is longer than our chunk size, give up now! -- If someone wants to start concatenating chunks to allow for -- longer matches then go right ahead. -- if length(pattern) < 255 then -- parse the statement sqlstatement := 'select ' || column_name|| ' from ' || table_name|| ' where ' || where_clause; -- and fetch the row. -- DBMS_SQL.PARSE(cur1, sqlstatement, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1); rc := DBMS_SQL.EXECUTE(cur1); rc := DBMS_SQL.FETCH_ROWS(cur1); -- -- Now process the field -- LOOP -- DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 254, long_len, long_piece, piece_len); EXIT WHEN piece_len = 0; -- -- We see if our patern exists. if upper(temp_string||long_piece) like upper('%'||pattern||'%') then -- match_ctr := match_ctr + 1; printLine ('FOUND MATCH IN: '||msg); printLine (' In this code chunk approx. after line: ' || to_char(lines_ctr)||chr(10)||chr(10)); printLine ; printLine (temp_string||long_piece); printLine ; printLine ('--- End of Match --- '); printLine ; -- end if; long_len := long_len + piece_len; -- -- We try to track line numbers. Count of lines in this -- chunk are determined by replacing each newline with -- two characters and then seeing the change in length. -- lines_ctr := lines_ctr + length(replace(long_piece,chr(10),'xx')) - length(long_piece); -- and we grab the largest tail end of the chunk that might -- have contained the start of a match to our pattern -- for inclusion in the next loop so we don't miss any hits, temp_string := substr(long_piece, (length(s_pattern)-1) * -1 ); -- END LOOP; DBMS_SQL.CLOSE_CURSOR(cur1); end if; -- and return the number of hits we found in this code. return match_ctr; exception when others then -- parse failure? printLine ('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); printLine ('Error occured in search of: '||sqlstatement); printLine ( sqlerrm ); printLine ('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); END; --of search_longs() -- begin --MAIN PROGRAM -- -- a bad choice like 'the' could generate a ton of hits -- so enable a big buffer. 1 mill is the max allowed -- buffer on 8.0.5. Can go to 2 mill with 8i. -- sys.dbms_output.enable(buffer_size => 1000000); -- printLine('-------------- Searching Source ---------------'); printLine; -- for mrec in sourceMatch loop -- Increment counter -- ctr := ctr + 1; -- -- output header line of where we found the match -- printLine('Match in schema: '||mrec.owner||' '||mrec.type|| ' '||mrec.name||' at line: '||to_char(mrec.line)); -- if mrec.type = 'PACKAGE BODY' then -- Try to determine the sub program in the package an print it. -- open subProc(mrec.owner, mrec.name, mrec.type, mrec.line); fetch subProc into procname; if subProc%found then -- NOTE: Returned name may not be correct. Its a pretty basic query. -- For example a line starting with the word Procedure in a multi-line -- slash-star comment block would fool this. -- Odds are in our favor, but still, we'll just say 'probably''... -- printLine(' Probably in: ' || procname ); end if; -- close subProc; end if; printLine; -- -- now get and print out the details for drec in sourceDetails(mrec.owner, mrec.name, mrec.type, mrec.line) loop printLine(drec.text); end loop; printLine; printLine('--- End of Match --- '); printLine; end loop; -- -- printLine; printLine('-------------- Searching Triggers --------------- '); printLine; for trec in getTrigs loop ctr := ctr + search_longs('trigger_body', 'all_triggers', ' owner = '''||trec.owner||''' and trigger_name = '''||trec.trigger_name||'''', ' Trigger '|| trec.trigger_name||' in Schema '||trec.owner, s_pattern); end loop; printLine; printLine('-------------- Searching Views --------------- '); printLine; for vrec in getViews loop ctr := ctr + search_longs('text', 'all_views', ' owner = '''||vrec.owner||''' and view_name = '''||vrec.view_name||'''', ' View '|| vrec.view_name||' in Schema '||vrec.owner, s_pattern); end loop; printLine; printLine('-------------- Search Complete ---------------- '); printLine; printLine(' Found '||to_char(ctr)||' matches'); printLine; exception when others then printLine; printLine('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); printLine(' Error occured in search: '||sqlerrm); printLine; printLine(' Exiting.......'); printLine('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'); -- Check and clean up any open cursors. if getViews%isopen then close getViews; end if; if getTrigs%isopen then close getTrigs; end if; if sourceDetails%isopen then close sourceDetails; end if; if subProc%isopen then close subProc; end if; if sourceMatch%isopen then close sourceMatch; end if; end; / undef SCHEMA PATTERN REM I leave serverout on in case I want to rerun REM immediately for something else with a simple '/' REM You may want to turn it back off though... REM set serverout off