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
Solved

PL/SQL Frequency Count

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SUM 2 INTEGER ARRAYS INTO 1 10 108
Is it possible to programatically fill out a fillable ADOBE PDF form? 9 99
Turning python script into an applet 12 116
Java Loop 6 67
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
A short article about problems I had with the new location API and permissions in Marshmallow
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 …

828 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