Hi Connor McDonald,

I need to write the procedure for inserting the records in to multiple tables, for example I am HAVING here 3 table,

CREATE TABLE SOURCE
(
SORT_CODE NUMBER,
FLAG CHAR(1)
);

INSERT INTO SOURCE VALUES(605096,5);

INSERT INTO SOURCE VALUES(605097,5);

INSERT INTO SOURCE VALUES(605098,5);

INSERT INTO SOURCE VALUES(605099,5);

INSERT INTO SOURCE VALUES(605100,5);

INSERT INTO SOURCE VALUES(605101,6);

INSERT INTO SOURCE VALUES(605102,6);

INSERT INTO SOURCE VALUES(605103,6);

INSERT INTO SOURCE VALUES(605104,6);

INSERT INTO SOURCE VALUES(605105,6);


SQL> SELECT * FROM SOURCE;

SORT_CODE F
---------- -
605096 5
605097 5
605098 5
605099 5
605100 5
605101 6
605102 6
605103 6
605104 6
605105 6

10 rows selected.


CREATE TABLE TARGET
(
SORT_CODE NUMBER,
TARGET_SORT_CODE NUMBER
);

Table created.


--INSERT 5 VALUES

INSERT INTO TARGET VALUES(605101,189873);

INSERT INTO TARGET VALUES(605102,189874);

INSERT INTO TARGET VALUES(605103,189875);

INSERT INTO TARGET VALUES(605104,189876);

INSERT INTO TARGET VALUES(605105,'');


SELECT * FROM TARGET;

SORT_CODE TARGET_SORT_CODE
---------- ----------------
605101 189873
605102 189874
605103 189875
605104 189876
605105


CREATE TABLE NEWID
(
SORT_CODE NUMBER,
ID_SCODE NUMBER
);

Table created.


--INSERT 2 VALUES


INSERT INTO TARGET VALUES(605103,189875);

INSERT INTO TARGET VALUES(605104,189876);


SELECT * FROM NEWID;

SORT_CODE ID_SCODE
---------- ----------------
605103 189875
605104 189876


--Creating intermediate tables with existing table's structure.
CREATE TABLE SOURCE_TEMP AS (SELECT * FROM SOURCE WHERE 1=2);

CREATE TABLE TARGET_TEMP AS (SELECT * FROM TARGET WHERE 1=2);

CREATE TABLE NEWID_TEMP AS (SELECT * FROM NEWID WHERE 1=2);

--MY Procedure for inserting the records
CREATE OR REPLACE PROCEDURE insert_sql
is
BEGIN

DELETE FROM SOURCE_TEMP;
INSERT INTO SOURCE_TEMP SELECT * FROM SOURCE; --insert query 1

DELETE FROM TARGET_TEMP;
INSERT INTO TARGET_TEMP SELECT * FROM TARGET; --insert query 2
--due to some network issue or table error this procedure GOT EXEPCTION here and above insert query 2(TARGET_TEMP) and below --insert query 3(NEWID_TEMP) is not inserted the values or not executed procedure is came out from this line.

DELETE FROM NEWID_TEMP;
INSERT INTO NEWID_TEMP SELECT * FROM NEWID; --insert query 3

EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ERROR');
END;

Point 1: The above procedure is executed only one insert query 1 SOURCE_TEMP is got the values.
Point 1: TARGET_TEMP and NEWID_TEMP is not inserted the values or not execute.

MyQues: can I able to re-execute this procedure with starting point of '--insert query 2' line?

becoz I am inserting the 100 tables records in new tables, if 50 tables are inserted the values during this time if i am getting any error in the proc execution, remaining 50 tables needs to insert the values, for I dont wish to delete the previous 50 tables inserted the values it will be the time consuming activity. any save point concepts is there for this type of issue in ORACLE (which is available in java and unix)

Thanks
Partheeban.J

if insert_source then
  delete ...
  insert ...
end if;

if target_source then
  delete ...
  insert ...
end if;