Link to home
Start Free TrialLog in
Avatar of NShaikh
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_LENGTH,DATA_PRECISION of the Columns and get frequency counts.
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_length,data_precision.
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_length,data_precision
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.column_name||',1,10)' ;

DBMS_OUTPUT.PUT_LINE('FIELD = Y '|| V_BUILD_FIELD);

            v_first_rec := 'N';

           else

                 v_build_field := RTRIM(v_build_field) || ',' ||'substr('||v_tab_col_rec.column_name||'1,10)' ;

DBMS_OUTPUT.PUT_LINE('FIELD =N '|| V_BUILD_FIELD);

                   end if;
           end if;

           END IF;
               
         END LOOP;

      CLOSE tab_col_cur;

DBMS_OUTPUT.PUT_LINE('FIELD '|| V_BUILD_FIELD);
END;
/

ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial