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;
The right way using BULK COLLECT and FORALL:
DECLARE
TYPE t_ids IS TABLE OF target_table.id%TYPE;
TYPE t_vals IS TABLE OF target_table.value%TYPE;
v_ids t_ids;
v_vals t_vals;
BEGIN
SELECT id, val BULK COLLECT INTO v_ids, v_vals FROM source_table;
FORALL i IN 1..v_ids.COUNT
UPDATE target_table SET value = v_vals(i) WHERE id = v_ids(i);
END;
Those 10,000 context switches become one single operation. The performance difference is massive, especially at scale. You can read more about this in the Oracle PL/SQL Optimization and Tuning documentation.
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 are telling Oracle to swallow every single error that happens and pretend nothing occurred. So if a transaction fails halfway through because of a constraint violation, your application keeps running as if everything was fine. The data is now corrupted and you have absolutely no record of what happened or why.
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
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;
Always log the error using SQLCODE and SQLERRM and then re-raise it. This way the application knows the operation failed and you have a trail to follow when debugging.
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. Every time the variable value changes, Oracle sees it as a brand new query and has to build a completely fresh execution plan. This is called hard parsing and it wastes CPU time on every single execution. At high traffic, this slows down the entire database for every user connected to it.
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 small change lets Oracle reuse the same execution plan every time, which saves CPU and protects your application from injection attacks at the same time.
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.
There is also another issue. SELECT * prevents Oracle from using covering indexes, which are indexes that can answer a query completely without touching the actual table. If you specify only the columns you need and those columns are part of an index, Oracle can skip the table entirely. That is significantly faster.
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 something important. Oracle has been around for decades and the built-in packages that come with it are written in low-level optimized code. Your custom PL/SQL logic will almost never be as efficient as what Oracle already provides out of the box.
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.
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
4 Comments
Comments are closed.