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.