| How to use Bulk Bind [message #36380] |
Thu, 22 November 2001 08:12  |
renjith
Messages: 5
Registered: November 2001
|
Junior Member |
|
|
Hello All,
How to use Buld bind..?. For example I have one table called emp, I want to select all records from emp and put into another dummy table called tempEmp. How it is possible using bulk bind?. Please give me an example.
Thanks in Advance,
Renjith
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: How to use Bulk Bind [message #37482 is a reply to message #36380] |
Mon, 11 February 2002 14:10  |
Hridaynath Gore
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
Attaching a sample of bulk bind
BEGIN
v_load_position NUMBER := 0;
v_total_records NUMBER := 0;
v_error_record_no NUMBER := 0;
v_source_cnt conversion_control_report.source_cnt%TYPE := 0;
v_migration_cnt conversion_control_report.migration_cnt%TYPE := 0;
v_error_cnt conversion_control_report.error_cnt%TYPE :=0;
TYPE v_person_id_tabtype IS TABLE OF people.person_id%TYPE;
v_person_id v_person_id_tabtype;
-- Selecting the details from PEOPLE of MJ to populate PEOPLE of MJ+.
-- Business email addr and the personal email addr are set to based on certain conditions in
-- the select statement itself.
-- Default next_availabity_date to sysdate.
CURSOR people_cur IS SELECT pepl.personid
FROM tfe.people pepl;
-- This function is used to load data in the table using bulk method
FUNCTION load_data_sf (a_load_position_inout IN OUT NUMBER) RETURN BOOLEAN IS
BEGIN
EXECUTE IMMEDIATE('SET TRANSACTION USE ROLLBACK SEGMENT conversion_rollback');
FORALL v_record_cnt IN a_load_position_inout..v_total_records
INSERT INTO people
(
person_id
)
VALUES
(
v_person_id(v_record_cnt)
);
COMMIT;
-- The migration count is incremented by the number of records inserted
v_migration_cnt := v_migration_cnt + (v_total_records - a_load_position_inout + 1);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
-- The load position variable is set to value from which the bulk insert
-- will start for the remaining set of records in the bulk
a_load_position_inout := a_load_position_inout + SQL%ROWCOUNT + 1;
v_error_record_no := v_error_record_no + SQL%ROWCOUNT + 1;
v_migration_cnt := v_migration_cnt + SQL%ROWCOUNT;
-- Logging the errors in conversion_error_log
DBMS_OUTPUT.PUT_LINE('Error For Person Id : ' || v_person_id(v_error_record_no));
-- Error count is incremented by one
v_error_cnt := v_error_cnt + 1;
COMMIT;
RETURN FALSE;
END;
BEGIN
OPEN people_cur;
-- Fetching every 10000 records in bulk from the cursor.
LOOP
FETCH people_cur BULK COLLECT INTO v_person_id
LIMIT 10000;
v_total_records := NVL(v_person_id.LAST,0);
IF (v_total_records = 0)
THEN
EXIT;
END IF;
-- Tracking the number of records read from source.
v_source_cnt := v_source_cnt + v_total_records;
v_load_position := 1;
LOOP
IF (load_data_sf(v_load_position) = TRUE)
THEN
v_error_record_no :=0;
EXIT;
END IF;
END LOOP;
EXIT WHEN people_cur%NOTFOUND;
END LOOP;
CLOSE people_cur;
COMMIT;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
