Solved

PL/SQL Frequency Count

Posted on 2001-08-08
1
629 Views
Last Modified: 2012-05-04

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;
/

0
Comment
Question by:NShaikh
1 Comment
 
LVL 47

Accepted Solution

by:
schwertner earned 150 total points
ID: 6367163
If you decide to use ref cursor you have  previously to declare the REF CURSOR within a package:

create or replace package result_pkg is
-- Type declarations
type genericCursor is ref cursor;
end resultset_pkg;


Once you compiled it, you will be able to return REF CURSORS in your procedures:

create or replace procedure test ( regout OUT resul_pkg.genericCursor)
is
begin
open regout for select * from user_objects;
end;



This is an example how to built package procedures using a REF Cursor:

-- Define Package Specification
PACKAGE emp_pkg IS
-- Defines the objects returned by the Ref Cursor
TYPE emprec IS RECORD ( empno emp.empno%TYPE,
ename emp.ename%TYPE );
-- Defines the Ref Cursor
TYPE empcur IS REF CURSOR RETURN emprec;
-- Defines the procedure used for querying records
PROCEDURE empquery_refcur ( block_data IN OUT empcur,
p_deptno IN NUMBER);
END;
-- Defines Package Body
PACKAGE BODY emp_pkg IS
PROCEDURE empquery_refcur ( block_data IN OUT empcur,
p_deptno IN NUMBER)
IS
BEGIN
OPEN block_data FOR
SELECT empno, ename
FROM emp
WHERE deptno = NVL( p_deptno, deptno )
ORDER BY empno;
END;
END;


 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now