Datatype conversion laziness … yet another reason

I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! Smile

There’s been many blog posts, videos, AskTOM questions etc over the years with numerous examples of how the optimizer gets confused, how the SQL engine gets confused…and even how developers will ultimately get confused if you do not spend that little tiny bit of extra effort in ensuring data type consistency.

I personally wish we had an init.ora parameter called (say) “response_to_data_type_conversion” = IMPLICIT (default) or ERROR, so that when we encountered a data type conversion we could choose to report it back to the developer as an error, or implicitly try to convert it as we currently do. Then we could have it set to “error” at least in non-production environments to get a handle on where we are not being diligent.

But anyway, enough ranting Smile. The reason for this blog post to give yet another example of how making assumptions about data type handling can lead to spurious errors.

Let’s assume we have a simple requirement – to collapse a result set into a single CSV result stored in a CLOB. Here is my first cut at the code



SQL> set serverout on
SQL> declare
  2    l_csv  clob := empty_clob();
  3  begin
  4   for i in (select *
  5             from dba_objects )
  6   loop
  7        l_csv := l_csv || i.owner||','||i.object_name||','||i.object_id;
  8    end loop;
  9    dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7
ORA-06512: at line 7

And it fails. To get an idea as to why and where it fails, I’ll add an exception handler to capture the state of the CLOB.



SQL> set serverout on
SQL> declare
  2    l_csv  clob := empty_clob();
  3  begin
  4   for i in (select *
  5             from dba_objects )
  6   loop
  7        l_csv := l_csv || i.owner||','||i.object_name||','||i.object_id;
  8    end loop;
  9    dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
 10  exception
 11    when others then
 12      dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
 13      raise;
 14  end;
 15  /
length=32776
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13
ORA-06512: at line 7
ORA-06512: at line 7

Now that number looks “interesting” in that it is right on the length limit for a VARCHAR2 in PL/SQL. But why would a VARCHAR2 come into play here? We are dealing with a CLOB and that should be allowed to get much larger. To further confuse things, look what happens when I perform functionally the same operation, but using an intermediate variable for each row fetched from the loop.



SQL> declare
  2    l_csv  clob := empty_clob();
  3    l_line varchar2(255);
  4  begin
  5   for i in (select *
  6             from dba_objects )
  7   loop
  8        l_line := i.owner||','||i.object_name||','||i.object_id;
  9        l_csv := l_csv || l_line;
 10    end loop;
 11    dbms_output.put_line('length ' || length(l_csv));
 12  end;
 13  /

PL/SQL procedure successfully completed.

And now it works! That seems extraordinary because the logic would appear to be identical.

The answer here is once again – not taking sufficient care with our data type conversions. The OBJECT_ID we are fetching is numeric. Because we are simply slamming that into a concatenation operator (||), we need to do some implicit data type conversion, and to achieve that, we need to do some casting into VARCHAR2. We are not privy to how the PL/SQL execution engine is performing the conversion, but the error suggests that all components of the expression (including the left hand side) are being casted to VARCHAR2 and hence our “clob” ultimately exceeds the 32k limit.

The resolution is simple – take control of the data type conversion as we should have done anyway:



SQL> set serverout on
SQL> declare
  2    l_csv  clob := empty_clob();
  3  begin
  4   for i in (select *
  5             from dba_objects )
  6   loop
  7        l_csv := l_csv || i.owner||','||i.object_name||','||to_char(i.object_id);
  8    end loop;
  9    dbms_output.put_line('length=' || dbms_lob.getlength(l_csv));
 10  end;
 11  /
length=3491433

PL/SQL procedure successfully completed.

The moral of the story remains the same. Consistent and explicit handling of data type conversions will give you more robust code.

2 Comments on “Datatype conversion laziness … yet another reason

  1. Hello Connor,

    While it is obviously beyond any discussion that we should always use explicit data type conversion,
    however, in this particular example the behavior is a little bit inconsistent.

    One question is: why would the left-hand side l_csv be converted to a VARCHAR2 for the first example,
    and not also for the second one, that worked without error ?

    Second, if one of the concatenated expressions is a CLOB, I would expect that ALL the concatenated
    expressions be converted to CLOB, and not to VARCHAR2.

    This would be consistent, for example, with similar behaviors in cases where a numeric overflow happens
    with all operands being PLS_INTEGER-s, but it is avoided when one operand is a NUMBER.

    The SQL documentation for the concatenation operation explicitly states that
    “If either argument is a CLOB, the result is a temporary CLOB.”.

    So, this seems to be indeed a “PL/SQL-specific idiosyncrasy”,
    as I tried the following SQL-version, and it only fails because of the LiveSQL (annoying) limitations:

    declare
    l_csv clob := empty_clob();
    begin
    for i in (select *
    from all_objects )
    loop
    — l_csv := l_csv || i.owner||’,’||i.object_name||’,’||i.object_id;
    select l_csv || i.owner||’,’||i.object_name||’,’||i.object_id
    into l_csv
    from dual;

    end loop;
    dbms_output.put_line(‘length=’ || dbms_lob.getlength(l_csv));
    exception
    when others then
    dbms_output.put_line(‘length=’ || dbms_lob.getlength(l_csv));
    dbms_output.put_line(sqlerrm);
    — raise;
    end;
    /
    length=445260
    ORA-00040: active time limit exceeded – call aborted

    And, even in PL/SQL, my test (like yours) only failed when l_csv was already effectively longer
    than the 32767 limit of a VARCHAR2 :

    declare
    l_csv clob := empty_clob();
    begin
    for i in (select *
    from all_objects )
    loop
    l_csv := l_csv || i.owner||’,’||i.object_name||’,’||i.object_id;
    end loop;
    dbms_output.put_line(‘length=’ || dbms_lob.getlength(l_csv));
    exception
    when others then
    dbms_output.put_line(‘length=’ || dbms_lob.getlength(l_csv));
    dbms_output.put_line(sqlerrm);
    — raise;
    end;
    /
    length=32793
    ORA-06502: PL/SQL: numeric or value error

    I think that a reasonable action from the side of the PL/SQL development team would be to make it
    consistent with SQL, for all the operations that they have in common.

    Cheers & Enjoy the OOW 2018 !

    Iudith Mentzel

  2. Hi Connor, I’m pretty sure that parameter would be useful!

    Another way would be to include the information in PL/Scope. I’ve found it very useful and powerful (in Oracle 12.2+ there are a lot of new features) and I believe it could be a good place to search for this kind of situations where a programmer may have forgotten to do the right explicit conversion.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: