INSERT and UPDATE a record using cursors in oracle
This is a highly inefficient way of doing it. You can use the merge statement and then there's no need for cursors, looping or (if you can do without) PL/SQL.
MERGE INTO studLoad l
USING ( SELECT studId, studName FROM student ) s
ON (l.studId = s.studId)
WHEN MATCHED THEN
UPDATE SET l.studName = s.studName
WHERE l.studName != s.studName
WHEN NOT MATCHED THEN
INSERT (l.studID, l.studName)
VALUES (s.studId, s.studName)
Make sure you commit, once completed, in order to be able to see this in the database.
To actually answer your question I would do it something like as follows. This has the benefit of doing most of the work in SQL and only updating based on the rowid, a unique address in the table.
It declares a type, which you place the data within in bulk, 10,000 rows at a time. Then processes these rows individually.
However, as I say this will not be as efficient as merge.
declare
cursor c_data is
select b.rowid as rid, a.studId, a.studName
from student a
left outer join studLoad b
on a.studId = b.studId
and a.studName <> b.studName
;
type t__data is table of c_data%rowtype index by binary_integer;
t_data t__data;
begin
open c_data;
loop
fetch c_data bulk collect into t_data limit 10000;
exit when t_data.count = 0;
for idx in t_data.first .. t_data.last loop
if t_data(idx).rid is null then
insert into studLoad (studId, studName)
values (t_data(idx).studId, t_data(idx).studName);
else
update studLoad
set studName = t_data(idx).studName
where rowid = t_data(idx).rid
;
end if;
end loop;
end loop;
close c_data;
end;
/
How to insert / update multiple records in oracle using cursor in stored procedure?
Error you got is due to OPEN P_NGTSHFTALL_CUR; - you don't open ref cursor here. Appart from that, is ref cursor really IN OUT? You aren't returning anything; should be just IN.
As of your code: merge might substitute separate INSERT and UPDATE (and you don't need IF-THEN-ELSE in that case.
Something like this:
CREATE OR REPLACE PROCEDURE INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS (
P_NGTSHFTALL_CUR IN SYS_REFCURSOR,
SPRESULT OUT VARCHAR2,
SPRESPONSECODE OUT VARCHAR2,
SPRESPONSEMESSAGE OUT VARCHAR2)
AS
id nightshiftallowances.id%TYPE;
requestnumber nightshiftallowances.requestnumber%TYPE;
shifttype nightshiftallowances.shifttype%TYPE;
startdate nightshiftallowances.startdate%TYPE;
enddate nightshiftallowances.enddate%TYPE;
shifttime nightshiftallowances.shifttime%TYPE;
BEGIN
LOOP
FETCH P_NGTSHFTALL_CUR
INTO id,
requestnumber,
shifttype,
startdate,
enddate,
shifttime;
EXIT WHEN P_NGTSHFTALL_CUR%NOTFOUND;
MERGE INTO NIGHTSHIFTALLOWANCES a
USING (SELECT id,
requestnumber,
shifttype,
startdate,
enddate,
shifttime
FROM DUAL) b
ON (a.id = b.id)
WHEN MATCHED
THEN
UPDATE SET a.requestnumber = b.requestnumber,
a.shifttype = b.shifttype,
a.startdate = b.startdate,
a.enddate = b.enddte,
a.shifttime = b.shifttime
WHEN NOT MATCHED
THEN
INSERT (REQUESTNUMBER,
SHIFTTYPE,
STARTDATE,
ENDDATE,
SHIFTTIME)
VALUES (b.REQUESTNUMBER,
b.SHIFTTYPE,
b.STARTDATE,
b.ENDDATE,
b.SHIFTTIME);
END LOOP;
COMMIT;
SPRESULT := 'OK';
SPRESPONSECODE := 'INSUPDNGTSHFTALL-001';
SPRESPONSEMESSAGE :=
'NIGHT SHIFT ALLOWANCE RECORD INSER/UPDATE IS SUCCESSFUL';
EXCEPTION
WHEN OTHERS
THEN
SPRESULT := 'NOK';
SPRESPONSECODE := 'INSUPDNGTSHFTALL-002';
SPRESPONSEMESSAGE := SQLERRM;
END INSERT_UPDATE_NIGHTSHIFALLOWANCES_RECORDS;
PL/SQL- How to insert into a table using all columns of a cursor
For performance you should put all your records into a collection, then you can use BULK INSERT... SAVE EXCEPTION like below:
DECLARE
TYPE t_tab IS TABLE OF ba%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
-- Fill the collection. ***
-- l_tab <--- ??
-- *** next question is to make l_tab fill with the result of your cursor
--
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO ba
VALUES l_tab(i);
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END;
/
Hope it helps
PL/SQL using cursors for rewriting values in table
As you're learning cursors and stuff, here's one option you might consider.
Sample table:
SQL> select * from testik;
ID ALC
---------- ---
1 Y
2 N --> should be updated to NO
3 YES
4 NO
5 N --> should be updated to NO
Cursor fetches only rows you're interested in; no sense in looping through the whole table if you don't want to do anything with rows whose alcohol column value is different from N.
Note that it is declared for update so that you could utilize update with the where current of clause; it'll update row you've just fetched.
I'm also displaying number of updated rows (you didn't ask for that, but no harm in doing it).
SQL> set serveroutput on;
SQL> declare
2 cursor c1 is select id, alcohol
3 from testik
4 where alcohol = 'N'
5 order by id
6 for update;
7 c1r c1%rowtype;
8 l_cnt number := 0;
9 begin
10 open c1;
11 loop
12 fetch c1 into c1r;
13 exit when c1%notfound;
14
15 update testik set
16 alcohol = 'NO'
17 where current of c1;
18 l_cnt := l_cnt + 1;
19 end loop;
20 close c1;
21 dbms_output.put_line('Updated ' || l_cnt || ' row(s)');
22 end;
23 /
Updated 2 row(s)
PL/SQL procedure successfully completed.
Result:
SQL> select * from testik;
ID ALC
---------- ---
1 Y
2 NO
3 YES
4 NO
5 NO
SQL>
Not able to insert the records in table using cursor in oracle
If it has to be a cursor loop, see another approach - cursor FOR loop. It is way easier to write and maintain as Oracle does most of things for you, i.e. you don't have to explicitly declare cursor and cursor variable(s), open it, fetch from it, pay attention when to exit the loop, close the cursor. All that dirty job is done for you.
All you have to worry about is that select you wrote actually returns some rows because I've seen comment you wrote that - although procedure was compiled, it didn't insert any rows. As we don't have your data, we can't help about it.
Here you go (presuming that tables and columns really exist):
create or replace procedure fiber_transm_valid_data as
begin
for cur_r in (select rj_span_id,
rj_maintenance_zone_name
from app_fttx.transmedia@sat
where length(rj_span_id) = 21
and inventory_status_code = 'IPL'
and regexp_like(rj_span_id, 'SP(N|Q|R|S).*_(BU|MP)$')
and rj_maintenance_zone_code in ('INMUNVMB01')
)
loop
if cur_r.rj_span_id > '0' then
insert into tbl_fiber_valid_trans_data
(span_id, maintenance_zone_name)
values (cur_r.rj_span_id, cur_r.rj_maintenance_zone_name);
end if;
end loop;
end fiber_transm_valid_data;
A few more notes: don't COMMIT in the loop as it causes problems (such as snapshot too old error). Consider moving it out of the procedure entirely and let the caller decide whether to commit or not.
Did you actually execute the procedure? You did create it but - if you never called it, that might be a reason why you don't see any rows being inserted. So:
begin
fiber_transm_valid_data ;
end;
/
Related Topics
How to Troubleshoot Ora-02049 and Lock Problems in General with Oracle
Sql Query to Find The Duplicate Records
How Much Real Storage Is Used with a Varchar(100) Declaration in MySQL
Return SQL Rows Where Field Contains Only Non-Alphanumeric Characters
Extract Time Part from Timestamp Column in Oracle
Oracle SQL: Understanding the Behavior of Sys_Guid() When Present in an Inline View
Oracle SQL - Max() with Null Values
Using SQL Query to Determine If a Table Exists
Sql Server Pivot on Multiple Columns
Sql Query for Insert in Grails
Update Statement Using Join and Group By
Create a Sqlite View Where a Row Depends on The Previous Row
Ora-06502: Pl/Sql: Numeric or Value Error: Character String Buffer Too Small