Hi guys, Xev here...
I am running into some problems with this program I am having to run. I got the concept and the syntax down, but i need to rethink How I am going about this.
Here is my code and all you need is a oracle database with access to the all_tab_columns view to re-produce what I am doing.
Here is the code and I am trying to do a search on all these different alpha numeric formats. The syntax is correct but, it's not producing the desired result.
I need to have the counts at the bottom of the code (find_alpha) loop through and count each occurrence of where it finds all those alpha numeric patterns.
like for instance if there is a certain column in a certain table and the dbms_output.put_line just prints that out. It very simple, but it comes back and tells me that
cannot read, which means its throwing errors...
Please help me on this and make Xev
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON
exec DBMS_OUTPUT.ENABLE(1000000);
DECLARE
find_alpha integer;
begin
for t in (
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner not in ('SYS','SYSTEM','ORDSYS','ORDPLUGINS','SYSMAN','DBSNMP','SCOTT','APEX_030200','EXFSYS','OWBSYS','FLOWS_FILES'
,'OLAPSYS','CTXSYS','OUTLN','PUBLIC','MDSYS','OWBSYS_AUDIT','APPQOSSYS','WMSYS','XDB','ORDDATA'))
loop
begin
execute immediate ' with a as (select case when regexp_like(' || t.column_name ||
',''^[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$' ||
'|^[0-9]{9}$'||
'|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'||
'|^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$'||
'|^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$' ||
'|^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$' ||
'|^([[:alpha:]]|[[:alpha:]]{3})[0-9]{7}$' ||
'|^([[:Alpha:]]|[[:Alpha:]]{3})[0-9]{12}$' ||
'|^[[:alpha:]]{3}\s[0-9]{9}$' ||
'|^[[:alpha:]]{3}\s[0-9]{9}$' ||
'|^([[:alpha:]]|[[:alpha:]]{3})[0-9]{11}$' ||
'|^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$' ||
'|^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{13}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'')
then ''Match Found'' else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')
select count(*) from a where a.output=''Match Found'' '
INTO find_alpha ;
IF find_alpha > 0 THEN
DBMS_OUTPUT.put_line (
t.owner
|| '.'
|| t.table_name
|| ' '
|| t.column_name
|| ' '
|| find_alpha);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Cannot Read '
|| t.column_name
|| ' from '
|| t.owner
|| '.'
|| t.table_name);
END;
END LOOP;
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------