Datatype conversion laziness … yet another reason

connor_mc_d's picture

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.

To prevent automated spam submissions leave this field empty.