?
Solved

Need help with Oracle Stored Procedure

Posted on 2007-08-02
12
Medium Priority
?
340 Views
Last Modified: 2008-01-09
I am trying to find out the each field count in table where the field is Null.So below is the script I am using but when I excute  this procedure in SQL Plus its just saying
PL/SQL procedure successfully completed.
But its not displaying any results.

What do I need to change in the script to see the results and do you think my script is good enough to get the results I am looking for?


CREATE OR REPLACE PROCEDURE FieldCount AS
BEGIN
Declare
  v_cnt Number;
  v_global_fields Number:=0;
BEGIN
  DBMS_OUTPUT.ENABLE(50000);
  DBMS_OUTPUT.PUT_LINE('START');
    for r1 in (select table_name,column_name from all_tab_columns where owner='AMI' and table_name='PROVIDER') LOOP
     execute immediate
      'Begin select count(1) into :1 from '||r1.table_name||'
       where '||r1.column_name||' is null;end;'
     using in out v_cnt;
     v_global_fields:=v_global_fields+v_cnt;
     DBMS_OUTPUT.PUT_LINE('Filled_fields: '||to_char(v_global_fields));
  end LOOP;
  DBMS_OUTPUT.PUT_LINE('Filled_fields: '||to_char(v_global_fields));
END;
end FieldCount;
0
Comment
Question by:Issolinc
  • 5
  • 5
  • 2
12 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 19620883
From SQL*Plus you need to issue the command:

SET SERVEROUTPUT ON;

In earlier releases, you had to specify the total size of the output from DBMS_OUTPUT commands...

SET SERVEROUTPUT ON SIZE 10000;

However, in Oracle 10g Rel 2, you can also specify:

SET SERVEROUTPUT ON SIZE UNLIMITED;
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 19620888
Sorry, hit submit before finishing...

Do the SET SERVEROUTPUT command first, then call your procedure.

Ok.... now I'm finished. :)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19624566
joebednarz is correct.

I'm just curious why you have a PL/SQL block within the EXECUTE IMMEDIATE?  You should be able to just do the select without the begin/end.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Issolinc
ID: 19638878
Thanks! Now I can see the out put.
I used EXECUTE IMMEDIATE because of the loop statement..I am  in kind of learning phase of Oracle PLSQL..so I am not sure if I really need to use it or not

With the above Script. I am able to get the out put as below
Filled_fields: 0
But I know there are many fields with null values..is there any way where I can get the field_name followed by the Count of null values in that in field

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19639014
I wasn't questioning the use of 'execute immediate', ust the use of 'begin' and 'end' in the text.  You can also move the 'into' to the outside with execute immediate.

you can change:
execute immediate
      'Begin select count(1) into :1 from '||r1.table_name||'
       where '||r1.column_name||' is null;end;'
     using in out v_cnt;

to:
execute immediate
      'select count(1) from '||r1.table_name||'
       where '||r1.column_name||' is null;'
     into v_cnt;

>>is there any way where I can get the field_name followed by the Count of null values in that in field

I'm not following.  Can you provide us with a small test case?  The test case should include a table definition, sample data and expected results.


0
 

Author Comment

by:Issolinc
ID: 19639210
We are trying to have new Staging Database based on existing production Database which will be used just for reporting purpose . In this process we are trying to figure out how many fields in each table are actual used based on the data.

For example the Total number of rows in Provider table are 1000 and when I do count on individual fields where its equal to null I am getting  987 for Lang3,1000 for ProvCat,992 for Spec2 .So we want to eliminate these fields while we get this table and data  to our new database.
Table Name : Provider
Fields : Prov_nbr,control_nbr,Amount,Degree,Ethic,Firstname,Lastname,Lan1,Lang2,Lang3,
Provcat,Spec1,Spec2

I am trying to see if we can write a script which will give output as below when we pass the Table name to the procedure

Table name :Provider
count of number  of rows equal to null
Prov_nbr :0
control_nbr = 0
Amount = 0
Degree = 32
Ethic = 123
Firstname = 0
Lastname = 0
Lan1 = 0
Lang2 = 234
Lang3 = 987
Provcat = 1000
Spec1 = 13
Spec2 = 992

Based on this our final condition before copying any table is that each field should have atleast 1% of whole data
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19639334
I'm not sure I fully understand the reasoning behind the need but...

From what I can gather is that for a given table you want to know how many of the columns are null?

Since you didn't provide test data, here's the test case I came up with.
-----------------------------
drop table tab1;
create table tab1 (
      col1 char(1),
      col2 char(1),
      col3 char(1),
      col4 char(1),
      col5 char(1)
);

insert into tab1 values('a',null,null,'a',null);
insert into tab1 values('b','b',null,'b','b');
insert into tab1 values('c','c','c','c','c');
insert into tab1 values('d','d','d',null,'d');
commit;

create or replace procedure junk is
      myCount      number;
begin
      for i in (select column_name from all_tab_columns where table_name='TAB1') loop
            execute immediate ' select count(*) from tab1 where ' || i.column_name || ' is null' into myCount;
            dbms_output.put_line(i.column_name || ':' || myCount);
      end loop;
end;
/

show errors

exec junk;
0
 

Author Comment

by:Issolinc
ID: 19639516
i am getting this error

5/13     PL/SQL: Statement ignored
6/25     PLS-00306: wrong number or types of arguments in call to
         'PUT_LINE'
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19640095
Some versions have a problem with implicit number to character conversion.

change:
dbms_output.put_line(i.column_name || ':' || myCount);

to:
dbms_output.put_line(i.column_name || ':' || to_char(myCount));
0
 

Author Comment

by:Issolinc
ID: 19641117
Below code is working as expceted .

CREATE OR REPLACE PROCEDURE FieldCount AS
BEGIN
Declare
  v_cnt Number;
  v_global_fields Number:=0;
BEGIN
  DBMS_OUTPUT.ENABLE(50000);
  DBMS_OUTPUT.PUT_LINE('START');
    for r1 in (select table_name,column_name from all_tab_columns where owner='AMIOWN'
    and table_name='PROVIDER')LOOP
     execute immediate
      'select count(1) into :1 from '||r1.table_name||'
       where '||r1.column_name||' is null;'
     using in out v_cnt;
     v_global_fields:=v_global_fields+v_cnt;
     DBMS_OUTPUT.PUT_LINE(r1.column_name ||to_char(v_global_fields));
  end LOOP;
END;
end FieldCount;

But i  changed the where condition to get accurate results because some columns might be numeric and just have zeros in it
I am getting couple of syntax errors,
With " like "  i am cheking the charecter field values starting with a space
Can you please tell me what is with the syntax  near   like ''' %''' or  '||r1.column_name||' = 0

CREATE OR REPLACE PROCEDURE FieldCount AS
BEGIN
Declare
  v_cnt Number;
  v_global_fields Number:=0;
BEGIN
  DBMS_OUTPUT.ENABLE(50000);
  DBMS_OUTPUT.PUT_LINE('START');
    for r1 in (select table_name,column_name from all_tab_columns where owner='AMIOWN'
    and table_name='PROVIDER')LOOP
     execute immediate
      'select count(1) into :1 from '||r1.table_name||'
       where '||r1.column_name||' like ''' %''' or  '||r1.column_name||' = 0 ;'
     using in out v_cnt;
     v_global_fields:=v_global_fields+v_cnt;
     DBMS_OUTPUT.PUT_LINE(r1.column_name ||to_char(v_global_fields));
  end LOOP;
END;
end FieldCount;
/
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 19641232
not sure exactly what you are trying to do with the 'like' command but I will tell you that a 'NULL' sill NEVER match anything so you still need the 'is null' in there.

I would go a different route and just replace 0 with null leaving everything else the way I had it.
-----------------------------------------------
drop table tab1;
create table tab1 (
      col1 char(2),
      col2 char(1),
      col3 char(1),
      col4 char(1),
      col5 number
);

insert into tab1 values('a0',null,null,'a',null);
insert into tab1 values('00','b',null,'b',123);
insert into tab1 values('0c','c','c','c',1);
insert into tab1 values('d','d','d',null,00000);
commit;

create or replace procedure junk is
      myCount      number;
begin
      for i in (select column_name from all_tab_columns where table_name='TAB1') loop
            execute immediate ' select count(*) from tab1 where replace(' || i.column_name || ',''0'',null) is null' into myCount;
            dbms_output.put_line(i.column_name || ':' || myCount);
      end loop;
end;
/

show errors

exec junk;


0
 

Author Comment

by:Issolinc
ID: 19665969
Thanks a lot!!
Here is what worked for me with some changes

CREATE OR REPLACE PROCEDURE FieldCount
(p_tablename IN varchar2) AS
BEGIN
Declare
 v_cnt Number;
 T_sql varchar2(2000) ;
 T_cnt number(15);
BEGIN
DBMS_OUTPUT.ENABLE(50000);
execute immediate 'select count(*) from '||p_tablename into T_cnt;
dbms_output.put_line('Total Count of Records in Table :'||p_tablename||' is :'||T_cnt);
for r1 in (select table_name,column_name from all_tab_columns where owner='AMIOWN'
   and table_name= p_tablename)LOOP
      execute immediate
        'select count(*) from '||r1.table_name||'
        where '||r1.column_name||' like '' %'' or '||to_char(r1.column_name)||' = ''0''
        order by '||r1.column_name||' desc' into v_cnt;
      DBMS_OUTPUT.PUT_LINE(r1.column_name ||':'|| to_char(v_cnt));
   end LOOP;
 END;
 end FieldCount;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 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