Top 60 PL/SQL Interview Questions (2026)

PL/SQL is Oracle's procedural extension to SQL. Plain SQL says what data you want; PL/SQL adds the how, with variables, IF and CASE branching, loops, cursors, and structured error handling, so you can express logic the database runs itself. It compiles into stored objects like procedures, functions, packages, and triggers that live in the database.

Oracle adds it so you can keep data-heavy logic close to the data. Instead of pulling every row to an application and pushing changes back, a PL/SQL block runs inside the engine, cutting network trips and keeping business rules in one place.

A block has three sections. The optional declaration section, introduced by DECLARE, holds variables, cursors, and types. The mandatory execution section sits between BEGIN and END and contains the actual statements. The optional exception section, starting at EXCEPTION, catches errors raised anywhere in the execution part and handles them.

Only the execution section is required. The smallest legal block is BEGIN NULL; END; which does nothing.

A function must return a single value through a RETURN clause, and because it yields a value it can be called inside a SQL expression or a SELECT list. A procedure instead performs an action and returns nothing directly, though it can hand values back to the caller through OUT parameters. That return requirement is the core split.

Use a function when the goal is to compute and return a value; use a procedure when the goal is to do something, like updating rows or sending a batch.

PL/SQL uses all the SQL scalar types (NUMBER, VARCHAR2, DATE, CHAR, CLOB) and adds PL/SQL-only types like BOOLEAN, PLS_INTEGER, and BINARY_INTEGER. On top of scalars it has composite types: records that group fields, collections that hold many elements, and cursor types that point at result sets. That range is what lets one language cover both data and logic.

BOOLEAN and PLS_INTEGER matter in interviews because they're PL/SQL-only: you can't store a BOOLEAN in a table column, and PLS_INTEGER is faster than NUMBER for loop counters because it maps to native machine integers.

Declare variables in the DECLARE section as name followed by a type, with an optional starting value after := or the DEFAULT keyword. To make a constant, add the CONSTANT keyword and an initial value; a constant must be initialized at declaration and can't be reassigned anywhere in the block, so the compiler rejects any later write to it.

Add NOT NULL to force a variable to always hold a value; assigning NULL then raises an error at runtime.

%TYPE declares a variable using the data type of an existing table column or another variable, written as v_sal employees.salary%TYPE. The variable inherits whatever type and size that column has, so you never restate the type by hand and never risk it drifting out of sync with the schema.

The benefit is that your code stays correct when the column definition changes. Widen salary from NUMBER(6) to NUMBER(8) and every %TYPE variable follows automatically, with no edits.

%ROWTYPE declares a record variable whose fields match every column of a table or the whole select list of a cursor. One variable then holds an entire row, and you reach each field with dot notation like v_emp.salary or v_emp.first_name. It's the row-level counterpart to %TYPE, which anchors a single scalar to one column.

It saves declaring each column separately and stays in sync with the table. Fetching a cursor row straight into a %ROWTYPE record is the tidiest way to loop over query results.

A cursor is a pointer to the result set of a SQL query, letting PL/SQL process rows one at a time. There are two kinds. Implicit cursors are the ones Oracle opens automatically for every single SQL statement you run. Explicit cursors are ones you declare, open, fetch from, and close yourself, which gives you row-by-row control over a multi-row query.

You reach for an explicit cursor when you need to walk many rows and act on each. For a single-row lookup, SELECT INTO with its implicit cursor is simpler and shorter.

Oracle opens an implicit cursor automatically for every SQL statement you run, including SELECT INTO and every INSERT, UPDATE, and DELETE, then closes it for you. An explicit cursor is one you declare yourself and drive manually through OPEN, FETCH, and CLOSE, which gives you row-by-row control that an implicit cursor doesn't.

Use an explicit cursor when you need to loop over many rows with full control. Implicit cursors handle single-row and DML statements, and you read their outcome through attributes like SQL%ROWCOUNT and SQL%FOUND.

These four attributes report a cursor's state. %FOUND is TRUE if the last fetch returned a row; %NOTFOUND is its opposite and drives loop exits; %ROWCOUNT counts rows fetched so far; %ISOPEN tells you whether the cursor is currently open.

For explicit cursors you prefix them with the cursor name (c_emps%NOTFOUND). For the last implicit statement you prefix them with SQL (SQL%ROWCOUNT after an UPDATE tells you how many rows changed).

A cursor FOR loop opens a cursor, fetches each row into a loop record, runs the body once per row, and closes the cursor automatically when the rows run out, all in one compact statement. You skip the manual OPEN, the FETCH, the EXIT WHEN test, and the CLOSE that a hand-written cursor loop needs.

It's preferred because it's shorter, harder to get wrong (no forgotten CLOSE, no infinite loop from a missing EXIT), and reads clearly. The loop variable is an implicit %ROWTYPE record, so you reach columns with dot notation.

IF...THEN...ELSIF...ELSE...END IF branches on boolean conditions, evaluated top to bottom until one is TRUE, then runs that branch. CASE comes in two forms: a simple CASE that matches one expression against a list of values, and a searched CASE that evaluates independent boolean conditions the way an IF chain does but more compactly.

CASE also works as an expression that returns a value, which IF can't do. Use IF for control flow and CASE when you're assigning a value based on conditions.

Three main ones. The basic LOOP runs until an explicit EXIT or EXIT WHEN breaks out, so it always executes at least once. The WHILE loop tests a condition before each pass and may run zero times. The numeric FOR loop counts through a fixed low-to-high range. On top of those, the cursor FOR loop iterates the rows of a query.

Pick the basic LOOP when you must run at least once and exit mid-body, WHILE when the condition should be checked first, and the numeric FOR loop when you know the range up front.

SELECT INTO runs a query and stores its single-row result directly in one or more variables, or in a %ROWTYPE record. It's the standard way to fetch exactly one row without the ceremony of declaring, opening, and closing an explicit cursor, so it reads cleanly for primary-key lookups.

Two things go wrong often. If the query returns no rows it raises NO_DATA_FOUND; if it returns more than one row it raises TOO_MANY_ROWS. A reliable SELECT INTO either targets a primary key or handles both exceptions.

When a statement in the execution section raises an error, control immediately jumps to the EXCEPTION section, where WHEN clauses match the raised exception by name and run their handler code. The first matching handler wins, and WHEN OTHERS at the end catches anything not named above it, so nothing escapes unhandled if you want a catch-all.

Exceptions come in three flavors: predefined ones with names (NO_DATA_FOUND, ZERO_DIVIDE), non-predefined Oracle errors you bind to a name with PRAGMA EXCEPTION_INIT, and user-defined exceptions you declare and RAISE yourself.

The ones the question expects: NO_DATA_FOUND (a SELECT INTO found no row), TOO_MANY_ROWS (it found more than one), ZERO_DIVIDE (division by zero), INVALID_CURSOR (an illegal cursor operation like fetching from a closed cursor), VALUE_ERROR (a conversion or size error), and DUP_VAL_ON_INDEX (an insert violated a unique constraint).

These have names, so you catch them by name rather than checking error numbers. That readability is the point of predefined exceptions.

DBMS_OUTPUT is a built-in package that writes text to a per-session buffer, mainly for debugging and simple status messages while you develop a block. PUT_LINE appends one line to that buffer, and the client reads the buffer back after the block finishes, so the messages appear together at the end rather than streaming live.

The output isn't shown automatically. In SQL*Plus or SQLcl you run SET SERVEROUTPUT ON first; in tools like SQL Developer you enable the DBMS Output panel. Forget that step and your PUT_LINE calls seem to do nothing, which trips up beginners constantly.

Two styles. A double hyphen starts a single-line comment that runs to the end of the line, good for short notes beside a statement. A slash-star to star-slash pair wraps a block comment that can span many lines, good for a header explaining what a procedure does. Both are ignored by the compiler.

Block comments don't nest, so wrapping a section that already contains one closes early. Keep comments explaining why, not restating what the code obviously does.

COMMIT makes all changes since the last commit permanent and visible to other sessions. ROLLBACK undoes uncommitted changes back to the last commit or to a named savepoint. SAVEPOINT marks a point you can roll back to without discarding the whole transaction.

In PL/SQL you usually let the caller control the transaction rather than committing inside a procedure, because a commit buried in a subprogram can break a larger unit of work the caller is coordinating.

DELETE is DML: it removes rows one at a time, fires row triggers, respects a WHERE clause, and can be rolled back. TRUNCATE is DDL: it empties the whole table fast by deallocating storage, ignores triggers and WHERE, and commits implicitly so you can't roll it back. DROP is DDL too, but it removes the entire table, its data, and its structure.

In PL/SQL, DELETE runs as a normal statement, but TRUNCATE and DROP are DDL, so you run them through EXECUTE IMMEDIATE. Pick DELETE when you need conditions or rollback, TRUNCATE to clear a table quickly, DROP to remove the object.

A package is a schema object that groups related procedures, functions, types, cursors, and variables under one name, split into two parts. The specification declares the public interface that callers can see, and the body holds the implementation plus any private objects. You call the parts through the package name, like payroll.annual_salary.

Packages give you encapsulation (hide the body, expose the spec), a namespace, and a performance edge: the whole package loads into memory on first call, and you can change the body without invalidating code that depends only on the spec.

The specification is the public contract: it declares procedure and function headers, types, constants, and public variables that callers are allowed to see and use. The body contains the actual code for those subprograms plus any private declarations, like helper procedures or internal variables, that callers can't reach at all. The two are compiled separately.

A subprogram declared in the spec must be defined in the body. Anything defined only in the body is private to the package. You can compile a spec alone, which is what lets dependent code compile before the body exists.

A trigger is a named PL/SQL block that runs automatically when a defined event happens. The common type is a DML trigger firing on INSERT, UPDATE, or DELETE, but there are also DDL triggers (on CREATE, ALTER) and database event triggers (on LOGON, STARTUP).

DML triggers vary along two axes: timing (BEFORE or AFTER the statement) and granularity (statement-level, firing once, or row-level with FOR EACH ROW, firing per affected row). Row-level triggers get :OLD and :NEW to read the pre- and post-change values.

:OLD and :NEW are pseudo-records available inside a row-level trigger that hold the column values before and after the change. On INSERT, :OLD is null and :NEW is the incoming row; on DELETE, :NEW is null and :OLD is the row being removed; on UPDATE both are populated.

In a BEFORE trigger you can assign to :NEW to change what gets written, which is how you default or normalize values. In an AFTER trigger the row is already set, so :NEW is read-only.

A statement-level trigger fires once per triggering statement, no matter how many rows it touches, and has no access to :OLD or :NEW. A row-level trigger (FOR EACH ROW) fires once for every affected row and can read and, in BEFORE triggers, change :OLD and :NEW.

Use statement-level for logging that a change happened or enforcing table-wide rules; use row-level when the logic depends on each row's values.

A mutating table error (ORA-04091) happens when a row-level trigger tries to read or change the very table that fired it, while that table is only partway through the change. Oracle blocks it because the table is in an inconsistent, half-updated state, so any query against it could see wrong or nondeterministic results depending on row order.

The modern fix is a compound trigger, which lets you collect affected keys during the row phase and act on the table in the after-statement phase, when the table is stable. Older code used a package with an array of ids across two triggers. Often the cleanest fix is a constraint or a redesign that doesn't query the mutating table at all.

Yes. You declare a cursor with a parameter list, then pass values when you OPEN it (or when you name it in a cursor FOR loop). The same cursor definition runs with different inputs each time, so you avoid declaring several near-duplicate cursors that differ only in a WHERE value.

Parameterized cursors keep queries readable and let you reuse one cursor inside a loop that opens it repeatedly with changing arguments, which is cleaner than concatenating values into the query text.

FOR UPDATE in a cursor's query locks the selected rows so no other session can change them while you process the cursor. WHERE CURRENT OF cursor_name then targets an UPDATE or DELETE at exactly the row the cursor last fetched, without repeating the key in a WHERE clause.

Together they give a safe read-then-modify loop: lock the rows, walk them, and change each one in place. The lock releases on commit or rollback.

BULK COLLECT fetches many rows from a query into a collection in a single operation instead of one row per fetch. It cuts the number of round trips between the PL/SQL engine and the SQL engine, and those context switches are a big part of row-by-row cost.

For large result sets, fetch in batches with the LIMIT clause so you don't load millions of rows into memory at once. Unbounded BULK COLLECT on a huge table can exhaust process memory.

FORALL sends a batch of DML statements (INSERT, UPDATE, or DELETE) to the SQL engine in one call, driven by a collection. A normal FOR loop running the same DML switches to the SQL engine on every iteration; FORALL does it once for the whole batch.

It's not a loop that runs PL/SQL between statements: it's a single bulk DML instruction. Pair it with BULK COLLECT to read and write in batches, and use SAVE EXCEPTIONS to keep going when some rows fail.

Three. Associative arrays, also called index-by tables, are keyed by an integer or a string and live only in PL/SQL memory, never in a table. Nested tables are unbounded, can be sparse after deletes, and can be stored in a column. VARRAYs have a fixed maximum size, stay dense, keep element order, and can also be stored in a column.

Reach for an associative array as a fast in-memory lookup or for BULK COLLECT targets. Nested tables and VARRAYs matter when you need to persist a collection in a table column.

A record is a composite variable that groups several fields, each with its own type, under one name, so it models a single row: you reach fields as v_emp.first_name or v_emp.salary. You can define a record type yourself with TYPE ... IS RECORD, or derive one automatically from a table or cursor with %ROWTYPE.

A record holds one item made of several fields; a collection holds many items of the same type. Combine them (a nested table of records) to hold many rows in memory.

A REF CURSOR is a cursor variable: a pointer to a result set that you can assign, pass between subprograms, and return to a client. Unlike a static cursor tied to one query, a ref cursor can point at different queries at runtime.

Use it to return a query result to an application or another procedure, or when the exact query is decided at runtime. SYS_REFCURSOR is the built-in weakly-typed version, so you don't have to declare your own type.

IN passes a value into a subprogram and is read-only inside it, and it's the default mode when you write none. OUT sends a value back to the caller; it starts null inside the subprogram and the caller's variable receives whatever you assign to it. IN OUT does both, passing a value in and returning a possibly changed value out.

Use IN for inputs, OUT for results a procedure produces, and IN OUT when the subprogram both reads and updates a caller's variable, like a running total it adjusts.

RAISE_APPLICATION_ERROR raises a custom error with a number you choose in the range -20000 to -20999 and a message text of your own. The calling application receives it like any Oracle error, so business-rule failures surface cleanly instead of as raw ORA codes.

It's how you turn 'salary can't go negative' into a real, catchable exception with a readable message. Pair it with a user-defined exception when you want callers to catch that specific condition by name.

Declare an exception in the DECLARE section as name EXCEPTION, then trigger it with RAISE name where the condition fails, and catch it in a WHEN name THEN handler. The exception carries no number by default; it's a named flag for a business condition.

For a condition tied to a specific Oracle error number, bind it with PRAGMA EXCEPTION_INIT so a raw ORA error maps to a name you can catch.

Inside an exception handler, SQLCODE returns the numeric error code of the exception: 0 when there's no error, a negative ORA number for most errors, and +100 for no-data-found in some contexts. SQLERRM returns the matching human-readable error message text for that code, which is what you'd write to a log to explain what actually went wrong.

Log both when you catch WHEN OTHERS so a swallowed error still leaves a trace. Reraising after logging, or using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for the line number, keeps you from hiding the real problem.

Dynamic SQL is SQL built as a string and run at runtime, for cases where the statement isn't known at compile time: a table name from a variable, DDL like CREATE, or a query assembled from conditions. EXECUTE IMMEDIATE parses and runs that string.

Bind values with USING placeholders rather than concatenating them into the text. Binds are faster (the statement is reused) and safe against SQL injection, which raw concatenation is not.

An autonomous transaction is a child transaction that commits or rolls back on its own, fully independent of the main transaction that called it. You mark a subprogram with PRAGMA AUTONOMOUS_TRANSACTION, and from then on its own COMMIT persists even if the caller later rolls everything else back, because the two transactions no longer share a fate.

The classic use is logging: you want an audit or error record to survive even when the main work is rolled back. Overuse causes confusion, so reserve it for genuinely independent side effects.

A view is a stored query you select from as if it were a table: it packages a SELECT for reuse, hides join complexity, and can restrict which columns and rows a user sees, but it holds no procedural logic. A stored procedure holds the logic instead: loops, conditions, DML, and error handling that a view simply can't express.

Use a view to simplify or secure a read; use a procedure when you need to do something with steps, branching, or transaction control. They're complementary, not competing.

PL/SQL code runs in the PL/SQL engine, but every SQL statement is handed to the SQL engine to execute. Each hand-off is a context switch with real overhead. A loop that runs one SQL statement per row pays that cost on every iteration.

This is why bulk processing wins. BULK COLLECT reads many rows in one switch and FORALL writes many in one switch, so a job that did ten thousand switches drops to a handful. On large volumes the difference is minutes versus seconds.

Measure first. Use DBMS_PROFILER or the hierarchical DBMS_HPROF to see where time goes, and check EXPLAIN PLAN and SQL trace for the SQL inside the code, because the slow part is usually a query, not the procedural logic. Guessing at bottlenecks is usually wrong.

Then fix in order of impact: replace row-by-row loops with BULK COLLECT and FORALL, fix bad SQL and missing indexes, avoid unnecessary commits inside loops, and cut redundant SQL-to-PL/SQL switches. Rewriting logic as a single set-based SQL statement often beats any PL/SQL tuning.

A compound trigger is a single trigger with up to four timing sections in one body: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT, sharing a common state area. It replaces the old pattern of coordinating several separate triggers through a package.

Its main job is dodging the mutating table error: collect affected keys in the row sections into a collection, then act on the table in AFTER STATEMENT, when the table is stable. One object, shared state, no package juggling.

By default a stored subprogram runs with definer's rights: it uses the privileges and schema of the user who created it, so every caller accesses the same owner's objects. Marking it AUTHID CURRENT_USER makes it run with invoker's rights, using the caller's privileges and name resolution.

Definer's rights centralize access (callers don't need direct table grants). Invoker's rights suit code that should act on each caller's own schema objects, like a shared utility that operates on whatever tables the current user owns.

PRAGMA EXCEPTION_INIT binds a named exception you declare to a specific Oracle error number. After the binding, you can catch that raw ORA error by a readable name in a WHEN clause, instead of falling into WHEN OTHERS and testing SQLCODE by hand. The code indicates intent (WHEN child_record_exists) rather than as a magic negative number.

It's how you handle non-predefined Oracle errors gracefully. For example, bind ORA-02292 (child record found on delete) to a name like child_exists and catch it directly.

By default PL/SQL compiles to bytecode (MCODE) that the PL/SQL runtime interprets at execution time. Native compilation instead translates the code into machine code the CPU runs directly, controlled by the PLSQL_CODE_TYPE setting, which you set to INTERPRETED or NATIVE per session or for the whole database.

Native compilation speeds up procedural-heavy code (loops, arithmetic, computation) but does little for code dominated by SQL, since the SQL executes the same way either way. So it helps compute-bound packages more than data-bound ones.

Marking a function RESULT_CACHE stores its return value keyed by its arguments in a shared, server-side cache that all sessions can read. Later calls with the same arguments return the cached result without re-running the body, and Oracle automatically invalidates the affected entries whenever a table the function depends on changes, so callers never see a stale value.

It fits pure, read-mostly functions whose inputs repeat: reference lookups, expensive derivations over slow-changing tables. It's the wrong tool for functions with side effects or fast-changing data, where the invalidation churn cancels the benefit.

A pipelined function returns rows one at a time as they're produced, using PIPE ROW, instead of building the whole collection first and returning it at the end. Callers query it with the TABLE() operator as if it were a table, and rows stream to the consumer immediately.

It suits transforming or generating large row sets inside a query pipeline without buffering everything in memory, and it plays well with parallel query. The consumer starts getting rows before the function finishes.

For OUT and IN OUT parameters, PL/SQL normally passes by value: it copies the argument in, works on the copy, and copies it back on a clean return. NOCOPY asks the compiler to pass by reference instead, skipping the copies.

It matters for large collections or records, where copying is expensive. The catch: with NOCOPY, a partial change followed by an unhandled exception can leave the caller's variable half-modified, because there was no safe copy to discard. It's a hint, so the compiler may ignore it.

A bind variable is a placeholder whose value is supplied at execution, so the SQL text stays identical across calls. Oracle parses and plans a statement once and reuses that plan for every value, instead of hard-parsing a new statement for each literal.

That reuse cuts parsing overhead and library cache contention on high-frequency statements. Binds also stop SQL injection, because the value can never change the statement's structure. Concatenating literals into dynamic SQL breaks both benefits.

Normally a FORALL stops at the very first element that raises an error and rolls back the whole bulk statement. Adding the SAVE EXCEPTIONS clause changes that: FORALL keeps processing every remaining element, quietly collects each failure as it goes, and then raises a single ORA-24381 at the end to tell you some rows failed.

You read the collected errors from the SQL%BULK_EXCEPTIONS collection, which holds the index and error code of each failed element. That lets a batch load skip and log bad rows instead of aborting on the first one.

DETERMINISTIC tells Oracle a function always returns the same output for the same inputs, with no dependence on session state, time, or table data that changes. That promise lets Oracle cache and reuse results in some contexts, like function-based indexes and materialized views.

It's a contract you make, not something Oracle verifies. Mark a function DETERMINISTIC when it truly is pure; do it wrongly on a function that reads changing data and you get stale, incorrect results from cached values.

Variables declared at package level (in the spec or body, outside any subprogram) hold their values for the life of a session, not just one call. That persistent package state can act as a session cache, but it also carries surprises.

Two bite in production: changing a package's spec or body invalidates that state and raises ORA-04068 on the next call in existing sessions, and stale cached values persist across unrelated calls in the same session. Pragma SERIALLY_REUSABLE resets state per call for stateless designs.

Frequent commits inside a loop add overhead (each commit forces a redo log flush) and, worse, break restartability: if the job fails halfway, some rows are committed and some aren't, so a rerun double-processes or skips work. It can also trigger ORA-01555 snapshot-too-old on long cursors.

The better pattern is one commit per logical unit of work, often the whole batch, or periodic commits every N thousand rows only when a single transaction is genuinely too large. Bulk processing usually removes the temptation entirely.

The methods to know: COUNT gives the number of elements, FIRST and LAST give the lowest and highest indexes, NEXT and PRIOR handle from one index to the next, EXISTS checks whether an index is present, EXTEND grows a nested table or VARRAY, and DELETE and TRIM remove elements. They turn a raw collection into something you can safely iterate.

A collection is sparse when its indexes have gaps, usually after DELETE. That's why you loop with FIRST/NEXT rather than 1..COUNT on a possibly-sparse collection: a plain numeric loop hits a deleted index and raises NO_DATA_FOUND.

Prefer static SQL whenever the statement is known at compile time. Static SQL is checked at compile time (invalid columns fail early), records dependencies so recompilation is automatic, and is easier to read and tune. Dynamic SQL loses all of that.

Use dynamic SQL only when you genuinely can't write the statement statically: unknown table or column names, DDL, or optional filters assembled at runtime. Even then, bind values with USING rather than concatenating them, for plan reuse and injection safety.

Overloading means declaring several subprograms with the same name but different parameter lists, so callers use one name and Oracle picks the version that matches the arguments. It's allowed inside a package or a block, not for standalone stored procedures and functions at schema level.

The versions must differ in the number, type, or family of parameters; you can't overload on return type alone or on parameter names. Overloading built-in-style helpers (a print that accepts a NUMBER or a VARCHAR2) keeps the caller-facing API small.

The modern tool is DBMS_SCHEDULER, which creates named jobs that run a PL/SQL block, a stored procedure, or an external script on a calendar or interval schedule. It replaces the older, thinner DBMS_JOB package, adding run logging, job classes for prioritization, scheduling windows, and dependency chains that let one job trigger another when it finishes.

You define the job with an action, a start time, and a repeat_interval expressed in a calendaring syntax like FREQ=DAILY. The scheduler then runs it, records success or failure, and retries per your settings, so batch work runs without an external cron.

The core rule is simple: never concatenate user input into a SQL string. Pass data values through bind variables with the USING clause of EXECUTE IMMEDIATE, so the input can only ever be treated as a value, never as part of the statement's structure. That single habit closes the door on the most common injection path in dynamic PL/SQL.

When the dynamic part is an identifier (a table or column name that can't be bound), validate it against a known allow-list or pass it through DBMS_ASSERT, which checks that a string is a legal, safe identifier. Prefer static SQL wherever the statement is fixed, since static SQL can't be injected at all.

Clarify first (source, target, transform, failure tolerance, restartability), then design around bulk processing and set-based work. Read with BULK COLLECT and a LIMIT so memory stays bounded, transform in memory, and write with FORALL plus SAVE EXCEPTIONS so bad rows are logged rather than aborting the run.

Commit per batch, not per row, and track progress (last processed key) so a failed job restarts where it stopped. Push whatever you can into a single set-based SQL statement, because one INSERT SELECT usually beats any procedural loop. The structure, clarify, bulk read, transform, bulk write, restartable commits, is what the question scores.