Solved

PL/SQL Frequency Count

Posted on 2001-08-08
1
625 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
copyEndy  challenge 15 55
matchUp  challenge 9 71
strCopies  challenge 17 73
listing all functions in JavaScript 19 112
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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 fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

757 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

16 Experts available now in Live!

Get 1:1 Help Now