How to add commit to my procedure?
460740Apr 18 2006 — edited Apr 20 2006Hi, I am trying to figure out how to add commit in every 5000 iteration in my procedure. Can you please help me add this little piece of 'check' in my procedure?
Thanks!
CREATE OR REPLACE PROCEDURE insertData( p_tname IN VARCHAR2, p_records IN NUMBER )
authid CURRENT_USER
AS
l_insert LONG;
l_rows NUMBER DEFAULT 0;
BEGIN
l_insert := 'INSERT INTO ' || p_tname ||
' SELECT ';
FOR x IN ( SELECT data_type, data_length, column_name,
rpad( '9',data_precision,'9')/power(10,data_scale) maxval
FROM user_tab_columns
WHERE table_name = upper(p_tname)
ORDER BY column_id )
LOOP
IF ( x.column_name = 'ATMOSPHERICPARAMSID' )
THEN
l_insert := l_insert || 'SEQ_AtmosphericParams.nextval,';
ELSIF ( x.data_type IN ('NUMBER', 'FLOAT' ))
THEN
l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
ELSIF ( x.data_type = 'DATE' OR x.data_type LIKE 'TIMESTAMP%' )
THEN
l_insert := l_insert || 'sysdate-1000+dbms_random.value+dbms_random.value(1,2000),';
ELSE
l_insert := l_insert || 'dbms_random.string(''A'',' || x.data_length || '),';
END IF;
END LOOP;
l_insert := rtrim(l_insert,',') || ' from all_objects where rownum <= :n';
LOOP
EXECUTE IMMEDIATE l_insert USING p_records - l_rows;
l_rows := l_rows + sql%rowcount;
EXIT WHEN ( l_rows >= p_records );
END LOOP;
END;
/