Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Collections

Rishi Phanindra PallapothuMar 13 2024 — edited Mar 18 2024

In the below script instead of writing all the 120 columns manually in update statement, is there any alternate method using collections to get the update script dynamically so that we can use EXECUTE IMMEDIATE command to update table.

Note: The cursor query can return multiple rows.

DECLARE
CURSOR C1 IS SELECT BO_DTLS_ID, MARK_UP, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24, M25, M26, M27, M28, M29, M30, M31, M32, M33, M34, M35, M36, M37, M38, M39, M40, M41, M42, M43, M44, M45, M46, M47, M48, M49, M50, M51, M52, M53, M54, M55, M56, M57, M58, M59, M60, M61, M62, M63, M64, M65, M66, M67, M68, M69, M70, M71, M72, M73, M74, M75, M76, M77, M78, M79, M80, M81, M82, M83, M84, M85, M86, M87, M88, M89, M90, M91, M92, M93, M94, M95, M96, M97, M98, M99, M100, M101, M102, M103, M104, M105, M106, M107, M108, M109, M110, M111, M112, M113, M114, M115, M116, M117, M118, M119, M120
FROM PRM_BO_DTLS;
BEGIN
FOR I IN C1
LOOP
UPDATE PRM_BO_DTLS SET M1 = (M1 * 23.45)/(1-(I.MARKUP/100)), M2 = (M2 * 23.45)/(1-(I.MARKUP/100)), M3 = (M3 * 23.45)/(1-(I.MARKUP/100))..………AND SO ON UPTO M120
WHERE BO_DTLS_ID = I.BO_DTLS_ID;
END LOOP;
END;

Comments
Post Details
Added on Mar 13 2024
8 comments
157 views