NShaikh
asked on
PL/SQL Frequency Count
I have to write a program that does a frequency count on a table with 1300 columns.
It's suppose to find the COLUMN_NAME,DATATYPE,DATA_
e.g
--for all columns which are VARCHAR2 datatype:
a) substr and get count of the left most digit;
--for all columns with DATE datatype:
a) get count for all null values;
b) substr and get count for the right most digit of the day of the month where value is
not null;
--for all columns with NUMBER datatype:
a) If one digit numeric get the count of the right most value;
b) If multi numeric :
1) get count for all null values;
2) get count for the right most character where value is not null;
I am trying to use DBA_TAB_COLUMNS table to get the column_name,data_type,data
I need a way to somehow dynamically fetch the values of all the columns into a cursor on a FROM and TO date range,then do the selects on them but i don't know how. Please help me am very confused and this assignment is due soon.I really appreciate your help.
Here is a small sample of the logic.
**************************
declare
v_build_field varchar2(32000) := NULL;
v_build_field1 varchar2(32000) := NULL;
v_build_field2 varchar2(32000) := NULL;
v_first_rec varchar2(1) := 'Y';
cursor tab_col_cur is
select column_name,data_type,data
from dba_tab_columns
where table_name = 'EMP'
order by column_id;
v_tab_col_rec tab_col_cur%ROWTYPE;
BEGIN
IF tab_col_cur%ISOPEN THEN
CLOSE tab_col_cur;
END IF;
OPEN tab_col_cur;
v_first_rec := 'Y';
v_build_field := ' ';
v_build_field1 := ' ';
v_build_field2 := ' ';
LOOP
FETCH tab_col_cur INTO v_tab_col_rec;
IF tab_col_cur%NOTFOUND THEN
exit;
ELSIF tab_col_cur%FOUND THEN
IF v_tab_col_rec.data_type = 'VARCHAR2' then
if v_first_rec = 'Y' then
v_build_field := 'substr('||v_tab_col_rec.c
DBMS_OUTPUT.PUT_LINE('FIEL
v_first_rec := 'N';
else
v_build_field := RTRIM(v_build_field) || ',' ||'substr('||v_tab_col_rec
DBMS_OUTPUT.PUT_LINE('FIEL
end if;
end if;
END IF;
END LOOP;
CLOSE tab_col_cur;
DBMS_OUTPUT.PUT_LINE('FIEL
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.