Beginner's mistakes in PL/SQL - Part 2

In the last blog post we’ve setup an example with some interesting errors which beginners in Oracle PL/SQL tend to do.

In this post we’ll take a look at one of them and hint another one.

First, let us observe very strange behavior on list of indexes. If I’ll run following query:

SELECT LISTAGG(index_name, ',') WITHIN GROUP (
ORDER BY index_name) index_name
FROM my_table_indexes
WHERE object_id_table=69048
GROUP BY object_id_table;

I’ll get this result:

SYS_C004167,SYS_IL0000069048C00004$$,SYS_IL0000069048C00007$$,SYS_IL0000069048C00015$$,SYS_IL0000069048C00016$$,SYS_IL0000069048C00017$$,SYS_IL0000069048C00022$$,SYS_IL000 …

Now, I’ll put this declaration in our package specification, which will allow me to run it from “outside” :

FUNCTION get_indexes ( p_object_id_in my_data_tmp.object_id%TYPE ) RETURN VARCHAR2;

And I’ll try to get same result from our function as it definitely has to be same:

SET SERVEROUTPUT ON;
BEGIN
 dbms_output.put_line('idx name:' || data_to_ascii.get_indexes( 69048 ));
END;
/

PL/SQL procedure successfully completed.
idx name:

Well that didn’t work out. But how is it possible?

If we check the code, one particular part should cache out eye:

FUNCTION get_indexes ( p_object_id_in my_data_tmp.object_id%TYPE ) RETURN VARCHAR2
  IS
    v_text VARCHAR2(1024);
  BEGIN
    --
    -- Select and concat indexes
    --
    FOR l_sel IN (SELECT index_name FROM my_table_indexes WHERE object_id_table = p_object_id_in ORDER BY index_name)
    LOOP
      v_text := v_text || l_sel.index_name || ',';
    END LOOP;
    --
    RETURN( v_text );
    --
    -- Error handling
    --
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN('');
      WHEN OTHERS THEN
        RETURN('');
  END get_indexes;

Yes … WHEN OTHERS THEN. Basically what it says is following “Whatever something else goes wrong, just return empty string” and that’s what will Oracle do. Corrupted block? Return empty string. Buffer overflow? Return empty string.

You would be surprised how often I do see this. My advice is: Just don’t do it. You’ll get wrong results when something goes wrong and you’ll have NO IDEA why. You’ll spend days by reproducing error which might never happen again. If there is an error, let it fail and handle the error based on your business (log it, etc.).

So let’s see what went wrong. I’ll delete general exception handling and rerun the block:

ORA-06502: PL/SQL: numeric of value error: character string buffer too small
ORA-06512: at "DATA_TO_ASCII", line 35
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"

Ok. That’s much better. Now we see that there is a problem with size of our v_text variable. Apparently concatenation of index names for this table is longer than 1024 bytes. So what we will probably do is to increase it.

Now, I should point out that your data might differ so you’ll have to find our own object id, which makes same problem as mine.

First problem solved. Now let’s take a bite in next one, shall we? I really do not like this line:

SELECT COUNT(*) INTO v_num_records FROM my_data_tmp;

Do you like it? What change would you make?

Continue to Part 3.