Happy Friday folks. I think I have to use BULK COLLECT here but I am not sure of the usability and now getting to far. I am really going through two loops here -- one through a table and one through all the columns for that table and find out counts and displaying them. Here is my code.
Thanks in advance.
declare
cursor cur_tables is
select DISTINCT(table_name) table_name from table_dictionary where table_name = 'STUDENT_TB'
order by table_name;
cursor cur_columns(p_table_name in varchar2) is
select DISTINCT(column_name) column_name from table_dictionary
where table_name = p_table_name
and code is not null
order by column_name;
t_count number;
t_sql_string varchar2(1000);
t_column_name varchar2(50);
begin
FOR rec_tables in cur_tables LOOP
FOR rec_columns in cur_columns(rec_tables.table_name) LOOP
t_sql_string := 'select count(*), '||rec_columns.column_name||' from '||rec_tables.table_name||' where '||
rec_columns.column_name||' is not null group by '||rec_columns.column_name;
dbms_output.put_line('SQL String = '||t_sql_string);
execute immediate t_sql_string into t_column_name, t_count;
END LOOP;
END LOOP;
end;
Error Reported:
The error reported is:
Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 23
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested
Scripts for Tables:
create table table_dictionary( table_name varchar2(100),
column_name varchar2(100),
code varchar2(1)
);
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','HOSPITALIZED','1');
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','HOSPITALIZED','0');
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','ADMIT_ICU','1');
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','ADMIT_ICU','0');
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','DECEASED','1');
insert into table_dictionary (table_name,column_name,code) values ('PATIENT_TB','DECEASED','0');
create table patient_tb( patient_id varchar2(10),
hospitalized varchar2(1),
admit_icu varchar2(1),
deceased varchar2(1)
);
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1001','1','0','0');
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1002','1','0','0');
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1003','1','0','0');
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1004','1','1','0');
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1005','1','1','0');
insert into patient_tb (patient_id,hospitalized,admit_icu,deceased) values ('1006','1','1','1');
My output should be like this:
select count(*), ADMIT_ICU from PATIENT_TB where ADMIT_ICU is not null group by ADMIT_ICU;
1 3
0 3
select count(*), HOSPITALIZED from PATIENT_TB where HOSPITALIZED is not null group by HOSPITALIZED;
6 1
select count(*), DECEASED from PATIENT_TB where DECEASED is not null group by DECEASED;
1 1
5 0