PROCEDURE TO GENERATE RECORDS IN ANY TABLE

Table having the columns of datatypes NUMBER,VARCHAR2,CHAR,DATE

DON'T KNOW THE NO: OF THE COLUMNS IN THE TABLE.

JUST PASS THE NAME OF THE TABLE AND THE NO: OF THE RECORDS TO GENERATE.


create or replace procedure populate(N number,T_name varchar2)
as
count_col number:=0;
col_id number;
str varchar2(100):=NULL;
d_t varchar2(10);
d_l number;
chr varchar(50);
num number;
dat date;
t_n varchar2(10);
str_1 varchar2(100);
d_p number;
d_s number;
begin
select table_name,count(*) into t_n,count_col from cols where table_name=upper(T_name) group by table_name;
for i in 1..N loop
col_id:=1;
str:=NULL;
for j in 1..count_col loop
select data_type,data_length,column_id,data_precision,data_scale into d_t,d_l,col_id,d_p,d_s from cols where table_name=upper(T_name) and column_id=col_id;
if (d_t = 'VARCHAR2' OR d_t='CHAR')
then
select dbms_random.string('U',d_l) into chr from dual;
str:=str','''''chr'''';
--dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'chr' & 'col_id);
elsif d_t ='NUMBER'
then
select substr(abs(dbms_random.random),1,(d_p-d_s)) into num from dual;
str:=str','num;
-- dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'to_char(num)' & 'col_id);
else
select TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454071, 2454071+300)), 'J') into dat from dual;
--str:=str','dat;
str:=str','''''dat'''';
--dbms_output.put_line(d_l);
--dbms_output.put_line(str' & 'to_char(dat)' & 'col_id);
end if;
col_id:=col_id+1;

end loop;
str_1:=substr(str,2);
--dbms_output.put_line(to_char(str_1));
-- execute immediate 'insert into 't_n' values''('str')';
EXECUTE IMMEDIATE 'INSERT INTO ' t_n ' VALUES ''('str_1')';
-- insert into t_n values (str);
end loop;
execute immediate 'commit';
end;
/