Hi
I have written a stored procedure..But it is taking huge time for 1928 records. I need a commit stmt for every 100 rows.
and please let me know if any mistakes in my coding . .
CREATE OR REPLACE PROCEDURE SP_UPDATECONNECTIONREQ
AS
CURSOR PI_cur
IS
SELECT D.SERIALNUMBER serial_number,DT.PRODUCT_CLASS product_class,DT.OUI oui
FROM DEVICE D, PROVISIONINGINFO P,DEVICETYPE DT
WHERE D.PROVISIONINGINFO_ID=P.ID
and D.DEVICETYPE_ID = DT.ID
AND P.CONNECTIONREQUESTPASSWORD is null
AND d.deleted=0;
BEGIN
FOR rec IN PI_cur
LOOP
UPDATE PROVISIONINGINFO SET CONNECTIONREQUESTUSERNAME = CONCAT(CONCAT(CONCAT(rec.oui,'-'),CONCAT(rec.product_class,'-')),rec.serial_number),CONNECTIONREQUESTPASSWORD = CONCAT(CONCAT(CONCAT(rec.oui,'-'),CONCAT(rec.product_class,'-')),rec.serial_number);
DBMS_OUTPUT.PUT_LINE('Updated::'|| CONCAT(CONCAT(CONCAT(rec.oui,'-'),CONCAT(rec.product_class,'-')),rec.serial_number));
END LOOP;
COMMIT;
END;
regards
-K