PL/SQL Compiler warning for implicit conversions

Pleiadian

The suggestion is based on this thread: How does Oracle evaluate this expression?

There was a serious bug in our code due to implicit conversion. It would be really helpful if the PL/SQL compiler could give a warning whenever it detects an implicit conversion.

In our case it was:

if INTERVAL > NUMBER then

Which seems to be converted to varchar2 with unexpected results.

if VARCHAR2 > VARCHAR2

Is it possible to have the compiler detect these cases and generate a warning?

Regards,

Rop

Sven W.

Here is a test case to show the problem.

create or replace procedure test_conversion is  i interval day to second;  d number;begin  i := interval '1' day;  d := 0.75;  if i < d then    dbms_output.put_line('interval is less than number of days');  else      dbms_output.put_line('interval is equal or higher than number of days');  end if;end;/set serveroutput onexecute test_conversion;interval is less than number of daysPL/SQL procedure successfully completed.

The problem is that the code implies that here number is the number of days.
However there is no direct conversion from number to interval possible.
See https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4…

So I understand the idea as:
If both sides of a comparison have a different datatype, and if one side of the comparsion can not be converted directly into the datatype of the other side (assuming the already defined datatype preference rules) then show a plsql compiler warning.

I think this is a good idea.

The solution for above example of cause would be to convert the number into an interval first using the numtodsinterval function. Then do the comparison.

create or replace procedure test_conversion is  i interval day to second;  d number;begin  i := interval '1' day;  d := 0.75;  if i < numtodsinterval(d,'day') then    dbms_output.put_line('interval is less than number of days');  else      dbms_output.put_line('interval is equal or higher than number of days');  end if;end;/set serveroutput onexecute test_conversion;interval is equal or higher than number of daysPL/SQL procedure successfully completed.

Pleiadian

Here is a test case to show the problem.

create or replace procedure test_conversion is  i interval day to second;  d number;begin  i := interval '1' day;  d := 0.75;  if i < d then    dbms_output.put_line('interval is less than number of days');  else      dbms_output.put_line('interval is equal or higher than number of days');  end if;end;/set serveroutput onexecute test_conversion;interval is less than number of daysPL/SQL procedure successfully completed.

The problem is that the code implies that here number is the number of days.
However there is no direct conversion from number to interval possible.
See https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4…

So I understand the idea as:
If both sides of a comparison have a different datatype, and if one side of the comparsion can not be converted directly into the datatype of the other side (assuming the already defined datatype preference rules) then show a plsql compiler warning.

I think this is a good idea.

The solution for above example of cause would be to convert the number into an interval first using the numtodsinterval function. Then do the comparison.

create or replace procedure test_conversion is  i interval day to second;  d number;begin  i := interval '1' day;  d := 0.75;  if i < numtodsinterval(d,'day') then    dbms_output.put_line('interval is less than number of days');  else      dbms_output.put_line('interval is equal or higher than number of days');  end if;end;/set serveroutput onexecute test_conversion;interval is equal or higher than number of daysPL/SQL procedure successfully completed.

Thank you Sven for adding a clear description and an example!

Sven W. wrote:So I understand the idea as:
If both sides of a comparison have a different datatype, and if one side of the comparsion can not be converted directly into the datatype of the other side (assuming the already defined datatype preference rules) then show a plsql compiler warning. 

Yes, I think that is exactly when problems can arise. If both sides need to be converted in order to make the comparison.

Thorsten Kettner

In my opinion this doesn't have to be restricted to two-side conversions. When only one side of a comparision gets implicitly converted, this is worth a warning, too, I think. Usually I would compare numbers with numbers, strings with strings. When comparing a number with a string, is this likely to be on purpose? Wouldn't I explicitely cast one side then?

Example where we'll get a runtime error, because 'August' is not a number:

select *
bulk collect into v_orders
from orders
where extract(year from order_date) = 2022
and extract(month from order_date) = 'August';

And will this return the orders for 2022-07-05? :

select *
bulk collect into v_orders
from orders
where trunc(order_date) = '05/07/2022';

or this? :

select *
bulk collect into v_orders
from orders
where trunc(order_date) = '07/05/2022';

Well, both may fail with a runtime error, depending on the implicit cast from string to date (which may expect Jul instead of 07 for the month for instance). So better get a proper warning and change the unreliable code.