Posted by I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! 
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 . 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.
Learning is not a spectator sport 
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
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.