My question is how Oracle treats an INSERT transaction before issuing a COMMIT.
While I am doing an INSERT transaction, will oracle wait until I have inserted all my records within that procedure and then when I issue a COMMIT statement will the records be saved in a sequence for this transaction?
In the following code, the first insert that is made is the number of rows (metadata) and then the cursor loops and starts inserting the actual data.
Is there a possibility, in one transaction when I call this procedure, first my metadata record is inserted and then some other data (not related to this transaction) be inserted and then rest of my data. So that, the first record and the rest of the records from the loop are not inserted in a Sequence.
-- This code belongs to proecdure when ever a user clicks on insert
-- button from the front end form
DECLARE
rowcnt NUMBER;
CURSOR c_get_employ IS
SELECT EMP.EMPLOYER_ID, EMP.EMPLOYER_NAME, EMP.EMPLOYER_LOCATION
FROM EMP
WHERE EMP.EMPLOYER_COUNTRY = 'USA'
ORDER BY EMP.EMPLOYER_ID;
BEGIN
Select count(*)
INTO rowcnt
FROM EMP
WHERE EMP.EMPLOYER_COUNTRY = 'USA'
ORDER BY EMP.EMPLOYER_ID;
-- I want to insert the 'number of employee records' that will be inserted (metadata)
INSERT INTO EMP_OUTPUT
(EMPID, EMPNAME, EMPLOC, ECOUNT)
VALUES
(,,,rowcnt);
-- Then loop through the cursor and start inserting the data
FOR c_post_employ IN c_get_employ LOOP
INSERT INTO EMP_OUTPUT
(EMPID, EMPNAME, EMPLOC)
VALUES
(c_post_employ.EMPLOYER_ID,c_post_employ.EMPLOYER_NAME,c_post_employ.EMPLOYER_LOCATION);
END LOOP;
COMMIT;
END;
Use a single SQL statement if possible. It will have statement-level read consistency, and will be much faster.
insert into emp_output(empid, empname, emploc, ecount)
with employees as
(
select employer_id, employee_name, employer_location
from emp
where employer_country = 'USA'
order by employer_id
)
select null, null, null, count(*) from employees
union all
select employer_id, employee_name, employer_location, null from employees;