apps-infra

    Forum Stats

  • 3,874,062 Users
  • 2,266,667 Discussions
  • 7,911,709 Comments

Discussions

PL/SQL Compiler warning for implicit conversions

Pleiadian
Pleiadian Member Posts: 521 Silver Badge
edited Feb 27, 2020 5:57AM in Database Ideas - Ideas

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.PleiadianWilliam RobertsonamadisThorsten KettnerberxdominiquecomtePeterGsdstuber
9 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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.
    William Robertson
  • Pleiadian
    Pleiadian Member Posts: 521 Silver Badge

    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
    Thorsten Kettner Member Posts: 44 Blue Ribbon
    edited Jul 5, 2022 1:49PM

    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.

apps-infra