Need help with Oracle Stored Procedure

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;

Sorry, hit submit before finishing...

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

Ok.... now I'm finished. :)

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.

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

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.

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

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;

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'

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

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

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;