Chuck Jolley, February 03, 2021 - 6:55 pm UTC
I'm sure it's been suggested before, but a setting to cause implicit conversions to fail at compile time would be a nice addition.
February 03, 2021 - 7:29 pm UTC
Yep, many people have asked for that!
plsql_warnings
Rajeshwaran, Jeyabal, February 04, 2021 - 11:46 am UTC
If this sql is part of PL/SQL code, then plsql compiler can produce warning during compilation for datatype conversions.
demo@ORA11G> create table t
2 nologging
3 as
4 select owner,object_type,to_char(object_id) object_id
5 from all_objects ;
Table created.
demo@ORA11G> create or replace procedure do_some_work
2 authid current_user
3 as
4 begin
5 for x in ( select * from t
6 where object_id = 55)
7 loop
8 null;
9 end loop;
10 end;
11 /
Procedure created.
demo@ORA11G> alter procedure do_some_work compile plsql_warnings='enable:all';
SP2-0805: Procedure altered with compilation warnings
demo@ORA11G> show err
Errors for PROCEDURE DO_SOME_WORK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/15 PLW-07204: conversion away from column type may result in
sub-optimal query plan
demo@ORA11G>
February 04, 2021 - 4:25 pm UTC
Good point
Chrishna0007@gmail.com, February 05, 2021 - 3:36 am UTC
Del_id is varchar2 and observer implicit is not because datatype conversion but due to col_id columns
In both table value is passed for table 1 col_id1 but in plan it shows value for colid2 when I has out table1.colid =table2.colid no internal conversion takes place
February 05, 2021 - 1:52 pm UTC
I'm unsure what you mean - please clarify.
And please share the DDL for your tables - or a simplified example that shows the same issue.
Chuck Jolley, February 05, 2021 - 12:42 pm UTC
"alter procedure do_some_work compile plsql_warnings='enable:all';"
I had no idea that existed.
Thanks!