?
Solved

PL/SQL Frequency Count

Posted on 2001-08-08
1
Medium Priority
?
641 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 48

Accepted Solution

by:
schwertner earned 450 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Six Sigma Control Plans

741 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