Everybody knows that it takes a long time to insert a large number of records into a table. To improve the speed of insertion, you can use the PL/SWL loop FORALL.
In PL/SQL, use a cursor to fetch the rows from one table and insert them into the other table.
To reduce fetching time, do a BULK COLLECT INTO:
FETCHBULK COLLECT INTOLIMIT To insert the fetched row, use the FORALL loop.
FORALL INDX IN .. SAVE EXCEPTIONS
You can also handle Exceptions by using the SAVE EXCEPTIONS option in the FORALL loop. Here, the FORALL loop acts as a bulk insert. When it encounters an exception, it just saves it and continues with the next record.
To view the exceptions that occurred during the insert, include the following statement after the FORALL loop:
EXCEPTIONWHEN bulk_errors THENFOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNTLOOPerrno := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;errmsg := SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);END LOOP;END;
Placing the keyword SAVE EXCEPTIONS in the FORALL loop stores all the encountered exceptions in BULK_EXCEPTIONS. Thus, the FORALL loop can be used for Bulk Insert and even Exceptions can be handled in that.
NOTE: The keyword SAVE EXCEPTIONS in the FORALL loop works only in Oracle9i.
About Our Editorial Process
At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.
See our full editorial policy.
About Our Journalist
Charlie Frank
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience. View Author Social security policy changes impact retiree benefits
Rashan Dixon May 2, 2024 5:43 PM Financial anxiety rises among US adults, says AARP study
Noah Nguyen May 2, 2024 5:01 PM Game Pass refreshes library with exciting additions
Johannah Lopez May 2, 2024 3:25 PM Google’s pixel 8 pro adopts minimalistic camera finish
Johannah Lopez May 2, 2024 1:48 PM Apple’s new Beats Solo Buds offer affordable audio quality
Rashan Dixon May 2, 2024 1:23 PM Double social security payments expected certain months
April Isaacs May 2, 2024 11:57 AM Market anticipates Federal Reserve’s rate decision
Rashan Dixon May 2, 2024 11:29 AM Bitcoin remains sturdy amid crypto market turbulence
Johannah Lopez May 2, 2024 8:09 AM Binance founder sentenced to prison, steps down as CEO
Noah Nguyen May 2, 2024 7:10 AM U.S. stock futures dip, challenging market optimism
Rashan Dixon May 1, 2024 5:20 PM Inadequate information delays necessary assistance
Johannah Lopez May 1, 2024 5:15 PM June’s social security payments released early
April Isaacs May 1, 2024 3:30 PM Financial instability grows among aging Americans
Johannah Lopez May 1, 2024 3:27 PM Google improves split-screen mode for Pixel devices
Cameron Wiggins May 1, 2024 11:52 AM Apple AirPods Pro now at lowest price on Amazon
April Isaacs May 1, 2024 11:44 AM HSBC’s CEO Noel Quinn set to depart
Rashan Dixon May 1, 2024 11:39 AM Leaked designs hint at unique iPhone 16 features
April Isaacs May 1, 2024 11:13 AM Yen’s value drops, stirs global market concerns
Johannah Lopez May 1, 2024 7:26 AM Boost your business empowers small enterprises
Rashan Dixon April 30, 2024 5:40 PM Oil prices drop amidst easing Middle East tensions
Rashan Dixon April 30, 2024 5:26 PM National Small Business Week celebrates resilience and innovation
April Isaacs April 30, 2024 5:16 PM How to Create and Deploy QR Codes Online: A Comprehensive Guide
Rashan Dixon April 30, 2024 5:04 PM Circle fintech firm announces major changes, resumes stablecoin use
Cameron Wiggins April 30, 2024 3:06 PM Beauty mogul Jackie Aina shares Forvr Mood entrepreneurial insights
Rashan Dixon April 30, 2024 1:28 PM From cart to cafes: Nitro Bar’s inspiring journey
Cameron Wiggins April 30, 2024 11:40 AM