Beginner's mistakes in PL/SQL - Part 3

Welcome to the Beginner's mistakes in PL/SQL - Part 3. In the last Blog, I’ve posed a question about this particular piece of code:

SELECT COUNT(*) INTO v_num_records FROM my_data_tmp; 

Well the point here is that why should I select the temporary table one more time to count number of rows, if I had it directly at hand before in form of information from cursor - SQL%ROWCOUNT.

Another thing is that there is no application orchestration what so ever in our code. If we try to locate our batch job in v$session view, our only way is by sniffing around and checking active SQL (if it is not run under particular user). Big, big mistake. So we will add some application and stage identification:

dbms_application_info.set_module(module_name => 'data_to_ascii',
                                                    action_name => 'load_tmp_table');

We will add this kind of information into other functions in our package, so that when we observe the session, we can easily see in which state our export is.

Last but not least I do not like our time measurement technique. First problem is, that SYSDATE is basically macro for

SELECT SYSDATE FROM DUAL;

That means that each call is PL/SQL – SQL context switch, which is quite expensive. Secondary it is not very precise, ‘cos it can measure only with precision up to 1 second. PL/SQL has much better tools for time measurement and it’s pure PL/SQL. That means no context switching and precision up to 1/100 of second. That tool is

dbms_utility.get_time;

This function returns PLS_INTEGER. By capturing this number in two points in time, you can get number of 100th of seconds between those two points. It’s very fast and very lightweight. The only thing you should keep in mind is, that counter number can wrap around, so if you measure for “very long time” the difference you’re going to get is nonsense.

After all what was said, sample code from package should look like following:

CREATE OR REPLACE PACKAGE BODY data_to_ascii AS
  --
  -- Number of rows in tmp table
  vg_row_count PLS_INTEGER;
  --
  -- Loades data into the temporary table
  --
  PROCEDURE load_tmp_table
  IS
    v_start PLS_INTEGER := dbms_utility.get_time;
    v_end   PLS_INTEGER;
  BEGIN
    --
    -- Orchestration
    --
    dbms_application_info.set_module(module_name => 'data_to_ascii',
                                     action_name => 'load_tmp_table');
    --
    DELETE my_data_tmp;
    --
    INSERT INTO my_data_tmp
    SELECT tabs.object_id,
           tabs.table_name,
           tabs.partitioned,
           tabs.temporary
    FROM my_tables tabs;
    --
    -- Get number of lines processed which is number of rows in temporary table
    --
    vg_row_count := SQL%ROWCOUNT;
    --
    -- Measure
    --
    v_end := dbms_utility.get_time;
    dbms_output.put_line('Tmp loaded in ' || TO_CHAR((v_end - v_start) / 100, '999,999.99') || ' sec');
    --
  END load_tmp_table;
  ...

Continue to Part 4.

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.