oracle11g - How to search the database for some characters and get the total count -
i trying query database characters , count of each character. here query have getting occurrence of each character:
spool /tmp/special_char_count.xls set serveroutput on size 100000 set linesize 9999 set pagesize 50000 declare match_count integer; type bin_array table of varchar2(30) index binary_integer; spec_char_array bin_array; begin spec_char_array(1) := 'k'; spec_char_array(2) := 't'; in 1 .. spec_char_array.count loop t in (select owner,table_name, column_name, data_type dba_tab_columns owner 'scott' , data_type not in ('float', 'date', 'number', 'timestamp(6)') ) loop execute immediate 'select count(*) ' || t.owner || '.' || t.table_name || ' '||t.column_name||' ''%'||spec_char_array(i)||'%''' match_count ; if match_count > 0 dbms_output.put_line( t.table_name ||chr(9)||t.column_name||chr(9)||spec_char_array(i)||chr(9)||match_count); end if; end loop; end loop; end; / spool off
how can total count of characters above query? spooling excel file thinking can use sum function in spread sheet. when excel file linux windows, says file corrupted when try open , don't see sum function working. thinking of getting total count query. appreciated.
thanks.
Comments
Post a Comment