By When I first started writing PL/SQL, I honestly thought I was doing great. My code worked, the data was moving, and nobody was complaining. But then I joined a team working on an enterprise application used by thousands of users and reality hit me pretty fast.
My code was slow. Sometimes embarrassingly slow.
Why many Oracle developers stop growing? The problem was not that I did not know PL/SQL. The problem was that I was using it the wrong way. I had a bunch of bad habits that nobody told me about as a junior developer and I only discovered them the hard way, through code reviews, production issues, and a lot of late nights trying to figure out why a simple update procedure was taking forever to finish.
So I am writing this article to save you from going through the same thing. These are the five most common PL/SQL mistakes I made early in my career and exactly how to fix each one.
Mistake 1: Processing Data Row by Row Inside a Loop
This was probably my biggest and most costly mistake. When I needed to update a large set of records, I would write a loop, fetch one row at a time, process it, and move to the next one. It felt logical. It is basically how you think when you come from a general programming background.
But in Oracle, this is called “slow-by-slow” processing and it is one of the worst things you can do to your database performance.
Here is why. Every time you execute a SQL statement inside a PL/SQL loop, the database has to switch between two engines: the PL/SQL engine and the SQL engine. If your loop runs 10,000 times, you get 10,000 of these context switches. That overhead alone can eat up most of your execution time before any real work even happens.
The wrong way:
BEGIN
FOR r IN (SELECT id, val FROM source_table) LOOP
UPDATE target_table SET value = r.val WHERE id = r.id;
END LOOP;
END;
Start with one set-based statement for this relational update:
-- Require a unique source_table.id and target_table.id.
MERGE INTO target_table t
USING (SELECT id, val FROM source_table) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.val;Define a unique source row for each target key before using this MERGE; duplicate source IDs need an explicit business rule, not an arbitrary winner. If procedural processing is actually needed, fetch bounded batches instead of loading the entire source into session memory:
DECLARE
CURSOR c_source IS SELECT id, val FROM source_table;
TYPE t_ids IS TABLE OF source_table.id%TYPE;
TYPE t_vals IS TABLE OF source_table.val%TYPE;
v_ids t_ids;
v_vals t_vals;
BEGIN
OPEN c_source;
LOOP
FETCH c_source BULK COLLECT INTO v_ids, v_vals LIMIT 500;
EXIT WHEN v_ids.COUNT = 0;
-- Apply any required procedural transformations to this batch here.
FORALL i IN 1..v_ids.COUNT
UPDATE target_table SET value = v_vals(i) WHERE id = v_ids(i);
END LOOP;
CLOSE c_source;
EXCEPTION
WHEN OTHERS THEN
IF c_source%ISOPEN THEN CLOSE c_source; END IF;
RAISE;
END;
/FORALL sends each batch of DML to the SQL engine with less PL/SQL-to-SQL overhead; it is not one atomic operation for an unlimited data set. The batch size is a starting point to measure, not a universal optimum. Neither example commits: the caller owns the transaction and error/rollback policy. These illustrative revisions were statically reviewed, not executed against your schema. See Oracle bulk SQL and bulk binding.
Mistake 2: Using WHEN OTHERS THEN NULL to Handle Exceptions
I used to think this was a clever way to prevent my application from crashing. If something goes wrong, just catch it silently and move on. What could go wrong, right?
Everything. Everything can go wrong.
When you write WHEN OTHERS THEN NULL, you hide failures from the caller. A failed statement may be rolled back while earlier successful work remains pending, and the application can mistakenly report success. Whether partial work is committed depends on transaction ownership; silent handling makes that much harder to diagnose.
I learned this lesson the hard way when a batch job was silently failing for three days and nobody noticed until a user reported missing records. Tracking that issue down with no error logs was a nightmare.
The wrong way:
EXCEPTION
WHEN OTHERS THEN
NULL; -- Error is gone forever
END;
The right way:
-- Exception-section excerpt, not a standalone block.
-- Declare v_err_code NUMBER and v_err_msg VARCHAR2(4000) above BEGIN.
-- Implement log_error_to_table (or use an installed logger).
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
log_error_to_table(v_err_code, v_err_msg, 'YOUR_PROC_NAME');
RAISE; -- Tell the calling application something went wrong
END;
For an unexpected error, capture SQLCODE, SQLERRM and the backtrace, then re-raise unless the caller contract deliberately handles it. The excerpt assumes a logger that preserves the original error and the chosen transaction policy. RAISE propagates an exception; it does not itself promise rollback of all earlier work. See Oracle exception handling and the full logger walkthrough.
Mistake 3: Building Dynamic SQL by Concatenating Variables
When I needed to build a dynamic query, I would just concatenate the variable directly into the SQL string. It worked perfectly in development. But this habit causes two serious problems in production.
The first problem is performance. Concatenating changing literal values often produces different SQL text and reduces cursor sharing, which can increase hard parsing. It does not force a fresh execution plan on every execution: existing cursors, cursor-sharing settings and invalidation all affect the outcome.
The second problem is security. Concatenating user input directly into SQL opens the door to SQL injection attacks. A malicious user can manipulate your query logic to expose or even delete your data.
The wrong way:
v_sql := 'SELECT name FROM emp WHERE id = ' || v_user_input;
EXECUTE IMMEDIATE v_sql INTO v_name;
The right way using bind variables:
v_sql := 'SELECT name FROM emp WHERE id = :1';
EXECUTE IMMEDIATE v_sql INTO v_name USING v_user_input;
That change makes values bind data rather than SQL text and supports cursor reuse. It does not guarantee one plan forever; invalidation and adaptive cursor sharing can produce new child cursors. Bind variables protect the value position shown here, not dynamic table or column names. Keep identifiers static or select them from a validated allowlist.
Mistake 4: Writing SELECT * Instead of Specific Columns
I know it feels convenient. Why type out 10 column names when you can just write SELECT * and get everything? But this habit has real costs that are easy to miss when you are working with small tables in development.
When you use SELECT *, Oracle fetches every column from the table even if you only need two of them. That means more I/O, more memory usage in the buffer cache, and slower queries. On tables with many columns or large data types like CLOBs, the performance hit becomes very noticeable.
Selecting unneeded columns can prevent an otherwise available index-only plan. It does not categorically prevent index use: Oracle can use an index plus rowid access, and a covering index may even contain all selected columns. Specify the required columns and inspect the actual plan. See Oracle index access paths.
The wrong way:
SELECT * INTO v_emp_record FROM employees WHERE id = 101;
The right way:
SELECT first_name, last_name, salary
INTO v_fname, v_lname, v_salary
FROM employees
WHERE id = 101;
It also makes your code easier to read and much less likely to break if someone adds or removes a column from the table later.
Mistake 5: Writing Custom Logic When Oracle Already Has a Built-in for It
Early in my career I spent days writing a custom string parsing function from scratch. It worked, but it was slow and hard to maintain. A senior colleague looked at it and asked why I did not just use REGEXP_SUBSTR. My entire custom function became two lines.
That moment taught me to check Oracle’s built-ins before writing a custom implementation. They can simplify code and are worth measuring against a hand-written alternative, but they are not automatically faster for every task. For simple string parsing, INSTR/SUBSTR may be a better fit than a regular expression; benchmark representative inputs.
Packages like DBMS_UTILITY, UTL_FILE, DBMS_SCHEDULER, and DBMS_LOB cover a massive range of tasks including string manipulation, file handling, job scheduling, and date calculations. The best thing you can do as a junior developer is spend time going through the Oracle documentation and AskTom before you write anything from scratch.
Before you start coding something, ask yourself: does Oracle already have this?
The Bigger Picture
Looking back, all five of these mistakes came from the same root cause. I was thinking like a general programmer instead of thinking like an Oracle developer. I was trying to solve database problems using logic that works fine in other languages but goes against how Oracle actually operates.
The shift that made the biggest difference for me was learning to work with the database engine instead of fighting against it. Oracle is not just a storage box. It is an intelligent, powerful engine and once you start writing code that takes advantage of how it works, everything gets faster, cleaner, and much easier to maintain.
For focused practice on these topics, use Oracle Dev Gym SQL and PL/SQL quizzes and review the explanations after each attempt.
If you work with Oracle APEX as well, I wrote about a related habit of keeping dev and prod environments clearly separated in this article: Oracle APEX Banner to Avoid Dev vs Prod Mistakes. Same idea, small habits protect you from big problems.
These five fixes are not complicated but applying them consistently will make a real difference in your code quality and your reputation as a developer.
What was the biggest PL/SQL mistake you made early in your career? Drop it in the comments below. I would love to know I was not the only one learning the hard way!
Hassan Raza
Oracle ACE Apprentice | SH Software Solution, Pakistan
7 Comments
Comments are closed.