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.